PostgreSQL Row-Level Security Explained with Practical Examples

Introduction

Modern applications often serve multiple users, teams, organizations, or customers from the same database. While traditional authorization mechanisms can restrict access at the application level, relying solely on application code introduces security risks. A bug, misconfigured API, or overlooked query can accidentally expose sensitive data.

To address this challenge, PostgreSQL provides Row-Level Security (RLS), a powerful feature that allows access control to be enforced directly within the database. Instead of trusting every application query, PostgreSQL evaluates security policies before returning data, ensuring users only see rows they are authorized to access.

Row-Level Security is widely used in multi-tenant SaaS platforms, enterprise applications, customer portals, and systems that require fine-grained access control.

In this article, you'll learn what PostgreSQL Row-Level Security is, how it works, how to implement it, and best practices for securing your applications with practical examples.

What Is Row-Level Security?

Row-Level Security (RLS) is a PostgreSQL feature that restricts access to individual rows within a table based on policies you define.

Without RLS:

Users Table
     ↓
All Rows Returned

With RLS:

Users Table
     ↓
Security Policy Applied
     ↓
Only Authorized Rows Returned

The database automatically filters results before sending them to the application.

This creates an additional layer of security independent of application code.

Why Row-Level Security Matters

Consider a multi-tenant SaaS application.

Table:

customers

Data:

id	company_id	customer_name
1	100	John
2	100	Sarah
3	200	Michael
4	200	Emma

If Company 100 users query the table, they should only see:

John
Sarah

They must never see:

Michael
Emma

RLS enforces this rule directly within PostgreSQL.

How Row-Level Security Works

RLS operates through policies.

A policy defines:

Who can access rows
Which rows can be accessed
Which operations are allowed

Supported operations include:

SELECT
INSERT
UPDATE
DELETE

The database evaluates policies automatically whenever users interact with protected tables.

Creating a Sample Table

Let's create a simple table for a project management application.

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    organization_id INTEGER,
    project_name TEXT
);

Insert sample data:

INSERT INTO projects
(organization_id, project_name)
VALUES
(1, 'Website Redesign'),
(1, 'Mobile App'),
(2, 'CRM Migration'),
(2, 'Analytics Dashboard');

At this stage, every authorized user can view all records.

Enabling Row-Level Security

Before creating policies, RLS must be enabled.

ALTER TABLE projects
ENABLE ROW LEVEL SECURITY;

Once enabled, PostgreSQL starts enforcing row-level policies.

Without policies, access may become restricted depending on permissions and configuration.

Creating a Basic Policy

Suppose each database user belongs to a specific organization.

We can create a policy that restricts access to matching organization records.

CREATE POLICY project_access_policy
ON projects
FOR SELECT
USING (
    organization_id =
    current_setting('app.organization_id')::INTEGER
);

This policy tells PostgreSQL:

Only return rows
where organization_id
matches the current user context.

The filtering happens automatically.

Setting User Context

Applications typically set the organization identifier when a user logs in.

Example:

SET app.organization_id = '1';

Query:

SELECT *
FROM projects;

Results:

Website Redesign
Mobile App

Rows belonging to organization 2 are automatically hidden.

No additional WHERE clause is required.

Protecting INSERT Operations

RLS can also control data creation.

Example:

CREATE POLICY project_insert_policy
ON projects
FOR INSERT
WITH CHECK (
    organization_id =
    current_setting('app.organization_id')::INTEGER
);

This policy ensures users can only insert records for their own organization.

Allowed:

INSERT INTO projects
(organization_id, project_name)
VALUES
(1, 'New Portal');

Rejected:

INSERT INTO projects
(organization_id, project_name)
VALUES
(2, 'Unauthorized Project');

The database prevents invalid inserts automatically.

Protecting Updates

Users should only modify their own records.

Policy:

CREATE POLICY project_update_policy
ON projects
FOR UPDATE
USING (
    organization_id =
    current_setting('app.organization_id')::INTEGER
);

Now PostgreSQL restricts updates to authorized rows.

Example:

UPDATE projects
SET project_name = 'Updated Name'
WHERE id = 1;

This succeeds only if the row belongs to the user's organization.

Protecting Deletes

Delete operations can also be restricted.

CREATE POLICY project_delete_policy
ON projects
FOR DELETE
USING (
    organization_id =
    current_setting('app.organization_id')::INTEGER
);

This ensures users cannot remove records belonging to other tenants.

Multi-Tenant SaaS Example

A typical SaaS architecture:

Customer A
      ↓
Application
      ↓
PostgreSQL + RLS
      ↓
Customer A Data

Customer B
      ↓
Application
      ↓
PostgreSQL + RLS
      ↓
Customer B Data

Benefits include:

Strong tenant isolation
Simplified application code
Reduced security risks
Centralized authorization

Many modern SaaS platforms rely heavily on this approach.

Comparing Application-Level Security and RLS

Feature	Application Security	PostgreSQL RLS
Enforcement Location	Application	Database
Risk of Developer Error	Higher	Lower
Data Isolation	Variable	Strong
Centralized Security	No	Yes
Multi-Tenant Support	Good	Excellent
Query Protection	Manual	Automatic

RLS complements application security rather than replacing it.

The strongest systems typically use both approaches.

Common Use Cases

Row-Level Security is commonly used for:

SaaS Platforms

Ensure customers only access their own data.

Enterprise Applications

Restrict records based on departments or teams.

Customer Portals

Limit visibility to customer-specific information.

Healthcare Systems

Protect patient records and sensitive information.

Financial Applications

Control access to transactions and account data.

Practical Example

Imagine a project management platform.

Organizations share the same database.

Tables include:

Projects
Tasks
Comments
Documents

Without RLS:

Application Code
     ↓
Must filter every query

With RLS:

Database Policies
     ↓
Automatic Filtering

Developers no longer need to remember tenant filters on every query.

Security becomes much easier to maintain.

Best Practices

Enable RLS on Sensitive Tables

Apply RLS to tables containing tenant-specific or sensitive data.

Use Application Context Variables

Store user or tenant identifiers using session settings.

This keeps policies flexible and maintainable.

Keep Policies Simple

Complex policies are harder to understand and audit.

Prefer clear, focused authorization rules.

Test Security Thoroughly

Verify:

Authorized access
Unauthorized access
Insert restrictions
Update restrictions
Delete restrictions

Security testing is critical.

Combine RLS with Application Authentication

RLS controls database access, but authentication still determines user identity.

Use both together.

Audit Security Policies Regularly

Review policies as business requirements evolve.

Outdated policies can introduce security gaps.

Conclusion

PostgreSQL Row-Level Security provides a powerful and reliable mechanism for enforcing fine-grained access control directly within the database. By automatically filtering rows based on defined policies, RLS reduces the risk of accidental data exposure and strengthens overall application security.

For multi-tenant SaaS platforms, enterprise systems, customer portals, and other applications handling sensitive data, Row-Level Security offers an elegant solution that centralizes authorization logic and simplifies application development.

Rather than relying entirely on application code to enforce permissions, developers can leverage PostgreSQL's built-in security model to ensure users only access the data they are authorized to see. When combined with strong authentication and proper security practices, Row-Level Security becomes a valuable tool for building secure and scalable applications.