PostgreSQL Row-Level Security
Table of Contents
This document describes Row-Level Security as a PostgreSQL feature in general. For the project-specific application — tenant isolation, service-role policies, schema patterns — see ores.sql (and the "Service Table Isolation" section of its component overview). Return to External knowledge / Knowledge.
PostgreSQL Row-Level Security (RLS) is a built-in database feature that provides fine-grained access control by restricting which rows of a table a user can see or modify, based on defined security policies.
It acts as an additional layer on top of standard SQL privileges
(GRANT / REVOKE). While table-level permissions control whether a
user can access a table at all, RLS filters individual rows
dynamically during SELECT, INSERT, UPDATE, and DELETE
operations.
Key Characteristics
RLS is enabled per table using:
alter table table_name enable row level security;
Once enabled, access to rows requires at least one matching policy
(otherwise a default-deny behaviour applies — no rows are
visible/modifiable). Policies are created with CREATE POLICY and
use a Boolean expression to determine which rows are accessible,
e.g.:
current_user = owner, ortenant_id = current_setting('app.current_tenant')
Policies can apply to specific commands (SELECT, INSERT,
UPDATE, DELETE, or ALL), specific roles, or be
permissive/restrictive. Table owners and superusers typically bypass
RLS (unless explicitly configured otherwise).
Whole-table operations like TRUNCATE or adding foreign keys are
not affected by RLS.
Common Use Cases
- Multi-tenant applications (each tenant only sees their own data).
- User-specific data isolation (e.g. employees see only their department's records).
- Implementing authorisation logic directly in the database for consistency and defence-in-depth.
RLS helps enforce data privacy and security at the database level, reducing the risk of accidental or malicious over-exposure of data through application bugs or direct database access.
See also
- ores.sql — how this project applies RLS for tenant isolation and service-role separation.
- PostgreSQL Architecture — broader database conventions: schema layout, roles, session settings, extensions, and utility functions.