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.

Practice

1. What is the main purpose of enabling Row-level security (RLS) on a PostgreSQL table?

easy

A. To create backups of the table data

B. To speed up query execution by indexing rows

C. To control access to individual rows based on user-defined policies

D. To encrypt the entire table data automatically

Solution

1. Step 1: Understand what RLS does

   Row-level security allows filtering which rows a user can see or modify based on policies.

2. Step 2: Compare options

   Only To control access to individual rows based on user-defined policies describes controlling access to individual rows, which is the purpose of RLS.

3. Final Answer:

   To control access to individual rows based on user-defined policies -> Option C

4. Quick Check:

   RLS controls row access = D [OK]

Hint: RLS filters rows per user rules, not speed or encryption [OK]

Common Mistakes:

• Confusing RLS with indexing or encryption
• Thinking RLS backs up data
• Assuming RLS applies to entire tables, not rows

2. Which of the following is the correct syntax to enable row-level security on a table named employees?

easy

A. ALTER TABLE employees SET ROW LEVEL SECURITY ON;

B. ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

C. ENABLE ROW LEVEL SECURITY ON employees;

D. ALTER TABLE employees ACTIVATE RLS;

Solution

1. Step 1: Recall the correct command to enable RLS

   The correct syntax is ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;.

2. Step 2: Match options with syntax

   Only ALTER TABLE employees ENABLE ROW LEVEL SECURITY; matches the exact syntax for enabling RLS on a table.

3. Final Answer:

   ALTER TABLE employees ENABLE ROW LEVEL SECURITY; -> Option B

4. Quick Check:

   Enable RLS uses ALTER TABLE ... ENABLE ROW LEVEL SECURITY [OK]

Hint: Use ALTER TABLE ... ENABLE ROW LEVEL SECURITY to activate RLS [OK]

Common Mistakes:

• Using SET instead of ENABLE
• Trying to enable RLS without ALTER TABLE
• Using incorrect keywords like ACTIVATE

3. Given the following policy on table documents:

CREATE POLICY user_policy ON documents
FOR SELECT USING (owner = current_user);

What rows will a user see when they run SELECT * FROM documents;?

medium

A. Only rows where the owner column matches the current user

B. No rows, because no policy allows access

C. All rows in the documents table

D. Only rows where owner is NULL

Solution

1. Step 1: Understand the policy condition

   The policy allows SELECT only if owner = current_user, so only rows owned by the current user are visible.

2. Step 2: Determine visible rows

   Rows where the owner matches the current user will be returned; others are filtered out.

3. Final Answer:

   Only rows where the owner column matches the current user -> Option A

4. Quick Check:

   Policy filters rows by owner = current_user [OK]

Hint: Policy USING clause filters rows visible to current_user [OK]

Common Mistakes:

• Assuming all rows are visible despite policy
• Thinking NULL owners are included
• Ignoring the current_user condition

4. You wrote this policy to allow users to update their own rows:

CREATE POLICY update_own ON orders
FOR UPDATE USING (user_id = current_user);

But users report they can update other users' rows too. What is the likely problem?

medium

A. The policy should use WITH CHECK instead of USING

B. The policy must be created with FOR SELECT, not FOR UPDATE

C. The current_user function is not supported in policies

D. Row-level security is not enabled on the orders table

Solution

1. Step 1: Check if RLS is enabled

   Policies only work if row-level security is enabled on the table; otherwise, they have no effect.

2. Step 2: Understand the symptom

   If users can update rows despite the policy, likely RLS is disabled, ignoring policies and allowing full access.

3. Final Answer:

   Row-level security is not enabled on the orders table -> Option D

4. Quick Check:

   RLS must be enabled for policies to work [OK]

Hint: Enable RLS to enforce policies [OK]

Common Mistakes:

• Confusing USING and WITH CHECK clauses
• Assuming current_user is unsupported
• Creating policy for wrong command type

5. You want to allow users to SELECT rows from projects table only if they are either the project owner or the project is marked as public (is_public = true). Which policy condition correctly implements this?

hard

A. USING (owner = current_user OR is_public = true)

B. USING (owner = current_user AND is_public = true)

C. USING (owner != current_user OR is_public = false)

D. USING (owner = current_user AND is_public = false)

Solution

1. Step 1: Translate the requirement to logic

   Users can see rows if they own the project OR if the project is public.

2. Step 2: Match logic to condition

   The OR condition matches the requirement: owner = current_user OR is_public = true.

3. Final Answer:

   USING (owner = current_user OR is_public = true) -> Option A

4. Quick Check:

   Owner or public projects visible = A [OK]

Hint: Use OR to allow either owner or public access [OK]

Common Mistakes:

• Using AND instead of OR, restricting access too much
• Negating conditions incorrectly
• Confusing true and false values