sqladvanced

Row-Level Security Policies

Enforce data access rules at the database level with PostgreSQL Row-Level Security policies.

sqlPress ⌘/Ctrl + Shift + C to copy

-- Enable RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own documents
CREATE POLICY select_own_docs ON documents
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::uuid);

-- Policy: Users can insert only for themselves
CREATE POLICY insert_own_docs ON documents
  FOR INSERT
  WITH CHECK (user_id = current_setting('app.user_id')::uuid);

-- Policy: Admins can see everything
CREATE POLICY admin_all_docs ON documents
  FOR ALL
  USING (current_setting('app.role') = 'admin');

-- Set user context before queries
SET app.user_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';
SET app.role = 'user';