---
title: "Advanced Row-Level Security Policies"
---

# 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

- HeliosDB Lite
- Familiarity with [RLS_POLICY_MANAGEMENT_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/rls_policy_management_tutorial/) basics
- 20 minutes

---

## 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

```sql
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
```

Owners are exempt unless you also force it:

```sql
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

```sql
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:

```sql
-- 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":

```sql
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

```sql
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 `PERMISSIVE` | Effect: `policy_a OR policy_b` |
|---|---|
| Two policies, one `PERMISSIVE`, one `RESTRICTIVE` | `permissive_set AND restrictive_set` |
| Two policies, both `RESTRICTIVE` | `restrictive_a AND restrictive_b` |
| `FOR ALL` | Applies to every command |
| `FOR SELECT` only | Other commands unaffected (i.e. allowed) |
| Multiple `FOR SELECT` | OR-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

```sql
-- 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

- [Multi-Tenancy](/docs/lite/features/multi-tenancy/) — pair RLS with tenant quotas for full isolation.
- [CDC and Tenant Migration Tutorial](https://www.heliosdb.com/docs/lite/guides/cdc_and_migration_tutorial/) — make sure your change feed honours the same boundaries.
- [DATABASE_BRANCHING_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/database_branching_tutorial/) — per-tenant branches inherit RLS.
- [RLS_POLICY_MANAGEMENT_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/rls_policy_management_tutorial/) — the basics, if you're new to RLS.
- [RLS_QUICKSTART](https://www.heliosdb.com/docs/lite/guides/rls_quickstart/) — 5-minute setup.