Row-Level Security (RLS)

Row-Level Security (RLS)

Row-Level Security lets you control which rows individual users can see or modify. Policies are defined per-table and per-operation, and multiple policies on the same table are combined with OR.

Prerequisites

• HeliosDB-Lite v3.5+
• Roles created for the users who will be restricted (see RBAC tutorial)

Step 1: Create Test Data

CREATE TABLE documents (
    id INT PRIMARY KEY,
    title TEXT,
    owner TEXT,
    department TEXT,
    classification TEXT
);

INSERT INTO documents VALUES (1, 'Q1 Report',   'alice', 'finance',     'internal');
INSERT INTO documents VALUES (2, 'API Spec',    'bob',   'engineering', 'public');
INSERT INTO documents VALUES (3, 'Salary Data', 'alice', 'finance',     'confidential');
INSERT INTO documents VALUES (4, 'Roadmap',     'carol', 'engineering', 'internal');
INSERT INTO documents VALUES (5, 'Budget Plan', 'alice', 'finance',     'confidential');

Step 2: Enable RLS on the Table

RLS is off by default. Enable it explicitly:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Once enabled, non-owner users see zero rows unless a policy permits access. The table owner (superuser) bypasses RLS automatically.

Step 3: Create a SELECT Policy

Policies use a USING expression that must evaluate to true for a row to be visible:

-- Users can only see documents they own
CREATE POLICY owner_select
    ON documents
    FOR SELECT
    USING (owner = current_setting('app.current_user'));

Now set the session user and query:

SET app.current_user = 'alice';
SELECT * FROM documents;

Expected output (alice sees only her rows):

id	title	owner	department	classification
1	Q1 Report	alice	finance	internal
3	Salary Data	alice	finance	confidential
5	Budget Plan	alice	finance	confidential

SET app.current_user = 'bob';
SELECT * FROM documents;

id	title	owner	department	classification
2	API Spec	bob	engineering	public

Step 4: Create Department-Wide Access

Add a second policy. Multiple policies on the same table are OR’d together:

CREATE POLICY dept_select
    ON documents
    FOR SELECT
    USING (department = current_setting('app.current_dept'));

SET app.current_user = 'bob';
SET app.current_dept = 'engineering';
SELECT * FROM documents;

Bob now sees his own row (via owner_select) OR any engineering row (via dept_select):

id	title	owner	department	classification
2	API Spec	bob	engineering	public
4	Roadmap	carol	engineering	internal

Step 5: INSERT Policy with WITH CHECK

WITH CHECK validates new rows on INSERT and UPDATE:

CREATE POLICY owner_insert
    ON documents
    FOR INSERT
    WITH CHECK (owner = current_setting('app.current_user'));

SET app.current_user = 'bob';

-- This succeeds (owner matches current user)
INSERT INTO documents VALUES (6, 'Design Doc', 'bob', 'engineering', 'internal');

-- This fails (cannot insert a row owned by someone else)
INSERT INTO documents VALUES (7, 'Fake Doc', 'alice', 'engineering', 'public');

Step 6: UPDATE and DELETE Policies

-- Users can only update their own documents
CREATE POLICY owner_update
    ON documents
    FOR UPDATE
    USING (owner = current_setting('app.current_user'));

-- Users can only delete their own documents
CREATE POLICY owner_delete
    ON documents
    FOR DELETE
    USING (owner = current_setting('app.current_user'));

Step 7: ALL-Command Shortcut

Instead of writing four separate policies, use FOR ALL:

CREATE POLICY tenant_isolation
    ON documents
    FOR ALL
    USING (owner = current_setting('app.current_user'));

This single policy applies to SELECT, INSERT, UPDATE, and DELETE.

Step 8: Monitor Policies

SELECT * FROM pg_rls_policies;

policy_name	table_name	command	roles	using_expr	check_expr	enabled
owner_select	documents	SELECT	*	owner = current_setting(‘app.current_user’)		true
dept_select	documents	SELECT	*	department = current_setting(‘app.current_dept’)	.	true
owner_insert	documents	INSERT	*		owner = …	true
owner_update	documents	UPDATE	*	owner = current_setting(‘app.current_user’)		true
owner_delete	documents	DELETE	*	owner = current_setting(‘app.current_user’)		true

Step 9: Drop Policies

-- Remove a specific policy
DROP POLICY dept_select ON documents;

-- Safe drop
DROP POLICY IF EXISTS dept_select ON documents;

Step 10: Disable RLS

To remove all row-level restrictions from a table:

ALTER TABLE documents DISABLE ROW LEVEL SECURITY;

All users will again see every row (subject to GRANT-based access).

Common Pitfalls

1. Owner bypass — The table owner (typically the superuser) always sees all rows regardless of RLS policies. Do not rely on RLS to hide data from the database administrator.

2. No policies = no rows — After ENABLE RLS, if no policies exist for a given operation, non-owner users get zero results. Always create at least one policy per operation you expect users to perform.

3. Policies are OR’d — If any policy for a command returns true, the row is accessible. Policies cannot restrict each other; they only grant access. Design them accordingly.

4. current_setting must be set — Policies referencing current_setting('app.current_user') require the application to SET that variable before each query. If unset, the expression evaluates to NULL and no rows match.

5. Performance — RLS adds a filter evaluation per row. For large tables, ensure the filtered column is indexed.