Postgres Row-Level Security (RLS) can make your data less secure if you don’t understand its subtle implications.
Let’s see RLS in action. Imagine a users table and a todos table, where each todo belongs to a specific user. We want to ensure users can only see their own todos.
-- Create tables
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
tenant_id INT NOT NULL -- Assume a tenant_id for multi-tenancy
);
CREATE TABLE todos (
todo_id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(user_id),
tenant_id INT NOT NULL,
task VARCHAR(255) NOT NULL,
completed BOOLEAN DEFAULT FALSE
);
-- Insert some data
INSERT INTO users (username, tenant_id) VALUES
('alice', 101),
('bob', 102);
INSERT INTO todos (user_id, tenant_id, task) VALUES
(1, 101, 'Buy groceries'),
(1, 101, 'Walk the dog'),
(2, 102, 'Deploy new feature');
Now, let’s set up RLS for the todos table. We’ll create a policy that only allows a user to see todos matching their tenant_id.
-- Enable RLS on the todos table
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;
-- Create a policy for tenant isolation
CREATE POLICY tenant_isolation_policy ON todos
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id')::INT);
-- Note: For INSERT, UPDATE, DELETE, you might want similar policies
-- CREATE POLICY tenant_isolation_policy ON todos
-- FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::INT);
-- CREATE POLICY tenant_isolation_policy ON todos
-- FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id')::INT)
-- WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::INT);
-- CREATE POLICY tenant_isolation_policy ON todos
-- FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id')::INT);
To make this work, we need to tell Postgres which tenant is currently active for a given connection. A common way is using SET within a session.
-- Simulate Alice's session (tenant_id 101)
SET app.current_tenant_id = '101';
SELECT * FROM todos;
-- Expected output:
-- todo_id | user_id | tenant_id | task | completed
-- --------|---------|-----------|-------------|-----------
-- 1 | 1 | 101 | Buy groceries | f
-- 2 | 1 | 101 | Walk the dog | f
-- Simulate Bob's session (tenant_id 102)
SET app.current_tenant_id = '102';
SELECT * FROM todos;
-- Expected output:
-- todo_id | user_id | tenant_id | task | completed
-- --------|---------|-----------|-------------------|-----------
-- 3 | 2 | 102 | Deploy new feature | f
-- If we try to access data from another tenant
SET app.current_tenant_id = '999'; -- A non-existent tenant
SELECT * FROM todos;
-- Expected output: (no rows)
The core problem RLS solves is enforcing access control at the data layer, rather than relying solely on application logic. This means even if there’s a bug in your application code that bypasses a check, the database itself will prevent unauthorized data access. RLS policies are attached to tables and run automatically for any query targeting that table, based on the USING clause. The current_setting() function is a common way to inject dynamic values, like the current user’s tenant ID, into your policies.
The mental model for RLS is simple: for any row in a table with RLS enabled, Postgres evaluates all policies applicable to the current operation (SELECT, INSERT, UPDATE, DELETE). A row is only included in the result set (for SELECT) or allowed to be modified (for INSERT, UPDATE, DELETE) if at least one policy permits it. For SELECT, the USING clause defines the condition; for INSERT, UPDATE, and DELETE, WITH CHECK is used. If no policy applies or all policies fail, the row is inaccessible or the operation fails.
The real power comes from combining RLS with other Postgres features. For instance, you can use SECURITY DEFINER functions that execute with the privileges of the function owner, not the caller, and then apply RLS policies within those functions. This allows for complex logic that needs elevated privileges but still needs to respect tenant isolation. You can also use pg_catalog.pg_get_userbyid(session_user::oid) to get the current user, or current_setting() for custom session variables.
A common pitfall is forgetting to enable RLS on the table itself. Without ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;, no RLS policies will have any effect, regardless of how many you define. Another is the implicit AND behavior: if you have multiple policies on a table, a row must satisfy all policies for SELECT operations, unless you explicitly use OR within a single policy definition. If you have a FOR ALL policy and then a FOR SELECT policy, the FOR SELECT policy overrides the FOR ALL policy for select operations.
The next concept you’ll likely grapple with is managing RLS policies across many tables, especially in a large application, and how to handle complex relationships where data spans multiple tables that might have different RLS configurations.