How to test Postgres row-level security as a specific user

To test PostgreSQL row level security properly, run your queries the way a real request does: as the application’s database role, with the same user and tenant context your app sets, inside a transaction. Testing as a superuser, the table owner or a service role proves nothing, because those roles skip the policies you’re trying to test.

Row-level security (RLS) is a Postgres feature that limits which rows a role can read or change, using policies attached to a table, as the PostgreSQL row security docs (opens in a new tab) describe. It’s popular in multi-tenant SaaS because a policy keeps protecting tenant data even when application code forgets a WHERE tenant_id = .... That only holds if the policies actually work, and a common testing mistake hides exactly the bugs you care about.

Why do RLS policies pass when you test as an admin?

They pass because admin roles aren’t subject to them. Postgres documents that superusers (opens in a new tab) and roles with the BYPASSRLS attribute always bypass row security. Table owners usually do too, unless the table is set to FORCE ROW LEVEL SECURITY.

That covers the roles most developers reach for first:

Role you test with	Subject to RLS?	Why
Superuser, such as a local postgres	No	Superusers always bypass row security
Role with BYPASSRLS	No	The attribute skips every policy
Table owner (often the migration role)	No, unless FORCE ROW LEVEL SECURITY is set	Owners bypass by default
Supabase service_role (secret key)	No	It has the bypassrls attribute
Your app’s runtime role, with user context	Yes	This is what you need to test

On Supabase, the Row Level Security guide (opens in a new tab) notes that the postgres role also has bypassrls, and a secret key only bypasses RLS when the request carries no user access token. Views are another trap: they bypass RLS by default unless you create them with security_invoker = true (Postgres 15 and later).

If you need to be sure you’re not being filtered, for example in a backup job, set row_security to off. It doesn’t bypass anything. Instead, Postgres raises an error whenever a policy would have filtered rows.

What does a minimal tenant policy look like?

Here’s an example schema for a tenant-scoped table. It’s a minimal illustration, not a drop-in migration. The application sets app.tenant_id for each request, and the policy compares every row against it:

-- Example only: one tenant-scoped table and an application role
create table projects (
  id        uuid primary key default gen_random_uuid(),
  tenant_id uuid not null,
  name      text not null
);

alter table projects enable row level security;

create role app_user nologin;
grant select, insert, update, delete on projects to app_user;

create policy projects_tenant_isolation on projects
  to app_user
  using      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

USING decides which existing rows are visible or modifiable. WITH CHECK decides which new or updated rows are allowed. When a USING expression is false or null, the row is silently hidden. When a WITH CHECK expression is false or null, the statement fails with an error. According to the CREATE POLICY reference (opens in a new tab), if RLS is enabled and no policy applies, Postgres denies everything by default.

Two details in that policy matter for testing:

• current_setting(name, true) returns null instead of raising an error when the setting doesn’t exist, per the Postgres settings functions (opens in a new tab) reference.
• After a transaction-local setting ends, the value can read back as an empty string rather than null. PostgREST’s transactions reference (opens in a new tab) documents this and suggests wrapping the call in nullif(..., ''). The nullif makes “no context” mean “no rows” instead of a cast error.

How do you test PostgreSQL row level security as a specific user?

Open a transaction, switch to the application role with SET LOCAL ROLE, set the request context transaction-locally, run your checks, then roll back:

-- Example test: act as a user in tenant 1111...
begin;
set local role app_user;
select set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);

select count(*) from projects;              -- only tenant 1111... rows

update projects set name = 'renamed'
where tenant_id = '22222222-2222-2222-2222-222222222222';
                                            -- UPDATE 0: rows are hidden, not an error

insert into projects (tenant_id, name)
values ('22222222-2222-2222-2222-222222222222', 'cross-tenant');
                                            -- ERROR: new row violates row-level security policy

rollback;

After SET ROLE, Postgres checks permissions as though that role had logged in. As the SET ROLE reference (opens in a new tab) explains, your test connection’s role needs the SET option on the target role (granted by membership), unless it’s a superuser. Per the Postgres SET reference (opens in a new tab), SET LOCAL lasts only until the transaction ends, and set_config(..., true) applies only to the current transaction. Rolling back leaves no role, setting or test data behind.

Keep it transaction-local in production code too. Postgres’s set_config documentation (opens in a new tab) notes that a setting made with set_config(..., false) lasts for the rest of the session. With a connection pool, that session gets reused by the next request.

How do you test Supabase row level security as a user?

