Row-level security policies in PostgreSQL

Introduction

Row-level security lets you control who can see or change each row in a table. It helps keep data safe by limiting access to only the right people.

You want each user to see only their own data in a shared table.

You need to restrict access to sensitive information based on user roles.

You want to enforce data privacy rules automatically in the database.

You have multiple departments sharing a table but each should see only their rows.

You want to simplify application code by moving access control into the database.

Syntax

PostgreSQL

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

CREATE POLICY policy_name ON table_name
  USING (condition);

-- Optional: To allow inserts or updates, add WITH CHECK clause
CREATE POLICY policy_name ON table_name
  FOR {SELECT | INSERT | UPDATE | DELETE}
  TO role_name
  USING (condition)
  WITH CHECK (condition);

Enable row-level security on a table before creating policies.

Policies define which rows are visible or modifiable based on conditions.

Examples

This policy allows users to see only rows where their user ID matches the row's user_id.

PostgreSQL

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_policy ON employees
  USING (user_id = current_user);

This policy lets users with hr_role insert rows only if the department is 'HR'.

PostgreSQL

CREATE POLICY insert_policy ON employees
  FOR INSERT
  TO hr_role
  WITH CHECK (department = 'HR');

Sample Program

This example creates a documents table with row-level security. Only rows where the owner matches the current user are visible. When the user is 'alice', only her document shows.

PostgreSQL

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  owner TEXT,
  content TEXT
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_policy ON documents
  USING (owner = current_user);

-- Insert sample data
INSERT INTO documents (owner, content) VALUES
  ('alice', 'Alice''s secret'),
  ('bob', 'Bob''s notes');

-- Simulate current_user = 'alice'
SET SESSION AUTHORIZATION 'alice';

SELECT * FROM documents;

Important Notes

Row-level security policies apply automatically after enabling RLS on a table.

Be careful to test policies to avoid accidentally hiding all rows.

Policies can be combined for different operations like SELECT, INSERT, UPDATE, DELETE.

Summary

Row-level security controls access to individual rows in a table.

Enable RLS on a table, then create policies with conditions to filter rows.

This helps keep data safe and simplifies access control in your database.