Row-Level Security (RLS) Policy Management Tutorial¶

Version: 3.2.0 Last Updated: December 12, 2025 Difficulty: Intermediate

Table of Contents¶

Introduction
Understanding RLS Concepts
Getting Started
Creating RLS Policies
Policy Commands Reference
Common Use Cases
Advanced Patterns
Troubleshooting
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 v3.2.0 or later
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¶

Policy Name: Unique identifier for the policy (e.g., "tenant_isolation")
Table: The table this policy applies to
Expression: Boolean condition that determines row visibility
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:

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¶

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

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

\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¶

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

🎉 Success! You've 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:

tenant_id=current_tenant()

Complex Conditions:

tenant_id=current_tenant() AND status='active'

With Functions:

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¶

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

Example:

\tenant rls create orders order_isolation tenant_id=current_tenant() ALL

List Policies¶

\tenant rls list <table>

Example:

\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¶

\tenant rls delete <table> <policy>

Example:

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

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:

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

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

\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)

Use Case 3: Status-Based Filtering¶

Scenario: Only show active records to tenants.

Policy:

\tenant rls create customers active_only tenant_id=current_tenant() AND status='active' SELECT

Testing:

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

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

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

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

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).

\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).

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

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.

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

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

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

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

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

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

-- List all policies
\tenant rls list customers

-- Remove conflicting policies
\tenant rls delete customers old_policy

-- Best practice: Use one comprehensive policy per table

Best Practices¶

1. Always Include tenant_id in Schema¶

-- ✓ 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¶

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¶

-- ✓ 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¶

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

-- policies.sql
-- RLS Policy Registry
-- Last Updated: 2025-12-12

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

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

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¶

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

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

Practice: Work through the SQL test scripts
Advanced: Read the Multi-Tenancy Implementation Report
Integration: Learn about CDC and Migration

Additional Resources¶

RLS Quick Start Guide
Multi-Tenancy Test Coverage
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: GitHub Issues