RLS (Row-Level Security) Quick Start Guide¶

What is RLS?¶

Row-Level Security (RLS) allows you to restrict which rows users can access in multi-tenant applications. Each tenant's data is automatically isolated without requiring manual WHERE clause filtering.

Quick Setup (5 minutes)¶

1. Register Tenants¶

use heliosdb_lite::{EmbeddedDatabase, tenant::*};
use uuid::Uuid;

let db = EmbeddedDatabase::new("./data")?;
let tenant_mgr = db.tenant_manager();

// Register two tenants
let tenant_a = tenant_mgr.register_tenant(
    "Acme Corp".to_string(),
    IsolationMode::SharedSchema,  // Enables RLS
);

let tenant_b = tenant_mgr.register_tenant(
    "Beta LLC".to_string(),
    IsolationMode::SharedSchema,
);

2. Create Table with Tenant Column¶

db.execute("
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        tenant_id TEXT NOT NULL,
        product TEXT,
        amount DECIMAL(10,2)
    )
")?;

3. Define RLS Policy¶

tenant_mgr.create_rls_policy(
    "orders".to_string(),                          // table_name
    "tenant_isolation".to_string(),                 // policy_name
    "Isolate orders by tenant".to_string(),        // description
    RLSCommand::All,                                // applies to all operations
    "tenant_id = current_tenant()".to_string(),    // using expression
    Some("tenant_id = current_tenant()".to_string()), // with_check expression
);

4. Set Tenant Context & Query¶

// Set context for Tenant A
tenant_mgr.set_current_context(TenantContext {
    tenant_id: tenant_a.id,
    user_id: "user@acme.com".to_string(),
    roles: vec!["user".to_string()],
    isolation_mode: IsolationMode::SharedSchema,
});

// Insert data - automatically validates tenant_id
db.execute(&format!(
    "INSERT INTO orders VALUES (1, '{}', 'Widget', 99.99)",
    tenant_a.id
))?;

// This will FAIL - wrong tenant_id
db.execute(&format!(
    "INSERT INTO orders VALUES (2, '{}', 'Gadget', 49.99)",
    tenant_b.id
))?;
// Error: Row-Level Security policy violation

// Query - automatically filters by tenant_id
let results = db.query("SELECT * FROM orders")?;
// Only returns orders for tenant_a

RLS Expression Syntax¶

Supported Operators¶

Operator	Example	Description
`=`	`tenant_id = current_tenant()`	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`	`tenant_id = current_tenant() AND active = true`	Logical AND
`OR`	`owner_id = 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

Supported Functions¶

Function	Returns	Example
`current_tenant()`	Current tenant ID	`tenant_id = current_tenant()`
`current_setting('var')`	Setting value	`user_id = current_setting('app.current_user')`

Policy Types¶

1. Using Expression (SELECT, UPDATE, DELETE)¶

Controls which rows are visible to the tenant:

"tenant_id = current_tenant()"

2. With Check Expression (INSERT, UPDATE)¶

Validates that new/modified rows satisfy the policy:

"tenant_id = current_tenant() AND status IN ('active', 'pending')"

Common Patterns¶

Pattern 1: Simple Tenant Isolation¶

tenant_mgr.create_rls_policy(
    table_name.to_string(),
    "tenant_isolation".to_string(),
    "Basic tenant isolation".to_string(),
    RLSCommand::All,
    "tenant_id = current_tenant()".to_string(),
    Some("tenant_id = current_tenant()".to_string()),
);

Pattern 2: User-Level Isolation¶

tenant_mgr.create_rls_policy(
    "private_notes".to_string(),
    "user_isolation".to_string(),
    "Users see only their notes".to_string(),
    RLSCommand::All,
    "user_id = current_setting('app.current_user')".to_string(),
    Some("user_id = current_setting('app.current_user')".to_string()),
);

Pattern 3: Hierarchical Access¶

// Managers see all in their tenant, users see only theirs
tenant_mgr.create_rls_policy(
    "tasks".to_string(),
    "hierarchical_access".to_string(),
    "Managers see all, users see own".to_string(),
    RLSCommand::Select,
    "tenant_id = current_tenant() AND (assigned_to = current_setting('app.current_user') OR current_setting('app.is_manager') = 'true')".to_string(),
    None,
);

Pattern 4: Soft Delete with RLS¶

tenant_mgr.create_rls_policy(
    "documents".to_string(),
    "hide_deleted".to_string(),
    "Hide soft-deleted records".to_string(),
    RLSCommand::Select,
    "tenant_id = current_tenant() AND deleted_at IS NULL".to_string(),
    None,
);

Per-Operation Policies¶

Different Policies for Different Operations¶

// Read policy - broad access
tenant_mgr.create_rls_policy(
    "reports".to_string(),
    "read_policy".to_string(),
    "Read access".to_string(),
    RLSCommand::Select,
    "tenant_id = current_tenant()".to_string(),
    None,
);

// Write policy - restricted access
tenant_mgr.create_rls_policy(
    "reports".to_string(),
    "write_policy".to_string(),
    "Write access".to_string(),
    RLSCommand::Insert,
    "tenant_id = current_tenant()".to_string(),
    Some("tenant_id = current_tenant() AND created_by = current_setting('app.current_user')".to_string()),
);

Testing Your Policies¶

Test Isolation¶

