# Row-Level Security in PostgreSQL — Multi-Tenant SaaS Architecture

Source: https://www.skillbyai.com/en/multi-tenant-saas/mt-row-level-security

> Enable PostgreSQL row-level security with a tenant policy, set the tenant per transaction, and know which roles bypass the policy.

## 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.

![Four stages: tenant context on the request, scoped repository queries, database row-level security policy, automated cross-tenant tests.](assets/figures/multi-tenant-saas/section-5-map.svg) — Figure 5.1: tenant context -> scoped queries -> row-level security -> isolation tests

## 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.

```sql
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.

**Quiz:** 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
- [x] The policy is bypassed, so queries can see every tenant's rows

*Answer:* The policy is bypassed, so queries can see every tenant's rows. Table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set. Use a non-owner application role and FORCE as a belt-and-braces measure.