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.