PostgreSQL RLS: Enforcing Data Isolation at the Database Layer
Date Published
Compare application-layer filtering with PostgreSQL Row-Level Security to prevent privilege escalation and data leakage in multi-tenant environments.
The Risk of Application-Layer Filtering
Relying solely on application code to filter data based on user identity introduces significant security risks. If an application bug allows a user to bypass a WHERE clause or manipulate a session variable, the database may expose data belonging to other tenants. PostgreSQL’s security model shares responsibility between the database and the application layer, but vulnerabilities in the application layer can lead to unauthorized access to privileges or data.
In multi-tenant environments, data isolation is critical. If the application fails to enforce isolation, a single compromised endpoint can leak sensitive information across tenants. This is a common failure mode that security audits must identify.
How Row-Level Security Works
PostgreSQL Row-Level Security (RLS) restricts, on a per-user basis, which rows can be returned by queries or modified by data commands. When RLS is enabled on a table, all normal access must be allowed by a row security policy. If no policy exists, a default-deny policy applies, meaning no rows are visible or modifiable.
Policies are defined using Boolean expressions that are evaluated for each row prior to any conditions from the user's query. Rows for which the expression does not return true are not processed. This ensures that even if an application sends a query without proper filters, the database engine enforces the visibility constraints.
Enable RLS with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
Create policies using CREATE POLICY with USING and WITH CHECK clauses.
Permissive policies are combined with OR; restrictive policies with AND.
Implementing Policies for Multi-Tenancy
A common pattern is to create a policy that restricts access to rows where a tenant identifier matches the current user's context. For example, a policy can be created to allow users to view only their own records by using an expression like user_name = current_user. This works similarly for different command types, ensuring that users cannot select, update, or delete rows belonging to other users.
Separate expressions can be specified for SELECT and modification commands. This allows for granular control, such as allowing all users to view public data but restricting modifications to their own records. The WITH CHECK clause ensures that new rows inserted or updated also comply with the security policy.
Audit and Verification
It is essential to test that the security system behaves as expected. Auditors should verify that users cannot access data outside their scope by executing queries as different roles. For instance, a user should not be able to update another user's record, and attempts to do so should result in zero rows updated or a permission error.
Superusers and roles with the BYPASSRLS attribute bypass RLS, so these roles must be carefully managed. Table owners typically bypass RLS unless FORCE ROW LEVEL SECURITY is used. Ensuring that the correct roles are assigned to policies and that the application connects with the appropriate user context is crucial for effective data isolation.
Talk to Worlber
Planning a PostgreSQL migration, enterprise deployment, or production database platform? Speak with Worlber Database Services.