How access control works

The rule

Access control is enforced by PostgreSQL. Not by React, not by the router, not by the server.

Every table in this application has row-level security enabled, which means PostgreSQL evaluates a policy for each row before deciding whether the caller may see it or change it. A caller who skips the application entirely, querying the API directly with their own token, is subject to exactly the same rules.

This has a direct consequence for how you work:

• A frontend check is never sufficient. Hiding a button changes what is convenient, not what is possible.
• A frontend check is never redundant either. Showing a button that will fail is a bad interface. Do both, and understand which one is load-bearing.
• A new query needs a policy. Without one it returns nothing, silently.

Reading a policy

A policy has two halves, and confusing them is the most common mistake in this area.

USING decides which existing rows the operation may touch. It answers: which rows can I see, update, or delete?

WITH CHECK decides what a row is allowed to become. It answers: is this new or modified row permitted to exist?

CREATE POLICY "Users update own profile" ON public.profiles
  FOR UPDATE TO authenticated
  USING      (user_id = auth.uid())   -- I may only modify my own row
  WITH CHECK (user_id = auth.uid());  -- and I may not reassign it to someone else

Omitting WITH CHECK on an update policy is a real vulnerability rather than a style issue. Without it, a caller can modify a row they legitimately control into something they should not be able to produce. This project has exactly one instance of that mistake in its storage policies, found during a security review.

auth.uid() is the verified identifier from the caller's token. It cannot be spoofed by the application; see Authentication.

The two roles

Every request arrives as one of two PostgreSQL roles:

• anon — nobody is signed in. auth.uid() is null.
• authenticated — somebody is signed in, and auth.uid() is their identifier.

Administrative levels are not separate roles. An administrator is an authenticated caller whose identifier has a sufficiently ranked row in user_roles, which policies check with has_role_at_least.

Grants and policies are separate layers

Row-level security decides which rows a caller may touch. Table grants decide whether the caller may perform the operation at all. Both apply, and the stricter one wins.

This matters more than it sounds, because TRUNCATE is not filtered by row-level security. A policy cannot stop it. If a role holds TRUNCATE on a table, the only thing preventing data loss is that nothing ever issues the command.

Supabase's defaults grant broad table access to anon and authenticated, which silently overrides what a migration asked for. Tables created before this was discovered still carry those broad grants, so for them, row-level security is the only layer. Newer tables revoke explicitly and enable FORCE ROW LEVEL SECURITY, which is the pattern to follow for anything new.

Functions that bypass policies

A function declared SECURITY DEFINER runs with the privileges of whoever defined it, not the caller. That is how has_role_at_least can read user_roles even though the caller cannot read other people's rows.

Two rules apply when writing one.

Set an empty search path and fully qualify every name. With a mutable search path, a caller who can create objects could shadow a table or operator referenced inside the function body and have it run with elevated privileges. This project revokes the ability to create objects in the public schema as a second line of defence, but the function should not depend on that.

Revoke execute from PUBLIC explicitly. Creating a function grants execute to everyone by default. This project has been caught by that more than once.

Where to look

• Table reference — the current rules, table by table.
• Reviewing database changes — how to change any of this without breaking production.