GRANTs versus RLS: two permission systems, one database

Supabase tables sit behind two permission systems at once: SQL grants and row-level security. They layer rather than replace each other, fail with different symptoms, and are routinely confused. This article maps the interaction with runnable tests for every claim.

Ask "who can read this table?" about a Supabase project and you've actually asked two questions wearing one coat. Postgres answers access through grants — table-level privileges like SELECT granted to roles — and through row security policies, which filter rows per command per role. Both systems must permit an action; either alone can block it; and they fail so differently that knowing which gate refused you is most of debugging.

Confusion between the layers is endemic because Supabase provisions sensible defaults: client roles arrive pre-granted on public-schema tables, so teams live entirely in policy-land and forget grants exist — until a revoke, a new role, or a fresh environment makes the forgotten layer bite. This article separates the two systems, shows their interaction with verified SQL, and gives you the diagnostic habit that tells you instantly which gate said no.

Everything below was executed against current Postgres during preparation; expected results are stated before each test, in the site's standard style. By the end you'll be able to read any access mystery — empty results, permission errors, tables that work in one environment and fail in another — as a question about one specific gate.

Two gates, checked in order

When a query arrives under a given role, Postgres consults two independent systems sequentially — and their verdicts compose multiplicatively: both must say yes for anything to happen. Neither can substitute for the other, which is why the order of checks below determines not just whether your query runs but what kind of failure you see when it doesn't:

1. Privilege check: does this role hold SELECT on this table?
 No -> ERROR: permission denied (42501 class)
 Yes -> continue
2. Row security: do policies admit each row?
 RLS disabled -> all rows pass
 RLS enabled -> rows failing USING are silently filtered

The critical asymmetry is in the failure shapes. A missing grant is loud — the query errors, naming the table and privilege. Missing or restrictive policies are quiet — empty results, no error, indistinguishable from absent data. Loud failures get noticed and fixed immediately; quiet ones can persist for months. That's why the interaction matters less than the symptom mapping: errors mean grants, empties mean policies, and every mystery in between is usually both layers interacting with assumptions nobody wrote down.

The interaction matrix

All four combinations, each verified during this article's preparation — this matrix is the article's centerpiece, and worth internalizing until you can reconstruct it from memory:

Grant	Policy situation	Result	Symptom
Yes	Permissive policy admits row	Row returned	Normal operation
Yes	Policies deny (or none exist)	Empty result	Silent; looks like missing data
No	Any policy state	Permission denied error	Loud; blocks feature visibly
No	RLS also disabled	Permission denied error	Grants were protecting you

Read row four twice, because it carries a surprise: a table with RLS disabled but grants revoked still refuses client reads. Teams discovering this sometimes conclude grants are redundant with RLS — the opposite lesson is correct. Default-deny grants on unexposed schemas are legitimate defense-in-depth; what makes Supabase's default posture work is that exposed-schema tables get client grants provisioned automatically, making RLS the single active filter in normal operation.

What each system is actually for

The two systems aren't redundant because they operate at different granularities for different purposes:

Dimension	Grants	Row security policies
Granularity	Whole table (or columns)	Individual rows
Expression	Privilege names (SELECT, INSERT)	Arbitrary boolean expressions
Failure shape	Error, every time	Silent filtering
Natural audience	Database administrators	Application developers modeling access
Change cadence	Rarely — structural	Often — follows product features

Grants answer "may this role touch this object at all" — a database-administration question, answered rarely, changed deliberately. Policies answer "which rows of this object may this caller see or change right now" — a product question, answered per query, evolved continuously. Supabase's design leans into exactly this division: provision broad grants once, then do all real access modeling through policies that developers can read and migrate like code.

The trouble starts when teams use one system to do the other's job. Modeling row access through grants is impossible — there's no WHERE clause. But modeling structural decisions through policies happens constantly: revoking table access via deny-all policies instead of explicit grants, or worse, leaving grants wide while assuming policies cover internal tables nobody wrote rules for yet. Each system left doing the other's work produces confusion that surfaces months later as either mysterious errors (grants) or silent exposure (policies).

