Postgres Row-Level Security skill for Codex

RLS is correct when a session that forgets its WHERE tenant_id = ... still sees only its own rows, when no application role can silently bypass policies, and when tests prove cross-tenant reads and writes fail. Policies are security code: review them like auth code, and test them with the same role the application uses.

Before writing policies

1. Read AGENTS.md and the connection setup: which database role the app connects as, whether a pooler (PgBouncer transaction mode, Supabase Supavisor, RDS Proxy) sits in between, and how the current tenant or user is known per request.
2. List roles and their bypass power:

SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname !~ '^pg_';
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relnamespace = 'public'::regnamespace AND relkind IN ('r','p');

Superusers and roles with BYPASSRLS ignore every policy. Table owners ignore policies unless the table has FORCE ROW LEVEL SECURITY. If the app connects as the owner (common with ORMs that run migrations as the same user), RLS does nothing until fixed.

Role design

CREATE ROLE app_owner NOLOGIN;                 -- owns tables, runs migrations
CREATE ROLE app_user LOGIN PASSWORD '...' NOBYPASSRLS;  -- the application
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Keep the password out of migrations; take it from the secret store the repo already uses. Background jobs that legitimately work across tenants get a separate role with BYPASSRLS, used only by those jobs, never by request handlers.

Tenant context

Set the tenant per transaction, never per session, so a pooled connection cannot carry one request’s tenant into the next:

BEGIN;
SELECT set_config('app.tenant_id', '42', true);   -- true = local to this transaction
-- queries...
COMMIT;

SET LOCAL app.tenant_id = '42' is equivalent but cannot take a bind parameter; set_config($1, $2, true) can, so use it from application code. Wire it into the one place every request opens a transaction (middleware, unit-of-work, ORM hook), not into individual queries.

Read it in policies through a helper that fails closed:

CREATE FUNCTION app.current_tenant_id() RETURNS bigint
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.tenant_id', true), '')::bigint
$$;

current_setting(..., true) returns NULL when unset; a NULL tenant matches no rows, so a request that forgot to set context sees nothing instead of everything.

Policies

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY invoices_tenant_isolation ON invoices
    AS PERMISSIVE FOR ALL TO app_user
    USING (tenant_id = (SELECT app.current_tenant_id()))
    WITH CHECK (tenant_id = (SELECT app.current_tenant_id()));

• USING filters rows a command can see (SELECT, and the rows UPDATE/DELETE target). WITH CHECK validates new row versions (INSERT, and UPDATE results). Without WITH CHECK on an UPDATE policy, a user can move a row into another tenant.
• Policies for the same command are OR-ed when PERMISSIVE and AND-ed with RESTRICTIVE ones. Use a RESTRICTIVE policy for a global rule (tenant isolation) and PERMISSIVE policies for role-specific access within the tenant.
• No policy for a command means deny for that command once RLS is enabled.
• Separate policies per command (FOR SELECT, FOR INSERT …) when read and write rules differ, for example members read all projects but only admins update.
• Policies on child tables must enforce tenancy themselves; a policy on orders does not protect order_items. Put tenant_id on child tables rather than joining to the parent inside the policy.

Supabase specifics

• Client requests run as the anon or authenticated role; service_role bypasses RLS and must never reach the browser.
• Use (select auth.uid()) rather than bare auth.uid() in policies so it is evaluated once per statement, and add TO authenticated to skip evaluation for anon.
• Tables created through SQL in the public schema are exposed through the Data API; enable RLS on every one. The Supabase security advisor flags tables without it.

Performance

Problem	Fix
Function in policy evaluated per row	Wrap as (SELECT fn()) so it becomes an InitPlan evaluated once; mark helpers STABLE
Seq Scan on large tenant table	Index with tenant_id as the leading column of the indexes queries use
Policy with subquery (EXISTS (SELECT 1 FROM memberships ...)) per row	Index the membership lookup; or precompute allowed ids into a setting or a SECURITY DEFINER function returning an array
Planner cannot push user filters below the policy	Non-LEAKPROOF functions in queries are evaluated after the policy filter; this is by design, avoid exotic functions in hot WHERE clauses

Compare EXPLAIN ANALYZE as app_user with and without the policy (as owner). A good policy adds a cheap index condition, not a filter over every row.

Gotchas

• Views run with the view owner’s privileges by default and can bypass RLS. Create them WITH (security_invoker = true) (PG 15+).
• SECURITY DEFINER functions run as their owner; if the owner bypasses RLS, so does the function. Set search_path explicitly on them.
• Unique constraints and foreign keys are checked without RLS. An insert that fails on a unique key reveals that another tenant’s row exists. Scope unique keys with tenant_id.
• pg_dump sets row_security = off and errors if policies would hide rows; run backups as a superuser or a role with BYPASSRLS.
• TRUNCATE is not subject to RLS; do not grant it to the app role.
• Session-level SET app.tenant_id with a transaction-mode pooler leaks context between clients. Only transaction-local settings are safe there.
• Materialized views do not apply RLS from base tables at query time.

Testing isolation

Write tests that run as the application role, not as a superuser:

BEGIN;
SET LOCAL ROLE app_user;
SELECT set_config('app.tenant_id', '1', true);
SELECT count(*) FROM invoices WHERE tenant_id = 2;           -- expect 0
SAVEPOINT s1;
INSERT INTO invoices (tenant_id, ...) VALUES (2, ...);       -- expect: new row violates row-level security policy
ROLLBACK TO SAVEPOINT s1;
UPDATE invoices SET tenant_id = 2 WHERE id = <tenant 1 row>; -- expect the same error
ROLLBACK TO SAVEPOINT s1;
SELECT set_config('app.tenant_id', '', true);
SELECT count(*) FROM invoices;                               -- expect 0 (fail closed)
ROLLBACK;

An error aborts the transaction, so each expected failure needs its own savepoint (as above) or its own transaction.

Turn these into automated tests (pgTAP, or the app’s integration test suite using $testcontainers) covering every RLS table. A script that lists tables with relrowsecurity = false in tenant-owned schemas makes a good CI check.

Verification

• App role is not owner, not superuser, NOBYPASSRLS
• Every tenant-owned table has RLS enabled and forced, with USING and WITH CHECK
• Tenant context set with set_config(..., true) inside each transaction
• Missing context returns zero rows
• Cross-tenant read, insert, update and delete tests fail as expected
• Hot queries’ plans as app role still use tenant-leading indexes

Output template

## RLS: <schema or feature>
**Roles:** <role -> owner/bypass/login>
**Context:** <how tenant/user is set, where in code>
| Table | RLS on | Forced | Policies (cmd: rule) | Index on tenant_id |
|---|---|---|---|---|
**Tests:** <file, cases covered>
**Known gaps:** <views, definer functions, jobs with BYPASSRLS>