Developer details six data leaks that passed correct Supabase RLS policies

A developer who runs a multi-tenant point-of-sale system on Supabase has published a first-person account on dev.to of six security holes discovered across three reviews in roughly six weeks. Every one of them passed row-level-security policies that were correct as written. The core argument: enabling RLS is a single layer of defense, sitting above the Postgres privilege system and below PostgREST, the generated API Supabase places in front of the database, and the leaks appear in the gaps between those layers.

Six ways correct policies still leaked data

1. Revoking EXECUTE from the anon role alone accomplishes nothing. Postgres grants EXECUTE on every new function to PUBLIC, and Supabase's anon role inherits from PUBLIC, so removing the role's own grant leaves the inherited one untouched, with no warning from Postgres. The fix is to revoke from public and anon together, then grant execute to authenticated. Trigger functions receive the same default grant even though they never need EXECUTE to fire, since Postgres runs them as part of the table operation. The author reports finding exposed trigger functions in one review, then two more created while fixing the first batch.

2. Column-level revokes are silently swallowed by table-level grants. If a role holds SELECT on an entire table, revoking SELECT on a single column has no effect and produces no error. The remedy is to drop the table-wide grant and re-grant only the columns each role needs. The cost is deliberate: select * stops working, and every new column must be added to both the grant and the queries.

3. New columns behave differently for reads and writes. Reads on a fresh column stay blocked until granted, while writes succeed until revoked. The author had restricted reads column by column but left a table-level UPDATE grant in place, so a browser session could reset a newly added invoice counter from 54 to 1 without an error — silently restarting a tax invoice sequence. A trigger plus a unique index on (business_id, invoice_number) closed the hole.

4. A view that bypasses RLS to read also bypasses it to write. In Postgres 15 and later, a view executes with its owner's rights unless created with security_invoker = true. A view built to expose one restricted column therefore returned data RLS would block — and because a simple single-table view is automatically updatable, PostgREST exposed INSERT, UPDATE and DELETE on it as well. In testing with a real employee account, a direct delete returned zero rows while a delete through the view removed one. A tenant-id guard trigger still fired through the view, but nothing covered deletes, so the fix was to revoke everything on the view and grant SELECT only.

5. auth.role() identifies the user, not the origin of a write. A trigger guarded with auth.role() = 'authenticated' would have blocked the application's own sales path, because auth.role() reads JWT claims and those claims do not change inside a SECURITY DEFINER function. current_user does change: PostgREST runs as authenticated, while inside a definer function owned by postgres it becomes postgres. Separating browser writes from reviewed server-side code requires the second check.

6. RLS policies validate the row, not what it points at. Acting through an admin role that reads across tenants, the author's code attached a stock batch stamped with their own tenant id to a product belonging to another tenant. The policy correctly allowed it, because it only asked whether the batch belonged to the caller's tenant — never whether the referenced product did. Rows joining two tables need policies covering both sides; the author added a trigger asserting that batch, product and warehouse all share a tenant.

What actually caught the problems

According to the post, reading the policies found none of the six. Three practices did. Querying the catalog — pg_proc, pg_policies, pg_class and information_schema.role_table_grants — in a read-only script run after every migration caught an abandoned SECURITY DEFINER function created in the SQL editor: callable by anon, writing to a cost column, and unexploited only because it referenced a table that did not exist. Impersonating a real low-privilege user by setting JWT claims and counting rows in every table exposed a shop owner's email address and a pending PIN to cashier-level accounts in about two minutes. The author also advises trusting the actual state of the database over what migrations claim to have done, and deleting SQL editor scratch work in the same session it is created, since it receives no review and never appears in a diff.

Why it matters

RLS has become the default answer to tenant isolation in the Supabase ecosystem, and this account shows that correct policies are necessary but far from sufficient. The failure modes described are Postgres fundamentals — default grants, grant precedence, view ownership semantics, session variables — that any Postgres-backed API can hit, but PostgREST's automatic exposure of tables, views and functions turns each one into a browser-reachable endpoint. The practical lessons travel beyond Supabase: audit the system catalog rather than the intended schema, test as your least privileged real user, and treat every new function, view and column as a security decision rather than a routine schema change.