Row-level security policies in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple RLS policy query

Given a table employees with columns id, name, and department, and a row-level security policy that allows users to see only rows where department = current_setting('app.current_department'), what will be the output of the following query if app.current_department is set to 'Sales'?

SELECT name FROM employees ORDER BY id;

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, department TEXT);
INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'HR'),
('Charlie', 'Sales');
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY sales_only ON employees FOR SELECT TO public USING (department = current_setting('app.current_department'));
SET app.current_department = 'Sales';

A Bob

B

Alice
Bob
Charlie

C

Alice
Charlie

D No rows returned

💡 Hint

Think about which rows match the department filter in the policy.

The policy restricts rows to those where department equals the current setting 'Sales'. Only Alice and Charlie belong to Sales, so only their names appear.

🧠 Conceptual

intermediate

Understanding RLS policy effect on UPDATE

Consider a table documents with a row-level security policy that allows UPDATE only if owner = current_user. What happens if a user tries to update a row where owner is different from their username?

PostgreSQL

CREATE TABLE documents (id SERIAL PRIMARY KEY, content TEXT, owner TEXT);
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY owner_update ON documents FOR UPDATE TO public USING (owner = current_user);

A The update succeeds only if the user owns the row; otherwise, no rows are updated.

B The update succeeds for all rows regardless of ownership.

C The update causes a syntax error due to policy conflict.

D The update silently ignores the policy and updates all rows.

💡 Hint

RLS policies filter rows visible for the operation based on the condition.

The policy restricts UPDATE to rows where owner matches the current user. Rows not owned by the user are invisible for UPDATE, so they cannot be changed.

📝 Syntax

advanced

Identify the syntax error in RLS policy creation

Which option contains a syntax error when creating a row-level security policy in PostgreSQL?

A CREATE POLICY user_insert ON orders FOR INSERT TO public WITH CHECK (user_id = current_user);

B CREATE POLICY user_update ON orders FOR UPDATE TO public USING (user_id = current_user);

C CREATE POLICY user_select ON orders FOR SELECT TO public USING (user_id = current_user);

D CREATE POLICY user_delete ON orders FOR DELETE TO public USING (user_id = current_user);

💡 Hint

Check the syntax of the USING clause.

Option D is missing parentheses around the USING expression, causing a syntax error.

❓ optimization

advanced

Optimizing RLS policy for performance

You have a large sales table with a row-level security policy filtering rows by region. Which approach improves query performance when using RLS?

A Create an index on the <code>region</code> column to speed up filtering.

B Disable RLS and filter rows in the application code instead.

C Add a trigger to copy rows to a separate table per region.

D Use a complex function in the policy condition to check region.

💡 Hint

Think about how databases speed up WHERE clause filtering.

Creating an index on the filtered column helps PostgreSQL quickly find matching rows, improving RLS query performance.

🔧 Debug

expert

Why does this RLS policy not restrict access as expected?

A table projects has RLS enabled with this policy:

CREATE POLICY project_access ON projects FOR SELECT TO public USING (owner = current_user);

However, users can still see all rows. What is the most likely reason?

PostgreSQL

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

A Row-level security is enabled, but the policy is not applied because <code>ALTER TABLE projects FORCE ROW LEVEL SECURITY</code> was not run.

B Row-level security is enabled, but the user has the <code>bypassrls</code> role attribute.

C The policy condition is incorrect; it should use <code>owner = session_user</code> instead of <code>current_user</code>.

D Row-level security is enabled, but the table is missing a primary key.

💡 Hint

Check user permissions and special role attributes that affect RLS.

Users with the bypassrls attribute ignore RLS policies and see all rows regardless of policies.

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