Postgres Row-Level Security: 5 Policies That Actually Work

I've been called into the same incident twice: a multi-tenant app where "we have RLS" turned out to mean RLS was enabled and the application connected as the table owner. Zero policies ran. Both times the fix was one ALTER TABLE away, and both times nobody had a test that would have caught it.

📖 Read the full guide: Postgres Row-Level Security: Policies That Actually Work

Postgres row-level security is a small rulebook. Deny by default once enabled. Permissive policies OR together, restrictive ones AND. USING filters what you can see, WITH CHECK validates what you write. And three roles walk straight past all of it: superusers, anything with BYPASSRLS, and the table owner unless you force it.

I walked through these five policies on the whiteboard in the companion video; this post is the copy-paste version with the edge cases the video didn't have room for.

TL;DR

• ENABLE ROW LEVEL SECURITY means deny by default. No policy, no rows, no error.
• The table owner bypasses its own policies until you add FORCE ROW LEVEL SECURITY.
• Superuser and BYPASSRLS always bypass. FORCE does not override that.
• Permissive policies combine with OR; restrictive with AND. Restrictive policies alone grant nothing.
• USING applies to existing rows (SELECT/UPDATE/DELETE), WITH CHECK to new row contents (INSERT/UPDATE). Omit WITH CHECK on UPDATE and the USING expression gets applied to the new row too — that's a fallback, not a guarantee you should rely on.
• RLS sits on top of GRANTs, not instead of them.
• Index the columns your policies filter on, and EXPLAIN as the app role, because as superuser the quals vanish.
• GUC-based tenancy (current_setting('app.tenant_id')) stops application bugs. It does not stop someone who can run arbitrary SQL on that connection.

The 90 seconds of setup that decide whether RLS does anything

Roles first. One role owns the schema, a different role runs the app.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app LOGIN PASSWORD 'redacted' NOBYPASSRLS;
CREATE ROLE support LOGIN PASSWORD 'redacted' NOBYPASSRLS;

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  customer    text   NOT NULL,
  amount_cents bigint NOT NULL,
  deleted_at  timestamptz
);
ALTER TABLE orders OWNER TO app_owner;

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app;
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app;
GRANT SELECT ON orders TO support;

That GRANT line matters. RLS narrows what a privilege reaches; it never hands out a privilege. A role with no SELECT grant gets a permission error regardless of how generous your policies are.

Now the switch, and the second switch people forget:

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

ENABLE turns policies on for everyone except the exempt roles. FORCE adds the table owner to the list of roles that must obey. If your migration tool, your background jobs, or your app connect as the owner, ENABLE alone buys you nothing.

Sanity check, run it as the role your app actually connects with:

SELECT rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user;

 rolsuper | rolbypassrls
----------+--------------
 f        | f

Two f values or you are not testing RLS, you are testing your own optimism.

