Row-level security policies in PostgreSQL - Deep Dive

Overview - Row-level security policies

What is it?

Row-level security (RLS) policies in PostgreSQL control which rows a user can see or modify in a table. They work by applying rules that filter rows based on the user's identity or other conditions. This means different users can have different views of the same table without changing the data itself. RLS helps keep data safe and private within shared databases.

Why it matters

Without row-level security, users might see or change data they shouldn't, risking privacy and security. RLS solves this by enforcing rules directly in the database, so applications don't have to handle complex filtering. This reduces mistakes and protects sensitive information, especially in multi-user or multi-tenant systems.

Where it fits

Before learning RLS, you should understand basic SQL queries, table structures, and user roles in PostgreSQL. After mastering RLS, you can explore advanced database security, access control, and application-level security integration.

Mental Model

Core Idea

Row-level security policies act like personalized filters on a table, showing each user only the rows they are allowed to access.

Think of it like...

Imagine a library where each visitor can only see books on shelves labeled with their name. The library is the database, the shelves are tables, and the labels are row-level security policies controlling access.

┌─────────────────────────────┐
│          Table              │
│ ┌───────────────┐           │
│ │ Row 1         │           │
│ │ Row 2         │           │
│ │ Row 3         │           │
│ │ ...           │           │
│ └───────────────┘           │
│                             │
│ User A sees: Rows 1, 3       │
│ User B sees: Row 2           │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic table access

Concept: How users normally access all rows in a table without restrictions.

In PostgreSQL, when a user queries a table, they see all rows unless restricted by permissions. For example, SELECT * FROM employees; returns every employee record visible to that user.

Result

The user sees all rows in the employees table.

Knowing that default access shows all rows helps understand why row-level filtering is needed for finer control.

2

Foundation

Introduction to PostgreSQL roles and permissions

Concept: How PostgreSQL manages user identities and grants access rights.

PostgreSQL uses roles to represent users or groups. Permissions like SELECT, INSERT, UPDATE control what actions a role can perform on tables. However, these permissions apply to whole tables, not individual rows.

Result

Roles can read or write entire tables if granted permission.

Understanding roles and permissions shows the limitation that RLS overcomes by controlling access at the row level.

3

Intermediate

Enabling row-level security on tables

Concept: How to activate RLS on a table to start filtering rows per user.

Use ALTER TABLE table_name ENABLE ROW LEVEL SECURITY; to turn on RLS. After enabling, PostgreSQL applies any defined policies to filter rows for queries and data modifications.

Result

The table now enforces row-level filtering based on policies.

Knowing that RLS must be explicitly enabled prevents confusion when policies don't apply.

4

Intermediate

Creating simple RLS policies

🤔Before reading on: do you think RLS policies can allow access to rows based on user names or roles? Commit to your answer.

Concept: Defining rules that specify which rows a user can access based on conditions.

Create policies using CREATE POLICY policy_name ON table_name FOR SELECT USING (condition); For example, to allow users to see only rows where owner = current_user, use: CREATE POLICY user_policy ON documents FOR SELECT USING (owner = current_user);

Result

Users see only rows where the owner column matches their username.

Understanding that policies use conditions tied to user identity enables personalized data views.

5

Intermediate

Combining multiple RLS policies

🤔Before reading on: do you think multiple RLS policies on the same table combine with AND or OR logic? Commit to your answer.

Concept: How PostgreSQL evaluates multiple policies together to decide row access.

When multiple policies exist for the same command (e.g., SELECT), PostgreSQL combines them with OR logic. A row is accessible if it matches any policy's condition.

Result

Users can access rows allowed by any one of the policies.

Knowing the OR combination helps design policies that cover different access cases without blocking each other.

6

Advanced

Using RLS with data modification commands

🤔Before reading on: do you think RLS policies affect only SELECT queries or also INSERT, UPDATE, DELETE? Commit to your answer.

Concept: RLS policies can control not just reading but also writing and deleting rows.

Policies can be created for INSERT, UPDATE, DELETE commands with FOR INSERT, FOR UPDATE, FOR DELETE clauses. For example, a policy can restrict updates to rows owned by the current user.

Result

Users can only modify rows allowed by the policy conditions.

Understanding that RLS controls all data operations ensures comprehensive security, not just read filtering.

7

Expert

Bypassing RLS with security definer functions

🤔Before reading on: do you think database functions can bypass RLS policies? Commit to your answer.

Concept: Security definer functions run with the function owner's rights, potentially bypassing RLS.

Functions created with SECURITY DEFINER run with the privileges of their creator, not the caller. This can be used to access or modify rows ignoring RLS, if carefully designed. For example, a function can return rows without RLS filtering if the owner has full access.

Result

Functions can provide controlled access paths that bypass RLS restrictions.

Knowing this helps design secure functions and avoid accidental data leaks through privilege escalation.

Under the Hood

