DBAtlas · Database operations atlas

Set up tenant isolation with row-level security

PostgreSQLSecurity

Command

ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;
-- Make the table owner subject to the policy too
ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_isolation ON app.orders
  FOR ALL
  TO app_readwrite
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int);

-- The application sets the tenant at the start of every transaction (pooler-friendly)
BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT * FROM app.orders;
COMMIT;

-- Configured policies
SELECT schemaname, tablename, policyname, roles, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'orders';

What it does

Enables RLS on the table and defines a policy that filters rows by the tenant id in a session variable (USING) and blocks writes for another tenant (WITH CHECK).

When to use it

To enforce data isolation in the database for multi-tenant applications instead of relying on application code.

Caution

Superusers and roles with the BYPASSRLS attribute always bypass policies; the table owner is subject to them only with FORCE ROW LEVEL SECURITY. With RLS enabled and no matching policy, the result is empty (no error). current_setting(..., true) returns NULL when the variable is undefined; NULLIF prevents the empty string left after RESET from failing as ''::int. Users can change app.tenant_id with SET; only a trusted application layer should set it and end users must not get SQL access. With a pooler in transaction mode, use SET LOCAL instead of SET. Index the tenant_id column used in the policy. pg_dump fails when run as a user subject to RLS; take backups with a BYPASSRLS role.

Open in DBAtlas →

Related