Adding row-level security to a live multi-tenant table

Prerequisites

• ▹A dedicated application database role that is NOT a superuser (superusers bypass RLS).
• ▹The tenant ID set per transaction via a session setting, and reset on connection release.
• ▹A staging copy of the schema to rehearse the cutover.

Procedure

1. 1

   Confirm the app connects as a non-superuser

   RLS is silently ignored for superusers and table owners without FORCE. Verify the role your application actually connects as is neither, or the policy you add will do nothing in production while passing in tests.

   SELECT current_user, rolsuper
   FROM pg_roles
   WHERE rolname = current_user;   -- rolsuper must be false

2. 2

   Wire the tenant setting per transaction — and verify it's always present

   Before enabling any policy, deploy the plumbing that sets the tenant on every transaction and resets it on release. Then verify, in real traffic, that `current_setting` is populated on every query path — a query that runs without it will match no rows once RLS is on.

   BEGIN;
   SET LOCAL app.current_tenant = '...';   -- scoped to this transaction
   -- ... queries ...
   COMMIT;
   -- On connection release back to the pool:
   RESET app.current_tenant;

3. 3

   Add the policy but don't force it yet — observe

   Create the policy and enable RLS while the app still applies its own scoping, so the two agree. Watch for any query returning fewer rows than expected — that's a path where the tenant setting is missing. Fix those before forcing.

   ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
   CREATE POLICY tenant_isolation ON invoices
     USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

4. 4

   Force RLS so it applies even to the table owner

   Once every query path is confirmed to carry the tenant setting, force RLS so the policy applies regardless of role ownership. This is the point at which a forgotten application filter is genuinely contained by the database.

   ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

5. 5

   Fail closed on a missing setting

   A NULL `current_setting` matches no rows — which can read as “this tenant has no data” rather than an error. Add an assertion at the application boundary so a missing tenant setting throws loudly instead of silently returning empty results.