Supabase works the same way, with its own roles and context. Requests run as anon or authenticated, and the auth.uid() helper returns the requesting user’s ID. Under the hood, the Supabase Auth function definition (opens in a new tab) shows that auth.uid() reads the sub claim from the request.jwt.claims setting, after first checking an older request.jwt.claim.sub setting. Supabase’s REST API (opens in a new tab) is built on PostgREST. PostgREST switches role with SET LOCAL ROLE for each request, per its authentication docs (opens in a new tab), and exposes the JWT claims through current_setting('request.jwt.claims', true), as its transaction settings reference (opens in a new tab) shows.

So you can reproduce a request in SQL. Here’s an example membership-based policy and a test, with made-up IDs:

-- Example policy: members read their tenants' projects
-- (memberships needs its own select policy for this subquery to see rows)
create policy "members read tenant projects" on projects
  for select to authenticated
  using (tenant_id in (
    select tenant_id from memberships where user_id = (select auth.uid())
  ));

-- Example test
begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa","role":"authenticated"}', true);
select auth.uid();                -- aaaaaaaa-...
select count(*) from projects;    -- only that member's tenants
rollback;

Supabase’s RLS guide recommends (opens in a new tab) one supabase/tests/<table>_rls.test.sql file per table, asserting allow and deny for each operation and for both anon and authenticated, run with supabase test db. Its testing overview (opens in a new tab) uses pgTAP inside begin and rollback, and its troubleshooting list starts with the two things above: set the role, and set the JWT claims.

One trap the same RLS guide calls out: don’t prove an allowed write with a check that only confirms “no error”. A write blocked by USING matches zero rows without raising anything. Add returning and assert the value came back.

What should a tenant isolation test matrix cover?

Cover each operation for three identities: a user in the right tenant, a user in another tenant, and a request with no context at all. Then add one check that your tests aren’t secretly running as a bypass role.

These are pass conditions to check in your own database, not results we’ve run there.

Case	Role and context	Statement	Pass condition
Own tenant read	App role, tenant A	select	Only tenant A rows
Own tenant write	App role, tenant A	insert ... returning	The row comes back
Other tenant read	App role, tenant A	select ... where tenant_id = B	Zero rows
Other tenant update or delete	App role, tenant A	update/delete tenant B rows	Zero rows affected; B’s rows unchanged
Cross-tenant insert	App role, tenant A	insert with tenant B	RLS violation error
Move a row out	App role, tenant A	update set tenant_id = B	RLS violation error
No context	App role, nothing set	select and insert	Zero rows; insert fails
No grant	A role with no table grant, such as Supabase anon after you revoke its default grants	select	Permission denied (42501)
Bypass sanity check	Owner or migration role	select	Sees all rows, proving the other tests didn’t run as this role

Run the “other tenant” cases with real IDs from both tenants, and confirm B’s rows afterwards from a separate, privileged session. A zero-row update only proves isolation if the rows still exist.

How does this help debug a customer permission issue?

It lets you reproduce what one customer’s request can see, rather than what your admin connection can see. When a customer reports missing records, set their role and their tenant or user context in a staging transaction and run the query their endpoint runs. If the rows disappear there, you’ve found the layer that filters them. The wider method for working through role, tenant, flag and entitlement inputs is in debugging customer permission issues.

The same rule applies to support access. If an agent opens a customer’s account through an impersonation session, the database work behind that session should run with the customer’s identity and tenant context, so RLS applies exactly as it does for the customer. A session that quietly uses a service role would show the agent rows the customer can’t see. It would also skip the tenant boundary that RLS is there to protect. Keep the agent’s identity separately for audit, and limit which customers each agent may open, as covered in assigned-customer impersonation.

Devora doesn’t manage database roles or RLS. Its integrations hand your backend verified context that names the customer (the subject) separately from the agent (the actor). Your app maps that subject to its normal user and tenant context, which is what lets your policies apply; Devora’s scope and security guide (opens in a new tab) covers the details. If you want approved, time-limited support sessions that keep the customer’s permissions in place, create a Devora account and try one staging session. Then run the matrix above against it.

Sources

• PostgreSQL: Row Security Policies (opens in a new tab)
• Supabase: Row Level Security (opens in a new tab)
• PostgreSQL: CREATE POLICY (opens in a new tab)
• PostgreSQL: System administration functions (current_setting, set_config) (opens in a new tab)
• PostgREST: Transactions (transaction-scoped settings) (opens in a new tab)
• PostgreSQL: SET ROLE (opens in a new tab)
• PostgreSQL: SET (opens in a new tab)
• Supabase Auth source: auth.uid() definition (opens in a new tab)
• Supabase: REST API (opens in a new tab)
• PostgREST: Authentication (opens in a new tab)
• Supabase: Testing overview (opens in a new tab)
• Devora: Scope and security (opens in a new tab)