Postgres & SupaBase Row Level Security

SupaBase which uses PostgreSQL’s Row-Level Security (RLS) are powerful database feature that gives administrators precise control over which data rows specific users are allowed to view or modify. It works by applying security policies directly to a table, which are then automatically evaluated for any query that accesses it. This means that even if two users run the exact same SELECT * FROM employees query, the database can return a different set of rows to each person based on their role or user attributes, providing a robust and transparent layer of data protection.

This approach moves security logic from the application layer directly into the database, simplifying development and reducing the risk of accidental data exposure.

How Does Row-Level Security Work?

At its core, RLS functions as an automated, mandatory filter. When a user queries a table with RLS enabled, PostgreSQL checks for any active policies associated with that table for the user’s role. These policies are essentially rules that define a USING condition (for read access like SELECT) and an optional WITH CHECK condition (for write access like INSERT or UPDATE).

• USING Clause: This expression is evaluated for each row in the table. If the expression returns true for a given row, that row is visible to the user. If it returns false or NULL, the row is silently filtered out of the query results. It’s applied before any WHERE clause or other filtering in the user’s query.
• WITH CHECK Clause: This expression is used for commands that add new rows or modify existing ones. If a user tries to INSERT or UPDATE a row, the new data is checked against this condition. If the expression returns false, the operation is rejected with an error, preventing users from storing data they would not be able to see later.

To use RLS, you must first enable it on the target table and then define one or more policies.

A Practical Guide to Implementing RLS

Implementing Row-Level Security involves three main steps: setting up the roles, enabling the feature on a table, and creating the policies. Let’s walk through a common scenario: a multi-tenant application where each tenant should only see its own data.

Consider a simple documents table:

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(50) NOT NULL,
    content TEXT NOT NULL
);

Step 1: Enable Row-Level Security

By default, RLS is disabled on tables. You must explicitly enable it.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Important: Once you enable RLS, all access to the table is denied by default until you create at least one policy. Even the table owner will be blocked.

Step 2: Create a Policy

Next, create a policy that allows users to access only the documents associated with their tenant_id. We can leverage session variables to securely pass the user’s tenant_id to the database.

The policy would look like this:

CREATE POLICY tenant_isolation_policy
ON documents
FOR ALL -- Applies to SELECT, INSERT, UPDATE, and DELETE
USING (tenant_id = current_setting('app.tenant_id'));

In this policy:

• tenant_isolation_policy is the name of our policy.
• ON documents specifies the table it applies to.
• FOR ALL means the policy is for all command types. You could also specify FOR SELECT or FOR UPDATE.
• The USING clause is the heart of the logic. It checks if the tenant_id column in a given row matches a custom session variable named app.tenant_id.

Step 3: Test the Policy

To test this, a user would connect to the database and set their session variable before running a query.

1. A user from “tenant-a” connects and sets their context:SET app.tenant_id = 'tenant-a'; SELECT * FROM documents; -- This query will ONLY return rows where tenant_id = 'tenant-a'.
2. Another user from “tenant-b” connects:SET app.tenant_id = 'tenant-b'; SELECT * FROM documents; -- This query will ONLY return rows where tenant_id = 'tenant-b'.

The application backend is responsible for setting this session variable for each user upon authentication, ensuring the database enforces the data separation automatically.

Why Use Row-Level Security?

1. Multi-Tenant Applications: As shown above, RLS is the ideal solution for SaaS applications where multiple clients’ data is stored in shared tables. It provides a strong guarantee of data isolation.
2. Hierarchical Access: It can model complex organizational structures, such as allowing managers to see records for their direct reports but not for other managers’ teams.
3. Compliance and Security: For regulations like GDPR or HIPAA, RLS helps ensure that employees can only access data on a need-to-know basis, enforcing security rules at the lowest possible level.
4. Simplified Application Code: By moving security logic into the database, application code becomes cleaner. Developers no longer need to add WHERE tenant_id = ? to every single query, reducing the chance of human error.