Row-Level Security

🔒 Fine-Grained Access Control with RLS

What is RLS?

Row-Level Security (RLS) allows you to control which rows users can access based on policies.

RLS is implemented on top of PostgreSQL's native row security (ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY + CREATE POLICY), managed via a REST API rather than raw SQL. The current authenticated user's JWT claims are made available to policy expressions through current_setting('request.jwt.claims', true)::json->>'<claim>' — there is no auth.uid()/auth.role() helper function.

Creating Policies

POST /v1/rls/:table/enable            # ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY
POST /v1/rls/:table/policies          # Create a policy
GET  /v1/rls/:table/policies          # List policies on a table
DELETE /v1/rls/:table/policies/:name  # Drop a policy
GET  /v1/rls/:table/status            # Whether RLS is enabled on a table
GET  /v1/rls/audit                    # Audit log of RLS changes
POST /v1/rls/:table/disable           # ALTER TABLE ... DISABLE ROW LEVEL SECURITY

# Example: Users can only see their own data
curl -X POST http://localhost:3000/v1/rls/documents/policies \
  -H "Authorization: Bearer <jwt_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "user_isolation",
    "operation": "SELECT",
    "using_expr": "owner_id = (current_setting(\'request.jwt.claims\', true)::json->>\'user_id\')::bigint",
    "permissive": true
  }'

operation is one of ALL (default), SELECT, INSERT, UPDATE, DELETE. using_expr controls which existing rows are visible/affected; check_expr (optional) constrains rows being inserted/updated. Both expressions are validated against a SQL-injection allowlist (SchemaGuard::validate_sql_expression) before being interpolated into the generated CREATE POLICY statement. permissive (default true) selects PERMISSIVE vs RESTRICTIVE policy semantics, matching Postgres's own policy combination rules.

Policy Evaluation

Rendering diagram…

Example Policies

// Policy 1: Users see own data — POST /v1/rls/documents/policies
{
  "name": "user_isolation",
  "operation": "SELECT",
  "using_expr": "owner_id = (current_setting('request.jwt.claims', true)::json->>'user_id')::bigint"
}

// Policy 2: Public documents visible to all — POST /v1/rls/documents/policies
{
  "name": "public_docs",
  "operation": "SELECT",
  "using_expr": "is_public = true"
}

// Policy 3: Editors can modify — POST /v1/rls/documents/policies
{
  "name": "editor_access",
  "operation": "UPDATE",
  "using_expr": "current_setting('request.jwt.claims', true)::json->>'role' IN ('editor', 'admin')"
}

Best Practices

Start restrictive - Deny all, then allow specific
Test policies - Use test mode to verify
Log denials - Monitor unauthorized access attempts
Keep it simple - Complex policies are hard to debug

Next: Storage