Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 4 · Lesson 29 · 6 min read

PostgreSQL Row-Level Security (RLS) Explained

On this page
  1. Why row-level security
  2. Step 1: Turn on RLS and write a policy
  3. Step 2: Test it as the application role
  4. Step 3: RLS behind PgBouncer transaction pooling
  5. Step 4: pg_hba.conf and TLS with sslmode=verify-full
  6. Check yourself

Why row-level security

In a multi-tenant SaaS app, every query is supposed to include WHERE tenant_id = .... Hundreds of queries get it right. Then one new endpoint, one report or one quick fix in psql forgets it, and customer A sees customer B's invoices. It only takes one.

Row-level security (RLS) moves that filter into the database. You write a policy once per table, and PostgreSQL adds it to every query on that table, whoever wrote the query and however it reaches the database.

Step 1: Turn on RLS and write a policy

SQL
CREATE TABLE projects (
  project_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  name        text NOT NULL
);
CREATE INDEX ON projects (tenant_id);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING      (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

The two clauses do different jobs:

  • USING decides which existing rows a query can see, update or delete.
  • WITH CHECK decides which rows may be written, so a tenant can't insert or update a row into another tenant's id. If you leave it out, PostgreSQL applies the USING expression to new rows as well. Writing it out makes the rule obvious, and it matters when the two need to differ.

The policy reads the current tenant from a custom setting, app.tenant_id, which your application sets after it authenticates the user. Index tenant_id, because the policy adds that filter to every query.

Step 2: Test it as the application role

Here's the trap almost everyone falls into: RLS doesn't apply to superusers or to the table's owner. If you test as postgres, every policy appears to work, because none of them run. Create the role your application will use and test as that role:

SQL
CREATE ROLE app_user LOGIN PASSWORD 'change-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;

INSERT INTO projects (tenant_id, name) VALUES (7, 'Apollo'), (8, 'Zephyr');   -- as the owner

SET ROLE app_user;
SET app.tenant_id = '7';
SELECT tenant_id, name FROM projects;               -- only Apollo
INSERT INTO projects (tenant_id, name) VALUES (8, 'Sneaky');
-- ERROR: new row violates row-level security policy for table "projects"
RESET ROLE;

Tenant 7 sees only its own row and can't write rows for tenant 8, even with no WHERE clause anywhere.

What if the application forgets to set the tenant? Then current_setting('app.tenant_id') raises an error, so the query fails instead of returning everyone's rows. That's the behavior you want: fail closed. Three more rules:

  • Enabling RLS with no policies means non-owners see zero rows, which is also fail-closed.
  • If the application connects as the table owner, add ALTER TABLE projects FORCE ROW LEVEL SECURITY so policies apply to the owner too. Better still, don't let the application own its tables.
  • Roles with the BYPASSRLS attribute skip all policies. Don't hand it out for convenience. If a reporting role needs to read every tenant, give it its own policy: CREATE POLICY reporting_read ON projects FOR SELECT TO reporting USING (true);.

Step 3: RLS behind PgBouncer transaction pooling

Each PostgreSQL connection is a process (Day 2), so busy applications put a pooler such as PgBouncer in front. In its common transaction mode, a client gets a server connection only for the length of one transaction. The next transaction may run on a different connection.

That collides with RLS. A plain SET app.tenant_id sticks to the server connection, so it can leak to whichever client uses that connection next. Set the tenant inside each transaction instead, so it's cleared automatically at COMMIT:

SQL
SET ROLE app_user;
BEGIN;
SELECT set_config('app.tenant_id', '7', true);   -- true = this transaction only, like SET LOCAL
SELECT name FROM projects;                        -- Apollo
COMMIT;
RESET ROLE;

Pooling modes in brief:

ModeServer connection held forWatch out for
SessionThe whole client sessionLittle reuse, so it barely reduces connections
TransactionOne transactionSession-level SET, LISTEN and advisory locks don't carry over. Prepared statements need PgBouncer 1.21+ with max_prepared_statements set.
StatementOne statementMulti-statement transactions are impossible, so it's rarely used

Step 4: pg_hba.conf and TLS with sslmode=verify-full

RLS limits what a connected role can see. Two more layers decide who can connect at all, and whether anyone can read the traffic.

pg_hba.conf (Day 1) is checked top to bottom, and the first matching line wins:

Code
# TYPE    DATABASE  USER      ADDRESS        METHOD
local     all       postgres                 peer
hostssl   appdb     app_user  10.0.1.0/24    scram-sha-256
host      all       all       0.0.0.0/0      reject

Use scram-sha-256 for passwords. md5 is a weaker legacy method, and trust means no password at all. Keep address ranges as narrow as you can. hostssl matches only encrypted connections, so the final reject line refuses everything else.

TLS encrypts connections. On the server, set ssl = on with a certificate and key (ssl_cert_file, ssl_key_file). On the client, the sslmode setting matters more than people think:

Shell
psql "host=db.example.internal dbname=appdb user=app_user sslmode=verify-full"

sslmode=require encrypts but doesn't check who's on the other end, so an attacker in the middle can still pose as your server. Only verify-full checks that the certificate is valid and matches the hostname. Use it for any connection that crosses a network you don't control.

Check yourself

Superusers and table owners bypass RLS, so the policies never ran. Test as the real, unprivileged application role, using SET ROLE or a separate login.

In transaction mode, later transactions can run on server connections whose tenant setting was left by a different client. Set the tenant in every transaction with set_config('app.tenant_id', ..., true) or SET LOCAL.

Add a read-only policy just for that role, CREATE POLICY reporting_read ON projects FOR SELECT TO reporting USING (true). It lets that role read all rows without granting any extra write access, and the exception is visible in the table's policies, not hidden in a role attribute.

require encrypts the connection but doesn't check who's on the other end, so a man-in-the-middle could present their own certificate. verify-full checks the certificate against a trusted authority and that the hostname matches, so the client knows it's talking to the real server.

No. Table owners and superusers bypass RLS unless you run ALTER TABLE ... FORCE ROW LEVEL SECURITY. That's one more reason the application shouldn't connect as the owner, and why tests must run as the real application role.