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

Need a 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

Need a 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

Need a 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

Need a hint?

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