Row-level security policies in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to enable row-level security on the table employees.

PostgreSQL

ALTER TABLE employees [1] ROW LEVEL SECURITY;

Drag options to blanks, or click blank then click option'

ADISABLE

BENABLE

CCREATE

DDROP

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISABLE instead of ENABLE.

Trying to CREATE or DROP row-level security directly.

To use row-level security, you must first enable it on the table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.

2fill in blank

medium

Complete the code to create a row-level security policy named user_policy on the employees table that allows users to see only their own rows based on user_id.

PostgreSQL

CREATE POLICY user_policy ON employees FOR SELECT TO PUBLIC USING (user_id = [1]);

Drag options to blanks, or click blank then click option'

Auser_id

Bsession_user

Ccurrent_user

Dcurrent_setting('app.current_user')

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name instead of a function.

Using session_user which can differ from current_user.

The function current_user returns the name of the current database user, which is commonly used in row-level security policies to filter rows.

3fill in blank

hard

Fix the error in the policy creation by completing the code to restrict UPDATE operations only to rows where user_id matches the current user.

PostgreSQL

CREATE POLICY update_policy ON employees FOR UPDATE TO PUBLIC USING ([1] = current_user);

Drag options to blanks, or click blank then click option'

Auser_id

Busername

Cemployee_id

Drole

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong column name that does not exist.

Using a role or employee_id instead of user_id.

The policy must check the user_id column against the current user to restrict updates to their own rows.

4fill in blank

hard

Fill both blanks to create a policy named delete_policy that allows DELETE only for rows where user_id matches the current user and only for the role admin.

PostgreSQL

CREATE POLICY delete_policy ON employees FOR DELETE TO [1] USING (user_id = current_user AND role = [2]);

Drag options to blanks, or click blank then click option'

Aadmin

BPUBLIC

C'admin'

Dcurrent_user

Attempts:

3 left

💡 Hint

Common Mistakes

Using admin without quotes for the role string.

Using current_user as a role in TO clause.

The policy applies to all users (TO PUBLIC) but restricts deletes to rows where role equals the string 'admin'.

5fill in blank

hard

Fill all three blanks to create a policy named select_policy that allows SELECT only for the role manager and only on rows where department matches the current user's department stored in current_setting('app.department').

PostgreSQL

CREATE POLICY select_policy ON employees FOR SELECT TO [1] USING (role = [2] AND department = [3]);

Drag options to blanks, or click blank then click option'

APUBLIC

B'manager'

Ccurrent_setting('app.department')

Dcurrent_user

Attempts:

3 left

💡 Hint

Common Mistakes

Using role without quotes.

Using current_user instead of current_setting for department.

Using a specific role name in TO clause instead of PUBLIC.

The policy applies to all users (TO PUBLIC), restricts to role 'manager', and matches the department column to the current user's department setting.

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