#[test]
fn test_tenant_isolation() {
    let db = EmbeddedDatabase::new(":memory:")?;
    let tenant_mgr = db.tenant_manager();

    // Setup
    let tenant_a = tenant_mgr.register_tenant("A".to_string(), IsolationMode::SharedSchema);
    let tenant_b = tenant_mgr.register_tenant("B".to_string(), IsolationMode::SharedSchema);

    db.execute("CREATE TABLE data (id INT, tenant_id TEXT, value TEXT)")?;

    tenant_mgr.create_rls_policy(
        "data".to_string(),
        "isolation".to_string(),
        "Test".to_string(),
        RLSCommand::All,
        "tenant_id = current_tenant()".to_string(),
        Some("tenant_id = current_tenant()".to_string()),
    );

    // Insert as tenant A
    tenant_mgr.set_current_context(TenantContext {
        tenant_id: tenant_a.id,
        user_id: "a".to_string(),
        roles: vec![],
        isolation_mode: IsolationMode::SharedSchema,
    });

    db.execute(&format!("INSERT INTO data VALUES (1, '{}', 'A data')", tenant_a.id))?;

    // Insert as tenant B
    tenant_mgr.set_current_context(TenantContext {
        tenant_id: tenant_b.id,
        user_id: "b".to_string(),
        roles: vec![],
        isolation_mode: IsolationMode::SharedSchema,
    });

    db.execute(&format!("INSERT INTO data VALUES (2, '{}', 'B data')", tenant_b.id))?;

    // Verify isolation
    let results = db.query("SELECT * FROM data")?;
    assert_eq!(results.len(), 1);  // Only sees tenant B's data

    // Switch back to A
    tenant_mgr.set_current_context(TenantContext {
        tenant_id: tenant_a.id,
        user_id: "a".to_string(),
        roles: vec![],
        isolation_mode: IsolationMode::SharedSchema,
    });

    let results = db.query("SELECT * FROM data")?;
    assert_eq!(results.len(), 1);  // Only sees tenant A's data
}

Debugging RLS¶

Check if RLS is Applied¶

let should_apply = tenant_mgr.should_apply_rls("orders", "SELECT");
println!("RLS applied: {}", should_apply);

Get Policy Conditions¶

if let Some((using_expr, with_check)) = tenant_mgr.get_rls_conditions("orders", "INSERT") {
    println!("Using: {}", using_expr);
    if let Some(check) = with_check {
        println!("With Check: {}", check);
    }
}

Verify Tenant Context¶

if let Some(context) = tenant_mgr.get_current_context() {
    println!("Tenant: {}", context.tenant_id);
    println!("User: {}", context.user_id);
    println!("Roles: {:?}", context.roles);
}

Performance Tips¶

1. Index Tenant Columns¶

CREATE INDEX idx_orders_tenant ON orders(tenant_id);

2. Use Proper Data Types¶

-- UUID for tenant_id (better performance)
CREATE TABLE orders (
    id INTEGER,
    tenant_id TEXT,  -- Store UUID as TEXT or BLOB
    ...
);

3. Keep Policies Simple¶

✅ Good:

"tenant_id = current_tenant()"

⚠️ Complex (slower):

"tenant_id = current_tenant() AND (status = 'active' OR (status = 'pending' AND created_at > '2025-01-01'))"

Security Best Practices¶

1. Always Set Context¶

// In your request handler
fn handle_request(tenant_id: Uuid, user_id: String) {
    tenant_mgr.set_current_context(TenantContext {
        tenant_id,
        user_id,
        roles: get_user_roles(&user_id),
        isolation_mode: IsolationMode::SharedSchema,
    });

    // ... handle request ...

    tenant_mgr.clear_current_context();  // Clean up
}

2. Validate Tenant Ownership¶

// Before setting context, verify user belongs to tenant
if !user_belongs_to_tenant(&user_id, &tenant_id) {
    return Err("Access denied".into());
}

3. Use WITH CHECK¶

Always provide with_check_expr for INSERT/UPDATE policies:

tenant_mgr.create_rls_policy(
    table.to_string(),
    policy.to_string(),
    desc.to_string(),
    RLSCommand::All,
    using_expr.to_string(),
    Some(with_check_expr.to_string()),  // ← Important!
);

4. Test Policy Violations¶

#[test]
fn test_cross_tenant_insert_blocked() {
    // ... setup ...

    let result = db.execute(&format!(
        "INSERT INTO orders VALUES (1, '{}', 'Product', 100)",
        different_tenant_id
    ));

    assert!(result.is_err());
    assert!(result.unwrap_err().to_string().contains("policy violation"));
}

Troubleshooting¶

Problem: RLS not enforced¶

Solution: Check isolation mode is SharedSchema:

let tenant = tenant_mgr.register_tenant(
    name,
    IsolationMode::SharedSchema,  // ← Must be SharedSchema
);

Problem: All queries return empty¶

Solution: Verify tenant context is set:

assert!(tenant_mgr.get_current_context().is_some());

Problem: Policy expression error¶

Solution: Use supported syntax only:

// ✅ Supported
"tenant_id = current_tenant()"

// ❌ Not supported yet
"tenant_id IN (SELECT id FROM allowed_tenants)"

Migration from Manual Filtering¶

Before (Manual)¶

let results = db.query(&format!(
    "SELECT * FROM orders WHERE tenant_id = '{}'",
    tenant_id
))?;

After (RLS)¶

// Set context once
tenant_mgr.set_current_context(context);

// Query without manual filtering
let results = db.query("SELECT * FROM orders")?;

Next Steps¶

Support¶

For issues or questions: 1. Check existing RLS policies: tenant_mgr.get_rls_policies(table_name) 2. Verify context: tenant_mgr.get_current_context() 3. Review error messages for policy violations 4. Test in isolation with unit tests

Version: v3.2.0 Status: Production Ready Last Updated: December 11, 2025