Row-Level Security

Policies applied by the database

Row-level security (RLS) adds policies that filter which rows a role can see or change, enforced by the database on every query. A common multi-tenant pattern sets the current user or tenant in a session setting and writes policies comparing a column to it. RLS does not apply to table owners and superusers by default (use FORCE ROW LEVEL SECURITY for owners), so connect applications with a separate role. Test policies carefully, including for writes (WITH CHECK).

Notes visible only to their owner, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. With RLS enabled and a policy comparing owner to the app.username setting, the app_user role sees only Asha's two notes when app.username is asha, and only Ravi's note when it is ravi.

CREATE TABLE notes (id serial PRIMARY KEY, owner text NOT NULL, body text NOT NULL);
INSERT INTO notes (owner, body) VALUES ('asha', 'asha private'), ('ravi', 'ravi private'), ('asha', 'asha todo');
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_notes ON notes USING (owner = current_setting('app.username'));
CREATE ROLE app_user NOLOGIN;
GRANT SELECT ON notes TO app_user;

SET ROLE app_user;
SET app.username = 'asha';
SELECT owner, body FROM notes ORDER BY id;
SET app.username = 'ravi';
SELECT owner, body FROM notes ORDER BY id;
RESET ROLE;

Output:

 owner |     body     
-------+--------------
 asha  | asha private
 asha  | asha todo
(2 rows)

 owner |     body     
-------+--------------
 ravi  | ravi private
(1 row)

Add WITH CHECK for writes

USING filters what can be read and updated; WITH CHECK validates new rows, so users cannot insert rows for other tenants.

Quick check: Why connect the application with a non-owner role when using RLS?

• Owners cannot read tables
• Table owners bypass RLS by default
• RLS only works for superusers
• It is faster