Skip to content
suparbase
All guides
GuideIntermediatepostgresrlssupabasesecurity

Add RLS to an Existing Postgres Database Without Breaking Production

A step-by-step playbook for enabling Row-Level Security on a Postgres database that's already in production. With rollback strategy, testing patterns, and the gotchas.

30 min to complete 11 min read

Turning on RLS for a table that's already serving production traffic is one of those changes that's scary because the failure mode is "nobody can see their data". The fix isn't to do it carefully on Friday afternoon. The fix is a playbook.

Pre-flight: audit your queries

Before you turn RLS on for any table, you need to know every query that hits it. Run this:

audit.sqlsql
-- All current activity touching the target table.
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%public.posts%'
ORDER BY calls DESC
LIMIT 20;

Look at the role each call uses. If anything is hitting the table as service_role, RLS won't affect it (good or bad). If your app uses authenticated or anon, RLS will start filtering on those queries.

Step 1: Pick a target table

Start with one table. Don't batch "enable RLS on everything" into one PR; you want clean rollback.

Step 2: Write the policies first

Write the policies you intend to use, but don't enable RLS yet. The policies sit in the schema unused until RLS is on.

policies.sqlsql
-- Read: anyone authenticated sees their own posts.
CREATE POLICY "Author reads their posts"
  ON public.posts FOR SELECT TO authenticated
  USING (auth.uid() = author_id);

-- Write: same constraint.
CREATE POLICY "Author writes their posts"
  ON public.posts FOR ALL TO authenticated
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

Step 3: Test in a transaction

Postgres lets you temporarily enable RLS on a table inside a transaction. Run your most common queries with simulated JWT claims; roll back when you're done.

rls-test.sqlsql
BEGIN;
-- Pretend to be the user whose data you're inspecting.
SET LOCAL ROLE authenticated;
SELECT set_config(
  'request.jwt.claims',
  '{"sub":"<real-user-uuid>","role":"authenticated"}',
  true
);

ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Run the queries your application makes. They should still work
-- for the simulated user.
SELECT id, title FROM posts LIMIT 5;

-- Now simulate a DIFFERENT user. They should see only their rows.
SELECT set_config(
  'request.jwt.claims',
  '{"sub":"<other-user-uuid>","role":"authenticated"}',
  true
);
SELECT id, title FROM posts LIMIT 5;

ROLLBACK;

Step 4: Enable RLS in a single migration

Once the simulator says all your application queries work, the production migration is one line:

migrations/0042_enable_rls_posts.sqlsql
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;
-- The policies you wrote earlier are now active.

Deploy. Monitor your error log for "permission denied".

Step 5: Verify production traffic

For the first hour after deploy, watch pg_stat_statementsfor new errors and check your app's error monitoring (Sentry, Logflare, whatever you use). If something denies, you'll see it within minutes.

Rollback plan

Rollback is one statement:

rollback.sqlsql
ALTER TABLE public.posts DISABLE ROW LEVEL SECURITY;

It's instant, doesn't require dropping policies, and reverts the table to wide-open. Have this prepared in your migration tool before you deploy step 4.

More guides