Row-Level Security in Postgres, Explained

RLS is enabled per table:

alter table documents enable row level security;

From that moment, all normal access must be allowed by a policy. The PostgreSQL documentation states that if no policy exists, "a default-deny policy is used, meaning that no rows are visible or can be modified" (PostgreSQL docs). QueryPlane points out why this is good design: forgetting a policy fails closed instead of exposing data.

The same documentation lists who is not subject to policies:

• Superusers and roles with the BYPASSRLS attribute always bypass row security.
• Table owners normally bypass it too, unless the table uses FORCE ROW LEVEL SECURITY.

This is the first trap. AWS warns that if your application connects as the same role that owns the tables, "your security policies aren't in effect by default," and recommends connecting as a role other than the owner (AWS). QueryPlane's recommended setup uses two roles:

-- migrations run as app_owner, the application runs as app_user
grant select, insert, update, delete on all tables in schema public to app_user;
alter table documents force row level security;  -- owner is filtered too

Supabase adds a second layer that is easy to miss: grants and policies are separate checks. Grants decide whether a role may run an operation on the table at all, and policies decide which rows it applies to. A table "protected only by policies still hands anon an insert path if you never revoke the grant" (Supabase). Set both for every exposed table, in the same migration.

A policy has two expressions, and confusing them is the most common logic error. The CREATE POLICY reference states the rule in one sentence: "Existing table rows are checked against the expression specified in USING, while new rows that would be created via INSERT or UPDATE are checked against the expression specified in WITH CHECK" (CREATE POLICY reference).

• USING filters existing rows. It applies to SELECT, UPDATE and DELETE, deciding which rows a command can see or touch. Rows that fail it are skipped silently.
• WITH CHECK validates rows being written. It applies to INSERT and to the resulting row of an UPDATE. The reference notes that a row failing it raises an error rather than being skipped, and that an insert policy cannot have a USING expression at all.

Supabase shows both on an update policy, where USING decides which rows can be updated and WITH CHECK "stops a user from reassigning user_id to someone else" (Supabase):

create policy "users update own profile"
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id )        -- which rows can be changed
with check ( (select auth.uid()) = user_id );  -- what the result may look like

If you leave out WITH CHECK, PostgreSQL uses the USING expression for both, as both the PostgreSQL and Supabase documentation note. QueryPlane describes what goes wrong when write checks are missing: a user can update a row's owner to someone else, and the row silently leaves their view (QueryPlane).

Two more rules from the Supabase guide save debugging time. Write a separate policy for each of select, insert, update and delete, because a single policy for all commands hides which operation each rule was meant to cover. And an UPDATE needs a matching SELECT policy, or it will not work as expected.

When several policies apply to the same query, PostgreSQL combines them. The documentation explains that permissive policies, the default, are combined with OR, while restrictive policies are combined with AND (PostgreSQL docs).

QueryPlane flags the consequence: with several permissive policies for the same command, a row is visible if any one of them allows it, which is "the opposite of how most authorization systems work" (QueryPlane). Adding a broad permissive policy for one purpose can open rows you meant to keep closed.

Restrictive policies are the tool for rules that must always hold. The PostgreSQL documentation's example adds a restrictive policy so an administrator can only see records when connected locally, on top of the permissive policies that grant access (PostgreSQL docs). In a multi-tenant app, a common pattern is a restrictive tenant policy plus permissive role-based policies inside each tenant:

create policy tenant_isolation on invoices
  as restrictive
  using (tenant_id = current_setting('app.tenant_id')::uuid);

create policy members_read on invoices
  for select
  using (true);  -- any member of the tenant may read; tenant rule still applies

There are two ways to tell the database who is asking. One database role per customer works for raw database access, but Crunchy Data notes that creating a user per request "is a lot of overhead" and gets in the way of connection management (Crunchy Data). The usual alternative is a shared application role plus a session setting that holds the current tenant.

QueryPlane lays out the pattern (QueryPlane):

1. Add a tenant_id column to every tenant-owned table, NOT NULL, with a foreign key to the tenants table.
2. Enable RLS and write a policy comparing tenant_id to a session setting.
3. Set that setting at the start of each request, inside the request's transaction.

begin;
set local app.tenant_id = '6f1c...';  -- scoped to this transaction only
select * from invoices where status = 'open';
commit;

The word LOCAL matters. QueryPlane explains that plain SET persists for the life of the connection, so the next request that borrows the pooled connection inherits the previous tenant's context, and calls this "the single most common way teams accidentally break tenant isolation." AWS adds that session variables may be incompatible with server-side connection poolers and that you should test whether your pooler shares session state (AWS).

Hosted platforms often do this step for you. Supabase, for example, exposes the signed-in user's ID through auth.uid(), derived from the request's token, so policies can compare against it directly (Supabase).

Most RLS failures are not bad policies. They are paths around the policies. Check each of these:

Supabase warns that views "bypass RLS by default because they are usually created with the postgres user," and that "a view over a protected table hands out every row its policies were meant to withhold" (Supabase). It also notes that a security definer function created by a superuser runs with bypassrls rights. Those functions are useful, for example to read a membership table without triggering its own policies, but every one of them is a door around RLS and needs review.

Two subtler issues come from the PostgreSQL documentation itself. Referential integrity checks, such as unique constraints and foreign keys, always bypass row security, so care is needed to avoid "covert channel" leaks, where a user learns that a hidden row exists because an insert fails. And policies that look up other tables can have race conditions under concurrent updates, which the documentation discusses with a worked example (PostgreSQL docs). The same page notes the reverse risk for backups: a backup taken under RLS could silently omit rows, which is why the row_security setting can be turned off to make such a dump fail loudly instead.

Because the policy becomes part of the query, it follows the same performance rules as any filter.

Index the filter columns. Supabase says to add an index on every column your policies filter on, because an unindexed filter column "turns a read into a sequential scan." It warns that a composite primary key indexes only its leading column, so a membership table keyed on (team_id, user_id) needs a separate index for policies that filter on user_id (Supabase). QueryPlane recommends making tenant_id the first column of indexes on tenant-owned tables, such as (tenant_id, created_at desc) (QueryPlane).

Evaluate request functions once. Supabase recommends wrapping functions such as auth.uid() in a sub-select, (select auth.uid()), which makes the optimizer run them once per statement instead of once per row. QueryPlane makes the related point for custom helper functions: mark them STABLE so the planner can treat their result as constant within the query.

Keep policies about the row itself. The PostgreSQL documentation notes that policies which consider only the current values in the row are "the simplest and best-performing case." Lookups into other tables work but cost more and can introduce the race conditions described above (PostgreSQL docs).

Check the result with EXPLAIN ANALYZE on your heaviest queries after enabling RLS. The plan should still use the tenant-leading index.

The PostgreSQL documentation is direct: "As with any security settings, it's important to test and ensure that the system is behaving as expected" (PostgreSQL docs). Supabase goes further and says that until a test suite asserting allow and deny for select, insert, update and delete passes for each role, "you don't know whether the policies do what you intended" (Supabase).

A useful test matrix per table:

1. An unauthenticated or wrong-tenant session reads nothing and writes nothing.
2. The owner can read and change their own rows.
3. Another user in the same tenant can do exactly what the policies allow and no more.
4. A user cannot change a row's owner or tenant to someone else.
5. Every denied write is followed by a read proving the target row is unchanged.

Supabase's guide explains why the last step matters: a blocked update often matches zero rows and raises no error, so "matching no rows is not proof on its own." Teams building on Supabase, including Quanta Intelligence, rely on exactly this kind of per-table test to prove that each tier of user sees only its own data.