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.