The tests, runnable as printed

Create the fixture and prove each cell:

create table gate_demo (
 id int primary key,
 note text not null
);

insert into gate_demo values (1, 'visible'), (2, 'also visible');

alter table gate_demo enable row level security;

grant select on gate_demo to authenticated;

create policy "gate_demo_all"
 on gate_demo for select
 to authenticated
 using (true);

Cell one — both gates open: rows return.

begin;
set local role authenticated;
select count(*) as both_open from gate_demo;
rollback;

Expected as executed: 2.

Row three — grant removed, loud failure:

revoke select on gate_demo from authenticated;

begin;
set local role authenticated;
select count(*) from gate_demo;
rollback;

Expected: ERROR: permission denied for table gate_demo — an exception, not an empty set. Restore with grant select ... before continuing.

Row two — grant restored, policy tightened to deny:

drop policy "gate_demo_all" on gate_demo;
-- zero policies remain: default deny

begin;
set local role authenticated;
select count(*) as deny_all from gate_demo;
rollback;

Expected: 0, no error. Same table, same grant, opposite symptom class from the previous test — that contrast is the entire diagnostic skill.

The anonymous variant, since public surfaces deserve their own proof:

begin;
set local role anon;
select count(*) from gate_demo;
rollback;

Expected: 0 — anon holds no grant issues (default provisioning covers it) but no policy targets anon, so default deny filters everything. Swap any element — revoke the grant, or add an anon policy with using (true) — and watch which symptom appears. Running these five variations against your own tables takes minutes and produces a complete behavioral fingerprint of both gates.

One more interaction surprises people: writes need sequence rights too when tables use serial-style defaults. Supabase's default privileges cover sequences alongside tables, so PostgREST inserts just work — but hand-rolled roles in fresh environments can hit insert failures whose fix (grant usage on sequence) has nothing to do with either RLS or the obvious table grant. When an INSERT fails with permission denied mentioning a sequence name, this is why — and it is a grant problem, not a policy problem, which is exactly the distinction our direct-grants finding documents.

How Supabase composes the two by default

Understanding defaults explains why the platform feels policy-centric:

• New public-schema tables receive client-role grants automatically via default privileges — so grants are effectively always-passing unless you changed them.
• RLS is opt-in per table, so the only active gate on a fresh unprotected table is... neither: grants pass, RLS off, rows flow.
• Once RLS enables, policies become the sole meaningful filter for client roles, and grants fade into background plumbing — which is also why a table can end up RLS disabled without anyone noticing until an outside check runs.

This composition is coherent — it makes protected tables work out of the box and keeps API behavior predictable — but it concentrates attention entirely on policies. The residual risks live at the edges: tables where someone revoked grants (features break loudly), environments where default privileges differ (staging behaving unlike production), and roles beyond the standard trio whose grants nobody reviewed since creation.

The mechanism behind those defaults is worth knowing by name, because it's also the tool for changing them: ALTER DEFAULT PRIVILEGES. Supabase uses it to pre-grant client roles on future tables; you can inspect or extend it:

select pg_get_userbyid(defaclrole) as granting_role,
 defaclnamespace::regnamespace as schema,
 defaclacl
from pg_default_acl;

Every row is a standing instruction — "whenever a table appears in this schema, grant these privileges to these roles." Teams adding custom roles (an analytics reader, an integration account) should add their own default-privilege rows rather than remembering per-table grants, which keeps new tables automatically consistent with intent instead of relying on migration authors copying grant statements forever.

Diagnosing which gate refused you

The symptom-to-cause table that saves debugging sessions:

Symptom	Refusing gate	First check
permission denied for table X	Grants	has_table_privilege(role, table, 'SELECT')
permission denied for schema	Schema usage grant	Grant USAGE on the schema
permission denied for sequence	Sequence grant behind serial column	Grant usage on the sequence
Query succeeds, returns fewer rows than expected	RLS policies filtering	Read policies for caller's role
Query succeeds, returns everything	RLS disabled or tautology	Catalog check: flags and quals

The first three produce errors pointing at their own names; only the last two require interpretation, and both resolve to reading the same catalog view you already know:

