Row-level security policies in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a row-level security policy in PostgreSQL?

A row-level security policy controls which rows a user can see or modify in a table, based on rules defined by the database administrator.

beginner

How do you enable row-level security on a PostgreSQL table?

Use the command ALTER TABLE table_name ENABLE ROW LEVEL SECURITY; to turn on row-level security for that table.

intermediate

What is the purpose of a policy in row-level security?

A policy defines the conditions under which rows are visible or modifiable by users. It acts like a filter for rows based on user roles or other criteria.

intermediate

How do you create a row-level security policy in PostgreSQL?

Use CREATE POLICY policy_name ON table_name FOR {SELECT | INSERT | UPDATE | DELETE} TO role_name USING (condition); to define who can access which rows and how.

advanced

Can a user bypass row-level security policies in PostgreSQL?

Yes, users with the BYPASSRLS privilege can bypass row-level security policies and see all rows regardless of policies.

Which command enables row-level security on a PostgreSQL table?

A ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

B CREATE POLICY ON table_name;

C GRANT SELECT ON table_name;

D ALTER TABLE table_name SET SECURITY POLICY;

The correct command to enable row-level security is ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;.

What does a row-level security policy control?

A Which columns a user can see

B Which rows a user can see or modify

C Which tables a user can access

D The database connection settings

Row-level security policies control access to individual rows, not columns or tables.

Which privilege allows a user to bypass row-level security policies?

A SUPERUSER

B ADMIN

C BYPASSRLS

D SECURITY_ADMIN

The BYPASSRLS privilege allows users to bypass row-level security policies.

In the command CREATE POLICY, what does the USING clause specify?

A The condition to filter rows

B The columns to include

C The user roles allowed

D The table name

The USING clause specifies the condition that filters which rows are accessible.

Can row-level security policies be applied to INSERT operations?

A No, only SELECT operations are controlled

B Policies only apply to SELECT and UPDATE

C Only UPDATE and DELETE are controlled

D Yes, policies can control INSERT, SELECT, UPDATE, and DELETE

Row-level security policies can be defined for INSERT, SELECT, UPDATE, and DELETE operations.

Explain what row-level security policies are and why they are useful in PostgreSQL.

Think about how you can limit what data different users see in the same table.

Describe the steps to enable and create a row-level security policy on a table in PostgreSQL.

Start with enabling, then define who can do what on which rows.

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