Implementing Row-Level Security
1. Understanding Row-Level Security
| Concept | Detail |
|---|
| Scope | Per-row filter on SELECT/INSERT/UPDATE/DELETE |
| Bypass | SUPERUSER, BYPASSRLS role attribute, table owner (unless FORCE) |
| Composition | Permissive (OR) + Restrictive (AND) |
2. Enabling RLS on Table
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
Warning: With no policies, ALL non-owner queries return zero rows.
3. Creating Policy
CREATE POLICY tenant_isolation ON accounts
USING (tenant_id = current_setting('app.tenant_id')::int);
4. Using FOR Commands
CREATE POLICY p_select ON orders FOR SELECT USING (...);
CREATE POLICY p_modify ON orders FOR ALL USING (...) WITH CHECK (...);
5. Using USING Expression
| Clause | Applied To |
|---|
| USING | Rows read / updated / deleted (visibility) |
| WITH CHECK | Rows written (INSERT/UPDATE result) |
6. Using WITH CHECK Expression
CREATE POLICY tenant_write ON orders
FOR INSERT WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);
7. Creating Permissive Policies
CREATE POLICY own_rows ON orders AS PERMISSIVE
FOR ALL TO app_user USING (owner = current_user);
8. Creating Restrictive Policies
CREATE POLICY no_archived ON orders AS RESTRICTIVE
FOR ALL USING (status <> 'archived');
| Kind | Combined |
|---|
| PERMISSIVE | OR with other permissive |
| RESTRICTIVE | AND with all others |
9. Using current_user in Policies
CREATE POLICY mine ON orders FOR ALL USING (owner = current_user);
10. Using current_setting in Policies
-- App sets per-request:
SELECT set_config('app.tenant_id', '42', true); -- TX-local
CREATE POLICY tenant ON orders USING (tenant_id = current_setting('app.tenant_id')::int);
11. Bypassing RLS
ALTER ROLE etl_user BYPASSRLS;
-- or run as SUPERUSER (bypass is automatic)
12. Forcing RLS for Table Owner
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
-- owner is now subject to policies too
13. Disabling RLS
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;
14. Dropping Policy
DROP POLICY tenant_isolation ON accounts;
15. Listing Policies
\d+ orders
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies;