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:
Supported operations include:
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:
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.