Implementing Row Level Security in PostgreSQL
What You’ll Need
- A cloud server such as a Hetzner VPS or DigitalOcean running Ubuntu 24.04 LTS with PostgreSQL 16 installed
- A registered domain from Namecheap if you plan to attach a public API gateway or connection pooler to your database instance
- Direct administrative access (
sudoorpostgressuperuser access) via SSH or a database management client
Table of Contents
- Understanding PostgreSQL Row Level Security
- Designing the Database Schema and Application Roles
- Writing Granular Row Level Security Policies
- Optimizing Policy Performance and Securing Dynamic Context
- Getting Started
Understanding PostgreSQL Row Level Security
When building multi-tenant SaaS platforms or internal enterprise tools, traditional database authorization relies on table-level permissions. You use GRANT SELECT, INSERT ON table_name TO role_name; to allow or deny actions. However, table-level grants are binary. If an application database connection has SELECT privileges on the documents table, it can read every row in that table unless the application backend manually appends WHERE tenant_id = current_tenant to every incoming SQL query.
Relying entirely on application-level filtering introduces risk. A single missing WHERE clause in a complex SQL join, ORM query, or raw reporting migration can leak sensitive data across customer accounts.
Row Level Security (RLS) moves access control enforcement from the application layer down to the PostgreSQL engine. When you enable RLS on a database table, PostgreSQL inspects every query, subquery, write, and delete operation executed against that table. It dynamically appends security policy expressions to the execution plan before execution.
+-----------------------------------------------------------------------+
| Incoming SQL Query |
| SELECT * FROM documents WHERE status = 'published'; |
+-----------------------------------------------------------------------+
|
v
+-----------------------------------------------------------------------+
| PostgreSQL Query Planner |
| Detects Row Level Security (RLS) is active on 'documents' |
+-----------------------------------------------------------------------+
|
v
+-----------------------------------------------------------------------+
| Evaluates Active RLS Policy |
| Policy: tenant_id = current_setting('app.current_tenant_id')::uuid |
+-----------------------------------------------------------------------+
|
v
+-----------------------------------------------------------------------+
| Modified Physical Execution Plan |
| SELECT * FROM documents WHERE status = 'published' |
| AND tenant_id = current_setting('app.current_tenant_id')::uuid; |
+-----------------------------------------------------------------------+
Unlike lightweight embedded engines where authentication and permissions are tightly coupled to pre-built application boundaries, such as when Deploying Self-Hosted PocketBase Backend on Ubuntu, PostgreSQL provides raw data engine primitives. You can run pooled connection backend services using a single database user, while injecting session variables on demand to isolate rows dynamically.
Designing the Database Schema and Application Roles
To implement RLS effectively, you must separate database administrative superusers from low-privilege application roles. By default, database table owners and superusers bypass RLS policies unless explicitly configured otherwise.
Log into your instance on your Hetzner VPS via SSH and launch the PostgreSQL command line tool:
sudo -u postgres psql
Create a new application user role named app_user with standard login permissions, along with a dedicated database named saas_platform. Execute the following SQL statements:
CREATE DATABASE saas_platform;
\c saas_platform;
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecureAppUserPassword123!';
GRANT USAGE ON SCHEMA public TO app_user;
CREATE TABLE tenants (
tenant_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
company_name VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
email VARCHAR(255) NOT NULL UNIQUE,
role_name VARCHAR(50) NOT NULL DEFAULT 'member',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE projects (
project_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
created_by_user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
project_title VARCHAR(255) NOT NULL,
is_private BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON tenants TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;
Now seed the database with initial tenant accounts, users, and project records using standard administrative execution:
INSERT INTO tenants (tenant_id, company_name) VALUES
('11111111-1111-1111-1111-111111111111', 'Acme Corp'),
('22222222-2222-2222-2222-222222222222', 'Globex Corporation');
INSERT INTO users (user_id, tenant_id, email, role_name) VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 'alice@acme.com', 'admin'),
('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '11111111-1111-1111-1111-111111111111', 'bob@acme.com', 'member'),
('cccccccc-cccc-cccc-cccc-cccccccccccc', '22222222-2222-2222-2222-222222222222', 'charlie@globex.com', 'admin');
INSERT INTO projects (project_id, tenant_id, created_by_user_id, project_title, is_private) VALUES
('99999999-9999-9999-9999-999999999991', '11111111-1111-1111-1111-111111111111', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Acme Q4 Expansion Strategy', true),
('99999999-9999-9999-9999-999999999992', '11111111-1111-1111-1111-111111111111', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'Acme Public Roadmap', false),
('99999999-9999-9999-9999-999999999993', '22222222-2222-2222-2222-222222222222', 'cccccccc-cccc-cccc-cccc-cccccccccccc', 'Globex Internal Infrastructure', true);
💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.
Writing Granular Row Level Security Policies
With the baseline data set in place, you must enable RLS on each table explicitly. Creating a table does not enable security enforcement automatically. Run the following ALTER TABLE statements:
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
The FORCE ROW LEVEL SECURITY clause ensures that even if the owner of the table executes queries against it, security policies are applied unless that session user explicitly possesses the BYPASSRLS superuser attribute.
Context Setting Strategy
Most modern Web applications connect to database instances using a connection pool (like PgBouncer or standard node-postgres / SQLAlchemy pools) under a single database role (app_user). To determine which tenant or user is querying data, you can pass custom transaction-scoped configuration settings using set_config() or SQL statements like SET LOCAL app.current_tenant_id = '...'.
Let me demonstrate how to define a security policy for SELECT actions on the projects table using the custom setting variable app.current_tenant_id:
CREATE POLICY tenant_isolation_select_projects ON projects
FOR SELECT
TO app_user
USING (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
);
The true parameter in current_setting('app.current_tenant_id', true) instructs PostgreSQL to return NULL instead of raising a runtime execution error if the configuration variable has not been initialized in the active transaction context.
Now create detailed policies governing INSERT, UPDATE, and DELETE queries on the projects table:
CREATE POLICY tenant_isolation_insert_projects ON projects
FOR INSERT
TO app_user
WITH CHECK (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
AND created_by_user_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid
);
CREATE POLICY tenant_isolation_update_projects ON projects
FOR UPDATE
TO app_user
USING (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
)
WITH CHECK (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
);
CREATE POLICY tenant_isolation_delete_projects ON projects
FOR DELETE
TO app_user
USING (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
AND (
created_by_user_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid
OR EXISTS (
SELECT 1 FROM users
WHERE users.user_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid
AND users.role_name = 'admin'
AND users.tenant_id = projects.tenant_id
)
)
);
Testing Policy Enforcement
To test how these policies function in real-world API routes, switch to the unprivileged app_user inside a transactional block. This context simulation matches how you configure background integrations or API engines. For instance, when Designing Idempotent Webhook Receivers for Distributed Systems, every incoming payload should set session state before performing database actions.
Execute a query session pretending to be tenant 11111111-1111-1111-1111-111111111111 (Acme Corp):
SET ROLE app_user;
BEGIN;
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.current_user_id', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', true);
SELECT project_id, project_title, tenant_id FROM projects;
COMMIT;
The output returns only Acme Corp projects:
project_id | project_title | tenant_id
--------------------------------------+----------------------------+-------------------------------------
99999999-9999-9999-9999-999999999991 | Acme Q4 Expansion Strategy | 11111111-1111-1111-1111-111111111111
99999999-9999-9999-9999-999999999992 | Acme Public Roadmap | 11111111-1111-1111-1111-111111111111
(2 rows)
If the application attempts to insert a record for a different tenant ID, PostgreSQL aborts the write with an explicit authorization violation:
BEGIN;
SELECT set_config('app.current_tenant_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.current_user_id', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', true);
INSERT INTO projects (tenant_id, created_by_user_id, project_title, is_private)
VALUES (
'22222222-2222-2222-2222-222222222222',
'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
'Malicious Tenant Injection',
false
);
ROLLBACK;
PostgreSQL halts execution instantly:
ERROR: new row violates row-level security policy for table "projects"
Reset back to administrative access for the next section:
RESET ROLE;
Optimizing Policy Performance and Securing Dynamic Context
While Row Level Security adds bulletproof data governance, naive policy expressions can destroy database query performance. Because PostgreSQL evaluates RLS predicates on matching rows, running unindexed expression checks across large data structures causes query degradation.
1. Wrapping Context Expressions in Subqueries
When writing expressions like tenant_id = current_setting('app.current_tenant_id', true)::uuid, PostgreSQL may re-evaluate current_setting() repeatedly for every single record scanned. Wrapping the dynamic setting call inside a scalar subquery forces the query optimizer to evaluate the context value once per query execution plan.
Look at the performance difference between these two policies:
-- Suboptimal Policy Pattern:
CREATE POLICY policy_slow ON projects
FOR SELECT
TO app_user
USING (
tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
);
-- Optimized Policy Pattern:
DROP POLICY tenant_isolation_select_projects ON projects;
CREATE POLICY tenant_isolation_select_projects_optimized ON projects
FOR SELECT
TO app_user
USING (
tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
);
2. Creating Indexing Strategy for RLS Columns
Ensure that every field referenced inside an RLS USING or WITH CHECK expression possesses a valid index. Run these optimization commands to create proper multi-column indexes:
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);
CREATE INDEX idx_projects_tenant_user ON projects(tenant_id, created_by_user_id);
CREATE INDEX idx_users_tenant_user_role ON users(tenant_id, user_id, role_name);
3. Encapsulating Logic in SECURITY DEFINER Helper Functions
Complex policies involving subqueries (such as checking if a requesting user has an admin role in the users table) can cause recursion bugs or bypass security policies if not handled cleanly. You can encapsulate context lookup logic inside SECURITY DEFINER functions.
A SECURITY DEFINER function executes with the privileges of the user who owns the function, rather than the user calling it. This allows low-privilege app_user sessions to execute system identity validation checks without granting them direct read access to sensitive tables like user role mappings.
Create a dedicated schema and helper function:
CREATE SCHEMA rls_helpers;
GRANT USAGE ON SCHEMA rls_helpers TO app_user;
CREATE OR REPLACE FUNCTION rls_helpers.get_current_tenant_id()
RETURNS UUID
LANGUAGE sql
STABLE
PARALLEL SAFE
AS $$
SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::uuid;
$$;
CREATE OR REPLACE FUNCTION rls_helpers.is_tenant_admin(p_tenant_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
SELECT EXISTS (
SELECT 1
FROM users
WHERE user_id = NULLIF(current_setting('app.current_user_id', true), '')::uuid
AND tenant_id = p_tenant_id
AND role_name = 'admin'
);
$$;
REVOKE EXECUTE ON FUNCTION rls_helpers.is_tenant_admin(UUID) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION rls_helpers.is_tenant_admin(UUID) TO app_user;
GRANT EXECUTE ON FUNCTION rls_helpers.get_current_tenant_id() TO app_user;
Now update the projects delete policy to utilize this high-performance security function:
DROP POLICY tenant_isolation_delete_projects ON projects;
CREATE POLICY tenant_isolation_delete_projects_v2 ON projects
FOR DELETE
TO app_user
USING (
tenant_id = (SELECT rls_helpers.get_current_tenant_id())
AND (
created_by_user_id = (SELECT NULLIF(current_setting('app.current_user_id', true), '')::uuid)
OR rls_helpers.is_tenant_admin(tenant_id)
)
);
When building automated administrative workflows, such as when Scheduling Background Python Scripts with Systemd Timers, background jobs can set session configurations using these exact helper parameters. This guarantees that automated batch processing tasks inherit identical enterprise security boundaries without exposing direct table overrides.
Getting Started
Implementing Row Level Security gives your platform a defense-in-depth model that protects customer data against application tier bugs and SQL injection vectors.
To launch a production environment with complete RLS capabilities:
- Provision a high-performance compute host using a Hetzner VPS or a scalable DigitalOcean droplet.
- Domain setup and secure SSL routing can be initialized via Namecheap to point traffic safely to your API gateway layers.
- Apply schema migrations with strict
FORCE ROW LEVEL SECURITYconfigurations on all multi-tenant tables. - Update application database pool connection hooks to initialize session-scoped configuration parameters on check-out.
Outsource Your Automation
Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.
Want to automate this yourself?
Start with n8n Cloud (free tier available) or self-host on a Hetzner VPS for full control.
Want this engine running on your own VPS?
This blog publishes itself — daily, unattended, on free API tiers. The full engine, Hugo theme, and setup guide are available as System 3.
Get System 3