# Row-Level Security — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/o-rls

> Each user sees only their rows.

## 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.

```sql
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.

**Quiz:** Why connect the application with a non-owner role when using RLS?

- [ ] Owners cannot read tables
- [x] Table owners bypass RLS by default
- [ ] RLS only works for superusers
- [ ] It is faster

*Answer:* Table owners bypass RLS by default. Make sure policies actually apply.