If one PostgreSQL database stores records for several customers, every query that reads or changes those records must respect the tenant boundary. A missed filter in one endpoint can expose another customer's data. Row-level security (RLS) adds that boundary to PostgreSQL itself, so it can apply even when a query forgets a tenant condition.
RLS is not a replacement for authentication, application authorization, or SQL privileges. It is a second enforcement layer. This guide shows a tenant-scoped policy, how to pass identity safely through a connection pool, and how to test both allowed and denied access.
What row-level security controls
PostgreSQL checks table privileges and RLS policies separately. A GRANT determines whether a database role can run an operation on a table; a policy determines which rows that operation can see or change. Enabling RLS without creating a matching policy gives roles with table privileges a default-deny result for row operations.
Policies can limit SELECT, INSERT, UPDATE, and DELETE. They do not govern every table operation: for example, TRUNCATE is not row-filtered. Grant only the operations the application actually needs, and do not assume that an RLS policy makes a broad table grant safe.
For a tenant-owned table, the basic invariant is:
A request may read or change a row only when that row's
tenant_idmatches a tenant the authenticated user is authorized to access.
The database needs a trustworthy way to know the current tenant. A value copied directly from a browser request is not proof of membership; the application must authenticate the user and verify access to the selected tenant before establishing the database context.
Add a tenant policy to a table
The following example assumes a server-side application role named app_runtime. Create and own the table through a separate migration or owner role; the application role should not own the table and must not have the BYPASSRLS attribute.
CREATE TABLE public.documents (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL,
title text NOT NULL,
body text NOT NULL
);
CREATE ROLE app_runtime NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE
ON public.documents TO app_runtime;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY documents_tenant_isolation
ON public.documents
FOR ALL
TO app_runtime
USING (
tenant_id = current_setting('app.tenant_id', true)::uuid
)
WITH CHECK (
tenant_id = current_setting('app.tenant_id', true)::uuid
);
Replace app_runtime and the table names with your own roles and schema. The application connection role must run as, or inherit, the role named by the policy. Keep ownership and migration credentials separate from request credentials.
USING filters existing rows for reads, updates, and deletes. WITH CHECK validates the row produced by an insert or update. Including both prevents a request for tenant A from changing a visible row so it belongs to tenant B, or inserting a new row for a different tenant.
The second argument to current_setting makes PostgreSQL return NULL when the setting is absent. A comparison with NULL is not true, so the policy does not expose rows when no tenant context has been set. The insert or update check also rejects a row without a matching context.
If different actions need different access rules, use separate policies for each operation and grant only the needed table privileges. PostgreSQL combines permissive policies with OR by default, so adding a second permissive policy can widen access rather than narrow it. Review all policies that apply to a role together.
Set identity in a transaction
Set the tenant context only after the application has authenticated the user and verified membership. Use a transaction-local setting, then run every query for that request in the same transaction:
BEGIN;
SELECT set_config('app.tenant_id', $1, true);
-- Run this request's queries through the same transaction.
SELECT id, title
FROM public.documents
ORDER BY title;
COMMIT;
Here $1 is a parameter supplied by your database driver, not a quoted value to paste literally into a query console. The final true makes set_config apply only for the current transaction. Commit or roll back the transaction on every code path.
Do not use a session-level SET app.tenant_id = ... with a shared connection pool. A pooled connection may serve another request, and session settings can outlive the request that set them. With transaction pooling, keep context setup and all protected queries inside one explicit transaction; check your pooler's documentation for its handling of session state. For example, PgBouncer documents that transaction pooling does not support session-level SET and RESET.
For help choosing between direct connections and pooler modes, see our guide to PostgreSQL connection pooling for serverless applications.
The custom setting is a carrier for identity, not proof of identity. A caller that can issue arbitrary SQL as app_runtime can try to set a different tenant value. Do not expose this shared database credential to browsers or other untrusted clients, use parameterized queries, and treat SQL injection as a serious authorization failure. If clients connect directly to PostgreSQL, use an identity model the database can validate rather than trusting a client-supplied custom setting.
For Supabase applications, the documented pattern uses the authenticated request's JWT identity through auth.uid() and policies tied to the authenticated role. For multi-tenant data, also check membership in the tenant being accessed; do not trust a tenant ID just because it came from a request. Follow Supabase's guidance for the grants, roles, and policies on every table involved.
Keep grants and privileged roles in scope
RLS applies only after a role has table privileges. Review existing grants when you add a policy, especially if a table is exposed through an API schema. Revoke unnecessary access and grant only the required operations. A policy cannot remove privileges another role already received.
PostgreSQL superusers and roles with BYPASSRLS always bypass row security. A table owner normally bypasses it too. Keep the application role separate from the owner and from administrative credentials. ALTER TABLE ... FORCE ROW LEVEL SECURITY can make the owner subject to policies during ordinary access, but it does not constrain superusers or BYPASSRLS roles.
RLS is also not a complete authorization system. Continue checking whether a user may perform an action, not just whether they belong to the row's tenant. Model tenant membership and ownership carefully, protect membership data, and avoid giving application roles privileges such as TRUNCATE that are outside row-policy enforcement.
Test isolation, including denied access
Test using the same non-owner role and request path your application uses. Tests run only as the table owner or a superuser can give false confidence because those roles may bypass RLS.
Seed records for at least two tenants, then cover these cases:
| Request context | Operation | Expected result |
|---|---|---|
| Tenant A | Select documents | Only tenant A's rows are returned |
| Tenant A | Insert a tenant A document | Allowed |
| Tenant A | Insert a tenant B document | Rejected by the policy check |
| Tenant A | Change a tenant A row to tenant B | Rejected by the policy check |
| Tenant B | Read, update, or delete tenant A's row | Not visible or changed |
| No tenant context | Read or write tenant-owned rows | No rows visible; writes rejected |
Also test that changing the active tenant between requests on a reused connection never carries the previous request's context. Include both commit and rollback paths. Where a request can access several tenants, test membership and non-membership separately; a matching tenant ID alone is not enough if the user is not authorized for that tenant.
Automate these checks in your database test suite and run them against a fresh database as part of migrations or CI. Supabase's guide demonstrates policy tests with supabase test db; for other stacks, use your existing SQL or integration-test tooling. Our guide to API testing and documentation workflows for small teams covers keeping API checks repeatable. Ship policy changes through a versioned migration and review them like other schema changes; see our guide to safe database schema migrations for small applications.
Watch for performance and data-model gaps
An RLS expression is evaluated as part of queries against the table. Index columns used to filter common tenant queries, such as tenant_id, and inspect query plans with realistic data. An index can help query performance; it does not prove the policy is correct.
Keep tenant ownership consistent across related tables. If a child row must reference a parent in the same tenant, enforce that relationship in the schema as well as in application logic. RLS filters access; it does not automatically make every cross-table relationship tenant-safe.
Review views, functions, background jobs, migrations, and support tools that access protected tables. A privileged view or job can use a different execution role from a normal request, so check which role it runs as and whether that access is intentional. Test those paths instead of assuming they inherit the same request policy.
A practical rollout checklist
Before relying on RLS in production:
- Add a non-null tenant identifier to every tenant-owned row and verify existing data before enabling the policy.
- Enable RLS and define policies for every required operation.
- Audit table and schema grants independently from policies.
- Keep table ownership, migration access, and
BYPASSRLSroles out of normal requests. - Derive the active tenant from trusted authentication and verify membership.
- Set request context transaction-locally and keep protected queries in that transaction.
- Test cross-tenant reads, writes, tenant changes, missing context, and pooled-connection reuse.
- Check related views, functions, jobs, constraints, and support workflows for alternate access paths.
RLS is most valuable as a database-enforced safety net around a well-defined authorization model. A policy that is correct for one table can still be undermined by broad grants, an untrusted tenant context, a privileged role, or an untested code path. Keep those boundaries explicit and make denied-access cases part of the test suite.