PostgreSQL Row-Level Security: Policies and Pitfalls

What row-level security actually does

PostgreSQL row-level security (RLS) lets you attach access policies to a table so that rows are filtered based on the role running the query. Unlike application-level filtering, the database enforces the rule every time, no matter which service or script connects.

Say a team runs a multi-tenant B2B app. Every document belongs to an organization. Instead of remembering to add WHERE organization_id = current_org_id to every query in every microservice, the team stores the tenant ID in a session variable and lets PostgreSQL filter rows automatically.

Enabling RLS on a table

RLS is off by default. Turn it on per table:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Once enabled, PostgreSQL evaluates policies for all non-superuser roles that do not have BYPASSRLS. If no policy exists for a given operation, the default is deny: zero rows are returned or modified.

Create a policy that reads the current tenant from a custom configuration parameter:

CREATE POLICY tenant_isolation_select ON documents
FOR SELECT
TO app_user
USING (organization_id = current_setting('app.current_org_id')::uuid);

USING applies to SELECT, UPDATE, and DELETE. For INSERT, use WITH CHECK to validate the row being written:

CREATE POLICY tenant_isolation_insert ON documents
FOR INSERT
TO app_user
WITH CHECK (organization_id = current_setting('app.current_org_id')::uuid);

You can combine both in one policy, but separating them makes the intent clearer.

Setting the tenant context

The application must tell PostgreSQL which tenant is active. A common approach is a custom GUC set at the start of every transaction or request:

SET LOCAL app.current_org_id = '550e8400-e29b-41d4-a716-446655440000';

SET LOCAL lasts only for the current transaction, which is convenient in a request-scoped connection. If you use a connection pooler, reset it in DISCARD ALL or at the end of the request so the next tenant does not inherit the previous value.

In a typical setup, the application runs all queries as app_user. After SET LOCAL, a plain query is automatically filtered:

SELECT * FROM documents;
-- Only rows where organization_id matches the setting are returned.

The owner bypass gotcha

By default, the table owner is not subject to RLS. This is surprising if you create tables as a superuser or as the application’s bootstrap role and then connect as that same role.

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

FORCE makes RLS apply even to the table owner. Superusers and roles with BYPASSRLS still bypass it, which is usually what you want for admin tooling and migrations.

Policies are ORed, not ANDed

If you create multiple policies for the same command, PostgreSQL ORs them. A row is visible if it matches any policy.

CREATE POLICY org_members_can_read ON documents
FOR SELECT TO app_user
USING (organization_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY admins_can_read_all ON documents
FOR SELECT TO app_user
USING (current_setting('app.user_role') = 'admin');

A user with role admin sees everything; everyone else sees only their own organization. This is useful, but it also means it is easy to accidentally widen access by adding a second policy. Audit policies regularly.

Testing without changing application code

You can test RLS directly in psql by switching roles and settings:

SET ROLE app_user;
SET LOCAL app.current_org_id = 'org-a';
SELECT count(*) FROM documents;

SET LOCAL app.current_org_id = 'org-b';
SELECT count(*) FROM documents; -- should be 0

RESET ROLE;

For automated tests, run each test case inside a transaction, set the tenant, assert row counts, then roll back. This keeps tests isolated without touching real data.

Performance and operational limits

RLS does not remove the need for indexes. A policy like organization_id = ... can use an index on organization_id, but a policy that calls a heavy function or joins another table can turn simple queries into sequential scans.

Keep policies simple. Avoid subqueries and user-defined functions in the USING clause unless you have measured the cost. If you need complex logic, consider a security definer function or filter in the application instead.

RLS does not apply to TRUNCATE, and ALTER TABLE ... DISABLE ROW LEVEL SECURITY opens the table to everyone. Protect DDL with separate database roles so application users cannot disable policies.

When not to use RLS

RLS is a strong fit for multi-tenant SaaS data where every row has an owner and the access rule is simple. It is a poor fit for:

• Hierarchical access that changes at runtime (manager sees subordinate rows, but the relationship is complex).
• Frequently changing row-level permissions for millions of rows.
• Cases where the application already filters correctly and you only want defense in depth without the operational complexity.

In those situations, application-level filtering or a separate authorization service may be simpler.

• Enable RLS on tables that store tenant or user data.
• Add FORCE ROW LEVEL SECURITY if the application connects as the table owner.
• Write separate USING and WITH CHECK policies for reads and writes.
• Set tenant context with SET LOCAL at the start of each request.
• Reset or discard session variables before returning connections to the pool.
• Test policies by switching roles and asserting row counts per tenant.
• Add indexes on columns used in policy expressions.
• Review new policies in pull requests to prevent accidental OR-based access widening.

Takeaway

Row-level security moves access control from application code into PostgreSQL, which removes an entire class of forgotten WHERE clause bugs. Turn it on per table, force it for table owners, keep policies simple and index-friendly, and test by switching roles. It is not a replacement for good application authorization, but it is a reliable safety net for multi-tenant data.

IFLEON can help teams design and review database authorization patterns like this as part of broader application architecture work.