---
title: "Row-Level Security (RLS) Policy Management Tutorial"
---

# Row-Level Security (RLS) Policy Management Tutorial

**Difficulty:** Intermediate

---

## Table of Contents

1. [Introduction](#introduction)
2. [Understanding RLS Concepts](#understanding-rls-concepts)
3. [Getting Started](#getting-started)
4. [Creating RLS Policies](#creating-rls-policies)
5. [Policy Commands Reference](#policy-commands-reference)
6. [Common Use Cases](#common-use-cases)
7. [Advanced Patterns](#advanced-patterns)
8. [Troubleshooting](#troubleshooting)
9. [Best Practices](#best-practices)

---

## Introduction

Row-Level Security (RLS) is HeliosDB-Lite's mechanism for enforcing data isolation in multi-tenant applications. RLS policies automatically filter data based on the current tenant context, ensuring complete isolation without application-level logic.

### What You'll Learn

- How to create and manage RLS policies through the REPL
- Understanding policy expressions and commands
- Building secure multi-tenant applications
- Debugging and testing RLS policies

### Prerequisites

- HeliosDB Lite
- Basic understanding of SQL
- Familiarity with tenant management (`\tenant` commands)

---

## Understanding RLS Concepts

### What is Row-Level Security?

RLS adds an invisible `WHERE` clause to every SQL query, filtering rows based on policy rules. When a tenant context is active, users can only see and modify rows that match their tenant ID.

### Key Components

1. **Policy Name**: Unique identifier for the policy (e.g., "tenant_isolation")
2. **Table**: The table this policy applies to
3. **Expression**: Boolean condition that determines row visibility
4. **Command**: Which SQL operations this policy affects (ALL, SELECT, INSERT, UPDATE, DELETE)

### How RLS Works

```
User Query:
  SELECT * FROM customers;

With RLS Policy Active:
  SELECT * FROM customers WHERE tenant_id = 'acme-corp';
                                  ^^^^^^^^^^^^^^^^^^^
                                  Auto-injected by RLS
```

### Policy Types

HeliosDB-Lite supports different expressions for different purposes:

- **using_expr**: Filters rows for SELECT, UPDATE, DELETE
- **with_check_expr**: Validates new/updated rows for INSERT, UPDATE
- **insert_expr**: Specifically for INSERT validation

---

## Getting Started

### Step 1: Create a Multi-Tenant Table

First, create a table with a `tenant_id` column:

```sql
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
```

**Important:** Always include a `tenant_id` column in tables that need RLS protection.

### Step 2: Create Tenants

```sql
\tenant create acme-corp free
\tenant create globex-inc pro
```

### Step 3: Insert Test Data (Admin Context)

Without a tenant context (admin mode), insert data for both tenants:

```sql
-- Ensure no tenant context is active
\tenant clear

INSERT INTO customers (id, tenant_id, name, email) VALUES
  (1, 'acme-corp', 'Alice Admin', 'alice@acme.com'),
  (2, 'acme-corp', 'Bob Builder', 'bob@acme.com'),
  (10, 'globex-inc', 'Charlie CEO', 'charlie@globex.com'),
  (11, 'globex-inc', 'Diana Dev', 'diana@globex.com');

-- Verify all data is inserted
SELECT * FROM customers;
-- Should show all 4 rows
```

### Step 4: Create Your First RLS Policy

```sql
\tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL
```

**Breakdown:**
- `customers` - Table name
- `tenant_isolation` - Policy name
- `tenant_id=current_tenant()` - Expression (filter rows where tenant_id matches current context)
- `ALL` - Apply to all SQL commands

### Step 5: Test the Policy

```sql
-- Switch to Acme Corp tenant
\tenant use acme-corp

-- Query customers
SELECT * FROM customers;
-- Result: Only shows Alice and Bob (acme-corp data)

-- Switch to Globex Inc tenant
\tenant use globex-inc

-- Query customers
SELECT * FROM customers;
-- Result: Only shows Charlie and Diana (globex-inc data)
```

You have created your first RLS policy.

---

## Creating RLS Policies

### Command Syntax

```
\tenant rls create <table> <policy_name> <expression> <command>
```

### Parameters

| Parameter | Description | Example |
|-----------|-------------|---------|
| `table` | Target table name | `customers` |
| `policy_name` | Unique policy identifier | `tenant_isolation` |
| `expression` | Boolean SQL expression | `tenant_id=current_tenant()` |
| `command` | SQL operation scope | `ALL`, `SELECT`, `INSERT`, `UPDATE`, `DELETE` |

### Expression Syntax

The expression is a SQL boolean condition that gets evaluated for each row:

**Simple Equality:**
```sql
tenant_id=current_tenant()
```

**Complex Conditions:**
```sql
tenant_id=current_tenant() AND status='active'
```

**With Functions:**
```sql
tenant_id=current_tenant() AND created_at >= current_date()
```

### Command Types

| Command | Affects | Use Case |
|---------|---------|----------|
| `ALL` | All operations | Complete table isolation |
| `SELECT` | Read queries only | View restrictions |
| `INSERT` | Insert operations | Prevent cross-tenant writes |
| `UPDATE` | Update operations | Protect existing data |
| `DELETE` | Delete operations | Prevent accidental deletion |

---

## Policy Commands Reference

### Create Policy

```sql
\tenant rls create <table> <policy> <expression> <command>
```

**Example:**
```sql
\tenant rls create orders order_isolation tenant_id=current_tenant() ALL
```

### List Policies

```sql
\tenant rls list <table>
```

**Example:**
```sql
\tenant rls list customers
```

**Output:**
```
RLS Policies for 'customers':
────────────────────────────────────────────────────────────
Policy Name         Command    Expression
────────────────────────────────────────────────────────────
tenant_isolation    ALL        tenant_id=current_tenant()
admin_override      SELECT     role='admin' OR tenant_id=current_tenant()
```

### Delete Policy

```sql
\tenant rls delete <table> <policy>
```

**Example:**
```sql
\tenant rls delete customers tenant_isolation
```

**Warning:** Deleting a policy removes data protection. Ensure this is intended.

---

## Common Use Cases

### Use Case 1: Basic Multi-Tenant Isolation

**Scenario:** SaaS application where each company should only see their data.

**Tables:**
```sql
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    name TEXT,
    email TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    customer_id INTEGER,
    amount DECIMAL,
    status TEXT
);

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    order_id INTEGER,
    total_amount DECIMAL,
    paid BOOLEAN
);
```

**Policies:**
```sql
-- Protect all tables
\tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create orders tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create invoices tenant_isolation tenant_id=current_tenant() ALL
```

**Result:** Complete data isolation across all tables.

---

### Use Case 2: Read-Only Access with Write Protection

**Scenario:** Tenant can view all data but only modify their own.

**Policy:**
```sql
-- Allow reading all data
\tenant rls create customers read_all 1=1 SELECT

-- Restrict modifications to own tenant
\tenant rls create customers write_own tenant_id=current_tenant() INSERT
\tenant rls create customers write_own tenant_id=current_tenant() UPDATE
\tenant rls create customers write_own tenant_id=current_tenant() DELETE
```

**Testing:**
```sql
\tenant use acme-corp

-- Can see all data
SELECT * FROM customers;  -- Shows all tenants

-- Can only modify own data
UPDATE customers SET email='new@acme.com' WHERE id=1;  -- ✓ Works (Acme row)
UPDATE customers SET email='new@globex.com' WHERE id=10;  -- ✗ Fails (Globex row)
```

---

**Scenario:** Only show active records to tenants.

**Policy:**
```sql
\tenant rls create customers active_only tenant_id=current_tenant() AND status='active' SELECT
```

**Testing:**
```sql
-- Insert mixed status data
INSERT INTO customers (id, tenant_id, name, status) VALUES
  (1, 'acme-corp', 'Active User', 'active'),
  (2, 'acme-corp', 'Inactive User', 'inactive'),
  (3, 'acme-corp', 'Suspended User', 'suspended');

\tenant use acme-corp

SELECT * FROM customers;
-- Result: Only shows "Active User"
```

---

### Use Case 4: Time-Based Access

**Scenario:** Users can only see records from the last 90 days.

**Policy:**
```sql
\tenant rls create orders recent_orders tenant_id=current_tenant() AND created_at >= date('now', '-90 days') SELECT
```

---

### Use Case 5: Role-Based Overrides

**Scenario:** Admin users can see all tenant data, regular users see only their tenant.

**Setup:**
```sql
-- Add role column to track user roles
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';

-- Policy with role check
\tenant rls create customers admin_or_tenant role='admin' OR tenant_id=current_tenant() ALL
```

**How it works:**
- If `role='admin'`: Expression evaluates to TRUE → sees all data
- If `role='user'`: Falls back to tenant check → sees only their data

---

## Advanced Patterns

### Pattern 1: Hierarchical Tenants

**Scenario:** Parent organizations can see child organization data.

```sql
-- Add parent_tenant_id column
ALTER TABLE customers ADD COLUMN parent_tenant_id TEXT;

-- Policy allowing parent access
\tenant rls create customers hierarchical tenant_id=current_tenant() OR parent_tenant_id=current_tenant() SELECT
```

**Example:**
```sql
INSERT INTO customers (id, tenant_id, parent_tenant_id, name) VALUES
  (1, 'acme-corp', NULL, 'Parent Org'),
  (2, 'acme-subsidiary', 'acme-corp', 'Child Org');

\tenant use acme-corp
SELECT * FROM customers;
-- Result: Shows both parent (1) and child (2)

\tenant use acme-subsidiary
SELECT * FROM customers;
-- Result: Shows only child (2)
```

---

### Pattern 2: Multi-Column Tenant Identification

**Scenario:** Tenant ID is composite (region + company).

```sql
\tenant rls create customers regional tenant_id=current_tenant() AND region=current_region() ALL
```

---

### Pattern 3: Cross-Tenant Shared Data

**Scenario:** Some rows are shared across all tenants (e.g., public products).

```sql
-- Add is_public flag
ALTER TABLE products ADD COLUMN is_public BOOLEAN DEFAULT FALSE;

-- Policy: Show own tenant's products OR public products
\tenant rls create products shared_products tenant_id=current_tenant() OR is_public=TRUE SELECT
```

**Testing:**
```sql
INSERT INTO products (id, tenant_id, name, is_public) VALUES
  (1, 'acme-corp', 'Acme Widget', FALSE),
  (2, 'globex-inc', 'Globex Gadget', FALSE),
  (99, 'system', 'Public Template', TRUE);

\tenant use acme-corp
SELECT * FROM products;
-- Result: Shows "Acme Widget" (own) and "Public Template" (shared)
```

---

### Pattern 4: Audit Logging Without Isolation

**Scenario:** Audit table should log all events but RLS shouldn't filter reads.

```sql
-- Don't create RLS policy on audit table
CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT,
    action TEXT,
    timestamp TEXT
);

-- No RLS policy applied
-- Admins can query full audit log
```

---

### Pattern 5: Dynamic Policy Expressions

**Scenario:** Policy changes based on time or configuration.

```sql
-- Example: Allow access only during business hours
\tenant rls create sensitive_data business_hours tenant_id=current_tenant() AND time('now') BETWEEN time('09:00') AND time('17:00') SELECT
```

---

## Troubleshooting

### Problem 1: Policy Not Filtering Data

**Symptom:** After creating a policy, users still see all data.

**Causes:**
1. No tenant context is set
2. Expression syntax error
3. Column name mismatch

**Solution:**
```sql
-- Check current context
\tenant current
-- Output: Should show active tenant, not "No tenant context"

-- Verify policy exists
\tenant rls list customers

-- Check table schema
\d customers
-- Verify tenant_id column exists
```

---

### Problem 2: "No Rows Returned" After Policy Creation

**Symptom:** Queries return empty results after adding RLS.

**Cause:** Expression evaluates to FALSE for all rows.

**Solution:**
```sql
-- Test expression manually
SELECT *,
       (tenant_id = current_tenant()) as policy_match
FROM customers;

-- If policy_match is always FALSE, check:
-- 1. Is current_tenant() returning correct value?
-- 2. Does tenant_id column have correct format?
-- 3. Are there rows for this tenant?
```

---

### Problem 3: Cannot Insert Data

**Symptom:** INSERT statements fail with policy active.

**Cause:** `with_check_expr` prevents writes that don't match tenant.

**Solution:**
```sql
-- Always include tenant_id in inserts
INSERT INTO customers (id, tenant_id, name) VALUES (1, current_tenant(), 'Alice');

-- Don't do this (missing tenant_id):
INSERT INTO customers (id, name) VALUES (1, 'Alice');  -- ✗ Fails
```

---

### Problem 4: Performance Degradation

**Symptom:** Queries slow after adding RLS policies.

**Cause:** Missing index on tenant_id column.

**Solution:**
```sql
-- Add index to tenant_id column
CREATE INDEX idx_customers_tenant ON customers(tenant_id);

-- Verify index exists
\di customers
```

---

### Problem 5: Policy Conflicts

**Symptom:** Multiple policies on same table causing unexpected behavior.

**Solution:**
```sql
-- List all policies
\tenant rls list customers

-- Remove conflicting policies
\tenant rls delete customers old_policy

-- Best practice: use one policy per table that covers every case
```

---

## Best Practices

### 1. Always Include tenant_id in Schema

```sql
-- ✓ Good
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,  -- Required for RLS
    ...
);

-- ✗ Bad
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    -- Missing tenant_id!
    ...
);
```

---

### 2. Create Indexes on tenant_id

```sql
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_customers_tenant ON customers(tenant_id);
```

**Why:** RLS adds `WHERE tenant_id =...` to every query. Indexes dramatically improve performance.

---

### 3. Use Consistent Naming

```sql
-- ✓ Good - Consistent policy names
\tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create orders tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create invoices tenant_isolation tenant_id=current_tenant() ALL

-- ✗ Bad - Inconsistent names make management harder
\tenant rls create customers policy1 ...
\tenant rls create orders iso ...
\tenant rls create invoices filter123 ...
```

---

### 4. Test Policies Thoroughly

```sql
-- Test script for new RLS policy
\tenant clear

-- 1. Insert test data for multiple tenants
INSERT INTO customers (id, tenant_id, name) VALUES
  (1, 'tenant-a', 'User A'),
  (2, 'tenant-b', 'User B');

-- 2. Verify admin can see all
SELECT * FROM customers;  -- Should show 2 rows

-- 3. Apply policy
\tenant rls create customers test_policy tenant_id=current_tenant() ALL

-- 4. Test tenant A
\tenant use tenant-a
SELECT * FROM customers;  -- Should show only User A

-- 5. Test tenant B
\tenant use tenant-b
SELECT * FROM customers;  -- Should show only User B

-- 6. Test cross-tenant write protection
INSERT INTO customers (id, tenant_id, name) VALUES (3, 'tenant-a', 'Hacker');
\tenant use tenant-a
SELECT * FROM customers WHERE id=3;  -- Should return 0 rows
```

---

### 5. Document Your Policies

Create a policy registry file:

```sql
-- policies.sql
-- RLS Policy Registry

-- Customers table: Complete tenant isolation
\tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL

-- Orders table: Tenant isolation with status filter
\tenant rls create orders tenant_isolation tenant_id=current_tenant() AND status!='deleted' ALL

-- Products table: Tenant data + shared public products
\tenant rls create products shared_access tenant_id=current_tenant() OR is_public=TRUE SELECT
```

---

### 6. Separate Admin and Tenant Operations

```sql
-- Admin operations: Clear context first
\tenant clear
-- ... create tenants, manage policies, view all data

-- Tenant operations: Set context
\tenant use acme-corp
-- ... normal business operations
```

---

### 7. Use Transactions for Multi-Table Policies

```sql
BEGIN TRANSACTION;

\tenant rls create customers tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create orders tenant_isolation tenant_id=current_tenant() ALL
\tenant rls create invoices tenant_isolation tenant_id=current_tenant() ALL

COMMIT;
```

---

### 8. Monitor Policy Performance

```sql
-- Check query execution plans
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE name LIKE 'A%';

-- Look for index usage on tenant_id
-- Expected: "SEARCH customers USING INDEX idx_customers_tenant"
```

---

### 9. Implement Policy Versioning

Keep a history of policy changes:

```sql
-- v1.0 - Initial policy (2025-01-15)
-- \tenant rls create customers v1 tenant_id=current_tenant() ALL

-- v2.0 - Added status filter (2025-03-20)
\tenant rls delete customers v1
\tenant rls create customers v2 tenant_id=current_tenant() AND status='active' ALL
```

---

### 10. Regular Policy Audits

Quarterly checklist:
- [ ] Review all policies: `\tenant rls list <table>` for each table
- [ ] Remove unused policies
- [ ] Verify expressions are still correct
- [ ] Check performance with `EXPLAIN`
- [ ] Test with sample tenant contexts

---

## Quick Reference Card

### Essential Commands

| Command | Purpose |
|---------|---------|
| `\tenant rls create <table> <policy> <expr> <cmd>` | Create new policy |
| `\tenant rls list <table>` | List table policies |
| `\tenant rls delete <table> <policy>` | Remove policy |
| `\tenant use <tenant>` | Set tenant context |
| `\tenant current` | Show active tenant |
| `\tenant clear` | Clear context (admin) |

### Policy Expression Examples

| Use Case | Expression |
|----------|------------|
| Basic isolation | `tenant_id=current_tenant()` |
| With status filter | `tenant_id=current_tenant() AND status='active'` |
| Time-based | `tenant_id=current_tenant() AND created_at >= date('now', '-90 days')` |
| Shared data | `tenant_id=current_tenant() OR is_public=TRUE` |
| Role override | `role='admin' OR tenant_id=current_tenant()` |

---

## Next Steps

1. **Integration:** Learn about [CDC and Migration](https://www.heliosdb.com/docs/lite/guides/cdc_and_migration_tutorial/)

---

## Additional Resources

- [RLS Quick Start Guide](https://www.heliosdb.com/docs/lite/guides/rls_quickstart/)
- [REPL Help System](./): Run `\h tenants` in REPL

---

**Need Help?**

- Check existing policies: `\tenant rls list <table>`
- Verify tenant context: `\tenant current`
- Test expressions manually with SELECT
- Review audit logs for policy violations

**Report Issues:** [Email support](mailto:support@heliosdb.com)