Most multi-tenant SaaS tutorials stop at schema separation. PostgreSQL's Row-Level Security offers a leaner, more scalable path — here's how to implement, test, and audit it properly.

How to Implement Row-Level Security in PostgreSQL for Multi-Tenant Apps

Schema-per-tenant feels safe until you're managing 400 migration scripts and your DevOps bill climbs to match. There is a better default for most SaaS products: a single shared schema, with PostgreSQL's built-in Row-Level Security (RLS) ensuring that every tenant sees exactly their data — nothing more, nothing less.

This guide walks through enabling RLS, writing policies that hold under complex queries, and auditing them so a JOIN can never leak a row to the wrong tenant.

Why Shared Schema + RLS Beats Schema Separation at Scale

Schema-per-tenant works well at a very small tenant count. Beyond roughly 50–100 active tenants, the operational cost compounds:

Migrations must run per-schema, turning a one-line ALTER TABLE into an orchestration job.
Connection poolers like PgBouncer become harder to tune when each tenant maps to its own schema search path.
Backup and restore granularity sounds appealing until you realise it also means 400 recovery runbooks.

RLS moves the isolation logic into the database itself, where it belongs. The application layer passes a tenant context; the database enforces the boundary. If your app code has a bug and runs the wrong query, PostgreSQL still refuses to return the wrong rows.

Step 1: Add a Tenant Column and Enable RLS

Every table in a shared-schema multi-tenant design needs a tenant_id column. Use a UUID — it avoids enumeration attacks and scales across distributed systems.

-- Create a representative table
CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL,
  customer    TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner (critical — skip this and owners bypass all policies)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is the line most tutorials omit. Without it, any role that owns the table — including a superuser-adjacent application role — skips every policy silently.

Step 2: Set the Tenant Context Per Session

PostgreSQL provides SET LOCAL and current_setting() as a lightweight way to pass arbitrary session variables. Use a custom GUC (Grand Unified Configuration) namespace:

-- At the start of every transaction, your app sets:
SET LOCAL app.current_tenant = '8f14e45f-ceea-467a-a866-4b5b5ead7f1b';

Your connection pool must call this inside a transaction, not just at connection open time. With SET LOCAL, the variable resets automatically when the transaction ends — so a pooled connection cannot bleed tenant context into the next request.

Step 3: Write the RLS Policy

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

USING clauses filter rows on read (SELECT, UPDATE, DELETE). If you also need to prevent a tenant from inserting rows under another tenant's ID, add a WITH CHECK clause:

CREATE POLICY tenant_isolation ON orders
  USING      (tenant_id = current_setting('app.current_tenant')::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::UUID);

Now reads, writes, and updates are all scoped. A single policy covers the full lifecycle of a row.

Step 4: Create a Restricted Application Role

Never connect your application as a superuser or the table owner. Create a dedicated role with no ability to bypass RLS:

CREATE ROLE app_user NOINHERIT LOGIN PASSWORD 'strongpassword';
GRANT CONNECT ON DATABASE yourdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
-- Do NOT grant BYPASSRLS

When app_user runs any query against orders, the tenant_isolation policy fires unconditionally.

Step 5: Verify RLS Holds Across JOINs

This is where many implementations have undetected gaps. A common mistake is enabling RLS on one table but leaving a related table unprotected. If order_items is not covered and you JOIN it to orders, an attacker who can influence a JOIN condition might still read unintended rows through the unprotected side.

Rules of thumb:

Enable RLS on every table that carries tenant_id.
For lookup/reference tables shared across tenants (e.g., product_catalog), either keep them in a separate schema with read-only access, or add an explicit permissive policy that allows all authenticated roles to read them.
Use EXPLAIN (ANALYZE, BUFFERS) and inspect the query plan — you should see a Filter or Index Cond referencing tenant_id on every table scan.

To run a quick sanity check in development, create a second test tenant, insert rows for both, set the session variable to Tenant A, and assert that a wildcard SELECT returns zero rows for Tenant B. Automate this as a database-level integration test in your CI pipeline.

Step 6: Audit and Monitor Policy Effectiveness

RLS policies are stored in pg_policies. Query it regularly as part of your security review:

SELECT tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public';

Beyond structural audits, instrument your application to log any query that raises a current_setting error — this surfaces cases where a request reached the database without setting the tenant context, which is a misconfiguration you want to catch in staging, not production.

Consider adding a trigger-based audit log table that records tenant_id, the acting role, and a timestamp on every UPDATE and DELETE. This gives you forensic capability if a tenant ever raises a data concern.

Handling Super-Admin and Internal Tooling Access

Your own engineering team will occasionally need to query across all tenants — for support, analytics, or migrations. The right pattern is a separate admin_role that is granted BYPASSRLS, used only through a locked-down internal tool, and never shared with the application connection pool. Log every query this role executes. Treat cross-tenant access as an elevated privilege that requires justification, not a default developer convenience.

Why This Matters for Your Project

If you are building a SaaS product and you are still in the early stages of choosing a data architecture, defaulting to shared schema with RLS gives you a codebase that stays lean as your tenant count grows from ten to ten thousand. The enforcement sits at the database layer — independent of your ORM, your framework, and your application code — which means it remains in force even as your team rotates and your stack evolves. At Code!nk Technologies, this is the pattern we reach for when designing multi-tenant backends that need to scale without accumulating operational debt. Getting the data isolation right at the foundation is always cheaper than retrofitting it later.