Row-level security policies in PostgreSQL - Mini Project: Build & Apply

Implementing Row-Level Security Policies in PostgreSQL

📖 Scenario: You are managing a company database where employees can only see their own sales records. To protect sensitive data, you need to implement row-level security (RLS) policies in PostgreSQL.

🎯 Goal: Build a PostgreSQL table with sales data and apply row-level security policies so that each employee can only access their own sales records.

📋 What You'll Learn

Create a table named sales with columns id, employee_id, and amount

Enable row-level security on the sales table

Create a policy named employee_policy that allows employees to see only their own rows based on employee_id

Test the policy by setting the current user ID and querying the table

💡 Why This Matters

🌍 Real World

Row-level security is used in companies to ensure employees see only their own data, protecting privacy and sensitive information.

💼 Career

Understanding and implementing RLS policies is important for database administrators and backend developers working with multi-tenant or sensitive data systems.

1

Create the sales table with sample data

Create a table called sales with columns id (integer primary key), employee_id (integer), and amount (numeric). Insert these exact rows: (1, 101, 500), (2, 102, 300), (3, 101, 700).

PostgreSQL

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Enable row-level security on the sales table

Enable row-level security on the sales table using the command ALTER TABLE sales ENABLE ROW LEVEL SECURITY;.

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  amount NUMERIC
);

INSERT INTO sales (id, employee_id, amount) VALUES
  (1, 101, 500),
  (2, 102, 300),
  (3, 101, 700);

-- Enable row-level security on sales
-- Your code here

Hint

Use ALTER TABLE sales ENABLE ROW LEVEL SECURITY; to turn on RLS.

3

Create a row-level security policy for employees

Create a policy named employee_policy on the sales table that allows access only to rows where employee_id equals the current setting current_setting('app.current_employee_id')::integer. Use USING clause for the condition.

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  amount NUMERIC
);

INSERT INTO sales (id, employee_id, amount) VALUES
  (1, 101, 500),
  (2, 102, 300),
  (3, 101, 700);

ALTER TABLE sales ENABLE ROW LEVEL SECURITY;

-- Create the employee_policy to restrict rows
-- Your code here

Hint

Use CREATE POLICY employee_policy ON sales USING (...); with the condition comparing employee_id to the current setting.

4

Set current employee ID and test the policy

Set the current employee ID to 101 using SET app.current_employee_id = '101';. Then write a query to select all columns from sales to see only rows for employee 101.

PostgreSQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  employee_id INTEGER,
  amount NUMERIC
);

INSERT INTO sales (id, employee_id, amount) VALUES
  (1, 101, 500),
  (2, 102, 300),
  (3, 101, 700);

ALTER TABLE sales ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_policy ON sales
  USING (employee_id = current_setting('app.current_employee_id')::integer);

-- Set current employee ID and select rows
-- Your code here

Hint

Use SET app.current_employee_id = '101'; to set the current employee, then SELECT * FROM sales; to query the table.

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