AdvancedAdvanced PostgreSQL

Row-Level Security (RLS)

The query

SQL

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

-- Policy: managers see only their direct reports
CREATE POLICY emp_manager_policy ON employees
  FOR SELECT
  USING (
    manager_id = CURRENT_SETTING('app.current_user_id')::INT
    OR emp_id = CURRENT_SETTING('app.current_user_id')::INT
  );

-- Set user context per connection
SET app.current_user_id = '42';
SELECT * FROM employees; -- only returns their reports

Tested against PostgreSQL 16

Note

“RLS enforces data access at database level. Policies use USING (read) and WITH CHECK (write).

Tables referenced

employees10 cols