Postgres Row-Level Security in Practice

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Postgres row-level security (RLS) lets the database itself decide which rows a query can see or modify, instead of relying on the application to add WHERE tenant_id = $1 to every statement. It is the difference between “we will remember to filter” and “we cannot forget to filter, because the planner does it for us.” For multi-tenant SaaS, internal tools, and any system where one Postgres instance serves users who must not see each other’s data, RLS is the right primitive — but the details are easy to get wrong.

This post is a practical walkthrough of how Postgres RLS works, the multi-tenant patterns that hold up under real traffic, and the pitfalls that silently bypass your policies.

In this post, we’ll cover:

• What RLS is, exactly - the mental model and where it sits in the query path
• Enabling RLS and writing your first policy - USING vs WITH CHECK
• The session-context multi-tenant pattern - SET LOCAL plus current_setting
• Roles, BYPASSRLS, and FORCE ROW LEVEL SECURITY - who escapes your policies
• Performance - how RLS interacts with indexes and the planner
• Pitfalls - the cases where policies look enforced but are not

What Postgres RLS actually does

Row-level security is a feature of the PostgreSQL planner that rewrites every query against an RLS-enabled table to add an extra predicate. When a user runs SELECT * FROM documents, Postgres internally turns it into something close to SELECT * FROM documents WHERE <your policy expression>. Inserts and updates pick up an analogous check on the new row.

The rewrite happens before planning, so the predicate participates in plan selection — a policy that reduces to tenant_id = 42 can use an index on tenant_id exactly like a hand-written WHERE clause. RLS is not a post-filter applied after rows are fetched; it changes the query itself.

The important consequences are:

• Policies live in the database, not the application. A new client (a script, a BI tool, a QueryPlane connection) cannot forget to apply them.
• The check runs as the role that issued the query. If you connect as the table owner, RLS does not apply by default — see the BYPASSRLS section below.
• Policies are SQL expressions, so they can reference columns, current role, session settings, and any other function the planner can evaluate.

Enabling RLS and your first policy

There are two steps. First, enable RLS on the table:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

This switches the table into a “deny by default” mode for non-owner roles. Until you add a policy, queries against the table return zero rows for everyone except the owner. That is a feature: forgetting to add a policy fails closed instead of silently exposing data.

Second, create a policy. A simple per-user policy might look like:

CREATE POLICY documents_owner_select
ON documents
FOR SELECT
USING (owner_id = current_setting('app.current_user_id')::bigint);

The USING clause is the predicate Postgres adds to SELECT, UPDATE, and DELETE. A row is visible if the expression evaluates to TRUE for that row.

Insert and update statements also need a WITH CHECK clause that runs against the new row:

CREATE POLICY documents_owner_modify
ON documents
FOR ALL
USING (owner_id = current_setting('app.current_user_id')::bigint)
WITH CHECK (owner_id = current_setting('app.current_user_id')::bigint);

Without WITH CHECK, a user could UPDATE documents SET owner_id = <someone else> and the row would silently leave their visibility — a hard-to-debug “where did my row go” bug. Always set WITH CHECK when you write a policy that covers writes.

The session-context multi-tenant pattern

Most multi-tenant Postgres apps want the same thing: every query a tenant runs should be implicitly scoped to their tenant_id. The clean pattern is:

1. Add a tenant_id column to every tenant-owned table, with a NOT NULL constraint and a foreign key to tenants(id).
2. Enable RLS and write a policy that compares tenant_id to a session-local setting.
3. Set that session-local setting in the connection’s per-request handler, before any tenant SQL runs.

The policy looks like this:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY invoices_tenant_isolation
ON invoices
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

The application sets the context at the start of each request:

-- inside a request-scoped transaction
SET LOCAL app.tenant_id = '4f9e2b...uuid';

-- now every query is automatically tenant-scoped
SELECT * FROM invoices WHERE status = 'open';

SET LOCAL is critical here. It scopes the setting to the current transaction, so even if your application server pools connections (most do), a leaked setting cannot bleed across requests. When the transaction ends — commit, rollback, or connection return to the pool — the setting reverts.

If you forget LOCAL and use plain SET app.tenant_id = ..., the setting persists for the rest of the connection’s life, which means the next request to grab that pooled connection inherits the previous tenant’s context. This is the single most common way teams accidentally break tenant isolation. Always SET LOCAL. Always inside a transaction.

For the same reason, never call SET in a connection pool’s on_acquire hook with values from the request — set it inside the transaction your handler runs.

Roles, BYPASSRLS, and FORCE ROW LEVEL SECURITY

