Row-Level Security
Row-level security pushes tenant and ownership filters into the database, so every query is constrained even when application code forgets a WHERE clause.
Definition
Row-level security is a database feature that attaches policies to tables and evaluates them for each query, filtering or rejecting rows based on the current user, session variables, or tenant context.
Context and scope
Use it as a defense-in-depth control in multi-tenant databases, especially when many services, analytics jobs, or ad hoc queries access the same tables.
Common misconceptions
- Enabling RLS protects the table owner; owners usually bypass policies unless FORCE is set
- A policy applies to every command by default; separate policies are needed for SELECT, INSERT, UPDATE, and DELETE
- RLS removes the need for tenant filters in application queries; it is a backstop, not a replacement
Anti-patterns
- Setting the tenant context once on a pooled connection and never resetting it
- Granting the application a superuser or BYPASSRLS role
- Testing only as an admin, where policies are invisible
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Policies instead of scattered filters
In a shared database, isolation depends on every query including the right WHERE tenant_id = .... One forgotten clause in a reporting endpoint leaks data. Row-level security moves that condition into the database, where it is applied to every statement regardless of which service wrote it.
How it works
A policy binds a table to an expression evaluated against the current session. In PostgreSQL, CREATE POLICY tenant_read ON orders FOR SELECT USING (tenant_id = current_setting('app.tenant_id')::uuid) limits visible rows. The application sets app.tenant_id per transaction, preferably with SET LOCAL so a pooled connection cannot leak context into the next request.
Policies are permissive by default and combine with OR; restrictive policies combine with AND. Writes need WITH CHECK expressions so a row cannot be inserted or moved into another tenant. Table owners bypass RLS unless ALTER TABLE ... FORCE ROW LEVEL SECURITY is set, and superusers and roles with BYPASSRLS ignore it entirely.
When to use it
RLS is valuable when many code paths touch the same tables, when support tools query production, or when compliance requires a database-enforced boundary. It pairs well with tenant-scoped credentials. For single-tenant databases or tables accessed by one service, application filters may be sufficient.
Trade-offs
Policies add planner work and can defeat indexes if the expression is not sargable; index the tenant column. Debugging becomes harder because queries silently return fewer rows. Connection pooling demands disciplined session reset. Migrations and admin tasks need an explicit bypass path with audit logging.
Example and misconceptions
A developer runs SELECT * FROM orders as the application role and sees only the current tenant’s rows. A support engineer connects as the owner and sees everything, which is exactly why production access should not use the owner role.
The common misconception is that RLS is automatic after ENABLE ROW LEVEL SECURITY. Without policies, the default is deny, and without FORCE, owners still bypass.
Related concepts: Tenant Isolation, Multi-Tenancy, and SQL Injection.