Introduction: Why PostgreSQL Row Level Security Matters for Multi‑Tenant SaaS
In any serious multi‑tenant SaaS, tenant isolation isn’t a nice‑to‑have—it’s the difference between a routine day and a company‑ending incident. Every time I design a new system, my first question is: how do we guarantee that Tenant A can never see Tenant B’s data? PostgreSQL row level security (RLS) is one of the strongest tools I rely on to make that guarantee enforceable at the database layer, not just in application code.
Most SaaS apps start with a simple pattern: add a tenant_id column and filter by it in queries. That works until it doesn’t—one missed WHERE tenant_id = ... or a rushed admin endpoint, and suddenly data from multiple customers is mixed in a single response. I’ve seen this happen in real projects, and it’s usually not a complex exploit; it’s a small bug with massive consequences.
PostgreSQL row level security flips this model. Instead of trusting every developer and every query to remember tenant filters, I define policy rules inside the database that determine which rows a given session can access. The app connects with a role that is “scoped” to a tenant (or a set of tenants), and the database silently adds the right filter conditions under the hood. Even a sloppy query can’t escape those rules.
For modern SaaS platforms under increasing regulatory and customer scrutiny, this has three big benefits I lean on:
Defense in depth: RLS backs up application checks with a hard database boundary.
Safer evolution: new features and endpoints are less likely to introduce cross‑tenant leaks.
Auditability: you can point to explicit, centralized policies that describe who can see what.
In the rest of this guide, I’ll walk through how I actually implement PostgreSQL row level security for multi‑tenant SaaS, the mistakes I’ve seen teams make, and patterns that have worked well in production.
Prerequisites, Assumptions, and Reference Architecture
What You Should Already Know
To get real value from PostgreSQL row level security in a multi‑tenant SaaS, I assume you’re comfortable with the basics of PostgreSQL and web app development. In my own projects, RLS only clicked once I understood:
Core SQL: SELECT, INSERT, UPDATE, DELETE, joins, and basic indexing.
PostgreSQL roles and privileges: how users, roles, and grants work.
Connection handling: using a pooler (like PgBouncer) or framework‑level pooling.
Basic backend stack: a typical REST or GraphQL API deployed behind a load balancer.
You don’t need to be a Postgres guru. When I first enabled RLS in production, I just needed a solid mental model of how sessions, roles, and queries behaved end‑to‑end.
PostgreSQL Versions, Features, and Extensions
RLS has been in PostgreSQL since 9.5, but in my experience things are smoother on more recent versions. For this guide I’ll assume:
PostgreSQL 13+ (ideally 14 or newer) for performance and policy stability.
Standard SQL role and schema features, no exotic patches.
Optional but common extensions like uuid-ossp or pgcrypto for tenant and user IDs.
Here’s a tiny example of the kind of setup I typically start with when preparing a database for multi‑tenant RLS:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE ROLE app_user NOINHERIT LOGIN PASSWORD 'example';
CREATE ROLE app_tenant; -- used as a group role for tenants
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name text NOT NULL
);
CREATE TABLE accounts (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
email text NOT NULL
);
This isn’t the final schema, but it reflects the assumptions I use later when we actually attach PostgreSQL row level security policies.
Target Multi‑Tenant SaaS Model
There are many ways to slice multi‑tenancy; in this article, I focus on the model I’ve used most in production:
Single database, shared schema: one Postgres database for all tenants.
Tenant column per row: every tenant‑scoped table has a tenant_id column.
Central auth service: the API authenticates users and resolves their active tenant context.
Scoped DB role per request: the app sets a session variable or switches role to represent the current tenant scope.
In my experience, this approach balances cost, simplicity, and operational overhead for most early‑ to mid‑stage SaaS platforms. RLS then becomes the guardrail that ensures every query is automatically scoped to the current tenant.
High‑Level Reference Architecture
To make the rest of the guide concrete, here’s the reference architecture I’ll implicitly use whenever I talk about PostgreSQL row level security decisions:
Client apps: browser, mobile, or server‑to‑server clients calling a JSON or GraphQL API.
API layer: a backend service (for example, Node.js, Python, or Java) handling auth, building queries, and talking to Postgres through a connection pool.
PostgreSQL cluster: primary instance (possibly with read replicas) hosting all tenants in a single logical database.
Security boundary: per‑request tenant context pushed into the database session (via SET LOCAL or similar) so that RLS policies can enforce row visibility.
In practice, this means each incoming request flows through auth, picks up a tenant identifier, and then uses that identifier consistently down to the database session. RLS policies then enforce that only rows matching that tenant context are visible, regardless of how the application constructs its SQL. Architect Multitenant Solutions on Azure – Azure Architecture Center
Designing a Tenant‑Aware Data Model for PostgreSQL Row Level Security
Core Pattern: Every Row Belongs to a Tenant
When I design for PostgreSQL row level security, I start with a simple rule: every tenant‑scoped row must explicitly say which tenant it belongs to. That usually means a tenant_id column on every table that should be protected by RLS. If I skip this step, policies quickly become fragile or overly complex.
Here’s a minimal example of a tenant‑aware core schema I’ve used in real SaaS projects:
CREATE TABLE tenants (
id uuid PRIMARY KEY,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE users (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL REFERENCES tenants(id),
email text NOT NULL,
role text NOT NULL DEFAULT 'member',
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (tenant_id, email)
);
CREATE TABLE projects (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL REFERENCES tenants(id),
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
By making tenant_id a first‑class part of the model from day one, PostgreSQL row level security policies later become simple predicates like tenant_id = current_setting('app.current_tenant')::uuid instead of messy joins or guesswork.
Choosing Tenant Keys and ID Strategies
The way I represent tenant identifiers affects both security and performance. In practice I’ve had the best results with:
UUIDs for tenant IDs: hard to guess, globally unique, and easy to pass around in APIs.
Surrogate keys for internal tables: I avoid natural keys for tenants because they tend to change (e.g., customer renames).
Stable, short external identifiers: optionally expose a slug or code (like acme) for URLs, but map it back to the internal UUID.
This is a pattern I now use almost by default:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE tenants (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
slug text UNIQUE NOT NULL, -- human-friendly
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
Internally, all foreign keys reference tenants.id, not slug. From experience, keeping external and internal identifiers separate makes it much easier to refactor URL structures or branding later without ripping through every table and policy.
Once the core tenant table is in place, the next challenge is making sure tenant context flows cleanly through the entire schema. The rule I try to enforce in code review is: if a table is tenant‑scoped, it must carry its own tenant_id and reference the tenant directly or through a strict chain.
For example, a typical hierarchy I’ve implemented looks like this:
CREATE TABLE projects (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
name text NOT NULL
);
CREATE TABLE tasks (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
project_id uuid NOT NULL REFERENCES projects(id),
title text NOT NULL,
status text NOT NULL DEFAULT 'open'
);
Notice that tasks has both project_id and tenant_id. Early in my career I tried to rely solely on the parent relationship (inferring tenant through joins). That made RLS policies harder to express and reason about. Now I prefer:
Redundant but explicit tenant_id on each tenant‑scoped table.
Foreign key from tenant_id straight to tenants(id).
Additional FKs to parent entities (like project_id) for domain integrity.
To enforce consistency, I often add a small check constraint to ensure tenant IDs line up across relations:
ALTER TABLE tasks
ADD CONSTRAINT tasks_tenant_matches_project
CHECK (
tenant_id = (SELECT tenant_id FROM projects WHERE projects.id = project_id)
);
This pattern prevents subtle cross‑tenant references from creeping in, which is exactly the type of bug that can undermine PostgreSQL row level security if left unchecked.
Indexing and Performance Considerations for RLS
Once every row carries a tenant_id, the next step is making sure RLS doesn’t kill performance. Most of the performance issues I’ve seen came down to missing or poorly chosen indexes on tenant columns.
For heavily accessed tables, I usually start with:
B‑tree index on tenant_id to support simple per‑tenant scans.
Composite indexes on (tenant_id, something_else) for common filters.
Partial indexes for hot subsets (e.g., active records) when needed.
Continuing the earlier example, I’d typically add:
CREATE INDEX idx_projects_tenant_id
ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_id
ON tasks (tenant_id);
CREATE INDEX idx_tasks_tenant_status
ON tasks (tenant_id, status);
Because RLS policies for multi‑tenant SaaS usually boil down to WHERE tenant_id = <current tenant>, these indexes give the planner the option to do fast index scans instead of full table scans per request. In my experience, once traffic grows, this is the difference between RLS feeling “invisible” and RLS being blamed for every slow query.
One more lesson I learned the hard way: always test query plans with EXPLAIN ANALYZEwith RLS enabled, because policies can change how Postgres estimates row counts and chooses indexes. Tuning the data model and indexes with real tenant‑scoped workloads makes PostgreSQL row level security a performance ally instead of a surprise bottleneck.
Defining Roles and Authentication Strategy for Tenant Isolation
Designing a Role Hierarchy that Works with RLS
In my experience, PostgreSQL row level security only pays off if the role model in the database is clean and predictable. I try to keep it simple: one application role that the backend uses, plus internal roles for migrations and admin tasks. I avoid per-user or per-tenant database users because they explode in number and complicate pooling.
A typical baseline hierarchy I use looks like this:
app_admin: super-privileged role for migrations and maintenance (no direct use by the app).
app_user: the role the API uses for regular requests, subject to RLS.
read_only or reporting roles: optional, for background jobs or analytics with tailored policies.
-- Admin role (used by migrations, CI, DBAs)
CREATE ROLE app_admin LOGIN PASSWORD 'change-me' SUPERUSER;
-- Application role (no superuser, no bypass RLS)
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER;
-- Make sure app_user cannot bypass RLS
ALTER ROLE app_user SET row_security TO on;
The key for me is that the role used by the app must not have privileges that bypass RLS. That forces all access through the policies we’ll define later.
Propagating Tenant Identity into the Database Session
For PostgreSQL row level security to know which tenant is active, the application has to push tenant identity into the database session. I usually do this with a custom GUC (a session variable) set on every request, after authentication.
The flow I’ve used successfully in production is:
Client authenticates (JWT, OAuth, etc.).
API decodes the token and resolves the tenant_id and user_id.
API grabs a connection from the pool and sets session variables.
All subsequent queries on that connection rely on those variables in RLS policies.
Here’s a minimal example using custom settings I commonly define:
-- Called once per request after auth is resolved
SELECT
set_config('app.current_tenant', '4c6e7d2b-...', true),
set_config('app.current_user', '9b231fa4-...', true);
In the policy definitions, I then reference these settings. Even when I switched backend frameworks over the years, this pattern stayed stable: authenticate in the app, then stamp the tenant context into the session before doing any work.
Connection Pooling and Per-Request Context
Connection pooling adds one subtle but crucial detail: database sessions are reused across requests. I learned early on that if I don’t reset or override session state on checkout, one tenant’s context can accidentally leak into another’s queries.
The two rules I follow with poolers like PgBouncer or framework-level pools are:
Always set tenant context on checkout: treat session variables as untrusted until you overwrite them.
Prefer transaction pooling or robust reset hooks: ensure state is cleared between transactions where possible.
In many stacks, I implement a small helper that runs at the start of each request to ensure the context is correct:
-- pseudo-SQL called per request
BEGIN;
SELECT set_config('app.current_tenant', $1, true);
SELECT set_config('app.current_user', $2, true);
-- now run all business queries in this transaction
COMMIT;
Step‑by‑Step: Enabling PostgreSQL Row Level Security on a Sample Schema
1. Creating the Sample Tenants and Invoices Schema
To make PostgreSQL row level security concrete, I like to walk through a simple but realistic example. Here we’ll model a multi‑tenant billing system with tenants, customers, and invoices. Each row is owned by exactly one tenant.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE tenants (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE customers (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
name text NOT NULL,
email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (tenant_id, email)
);
CREATE TABLE invoices (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
customer_id uuid NOT NULL REFERENCES customers(id),
amount_cents integer NOT NULL CHECK (amount_cents >= 0),
currency text NOT NULL DEFAULT 'USD',
status text NOT NULL DEFAULT 'draft',
issued_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_customers_tenant_id ON customers (tenant_id);
CREATE INDEX idx_invoices_tenant_id ON invoices (tenant_id);
In my own projects, this is the kind of baseline I put in place before touching any RLS policies: every tenant‑scoped table has a tenant_id and indexes to back it up.
2. Enabling Row Level Security on the Right Tables
PostgreSQL row level security is disabled by default on new tables, so the first explicit step is to turn it on for any table that holds tenant data. I usually don’t enable RLS on lookup tables or global config tables that truly are shared.
-- Enable RLS on tenant-owned tables
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
-- Optional but recommended for safety: force RLS even for table owners
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
One thing I learned early: enabling RLS alone does not grant any access. Until you define policies, non‑superuser roles won’t see any rows at all.
3. Defining Session Context for the Current Tenant
RLS policies need a way to know which tenant is active. In my experience, the cleanest pattern is to set a custom GUC (configuration parameter) in the session for every request, then reference it in policies.
First I define the convention I’ll use: a setting named app.current_tenant will hold the tenant_id for the current request. The application sets it immediately after authenticating the user:
-- Example of what the app runs per request (pseudo-code in SQL)
SELECT set_config('app.current_tenant', 'e2af...-tenant-uuid', true);
In real code (for example in Python), this often looks like:
import psycopg2
TENANT_ID = "e2af..." # extracted from JWT or session
with psycopg2.connect(dsn) as conn:
with conn.cursor() as cur:
cur.execute(
"SELECT set_config('app.current_tenant', %s, true)",
(TENANT_ID,)
)
# Now safely run tenant-scoped queries
cur.execute("SELECT id, amount_cents FROM invoices WHERE status = 'paid'")
rows = cur.fetchall()
Once this pattern is wired into the app, PostgreSQL row level security can use current_setting('app.current_tenant') to filter rows.
4. Writing RLS Policies for Tenants, Customers, and Invoices
With the session context in place, I can create policies that define exactly which rows the application role may see or modify. I like to start with a simple rule for each table: the row’s tenant_id must match app.current_tenant.
-- Tenants: allow each tenant to see only its own row
CREATE POLICY tenants_isolation ON tenants
USING (id = current_setting('app.current_tenant')::uuid);
-- Customers: scoped by tenant_id
CREATE POLICY customers_isolation ON customers
USING (tenant_id = current_setting('app.current_tenant')::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);
-- Invoices: scoped by tenant_id
CREATE POLICY invoices_isolation ON invoices
USING (tenant_id = current_setting('app.current_tenant')::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);
The USING clause controls which rows are visible for SELECT, UPDATE, and DELETE. The WITH CHECK clause controls what rows can be inserted or updated. In my early experiments I forgot WITH CHECK on write‑heavy tables and discovered the app could accidentally create cross‑tenant rows; now I always define both.
For more nuanced needs (like per‑user roles within a tenant), I layer additional session variables and conditions into these policies, but this tenant‑only version is the foundation I build on.
5. Testing and Verifying the Policies in Practice
Once the policies are in place, I always run through a few manual checks in psql before trusting them in production. The idea is to mimic real requests by changing app.current_tenant and confirming that results change accordingly.
-- Assume we have two tenants: t1 and t2
SELECT id, name FROM tenants;
-- Act as tenant t1
SELECT set_config('app.current_tenant', '11111111-1111-1111-1111-111111111111', true);
SELECT id, tenant_id, amount_cents FROM invoices ORDER BY created_at DESC LIMIT 5;
-- Act as tenant t2
SELECT set_config('app.current_tenant', '22222222-2222-2222-2222-222222222222', true);
SELECT id, tenant_id, amount_cents FROM invoices ORDER BY created_at DESC LIMIT 5;
If both queries return different sets of invoices with matching tenant_id values, RLS is doing its job. I also test write operations:
-- As tenant t1, try to insert an invoice for tenant t2 (should fail)
SELECT set_config('app.current_tenant', '11111111-1111-1111-1111-111111111111', true);
INSERT INTO invoices (tenant_id, customer_id, amount_cents)
VALUES ('22222222-2222-2222-2222-222222222222', 'some-customer-id', 5000);
-- Expect: ERROR due to RLS WITH CHECK violation
In my experience, taking the time to run these small experiments early saves hours of debugging later. It also gives me confidence that PostgreSQL row level security is acting as a hard boundary between tenants, not just an extra filter I hope the ORM remembers.
Combining PostgreSQL Row Level Security with Encryption at Rest and In Transit
Why RLS Alone Is Not Enough
PostgreSQL row level security gives me strong logical separation between tenants, but it doesn’t protect against every threat. If someone gets low‑level access to disk, backups, or network traffic, RLS won’t stop them from reading raw bytes. In every production SaaS I’ve worked on, RLS is just one layer in a broader defense‑in‑depth story that includes encryption at rest and in transit.
The way I think about it is simple: RLS controls who can see which rows once they’re inside Postgres; encryption makes sure that the same data isn’t trivially exposed if a disk, backup, or network path is compromised.
Encryption at Rest: Storage, Backups, and Keys
For encryption at rest, I try to do as much as possible at the platform level instead of hand‑rolling inside the database. In practice that usually means:
Disk or volume encryption (e.g., cloud provider managed keys for database volumes).
Encrypted backups and snapshots, using the same or dedicated KMS keys.
Strict key management: keys stored in a KMS, rotated regularly, and never hard‑coded in app configs.
When I’ve needed extra protection for particularly sensitive columns (like card tokens or national IDs), I’ve used Postgres extensions such as pgcrypto to encrypt specific fields on write and decrypt them only when strictly necessary:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE payment_methods (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
encrypted_pan bytea NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Insert with application-supplied key
INSERT INTO payment_methods (tenant_id, encrypted_pan)
VALUES (
:tenant_id,
pgp_sym_encrypt(:plain_pan, :encryption_key)
);
RLS still applies to payment_methods through tenant_id, but even if someone exfiltrates a backup, they see only ciphertext unless they also compromise the encryption keys.
Encryption in Transit: Securing App–DB and Client–App Traffic
For data in transit, I learned early that “we’re inside a VPC” is not a security strategy. I now consider TLS mandatory between every boundary:
Client → API: HTTPS with modern TLS versions and strict certificate handling.
API → PostgreSQL: TLS‑encrypted connections, with certificates managed by the platform or manually configured.
Internal services → Postgres (jobs, workers, BI tools): same TLS rules as the main app.
On the Postgres side, this usually means enabling SSL, loading server certificates, and requiring SSL for application roles. In configuration, it looks something like this:
From the application side, I always configure the driver to require TLS and validate the server certificate. Combined with PostgreSQL row level security, this ensures tenant‑scoped data is protected both by cryptographic boundaries in transit and logical boundaries once inside the database.
Putting It Together: Defense in Depth for Tenant Data
When I review a SaaS architecture now, I mentally check for all three layers working together:
RLS: each row tagged with tenant_id and protected by well‑tested policies.
Encryption at rest: database storage, logs, and backups encrypted with managed keys.
Encryption in transit: TLS everywhere, verified certificates, and secure driver settings.
In audits and customer security reviews, being able to show this combination has made conversations much easier. I can point to RLS as the mechanism that keeps tenants isolated inside Postgres, and to encryption as the control that protects those same bits whenever they leave memory—on disk, in backups, or over the wire. Centralized Data Access Control Policies – Privacera
Advanced PostgreSQL Row Level Security Patterns for Real‑World SaaS
Cross‑Tenant Admin Access Without Breaking Isolation
In almost every SaaS I’ve worked on, we eventually need a support or ops role that can see multiple tenants for debugging, but we don’t want to punch a hole through PostgreSQL row level security. The pattern that’s worked best for me is to introduce a separate admin context flag in the session and keep the tenant filtering logic in one place.
I usually add two settings:
app.current_tenant – the tenant a normal user is scoped to.
app.is_platform_admin – a boolean (as text) indicating platform admin sessions.
-- App sets this for platform admins only
SELECT
set_config('app.current_tenant', '00000000-0000-0000-0000-000000000000', true),
set_config('app.is_platform_admin', 'true', true);
-- Helper expression: who can see which tenant rows?
CREATE OR REPLACE FUNCTION app.tenant_visible(tenant_id uuid)
RETURNS boolean LANGUAGE sql STABLE AS $$
SELECT
current_setting('app.is_platform_admin', true) = 'true'
OR tenant_id = current_setting('app.current_tenant')::uuid;
$$;
-- Use the helper in policies
CREATE POLICY invoices_isolation ON invoices
USING (app.tenant_visible(tenant_id))
WITH CHECK (app.tenant_visible(tenant_id));
I like this pattern because the policy reads clearly and I can audit all tenant‑visibility logic in a single function. When I’ve had to change admin behavior later (e.g., restrict admins to a subset of tenants), I only edited app.tenant_visible, not dozens of policies.
Hierarchical Access: Organizations, Teams, and Users
Real SaaS apps rarely have flat permissions; they have organizations, teams, and per‑user roles. Early on, I tried encoding all of that directly in RLS conditions and quickly ended up with unreadable policies. Now I centralize the hierarchy in tables and use views or helper functions to keep policies small.
A common structure I’ve used looks like this:
CREATE TABLE org_memberships (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
user_id uuid NOT NULL,
role text NOT NULL, -- 'owner', 'admin', 'member'
UNIQUE (tenant_id, user_id)
);
CREATE TABLE documents (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
owner_id uuid NOT NULL, -- application user id
title text NOT NULL,
content text NOT NULL
);
I then capture the authenticated user in the session:
Instead of stuffing complex joins into the policy, I use a helper function that encodes the hierarchy logic once:
CREATE OR REPLACE FUNCTION app.can_access_document(doc documents)
RETURNS boolean LANGUAGE sql STABLE AS $$
WITH current_ctx AS (
SELECT
current_setting('app.current_tenant')::uuid AS tenant_id,
current_setting('app.current_user')::uuid AS user_id
)
SELECT EXISTS (
SELECT 1
FROM current_ctx c
LEFT JOIN org_memberships m
ON m.tenant_id = c.tenant_id AND m.user_id = c.user_id
WHERE
doc.tenant_id = c.tenant_id
AND (
doc.owner_id = c.user_id -- owner can always access
OR m.role IN ('owner', 'admin') -- org admins see all docs
)
);
$$;
CREATE POLICY documents_access ON documents
USING (app.can_access_document(documents))
WITH CHECK (app.can_access_document(documents));
This way, the hierarchical rules live in SQL where I can test them independently with SELECTs. In practice, this has made tricky “who can see what?” bugs much easier to reason about than burying everything inside raw policy predicates.
Feature Flags and Plan‑Based Policies
Many SaaS products gate features by subscription plan: some tenants get advanced reporting, others don’t. I’ve found RLS can help here too, by preventing writes or reads to certain tables when a tenant’s plan doesn’t allow it, even if the application code makes a mistake.
I typically start with a simple plan model:
CREATE TABLE plans (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
name text NOT NULL,
allow_advanced_reports boolean NOT NULL DEFAULT false
);
ALTER TABLE tenants
ADD COLUMN plan_id uuid NOT NULL REFERENCES plans(id);
CREATE TABLE advanced_reports (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id uuid NOT NULL REFERENCES tenants(id),
name text NOT NULL,
payload jsonb NOT NULL
);
Then I create a helper that checks the current tenant’s plan against the feature flag:
CREATE OR REPLACE FUNCTION app.tenant_has_feature(feature text)
RETURNS boolean LANGUAGE sql STABLE AS $$
WITH t AS (
SELECT plan_id
FROM tenants
WHERE id = current_setting('app.current_tenant')::uuid
)
SELECT CASE feature
WHEN 'advanced_reports' THEN EXISTS (
SELECT 1
FROM t
JOIN plans p ON p.id = t.plan_id
WHERE p.allow_advanced_reports
)
ELSE false
END;
$$;
CREATE POLICY advanced_reports_feature_guard ON advanced_reports
USING (
tenant_id = current_setting('app.current_tenant')::uuid
AND app.tenant_has_feature('advanced_reports')
)
WITH CHECK (
tenant_id = current_setting('app.current_tenant')::uuid
AND app.tenant_has_feature('advanced_reports')
);
One lesson from production: I cache plan decisions in the app when I care about latency, but I still keep this RLS layer in place as a safety net. That way, no matter what UI bug or API misuse appears, tenants on the wrong plan can’t accidentally see or create data tied to features they don’t pay for.
Auditing, Debugging, and Observability for Complex RLS
As RLS policies get more advanced, visibility becomes critical. Early on I struggled to debug “missing data” reports because it wasn’t obvious whether RLS or the application logic was to blame. Now I always invest a bit in observability around PostgreSQL row level security.
A pattern that’s served me well is to log effective context and key decisions in a dedicated audit table:
CREATE TABLE rls_audit_log (
id bigserial PRIMARY KEY,
logged_at timestamptz NOT NULL DEFAULT now(),
tenant_id uuid,
user_id uuid,
action text NOT NULL,
entity text NOT NULL,
entity_id uuid,
details jsonb
);
CREATE OR REPLACE FUNCTION app.log_rls_event(
p_action text,
p_entity text,
p_entity_id uuid,
p_details jsonb DEFAULT '{}'::jsonb
) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO rls_audit_log (tenant_id, user_id, action, entity, entity_id, details)
VALUES (
NULLIF(current_setting('app.current_tenant', true), '')::uuid,
NULLIF(current_setting('app.current_user', true), '')::uuid,
p_action,
p_entity,
p_entity_id,
p_details
);
END;
$$;
From there, I selectively call app.log_rls_event from triggers on sensitive tables or from key application paths when something is denied or looks suspicious. In my experience, having a few concrete audit trails tied to RLS context makes security reviews far smoother, and it significantly shortens the time to understand “why didn’t this user see that row?” when support tickets come in.
These advanced patterns take a bit more upfront design, but once in place, they let PostgreSQL row level security scale with the complexity of a real SaaS product instead of becoming a constraint you constantly fight against.
Verifying, Auditing, and Troubleshooting PostgreSQL Row Level Security
Systematically Verifying RLS Policies
Whenever I roll out PostgreSQL row level security in a multi‑tenant app, I treat verification as a testable contract, not a one‑time sanity check. I start by confirming RLS is actually active where I expect it:
-- Check that RLS is enabled and forced
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relkind = 'r' AND relname IN ('tenants', 'customers', 'invoices');
-- List policies on a table
SELECT * FROM pg_policies WHERE tablename = 'invoices';
Then I simulate real sessions with different tenant contexts. I’ve found a small regression test script in SQL goes a long way:
-- Act as tenant A
SELECT set_config('app.current_tenant', '1111...-tenant-a', true);
SELECT COUNT(*) FROM invoices; -- expect only tenant A rows
-- Act as tenant B
SELECT set_config('app.current_tenant', '2222...-tenant-b', true);
SELECT COUNT(*) FROM invoices; -- expect only tenant B rows
In my own CI pipelines, I like to assert explicit expectations (e.g., given seed data, tenant A should see 3 invoices and tenant B should see 5). That way, policy changes that accidentally broaden access fail builds before they reach production. Testing Your Database | Supabase Docs
Auditing Access and Detecting Potential Bypasses
RLS makes it harder to mount direct data‑leak bugs, but I still want visibility into who accessed what. I typically combine PostgreSQL’s logging with a light‑weight audit table so I can answer questions like “which tenant read this invoice last week?”
CREATE TABLE access_audit (
id bigserial PRIMARY KEY,
logged_at timestamptz NOT NULL DEFAULT now(),
tenant_id uuid,
user_id uuid,
table_name text NOT NULL,
action text NOT NULL,
row_id uuid
);
CREATE OR REPLACE FUNCTION app.audit_row_access()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO access_audit (tenant_id, user_id, table_name, action, row_id)
VALUES (
NULLIF(current_setting('app.current_tenant', true), '')::uuid,
NULLIF(current_setting('app.current_user', true), '')::uuid,
TG_TABLE_NAME,
TG_OP,
NEW.id
);
RETURN NEW;
END;
$$;
CREATE TRIGGER invoices_audit
AFTER INSERT OR UPDATE ON invoices
FOR EACH ROW EXECUTE FUNCTION app.audit_row_access();
On top of this, I periodically scan for roles with dangerous privileges like BYPASSRLS or SUPERUSER. One thing I learned the hard way was to keep the application role strictly non‑privileged and reserve elevated roles only for migrations and DBA work.
Using EXPLAIN, Logs, and Session Introspection to Debug RLS
Most RLS issues I debug in the field fall into two categories: users not seeing rows they expect, or performance regressions. For visibility bugs, I first check the active session context and policy definition:
-- Inspect current context
SELECT
current_setting('app.current_tenant', true) AS tenant_id,
current_setting('app.current_user', true) AS user_id;
-- Re-run the failing query with EXPLAIN
EXPLAIN (ANALYZE, VERBOSE)
SELECT id, amount_cents FROM invoices WHERE status = 'paid';
The EXPLAIN VERBOSE output shows the rewritten query, including the RLS predicates Postgres injected. When I first started using RLS, this view was essential to realizing a missing WITH CHECK clause or a typo in current_setting was silently dropping rows.
For performance tuning, I’ve seen RLS cause full table scans when the planner underestimates row counts. Running EXPLAIN ANALYZE with realistic session settings and then adding or adjusting indexes on tenant_id (and other policy‑relevant columns) has usually fixed those hotspots.
Common Pitfalls and How I Avoid Them
After a few real‑world deployments, I’ve noticed the same mistakes keep cropping up around PostgreSQL row level security:
Forgetting RLS on a new table: I use checklists and code review rules: if a table has tenant_id, it must have RLS and a policy.
Application role can bypass RLS: I always confirm the app role has neither SUPERUSER nor BYPASSRLS, and I set row_security = on at the role level.
Session state leaks with pooling: every request overwrites app.current_tenant and app.current_user before running queries; I never assume the connection is “clean.”
Owner account surprises: if the table owner runs queries, RLS can be bypassed unless FORCE ROW LEVEL SECURITY is enabled. In my setups, I almost always enable FORCE on tenant‑scoped tables.
Taking the time to bake these checks into migrations, CI tests, and observability has made RLS feel reliable instead of mysterious, and it’s given me much more confidence that tenant isolation remains intact even as the schema and policies evolve.
Conclusion and Next Steps for Hardening PostgreSQL Row Level Security
Key Takeaways from Implementing RLS in Multi‑Tenant SaaS
Working with PostgreSQL row level security across several SaaS projects, I’ve come to see it as a powerful but sharp tool: it can give you strong tenant isolation, but only if your role model, session context, and schema are all aligned. Every tenant‑scoped table needs a clear tenant_id, the application role must never bypass RLS, and the app has to set per‑request context (like app.current_tenant and app.current_user) consistently. Once that foundation is in place, advanced patterns—cross‑tenant admin, hierarchical access, and plan‑based feature gating—become much easier to layer on without losing control or clarity.
Practical Checklist and Next Steps
When I’m hardening a new or existing deployment, I walk through a simple checklist:
Identify all tenant‑scoped tables and ensure each has a tenant_id column and supporting indexes.
Enable RLS (and usually FORCE RLS) on those tables and define both USING and WITH CHECK policies.
Review roles to confirm the application role is non‑superuser, has no BYPASSRLS, and has row_security set to on.
Standardize how your app sets session context for tenant and user IDs on every request, even with pooling.
Wire in encryption at rest and TLS in transit so RLS sits inside a broader defense‑in‑depth story.
Add regression tests and basic auditing so policy changes can be verified and investigated over time.
From here, the next steps I usually recommend are to document your RLS patterns for your team, add CI checks that fail on missing policies for tenant tables, and periodically review logs and policies as your product grows. Done this way, PostgreSQL row level security becomes a stable foundation you can build on—not a fragile layer you’re afraid to touch.
Hi, I’m Cary Huang — a tech enthusiast based in Canada. I’ve spent years working with complex production systems and open-source software. Through TechBuddies.io, my team and I share practical engineering insights, curate relevant tech news, and recommend useful tools and products to help developers learn and work more effectively.
