How to implement row level security in PostgreSQL

Short answer

Row-Level Security (RLS) allows you to define policies that control which rows a user can see or modify, automatically enforced on every query.

Steps

1. Enable RLS on a table: ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
2. Create a policy: CREATE POLICY user_isolation ON documents FOR SELECT USING (owner = current_user);
3. Define separate policies for SELECT, INSERT, UPDATE, and DELETE.
4. Use FORCE ROW LEVEL SECURITY to apply policies even to table owners.
5. Test with different roles to ensure policies behave as expected.

Tips

• Combine RLS with application-level user IDs stored in current_setting for multi-tenant apps.
• Policies can reference other tables, but be mindful of performance and recursion.

Common issues

• Superusers and table owners bypass RLS by default unless forced.
• Complex policies with subqueries can introduce significant query overhead.r