select c.relname,
 c.relrowsecurity as rls_enabled,
 has_table_privilege('authenticated', c.oid, 'SELECT') as auth_can_select
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
order by c.relname;

Two boolean columns per table — grant and flag together — answer "is this table's silence a policy question or a privilege question" for your whole schema at once. Add the policy count subquery from earlier audits when the answer needs depth.

A quick story makes the diagnostic stick. A team reports "the dashboard shows no data but there are no errors." Error-free plus empty means policies filtering — or grants fine, since errors would appear otherwise. Their policy dump shows a correct-looking select policy... targeting service_role by mistake in a copy-paste. The caller is authenticated; no policy matches that role; default deny filters everything; zero errors. One catalog read, one wrong role list, fixed by editing one word. Before learning this mapping, the same team had spent two days on cache-busting and client refactors.

Practical rules for living with both

1. Leave Supabase's default grants alone for client-facing tables; express access control through policies, which is where row-level decisions belong and where review tooling looks.
2. Use revokes deliberately for defense-in-depth on internal tables — but document them, because revokes are invisible in application code and surprising in fresh environments.
3. Never treat grants as your access control for exposed tables. They're all-or-nothing per table; they bypass nothing about row scoping; and they're one accidental re-grant away from irrelevance. Policies carry the actual model. For the full inventory habit, pair it with listing every RLS policy after environment changes.
4. Test both gates after environment changes: one query per role per sensitive table catches grant drift that policy reviews never see. Grants fail loud, policies fail quiet - and a five-line test script covering both takes minutes to run against any environment.

A fifth rule ties the set together: when documenting your authorization model, document both layers explicitly — a one-line note per table stating "grants: default provisioning; policies: the four-command ownership set" prevents future contributors from guessing which system carries which decision. The documentation cost is minutes; the debugging it prevents is measured in evenings.

Grants and RLS aren't competitors; they're different altitudes of the same air-traffic system. Grants decide which planes may enter the airspace at all; policies decide where each may fly once inside. Confusing them produces either empty skies or collisions — while understanding them produces systems where both layers quietly do their jobs. For the vocabulary of each layer, the RLS glossary keeps terms straight.

Common questions

If RLS is enabled, do grants even matter?

Yes, as a precondition: without the grant, queries error before policies evaluate. In normal Supabase operation the default provisioning makes grants a non-issue — which is precisely why anomalies (revokes, custom roles, new environments) surface as confusing errors. Check grants whenever an error mentions permission, and check policies whenever results look wrong.

Why did my staging environment behave differently from production?

Default privileges are established per-database by the provisioning process. Environments built differently — manual restores, partial dumps, recreated roles — can end up with divergent grants, so identical policies yield different behavior. When staging and production disagree on the same schema, diff both the policies and the grants before touching code.

Do column-level grants interact with RLS usefully?

They stack too, and sometimes helpfully: GRANT SELECT (id, title) ON documents TO anon narrows visible columns at the privilege layer while policies filter rows — two dimensions from two systems in one request. The operational caveat is forgettability: column grants are invisible in application code and easy to contradict when schemas grow. Many teams prefer narrowed views for public column scoping instead, keeping all row-and-column logic in one reviewable place.

Should I revoke the default grants and manage everything explicitly?

That trades convenience for control at real cost: every table needs explicit grants forever, every new role needs its grants enumerated, and mistakes become loud errors rather than quiet drift. The safer default is to leave grants defaulted and put all access modeling into policies — reserving revokes for specific internal tables where belt-and-suspenders genuinely helps.

Where do function execute privileges fit?

Functions carry their own EXECUTE privilege, granted to PUBLIC by default — which means every function in your schema is callable by every role unless revoked. For definer functions that elevate privileges, that default deserves review: revoke PUBLIC execute and grant explicitly to the roles that should call it. It's the grant-side twin of policy hygiene, and one of the highest-yield items in a manual audit.

---

Unsure which gates your tables are actually running? Run the free scan — paste your app URL and see the outside-visible consequences of both layers, findings included.

RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.