Row-level security policies in PostgreSQL - Step-by-Step Execution

Concept Flow - Row-level security policies

Enable RLS on Table

↓

Create Policy with Condition

↓

User Queries Table

↓

Check Policy Condition for Each Row

Yes No↓

Return Row

↓

Result Set

Row-level security (RLS) filters rows based on policies when a user queries a table, returning only rows that meet the policy conditions.

Execution Sample

PostgreSQL

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY emp_policy ON employees
  FOR SELECT USING (department = current_setting('app.current_department'));
SET app.current_department = 'Sales';
SELECT * FROM employees;

This code enables RLS on 'employees', creates a policy to allow access only to rows where department matches the current user's department, sets the department to 'Sales', then selects rows.

Execution Table

Step	Action	Row Data	Policy Condition (department = 'Sales')	Row Included in Result
1	Check row	{id:1, name:'Alice', department:'Sales'}	Sales = Sales	Yes
2	Check row	{id:2, name:'Bob', department:'HR'}	HR = Sales	No
3	Check row	{id:3, name:'Carol', department:'Sales'}	Sales = Sales	Yes
4	Check row	{id:4, name:'Dave', department:'IT'}	IT = Sales	No
5	Return result set	Only rows with department 'Sales'	N/A	Rows 1 and 3 included

💡 All rows checked; only rows matching policy condition included in final result.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Current Department	NULL	Sales	Sales	Sales	Sales	Sales
Result Set	Empty	Row 1 included	Row 1 included	Rows 1 and 3 included	Rows 1 and 3 included	Rows 1 and 3 included

Key Moments - 2 Insights

Why are some rows excluded even though they exist in the table?

Rows are excluded because the policy condition filters them out. For example, in execution_table rows 2 and 4, the department does not match 'Sales', so those rows are not included.

What happens if RLS is not enabled on the table?

If RLS is not enabled, the policy conditions are ignored and all rows are returned regardless of the policy. Enabling RLS activates the filtering shown in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, which row is included at Step 3?

A Row with id 4 (Dave)

B Row with id 3 (Carol)

C Row with id 2 (Bob)

D No rows included

💡 Hint

Check the 'Row Included in Result' column at Step 3 in the execution_table.

At which step does the policy exclude a row because the department is 'HR'?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look for the row with department 'HR' and see when it is checked in the execution_table.

If the current department was set to 'IT', which rows would be included?

A Rows with department 'IT'

B Rows with department 'HR'

C Rows with department 'Sales'

D All rows

💡 Hint

Refer to the policy condition and variable_tracker for 'Current Department'.

Concept Snapshot

Row-level security (RLS) lets you control which rows a user can see.
Enable RLS on a table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
Create policies with CREATE POLICY ... USING (condition).
When querying, only rows matching the policy condition are returned.
Set context variables to customize policy behavior per user.

Full Transcript

Row-level security policies in PostgreSQL control access to rows in a table based on conditions. First, you enable RLS on the table. Then, you create a policy that defines which rows a user can see, for example, rows where the department matches the user's current department. When a user queries the table, PostgreSQL checks each row against the policy condition. Only rows that satisfy the condition are included in the result. Rows that do not match are excluded. This filtering happens automatically once RLS is enabled and policies are defined. You can set session variables to customize the policy condition dynamically. This way, users only see data they are allowed to access, improving security and data privacy.

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