Skip to content
HeliosDB HeliosDB Lite

Advanced Row-Level Security Policies

Build: default — no feature flag required

UVP

PostgreSQL-shaped RLS without the overhead — policies with explicit USING and WITH CHECK expressions, four command targets (SELECT, INSERT, UPDATE, DELETE, plus All), evaluated against each row and the request’s tenant context. Multiple policies on one table compose with OR; combine with FORCE for table-owner enforcement; pull current_user, current_setting, and tenant claims directly into the predicate. RLS is the bedrock of multi-tenant isolation in Lite — and it’s a single CREATE POLICY away.

Prerequisites

1. Anatomy of a Policy

USING and WITH CHECK look identical but apply at different times:

  • USING (expr) — evaluated at scan time. Rows where expr is false are invisible.
  • WITH CHECK (expr) — evaluated on INSERT/UPDATE. Rows that would violate the check are rejected.

If you write USING and omit WITH CHECK, the same predicate applies to writes (PostgreSQL semantics).

2. Enabling RLS on a Table

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

Owners are exempt unless you also force it:

ALTER TABLE documents FORCE ROW LEVEL SECURITY;

The FORCE form is the multi-tenant default — even the table owner needs to satisfy the policy.

3. Standard Patterns

Tenant isolation

CREATE POLICY tenant_isolation ON documents
  AS PERMISSIVE
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

current_setting('app.tenant_id') is set per-request by the auth layer (the embedded server, the REST handler, or your application code via set_current_context).

Read-only public data + writeable owned data

Two policies on one table compose with OR:

-- Anyone can SELECT a public document.
CREATE POLICY public_select ON documents
  AS PERMISSIVE
  FOR SELECT
  USING (visibility = 'public');


-- Owners can do anything with their own documents.
CREATE POLICY owner_full ON documents
  AS PERMISSIVE
  FOR ALL
  USING (owner_id = current_setting('app.user_id')::uuid)
  WITH CHECK (owner_id = current_setting('app.user_id')::uuid);

A SELECT is allowed if either policy permits it; an INSERT succeeds only if the WITH CHECK of the matching policy passes.

Restrictive policies (AND)

AS RESTRICTIVE makes the policy AND-combine instead of OR-combine. Useful for “must satisfy both privacy and tenancy”:

CREATE POLICY pii_redaction ON users
  AS RESTRICTIVE
  FOR SELECT
  USING (NOT pii_only OR has_role('compliance'));

A user with compliance role bypasses pii_only; everyone else has the row hidden.

Role-aware policies

CREATE POLICY admins_see_all ON audit_log
  FOR SELECT
  USING (current_setting('app.user_role') = 'admin');


CREATE POLICY users_own_only ON audit_log
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::uuid);

Either policy can satisfy the SELECT; admins get the unrestricted view.

4. Policy Expressions

Policy expressions use PostgreSQL syntax. Compound identifiers (schema.table.column) are preserved with table qualifiers — joins work without ambiguity. Binary ops, unary ops, function calls, IN, LIKE, etc. are supported — anything you can put in a SQL WHERE clause is fair game in a policy.

5. Composition Reference

Two policies, both PERMISSIVEEffect: policy_a OR policy_b
Two policies, one PERMISSIVE, one RESTRICTIVEpermissive_set AND restrictive_set
Two policies, both RESTRICTIVErestrictive_a AND restrictive_b
FOR ALLApplies to every command
FOR SELECT onlyOther commands unaffected (i.e. allowed)
Multiple FOR SELECTOR-combined for visible rows

Match the table-owner contract: if FORCE is on, the owner is also subject to the same set.

6. Putting It Together — Multi-Tenant SaaS

-- Schema
CREATE TABLE documents (
    id          UUID PRIMARY KEY,
    tenant_id   UUID NOT NULL,
    owner_id    UUID NOT NULL,
    title       TEXT,
    body        TEXT,
    visibility  TEXT NOT NULL CHECK (visibility IN ('private', 'team', 'public'))
);


ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;


-- 1. Tenancy is mandatory.
CREATE POLICY tenant_only ON documents
  AS RESTRICTIVE FOR ALL
  USING       (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK  (tenant_id = current_setting('app.tenant_id')::uuid);


-- 2. Public docs visible to anyone in the tenant.
CREATE POLICY public_read ON documents
  AS PERMISSIVE FOR SELECT
  USING (visibility = 'public');


-- 3. Owner can do anything with their own.
CREATE POLICY owner_full ON documents
  AS PERMISSIVE FOR ALL
  USING       (owner_id = current_setting('app.user_id')::uuid)
  WITH CHECK  (owner_id = current_setting('app.user_id')::uuid);


-- 4. Team members can read team-visible docs.
CREATE POLICY team_read ON documents
  AS PERMISSIVE FOR SELECT
  USING (
      visibility = 'team'
      AND owner_id IN (
          SELECT user_id FROM team_membership
          WHERE team_id = current_setting('app.team_id')::uuid
      )
  );

The combined effect: every operation must satisfy the restrictive tenant_only AND at least one permissive policy.

Where Next