By default, two kinds of roles ignore RLS entirely:

• The table’s owner.
• Any role with the BYPASSRLS attribute (and superusers, which implicitly have it).

This is intentional: the role that ran the migration to create the table needs to be able to maintain it without writing a policy that allows itself. But it also means production application connections must not run as the table owner — otherwise every policy you write is silently a no-op.

The recommended setup is two roles:

-- runs migrations, owns schema
CREATE ROLE app_owner;
GRANT ALL ON SCHEMA public TO app_owner;

-- runs application queries, no DDL
CREATE ROLE app_user NOINHERIT;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Application servers connect as app_user. Migration jobs connect as app_owner. RLS applies to app_user queries; app_owner retains full access for admin and migration purposes.

If you want RLS to apply even to the owner — a stronger guarantee that catches mistakes in admin scripts and ad-hoc queries — use FORCE ROW LEVEL SECURITY:

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

With FORCE enabled, the owner sees the same filtered view as everyone else. This is a good default for tables holding user data, where there is rarely a legitimate reason for the owner role to read across tenants.

The opposite knob is BYPASSRLS, granted at the role level:

ALTER ROLE analytics_reader BYPASSRLS;

Reserve BYPASSRLS for roles that actually need cross-tenant reads — analytics ETL, support tooling running with admin context, the read replica that feeds your data warehouse. Granting it to the application role defeats the entire feature.

Performance and indexing under RLS

Because RLS rewrites the query, the policy expression is just another predicate. The planner sees:

SELECT * FROM invoices WHERE tenant_id = current_setting('app.tenant_id')::uuid AND status = 'open';

If you have an index on (tenant_id, status), the planner uses it. If you only have an index on status, the planner has to filter on tenant_id after the index lookup. RLS does not break index usage, but it changes which indexes pay off.

A few practical guidelines:

The first column of any index on a tenant-owned table should be tenant_id. This matches the leading predicate that RLS adds to every query. Composite indexes like (tenant_id, created_at DESC) and (tenant_id, status, updated_at) are usually the right shape.

If the policy expression involves a function call — current_user, current_setting, or a custom helper — Postgres re-evaluates it once per query, not once per row, when the function is marked STABLE or IMMUTABLE. The standard current_setting is STABLE, so this works correctly. If you wrap the lookup in a custom function, mark it STABLE explicitly:

CREATE FUNCTION current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS
$$ SELECT current_setting('app.tenant_id')::uuid $$;

Without STABLE, the planner has to assume the function may return different values per row and falls back to a sequential scan. This is the second most common cause of “RLS made my queries slow.”

Run EXPLAIN ANALYZE against any query that materially changes shape after enabling RLS. The plan should still show an Index Scan on the tenant-leading index, with the policy predicate folded into the index condition rather than appearing as a separate filter.

Pitfalls and gotchas

Policies stack with OR, not AND

If a table has multiple policies for the same command (multiple FOR SELECT policies, for example), Postgres combines them with OR. A row is visible if any policy allows it. This is the opposite of how most authorization systems work.

The pattern that bites people:

CREATE POLICY users_can_read_own ON documents
FOR SELECT USING (owner_id = current_setting('app.user_id')::bigint);

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

A regular user who somehow influences app.role to be 'admin' now sees everything. If you want all conditions to apply, combine them in a single policy with AND. If you want different roles to have different visibility, gate the policies with TO role_name so they only apply to the right role.

current_setting raises on missing keys

current_setting('app.tenant_id') raises an error if the key was never set. In production, a request that forgets to set the context fails loud — usually the right behavior. But the same query run interactively, on a connection that never had context set, fails with unrecognized configuration parameter, which is a confusing first encounter with RLS.

Use the two-argument form when you want a default instead of an error:

USING (tenant_id = current_setting('app.tenant_id', true)::uuid)

The true second argument makes Postgres return NULL for missing keys. The policy then naturally evaluates to NULL (treated as false), so the row is hidden — which is exactly the deny-by-default behavior you want.

pg_dump and logical replication see all rows or no rows

pg_dump uses a connection that is typically the table owner. With FORCE ROW LEVEL SECURITY enabled, your dump may export zero rows for tenant tables, because no app.tenant_id is set. There are two correct fixes: dump as a BYPASSRLS role, or temporarily disable FORCE for the dump. Always test your backup pipeline against an RLS-enabled table before it is the only copy of your data.

The same warning applies to logical replication slots and CDC tools. The replication user needs BYPASSRLS (or to be granted explicit policies that allow cross-tenant reads), or downstream consumers will receive an empty stream.

Functions run with the caller’s policies, except when they don’t