Postgres RLS multi-tenant isolation on one shared table

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING      (tenant_id = current_setting('app.tenant_id', true)::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

The true second argument to current_setting is doing real work. Without it, an unset app.tenant_id raises an error. With it, you get NULL, the comparison yields NULL, and no rows qualify. Fails closed, which is what you want when a connection escapes the pooler without setup.

Usage:

BEGIN;
SET LOCAL app.tenant_id = '42';
SELECT count(*) FROM orders;
COMMIT;

SET LOCAL scopes the value to the transaction. Under PgBouncer transaction pooling, a plain SET sticks to the server connection after your transaction ends and the next client inherits it. I have watched tenant 7 read tenant 3's dashboard because of exactly one missing LOCAL. Wire the SET LOCAL into your pool checkout or your ORM's transaction hook, never into "connection init".

Now the honest part. app.tenant_id is a customized option, and any session can change it with a plain SET. If an attacker reaches arbitrary SQL execution on that connection, they set the GUC to 1 and read tenant 1. This design defends against forgotten WHERE clauses, a misconfigured ORM scope, a raw SQL report someone pasted in. Against SQL injection it does nothing at all.

If you need the stronger version, two options. Set the tenant from the pooler or a SECURITY DEFINER login function that derives it from a signed token, so the value never comes from client-controlled SQL. Or issue one database role per tenant and write the policy against current_user, which the session cannot change without the password.

Per-user rows, and the WITH CHECK you forgot

CREATE TABLE notes (id bigserial PRIMARY KEY, owner name NOT NULL, body text);
ALTER TABLE notes OWNER TO app_owner;
GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO app;
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
ALTER TABLE notes FORCE ROW LEVEL SECURITY;

CREATE POLICY notes_owner ON notes
  FOR ALL
  USING      (owner = current_user)
  WITH CHECK (owner = current_user);

Here's the failure mode I've seen twice in code review — someone writes only USING and skips WITH CHECK:

CREATE POLICY notes_owner_broken ON notes
  FOR ALL
  USING (owner = current_user);

For UPDATE, if WITH CHECK is omitted, Postgres reuses the USING expression against the new row. That sounds safe until you notice what it actually verifies: the row you started with, not the row you're leaving behind. I can see my row, and I can update it to say owner = 'alice', giving away a row I no longer control. Don't rely on the fallback as your actual defense — write WITH CHECK explicitly whenever ownership is a mutable column.

With the correct policy above, the violation looks like this:

app=> UPDATE notes SET owner = 'alice' WHERE id = 9;
ERROR:  new row violates row-level security policy for table "notes"

SQLSTATE 42501, insufficient_privilege. Same error you get on a cross-tenant insert:

app=> BEGIN; SET LOCAL app.tenant_id = '42';
app=> INSERT INTO orders (tenant_id, customer, amount_cents) VALUES (43, 'acme', 1000);
ERROR:  new row violates row-level security policy for table "orders"

	SELECT	INSERT	UPDATE	DELETE
USING	filters visible rows	not used	picks which rows may be updated	picks which rows may be deleted
WITH CHECK	not used	validates the new row	validates the resulting row (falls back to USING)	not used
PERMISSIVE	OR'd with other permissive policies
RESTRICTIVE	AND'd on top of the permissive result

Admin override, because permissive policies OR

Never edit the tenant policy to carve out exceptions. Add a second one.

CREATE POLICY support_read_all ON orders
  FOR SELECT
  TO support
  USING (true);

A policy with no TO clause applies to PUBLIC. Scoping to TO support means the support role gets tenant_isolation OR support_read_all, which is true, while the app role is unaffected. If you'd rather not name roles in the policy, USING (pg_has_role(current_user, 'support', 'member')) gets you the same effect through group membership.

Check your work:

SELECT policyname, permissive, roles, cmd, qual
FROM pg_policies WHERE tablename = 'orders';

   policyname    | permissive | roles     |  cmd   | qual
-----------------+------------+-----------+--------+---------------------------------
 tenant_isolation| PERMISSIVE | {public}  | ALL    | (tenant_id = (current_setting(...
 support_read_all| PERMISSIVE | {support} | SELECT | true

\d+ orders shows the same thing in psql if you prefer.

Team rows via a membership table

CREATE POLICY doc_membership ON documents
  FOR SELECT
  USING (EXISTS (
    SELECT 1 FROM doc_members m
    WHERE m.doc_id = documents.id
      AND m.user_id = current_setting('app.user_id', true)::bigint
  ));

This one reads well and plans badly if you skip the index. As the app role:

Seq Scan on documents  (cost=0.00..48210.00 rows=3333 width=64)
                       (actual time=0.4..612.9 rows=41 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 99959
  Buffers: shared hit=812 read=41520
  SubPlan 1
    ->  Seq Scan on doc_members m ...

Add the index the subquery actually wants:

CREATE INDEX ON doc_members (user_id, doc_id);

The subplan turns into an index-only lookup and the runtime drops off a cliff. When the join stays hot at scale, the escape hatch is denormalization: carry a team_id on documents and write the policy against that column, keeping the membership table for the UI.

Restrictive policies for soft delete and an append-only log

CREATE POLICY hide_deleted ON orders
  AS RESTRICTIVE
  FOR ALL
  USING (deleted_at IS NULL);

Restrictive policies AND with everything else, so no future permissive policy, including one added at 2am during an incident, can expose tombstoned rows. Remember the rule: a table with only restrictive policies grants nobody anything, because access still requires at least one permissive policy to pass.

Write-only audit log, same idea from the other direction:

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log FORCE ROW LEVEL SECURITY;
GRANT INSERT ON audit_log TO app;

CREATE POLICY audit_append_only ON audit_log
  FOR INSERT WITH CHECK (true);

No SELECT/UPDATE/DELETE policy means the app role can write and never read back. One warning: there is no TRUNCATE policy in RLS at all. Any role holding the TRUNCATE privilege empties the table regardless. Don't grant it.

Postgres RLS performance: what it actually costs

Three real costs, in the order they bite.

The invisible predicate needs an index. Every query against orders now carries tenant_id = ..., so your indexes should lead with tenant_id: (tenant_id, created_at), not (created_at).

Policy expressions are security-barrier conditions. Any user-supplied condition using a non-leakproof function or operator is evaluated only after the policy quals, which can block an index or pushdown the planner would otherwise use. Plans get worse in ways that look mysterious until you remember this.

The GUC lookup is a per-row function call. Wrap it in a scalar subquery so it becomes a one-time InitPlan:

ALTER POLICY tenant_isolation ON orders
  USING      (tenant_id = (SELECT current_setting('app.tenant_id', true)::bigint))
  WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id', true)::bigint));

Index Scan using orders_tenant_created_idx on orders
  Index Cond: (tenant_id = $0)
  InitPlan 1 (returns $0)
    ->  Result

Always EXPLAIN (ANALYZE, BUFFERS) as the app role. Run it as superuser and the quals disappear, and you tune a query that never runs in production. This is also where a second set of eyes helps — I've had a MyDBA review catch a missing composite index on a policy column before it turned into a slow-query page at 2am.

Edge cases that bite

• A plain view over an RLS table executes with the view owner's permissions, so the owner's policies apply, not the caller's. PostgreSQL 15 added CREATE VIEW ... WITH (security_invoker = true) to flip that.
• SECURITY DEFINER functions run as the function owner. If the owner bypasses RLS, so does the function. Useful on purpose, dangerous by accident.
• Foreign key and unique constraint checks always bypass row security. A duplicate-key error can confirm a row exists that the querying role cannot see.
• COPY orders TO applies SELECT policies for a non-exempt role, so an "export everything" script quietly exports a subset.
• pg_dump sets row_security = off and errors out if the dumping role cannot bypass RLS. Passing --enable-row-security makes the dump succeed and contain only visible rows, which is an incomplete backup. Document which role takes backups.
• With row_security = off, a query that needs a policy errors rather than silently returning fewer rows. That's a feature for batch jobs.
• Policies are per-table. A policy on the partitioned parent does not protect a partition queried directly by name, and the same goes for inheritance children.
• Logical replication operates at the WAL level and ignores RLS entirely — a publication built off an RLS-protected table replicates every row to the subscriber, policies or not. Don't assume a replica inherits your row security.

RLS landed in 9.5 and AS RESTRICTIVE in 10, so all of this works on every supported major version.

Pre-flight checklist

1. App role: rolsuper = f, rolbypassrls = f, verified in production.
2. FORCE ROW LEVEL SECURITY on every table the owner role touches.
3. At least one permissive policy for every command the app issues.
4. SET LOCAL wired into the pooler or transaction hook, never SET.
5. Composite indexes led by the policy column.
6. A CI test asserting tenant B sees zero of tenant A's rows.
7. Backup role documented, with its RLS exemption explicit.
8. Migration role documented (it usually needs to bypass; know that it does).
9. No TRUNCATE grant on RLS-protected tables.
10. Views audited for security_invoker, functions audited for SECURITY DEFINER.

Test it or it isn't real

SET ROLE app;
BEGIN;
  SET LOCAL app.tenant_id = '1';
  SELECT count(*) = 3 AS tenant1_ok FROM orders;
  SET LOCAL app.tenant_id = '2';
  SELECT count(*) = 0 AS no_leak_ok FROM orders WHERE customer = 'tenant1-acme';
  SAVEPOINT s;
  INSERT INTO orders (tenant_id, customer, amount_cents) VALUES (1, 'x', 1);
  -- expect: ERROR: new row violates row-level security policy for table "orders"
  ROLLBACK TO SAVEPOINT s;
COMMIT;
RESET ROLE;

Run it in CI on every migration. The failure mode I keep meeting: a policy that was fine on the day it shipped, then quietly stopped applying six months later when someone changed the connection role and nobody re-ran this test.

---

Tags: postgres database sql security ## Wrapping up

Row-level security earns its keep the moment you stop treating it as a checkbox and start treating it as code: it needs the same review, the same indexes, and the same tests as anything else that decides who sees what. The five policies above cover almost every shape of multi-tenant, per-user, and append-only access I've run into, and the edge cases are the ones that turn "we have RLS" into an incident report. Enable it, force it, index it, and — most importantly — write the CI test that fails loudly when someone quietly breaks it later.

If you want a second set of eyes on whether your policies are actually doing what you think, checks like the ones in MyDBA are a fast way to catch a missing FORCE, an unindexed policy column, or a role that shouldn't have BYPASSRLS before it becomes a postmortem.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-row-level-security-multi-tenant

If this saved you a debugging session, give your RLS setup a quick audit with MyDBA — it's a lot cheaper than the incident.