RLS (Row-Level Security) Quick Start Guide

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

• Read the full RLS implementation guide
• Review tenant management API
• See RLS implementation complete

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