💻 Coding
PostgreSQL Row Level Security Policy Writer for Multi Tenant SaaS: Tenant Context with set_config, USING vs WITH CHECK, FORCE ROW LEVEL SECURITY, Roles Without BYPASSRLS, Pooler Safety, and Isolation Tests
Add tenant isolation to a shared schema Postgres database: a safe tenant context function, ENABLE and FORCE row level security, USING and WITH CHECK policies per table, an application role that cannot bypass them, transaction scoped settings that survive PgBouncer, composite foreign keys, view settings, and SQL tests that prove one tenant cannot see another.
0Reviews
Prompt
Act as a PostgreSQL database engineer who retrofits row level security onto multi tenant SaaS schemas, and who has seen every leak: the app connecting as the table owner, a session setting that survives into the next pooled request, and views that run as their owner. Inputs: - Table definitions (CREATE TABLE statements) and which column identifies the tenant on each: [SchemaDDL] - Roles today: which role owns the tables, which role the app connects as, and their attributes: [CurrentRoles] - How the app connects (driver, ORM, PgBouncer or another pooler and its pool mode): [ConnectionSetup] - Access rules beyond tenant isolation (admins, read only support staff, cross tenant reporting jobs): [AccessRules] - PostgreSQL major version: [PgVersion] - Views and functions that read tenant tables: [ViewsAndFunctions] - Output format: [Format] Generate: 1. A role plan: a migration or owner role, an application role that is not the owner, not superuser, and NOBYPASSRLS, and a separate role for any cross tenant job in AccessRules. Include the GRANT statements. 2. A tenant context function that reads current_setting with missing_ok true and turns an empty string into NULL, so an unset context returns no rows instead of an error or every row. 3. For each tenant table: ALTER TABLE ... ENABLE ROW LEVEL SECURITY and FORCE ROW LEVEL SECURITY, then CREATE POLICY statements. Explain USING (which existing rows are visible or changeable) versus WITH CHECK (which new or updated rows are allowed), and write both. 4. Child tables: composite foreign keys on (parent_id, tenant_id) so a child row cannot point at another tenant's parent, plus the indexes that make policies fast. 5. Application code for ConnectionSetup: set the tenant per transaction with set_config(name, value, true) or SET LOCAL, never a session level SET under transaction pooling, with a short example in the user's driver. 6. Views and functions: security_invoker on views for PgVersion 15 and later, or the workaround for older versions, and review of SECURITY DEFINER functions. 7. Isolation tests in plain SQL inside transactions that roll back: visible row counts per tenant, a blocked cross tenant insert, a blocked update that moves a row to another tenant, and no rows with no context set. 8. A verification query on pg_class and pg_roles showing RLS enabled and forced, and that the app role cannot bypass it. Constraints: - Use only tables and columns from SchemaDDL; flag any table without a tenant column. - Note that foreign key checks and unique constraints are not filtered by RLS and can reveal that a value exists. - No destructive statements; wrap changes in a migration the user can review. No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
SchemaDDL: CREATE TABLE projects (id uuid PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES tenants(id), name text NOT NULL); CREATE TABLE invoices (id uuid PRIMARY KEY, tenant_id uuid NOT NULL, project_id uuid NOT NULL REFERENCES projects(id), amount_cents int NOT NULL); CurrentRoles: app_owner owns all tables; the Node app also connects as app_owner ConnectionSetup: Node.js with node-postgres (pg) through PgBouncer in transaction pool mode AccessRules: nightly billing export must read all tenants PgVersion: 16 ViewsAndFunctions: view invoice_totals (project_id, total_cents) Format: migration SQL, app snippet, tests
Example Output
-- 1. Roles
CREATE ROLE app_rw LOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE billing_export LOGIN NOSUPERUSER BYPASSRLS;
GRANT USAGE ON SCHEMA public TO app_rw, billing_export;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects, invoices TO app_rw;
GRANT SELECT ON projects, invoices TO billing_export;
-- Point the Node app at app_rw. app_owner stays for migrations only.
-- 2. Tenant context
CREATE FUNCTION app_current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS $$
SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid
$$;
-- After a SET LOCAL ends, the setting can remain defined as an empty string; NULLIF stops ''::uuid from erroring.
-- 3. Policies
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
CREATE POLICY projects_tenant ON projects FOR ALL TO app_rw
USING (tenant_id = app_current_tenant())
WITH CHECK (tenant_id = app_current_tenant());
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY invoices_tenant ON invoices FOR ALL TO app_rw
USING (tenant_id = app_current_tenant())
WITH CHECK (tenant_id = app_current_tenant());
-- USING hides other tenants' rows from SELECT, UPDATE, DELETE. WITH CHECK rejects INSERT or UPDATE that writes another tenant_id.
-- 4. Composite FK and indexes
ALTER TABLE projects ADD CONSTRAINT projects_id_tenant_uniq UNIQUE (id, tenant_id);
ALTER TABLE invoices ADD CONSTRAINT invoices_project_tenant_fk
FOREIGN KEY (project_id, tenant_id) REFERENCES projects (id, tenant_id);
CREATE INDEX invoices_tenant_project_idx ON invoices (tenant_id, project_id);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);
-- 6. View runs with the caller's rights (PG 15+)
ALTER VIEW invoice_totals SET (security_invoker = true);
// 5. Node (pg) per request, safe under transaction pooling
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
const { rows } = await client.query('SELECT id, name FROM projects');
await client.query('COMMIT');
} catch (e) { await client.query('ROLLBACK'); throw e; } finally { client.release(); }
-- 7. Tests (run as owner, switch role inside)
BEGIN;
SET LOCAL ROLE app_rw;
SELECT count(*) FROM projects; -- expect 0, no context set
SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
SELECT count(*) FROM projects; -- expect tenant A's count only
INSERT INTO projects (id, tenant_id, name)
VALUES (gen_random_uuid(), '22222222-2222-2222-2222-222222222222', 'x');
-- expect ERROR: new row violates row-level security policy
ROLLBACK;
-- 8. Verify
SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname IN ('projects','invoices');
SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname IN ('app_rw','billing_export');
-- Note: the unique constraint on invoices.id still reports duplicates across tenants; use random uuids.