PostgreSQL Row-Level Security (RLS): Complete Guide for Multi-Tenant Applications

Why Application-Level Filtering Isn't Enough

Most applications filter data in the application layer:

SELECT * FROM orders WHERE tenant_id = :current_tenant;

This works until someone forgets the WHERE clause, or an ORM generates a query without it, or a new endpoint doesn't include the filter. One missing condition exposes all tenant data.

Row-Level Security moves this filter into PostgreSQL itself. Every query — whether from your application, a developer's psql session, or a reporting tool — automatically includes the filter.

Enabling RLS

The Manual Way

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

The pgcomply Way

SELECT pgcomply.enable_rls('orders', 'tenant_id');

One call. Creates the policy, enables RLS, and forces it for table owners.

Setting the User Context

Your application sets the context before each request:

SET LOCAL app.tenant_id = 'tenant-abc-123';
-- All subsequent queries are automatically filtered
SELECT * FROM orders;  -- Only sees tenant-abc-123's orders

In a connection pool (like PgBouncer), use SET LOCAL within a transaction to ensure the setting doesn't leak between requests.

Common Pitfalls

Forgetting FORCE ROW LEVEL SECURITY: Without FORCE, table owners bypass RLS. Always use FORCE for PII tables.

Not indexing the filter column: RLS adds a WHERE clause to every query. Without an index on tenant_id, this becomes a sequential scan.

Complex policy expressions: Keep policies simple. An equality check on an indexed column is fast. A subquery in the policy expression can kill performance.

Superuser access: Superusers always bypass RLS. This is by design. Limit superusers to one.

Verifying RLS Status

SELECT * FROM pgcomply.rls_status();

Shows which tables have RLS enabled, which PII tables are missing it, and which policies exist.

Summary

Row-Level Security is the most powerful data isolation mechanism in PostgreSQL. It enforces access control at the database level, preventing data leaks from application bugs. For multi-tenant SaaS and GDPR compliance, RLS should be enabled on every table containing personal data. pgcomply.enable_rls() makes this a one-command operation.

Frequently Asked Questions

What is Row-Level Security in PostgreSQL?▼

Row-Level Security (RLS) is a PostgreSQL feature that restricts which rows a user can see or modify in a table. When RLS is enabled, PostgreSQL automatically appends a WHERE clause to every query based on policies you define. This means data isolation is enforced by the database engine itself, not by application code.

Does RLS affect query performance?▼

RLS adds a filter condition to every query, which can affect performance if the policy expression is complex or if the filtered column is not indexed. For simple equality checks (like tenant_id = current_setting('app.tenant_id')), the overhead is minimal — typically under 5% with a proper index on the filter column.

Can superusers bypass Row-Level Security?▼

Yes. PostgreSQL superusers and table owners bypass RLS by default. This is why minimizing superuser accounts is critical — every superuser can read all data regardless of RLS policies. Use ALTER TABLE ... FORCE ROW LEVEL SECURITY to apply RLS even to table owners.

How does RLS help with GDPR compliance?▼

GDPR Article 25 requires data protection by design and by default. RLS implements this at the database level by ensuring users can only access data they're authorized to see. This prevents accidental data exposure from application bugs or SQL injection — even if the application layer is compromised, RLS limits the damage.