Row-Level Security in PostgreSQL

Let the database add the WHERE clause

Row-level security (RLS) lets PostgreSQL filter rows by a policy on every query against a table, so a forgotten WHERE tenant_id = ... in application code no longer leaks data. You enable RLS on a table and create a policy whose USING expression decides which existing rows are visible to SELECT, UPDATE and DELETE, and whose WITH CHECK expression decides which new or changed rows may be written. A common pattern stores the current tenant in a custom setting such as app.tenant_id, set at the start of each transaction, and the policy compares each row's tenant_id with it. If RLS is enabled and no policy allows access, the default is to deny. Important exceptions: superusers and roles with BYPASSRLS always skip policies, and the table owner skips them too unless you run FORCE ROW LEVEL SECURITY. So the application must connect as a separate, non-owner role.

Defence in depth for shared tables

Isolation in a pooled database is layered: the request carries a tenant context, the data-access layer scopes queries, the database policy filters rows, and tests prove that cross-tenant reads fail.

A tenant isolation policy

PostgreSQL. set_config(..., true) is the parameter-friendly equivalent of SET LOCAL, so the driver can bind the tenant id safely.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;   -- apply to the owner too

CREATE POLICY tenant_isolation ON projects
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- What the app runs for each request (as a non-owner role):
BEGIN;
SELECT set_config('app.tenant_id', $1, true);    -- true = local to this transaction
SELECT id, name FROM projects;                   -- only this tenant's rows
COMMIT;

Test with the real app role

RLS bugs hide when developers test as the database owner or a superuser, because those bypass policies (the owner only until you add FORCE). Run integration tests with the same restricted role the application uses, and check that an unset tenant returns no rows or an error, never all rows.

त्वरित जाँच: The app connects to PostgreSQL as the role that owns the `projects` table. RLS is enabled with a tenant policy, but FORCE was never run. What happens?

• Queries fail because owners cannot read their own tables
• Policies apply normally
• Only INSERT statements are filtered
• The policy is bypassed, so queries can see every tenant's rows