---
title: "RLS (Row-Level Security) Quick Start Guide"
---

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

```bash
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`)

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

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

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

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

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

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

## Common Patterns

### Pattern 1: Owner Isolation

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

### Pattern 2: Public Rows Plus Owned Rows

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

### Pattern 3: Soft Delete

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

### Pattern 4: Tenant Isolation in the REPL

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

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

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

-- Who am I connected as?
SELECT current_user;
```

## Performance Tips

### 1. Index Policy Columns

```sql
CREATE INDEX idx_documents_owner ON documents(owner);
```

### 2. Keep Policies Simple

Good:

```sql
USING (owner = current_user)
```

Complex (slower):

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

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

### After (RLS)

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

## Support

- [Row-Level Security](/docs/lite/features/rls/)
- [RLS Policy Management Tutorial](/docs/lite/guides/rls_policy_management_tutorial/)
- [Advanced Row-Level Security Policies](/docs/lite/guides/tutorials/rls_policy_advanced/)