Row-Level Security (RLS) Policy Management Tutorial

Row-Level Security (RLS) Policy Management Tutorial

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

---

Table of Contents

1. Introduction
2. Understanding RLS Concepts
3. Getting Started
4. Creating RLS Policies
5. Policy Commands Reference
6. Common Use Cases
7. Advanced Patterns
8. Troubleshooting
9. Best Practices

---

Introduction

Row-Level Security (RLS) is HeliosDB Nano’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 Nano 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

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

1. Practice: Work through the SQL test scripts
2. Advanced: Read the Multi-Tenancy Implementation Report
3. 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](https://github.com/dimensigon/HeliosDB Nano/issues)