RLS (Row-Level Security) Quick Start Guide

What is RLS?

Row-level security filters the rows each user can see and change, inside the database. Once a table has RLS enabled, every query on it, over the PostgreSQL port, the REST API or the REPL, is checked against the table’s policies.

Quick Setup (5 minutes)

1. Start the Server with Password Authentication

RLS is evaluated against the connected user, so use real logins:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir ./mydb \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"

2. Create a Table and Users (as postgres)

CREATE TABLE documents (
    id    INT PRIMARY KEY,
    owner TEXT NOT NULL,
    title TEXT
);

CREATE ROLE alice LOGIN PASSWORD 'alice-long-password';
CREATE ROLE bob   LOGIN PASSWORD 'bob-long-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO alice;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO bob;

3. Define an RLS Policy

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_only ON documents
    FOR ALL
    USING (owner = current_user)
    WITH CHECK (owner = current_user);

4. Query as Each User

psql "host=127.0.0.1 port=5432 user=alice"

INSERT INTO documents VALUES (1, 'alice', 'Alice plan');
INSERT INTO documents VALUES (2, 'bob', 'Forged');   -- rejected by WITH CHECK
SELECT * FROM documents;                             -- only Alice's rows

Connected as bob, the same SELECT returns only Bob’s rows.

RLS Expression Syntax

Supported Operators

Operator	Example	Description
=	owner = current_user	Equality
!=	status != 'deleted'	Not equal
<	created_at < '2025-01-01'	Less than
>	amount > 100	Greater than
<=	age <= 65	Less than or equal
>=	score >= 80	Greater than or equal
AND	owner = current_user AND active = true	Logical AND
OR	owner = current_user OR public = true	Logical OR
NOT	NOT archived	Logical NOT
IS NULL	deleted_at IS NULL	Null check
IS NOT NULL	tenant_id IS NOT NULL	Not null check

Row constructors such as (a, b) = (x, y) are rejected; write them as a = x AND b = y.

Identity Functions

Function	Returns	Notes
current_user	Connected user	Set by the PostgreSQL login or the REST token’s sub
current_tenant()	Current tenant ID	Bound by \tenant use <tenant> in the REPL; NULL elsewhere, which denies the row

Policy Types

1. USING (SELECT, UPDATE, DELETE)

Controls which rows are visible:

CREATE POLICY see_own ON documents FOR SELECT USING (owner = current_user);

2. WITH CHECK (INSERT, UPDATE)

Validates that new or modified rows satisfy the policy:

CREATE POLICY write_own ON documents FOR INSERT WITH CHECK (owner = current_user);

Common Patterns

Pattern 1: Owner Isolation

CREATE POLICY owner_only ON documents
    FOR ALL USING (owner = current_user) WITH CHECK (owner = current_user);

Pattern 2: Public Rows Plus Owned Rows

CREATE POLICY read_public_or_own ON documents
    FOR SELECT USING (public = true OR owner = current_user);

Pattern 3: Soft Delete

CREATE POLICY hide_deleted ON documents
    FOR SELECT USING (owner = current_user AND deleted_at IS NULL);

Pattern 4: Tenant Isolation in the REPL

CREATE POLICY tenant_isolation ON orders
    FOR ALL USING (tenant_id = current_tenant());

heliosdb> \tenant use acme_corp
heliosdb> SELECT * FROM orders;   -- only acme_corp rows

Testing Your Policies

1. Connect as each application role and run the queries your application runs.
2. Try to insert a row that violates WITH CHECK; it must be rejected.
3. Connect as a role with no matching rows; SELECT must return nothing.

Debugging RLS

-- Policies on all tables
SELECT * FROM pg_rls_policies();

-- Who am I connected as?
SELECT current_user;

Performance Tips

1. Index Policy Columns

CREATE INDEX idx_documents_owner ON documents(owner);

2. Keep Policies Simple

Good:

USING (owner = current_user)

Complex (slower):

USING (owner = current_user AND (status = 'active' OR (status = 'pending' AND created_at > '2025-01-01')))

Security Best Practices

1. Connect applications as non-superuser roles. Superusers, including the built-in postgres and helios accounts, bypass RLS.
2. Use password authentication. With the default --auth trust a client can connect as any user, including postgres.
3. Always add WITH CHECK to policies on tables that users write to.
4. Test policy violations, not only the happy path.

Troubleshooting

Problem: RLS not enforced

• Check that ALTER TABLE ... ENABLE ROW LEVEL SECURITY was run on the table.
• Check that you are not connected as a superuser (SELECT current_user;).

Problem: All queries return empty

• A policy that uses current_tenant() denies every row when no tenant context is bound (PostgreSQL port and REST API). Use current_user there.
• Check the values in the policy column match the role names exactly.

Problem: Policy expression error

• Use only the operators and functions listed above; rewrite row constructors as AND chains.

Migration from Manual Filtering

Before (Manual)

-- Every application query had to remember the filter
SELECT * FROM documents WHERE owner = 'alice';

After (RLS)

-- The database applies the filter for the connected user
SELECT * FROM documents;

Support

• Row-Level Security
• RLS Policy Management Tutorial
• Advanced Row-Level Security Policies