PostgreSQL applies RLS policies by rewriting queries internally to add WHERE clauses that enforce the policy conditions. When a query runs, the database checks the user's identity and applies all relevant policies combined with OR logic. This filtering happens at the storage engine level, ensuring no unauthorized rows are returned or modified.

Why designed this way?

RLS was designed to provide fine-grained access control directly in the database, reducing reliance on application logic. Combining policies with OR allows flexible access rules. The internal query rewriting ensures security is enforced consistently and efficiently without changing client queries.

┌───────────────┐
│ User Query    │
│ SELECT * FROM │
│ documents     │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Query Rewriter adds WHERE    │
│ clause based on RLS policies │
│ (e.g., owner = current_user) │
└──────────────┬──────────────┘
               │
               ▼
┌─────────────────────────────┐
│ Storage Engine returns only  │
│ rows matching the condition  │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does enabling RLS automatically restrict all users' access to rows? Commit to yes or no.

Common Belief: Enabling RLS alone restricts all users from seeing any rows until policies are added.

Reality: Enabling RLS without any policies blocks all access except for superusers and table owners.

Why it matters: Without policies, users may be locked out unexpectedly, causing application failures or confusion.

Quick: Do multiple RLS policies combine with AND logic? Commit to yes or no.

Common Belief: Multiple RLS policies on a table combine with AND, so all must be true for access.

Reality: Policies combine with OR logic; a row is accessible if it matches any policy.

Why it matters: Misunderstanding this can lead to overly restrictive or overly permissive access controls.

Quick: Can RLS policies restrict access to columns? Commit to yes or no.

Common Belief: RLS can filter which columns a user sees in a table.

Reality: RLS only filters rows, not columns. Column-level security requires other features like views or column privileges.

Why it matters: Assuming RLS controls columns can cause data leaks if sensitive columns are not separately protected.

Quick: Can security definer functions bypass RLS? Commit to yes or no.

Common Belief: All database operations must obey RLS policies, no exceptions.

Reality: Security definer functions run with the owner's rights and can bypass RLS if not carefully designed.

Why it matters: Ignoring this can create security holes where users access restricted data through functions.

Expert Zone

1

RLS policies are applied after row-level permissions but before column-level permissions, affecting query planning and performance subtly.

2

Using RLS with partitioned tables requires defining policies on each partition, which can be complex and error-prone.

3

The current_user and session_user can differ, affecting policy conditions if roles are switched during a session.

When NOT to use

RLS is not suitable when you need to restrict access at the column level or when performance is critical on very large tables with complex policies. Alternatives include using views for column filtering or application-level filtering for complex logic.

Production Patterns

In multi-tenant SaaS applications, RLS is used to isolate tenant data in shared tables. Policies often check tenant_id = current_setting('app.current_tenant')::uuid. Security definer functions provide controlled admin access. Auditing and logging complement RLS for compliance.

Connections

Access Control Lists (ACLs)

RLS builds on ACLs by adding row-level granularity beyond table-level permissions.

Understanding ACLs helps grasp why RLS is needed for finer control inside tables.

Operating System File Permissions

Both control access to resources but at different levels: OS controls files, RLS controls database rows.

Knowing OS permissions clarifies the layered approach to security, from files to data rows.

Data Privacy Regulations (e.g., GDPR)

RLS helps implement legal requirements by restricting personal data access to authorized users.

Understanding RLS aids compliance with privacy laws by enforcing data minimization at the database level.

Common Pitfalls

#1 Enabling RLS but forgetting to create any policies.

Wrong approach:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

Correct approach:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
CREATE POLICY emp_policy ON employees FOR SELECT USING (department = current_user);

Root cause:

Assuming enabling RLS alone enforces restrictions without defining access rules.

#2 Writing policies that use AND logic expecting all conditions to apply.

Wrong approach:

CREATE POLICY multi_cond ON documents FOR SELECT USING (owner = current_user AND status = 'active');
CREATE POLICY another_cond ON documents FOR SELECT USING (shared = true);

Correct approach:

CREATE POLICY multi_cond ON documents FOR SELECT USING (owner = current_user);
CREATE POLICY another_cond ON documents FOR SELECT USING (shared = true);

Root cause:

Misunderstanding that multiple policies combine with OR, not AND.

#3 Trying to restrict columns using RLS policies.

Wrong approach:

CREATE POLICY col_policy ON employees FOR SELECT USING (salary < 100000);

Correct approach:

Use views or column privileges to restrict columns, e.g., CREATE VIEW limited_employees AS SELECT id, name FROM employees;

Root cause:

Confusing row-level filtering with column-level security.

Key Takeaways

Row-level security policies filter table rows based on user identity or conditions, enabling personalized data access.

RLS must be explicitly enabled and paired with policies to control access; otherwise, it blocks all non-superuser access.

Multiple RLS policies combine with OR logic, allowing flexible access rules covering different cases.

RLS applies to SELECT, INSERT, UPDATE, and DELETE commands, securing all data operations at the row level.

Security definer functions can bypass RLS, so they must be carefully designed to avoid security risks.

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