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.