A SECURITY DEFINER function runs as its owner, which means RLS evaluates the owner’s policies, not the caller’s. This is occasionally what you want — a stored procedure that needs to read across tenants for an aggregate calculation — and almost never what you want for general-purpose code. Audit every SECURITY DEFINER function you write against an RLS-protected table; it is the most common way to accidentally hand out cross-tenant access.

RLS does not protect against side-channel inference

A policy hides rows; it does not hide the fact that rows exist. A user can still observe sequence gaps in id, total row counts via EXPLAIN, or timing differences in queries that touch hidden rows through indexes. For most applications this is acceptable; for high-sensitivity data, plan side-channel mitigations separately. RLS is a strong access control, not a covert-channel-proof one.

Try the policies in your browser

You can paste any of the policies above into our free SQL playground and watch the row-filtering behavior on a real PostgreSQL instance — no account or local setup needed. For longer policies that reference multiple session settings, the PostgreSQL syntax checker catches expression and casting mistakes before they hit production.

For the full workflow — write SQL with AI against your real Postgres, run it under your application’s RLS-enabled role, and turn the results into a shareable internal tool — connect a database to QueryPlane. QueryPlane respects whatever role and session context you connect with, so RLS-protected tables stay RLS-protected when team members run ad-hoc queries through the UI.

Frequently asked questions

What is row level security in Postgres? Row level security (RLS) is a Postgres feature that restricts which rows a user can see or modify, evaluated at query time by the database itself. You enable it with ALTER TABLE ... ENABLE ROW LEVEL SECURITY and define one or more CREATE POLICY rules. Once enabled, every SELECT, INSERT, UPDATE, and DELETE on the table is automatically filtered through the policies — application code does not need to add WHERE tenant_id = ... to every query.

What is the difference between USING and WITH CHECK in a Postgres RLS policy? USING is the visibility predicate — it controls which existing rows a query can read or target for UPDATE/DELETE. WITH CHECK is the write predicate — it controls which new or modified rows the user is allowed to insert. If you only specify USING, Postgres uses the same expression for WITH CHECK on UPDATE but not on INSERT. For multi-tenant safety, write both explicitly so a user cannot insert rows for a tenant they cannot read.

Does RLS apply to the table owner? Not by default. The table owner and any superuser bypass RLS unless you run ALTER TABLE ... FORCE ROW LEVEL SECURITY. This is the most common production pitfall: the application connects as a role that owns the table, RLS is enabled but not forced, and policies are silently ignored. Always run the application as a non-owner role with no BYPASSRLS attribute, and add FORCE ROW LEVEL SECURITY if the application role does happen to own the table.

How do I pass the current tenant to an RLS policy? The standard pattern is SET LOCAL app.current_tenant = '...' at the start of every transaction, then reference current_setting('app.current_tenant', true) inside the policy expression. The true second argument makes the function return NULL when the setting is missing rather than raising — which lets you write a policy that fails closed (returns zero rows) when the application forgets to set the context.

Does RLS slow down queries? RLS can be free or expensive depending on how policies are written. A simple tenant_id = current_setting(...) predicate on an indexed column gets pushed into the index scan and adds no measurable cost. Policies that wrap subqueries, call non-STABLE functions, or fail to match the leading column of an index degrade to sequential scans on the protected table. Always run EXPLAIN ANALYZE after enabling RLS and verify that the planner still picks an index.

Can I bulk-load data into a table with RLS enabled? Yes, but you usually want to bypass RLS for the loader. Either COPY as the table owner (and skip FORCE ROW LEVEL SECURITY), or grant BYPASSRLS to a dedicated loader role and revoke it from the application role. pg_dump and logical replication slots see all rows or no rows depending on which role they run as — make sure your backup role has BYPASSRLS so dumps are complete.

Wrapping up

Postgres RLS is the right primitive for tenant isolation, customer-facing internal tools, and any application where the cost of a forgotten WHERE clause is unacceptable. The mechanics are simple — ALTER TABLE ... ENABLE ROW LEVEL SECURITY, write a policy with USING and WITH CHECK, set a session variable per request — but the surrounding details (role separation, FORCE ROW LEVEL SECURITY, BYPASSRLS, SET LOCAL, index leading columns) are where production deployments succeed or quietly fail.

When you put RLS in, prove it works with two tests: a query as the application role with the wrong tenant context returns zero rows, and EXPLAIN ANALYZE for a normal request still shows an index scan on a tenant-leading index. If both are true, the policy is real; if either fails, the policy is theater. Re-run those checks after every migration that adds a new table with tenant data, because the failure mode is silent.