Supabase Row Level Security: Avoiding the Silent‑Deny Trap

The silent‑deny moment

You enable Row Level Security in Supabase, run a quick API call, and rows vanish. No error message, just an empty array. The culprit is straightforward: RLS, once active, refuses every query for non‑service roles until you add at least one policy. Without a policy, the database behaves as if the table is empty for every client that isn’t using the service_role key.

Policy patterns that actually work

The canonical pattern ties a policy’s USING clause to auth.uid(), which reads the user ID from the verified JWT that Supabase Auth issues. Here’s the structure that appears in almost every Supabase RLS tutorial:

CREATE POLICY users_view_own_projects ON projects FOR SELECT USING (auth.uid() = user_id);

In this expression, auth.uid() resolves to the sub claim of the JWT Supabase Auth generated for the current session. The user_id column is typically a UUID you store alongside each row. When the anon key makes a request, Supabase extracts the JWT, evaluates the boolean expression row by row, and only returns rows where the statement is true.

Anon vs service_role

It’s critical to distinguish the three principal keys Supabase provides:

• anon: Subject to RLS policies. Used by browsers, mobile clients, and frontend code.
• authenticated: Also subject to RLS, but the JWT contains the real user ID from a signed-in session.
• service_role: Bypasses RLS entirely. Granting this key to a browser client gives full database access—never do this.

Worked example: adding an ownership policy

Below is a concrete, research‑backed walkthrough you can run in your Supabase project. I’ll include where to execute each step, what permissions are needed, what to check, and the relevant risks.

Step 1: Enable RLS on the table

Run this in the Supabase SQL Editor or any psql connection as the postgres (database owner) user.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Where: SQL Editor → New query → paste and run.

Permissions: Database owner (the postgres user that Supabase provisions).

Expected check: After running, check the table dashboard; the row level security toggle should show enabled.

Risk: If you enable RLS without adding policies immediately, all subsequent queries from the anon key will return zero rows. This is the silent‑deny trap.

Step 2: Create a select policy tied to the user

CREATE POLICY users_view_own_projects ON projects FOR SELECT USING (auth.uid() = user_id);

Where: Same SQL editor, after RLS is enabled.

Permissions: Database owner (the policy is written to the system catalog).

Expected check: Run SELECT * FROM pg_policies WHERE tablename = 'projects';. You should see one row with polcmd = SELECT.

How to verify access: Obtain two different anon JWTs (you can use the Supabase Dashboard → Authentication → JWT section, or generate them with the CLI supabase auth generate-jwt). Then run a GraphQL or REST request via the anon endpoint, each time with a different JWT. Each user should see only the rows where user_id matches their own auth.uid() value.

Risk: If the user_id column isn’t present on the table, the policy will never evaluate to true, and queries will silently return no rows. Make sure the column exists and holds a UUID that corresponds to the authenticated user’s ID.

Step 3: (Optional) Add an insert policy

CREATE POLICY users_insert_own_projects ON projects FOR INSERT WITH CHECK (auth.uid() = user_id);

Where: Same editor.

Expected check: The new policy appears in pg_policies with polcmd = INSERT.

Verification: Attempt an insert via the anon API with a JWT whose subject matches the user_id you’re inserting. The row should persist. Try inserting with a mismatched JWT; the insert should be rejected.

Risk: Omitting the WITH CHECK clause means the row is inserted, but the check column isn’t validated by the policy. This can lead to rows that no user can later select, depending on other policies.

Trade‑offs and performance at scale

Even a correctly written policy has consequences. Postgres evaluates RLS policies on every row that a query touches. If the expression references a column that isn’t indexed—and especially if it involves a join—the planner may choose a sequential scan, and query latency can grow sharply as the table expands.

Caching the JWT subject

A practical mitigation is to wrap the auth.uid() call in a subselect that Postgres can materialize once per query:

USING ((SELECT auth.uid()) = user_id)

Historically, this form helps the planner treat the JWT subject as a constant, avoiding a repeated function call per row. The performance gain depends on your Postgres version and the complexity of the overall query, but it’s a low‑cost rewrite if you’re seeing sequential scans on large tables.

Realtime and storage don’t inherit table RLS

Enabling RLS on a Postgres table doesn’t automatically secure Realtime subscriptions or Storage buckets. Realtime has its own policy editor, and Storage paths require separate policies that check the authenticated user’s ID against the bucket’s allowed paths. If your app uses any of these, you’ll need to define those policies independently.

The service_role trap

Never—repeat never—expose the service_role key to frontend code, mobile builds, or untrusted environments. It bypasses every RLS policy and grants unrestricted read/write access to every table in your project. If you need a server‑side token for administrative tasks, keep it in an environment variable, a secrets manager, or a backend-only service, and never ship it in client bundles.

Policy version drift

Supabase’s helper functions and the underlying Postgres behavior have evolved. Older tutorials may show current_user or raw session_user patterns that no longer map cleanly to the JWT claims Supabase now issues. Always test policies with a fresh anon JWT after you copy an example from the web.

Actionable closing: a quick verification checklist

1. ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;
2. CREATE POLICY users_view_own_projects ON table_name FOR SELECT USING (auth.uid() = user_id);
3. SELECT * FROM pg_policies WHERE tablename = 'projects';
4. Test with two different anon JWTs: each user should see only their matching rows.
5. Run EXPLAIN SELECT * FROM table_name WHERE ...; and check that the policy doesn’t trigger a sequential scan on a large table. If it does, rewrite the USING clause with a subselect as shown earlier.
6. Check the Supabase security advisor dashboard—it flags tables with RLS disabled or missing policies.
7. Never expose service_role to clients.

If you’re migrating access checks from application code to the database, start with a read‑only policy, verify the behavior with real user tokens, then add write policies. The database‑layer enforcement pays off across every entry point—REST API, Realtime, Storage, and direct SQL—but only when the policies are explicit, indexed, and tested.