Tenant Row-Level Security (RLS) Policy API Documentation¶

Version: v1 Base Path: /api/v1/tenants/{id}/rls/policies Authentication: Required Rate Limit: 50 requests/minute per tenant

Overview¶

The Tenant RLS Policy API provides Row-Level Security policy management for fine-grained data access control. RLS policies automatically filter query results based on user context, ensuring complete tenant isolation and preventing cross-tenant data leakage.

Table of Contents¶

Endpoints
POST /tenants/{id}/rls/policies
GET /tenants/{id}/rls/policies
DELETE /tenants/{id}/rls/policies/{policy_id}
Data Models
RLS Expressions
Query Rewriting
Examples
Best Practices
Error Handling

Endpoints¶

POST /tenants/{id}/rls/policies¶

Creates a new Row-Level Security policy for a tenant.

Request¶

POST /api/v1/tenants/{id}/rls/policies HTTP/1.1
Host: api.heliosdb.com
Authorization: Bearer {token}
Content-Type: application/json

{
  "name": "tenant_isolation_users",
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["SELECT", "UPDATE", "DELETE"],
  "allow_superuser_bypass": true,
  "description": "Isolate users by tenant_id column"
}

Path Parameters: - id (UUID, required): Tenant identifier

Request Body:

Field	Type	Required	Description
`name`	string	Yes	Policy name (3-128 chars, alphanumeric, underscores, hyphens)
`table`	string	Yes	Target table name (1-255 chars, alphanumeric, underscores)
`expression`	string	Yes	SQL WHERE clause condition (1-2048 chars)
`operations`	array[string]	Yes	Operations: SELECT, INSERT, UPDATE, DELETE
`allow_superuser_bypass`	boolean	No	Allow superuser bypass (default: true)
`description`	string	No	Policy description (max 512 chars)

Response¶

Success (201 Created):

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_id": "users_tenant_isolation_users",
  "name": "tenant_isolation_users",
  "table": "users",
  "enabled": true,
  "created_at": "2025-12-09T10:30:00Z",
  "message": "RLS policy created successfully"
}

Error Responses: - 400 Bad Request: Invalid policy or validation error - 404 Not Found: Tenant does not exist - 409 Conflict: Policy already exists - 500 Internal Server Error: Server-side error

GET /tenants/{id}/rls/policies¶

Lists all RLS policies for a tenant with optional filtering.

Request¶

GET /api/v1/tenants/{id}/rls/policies?table=users&enabled=true HTTP/1.1
Host: api.heliosdb.com
Authorization: Bearer {token}

Path Parameters: - id (UUID, required): Tenant identifier

Query Parameters:

Parameter	Type	Description
`table`	string	Filter by table name
`enabled`	boolean	Filter by enabled status
`operation`	string	Filter by operation (SELECT, INSERT, UPDATE, DELETE)

Response¶

Success (200 OK):

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policies": [
    {
      "policy_id": "users_tenant_isolation",
      "name": "tenant_isolation",
      "table": "users",
      "expression": "tenant_id = {tenant_id}",
      "operations": ["SELECT", "UPDATE", "DELETE"],
      "allow_superuser_bypass": true,
      "enabled": true,
      "description": "Tenant isolation for users table"
    },
    {
      "policy_id": "orders_customer_access",
      "name": "customer_access",
      "table": "orders",
      "expression": "(tenant_id = {tenant_id} AND status = 'active') OR owner_id = {user_id}",
      "operations": ["SELECT"],
      "allow_superuser_bypass": false,
      "enabled": true,
      "description": "Customer access control for orders"
    }
  ],
  "total_count": 2
}

Error Responses: - 404 Not Found: Tenant does not exist - 500 Internal Server Error: Server-side error

DELETE /tenants/{id}/rls/policies/{policy_id}¶

Deletes an existing RLS policy.

Request¶

DELETE /api/v1/tenants/{id}/rls/policies/users_tenant_isolation HTTP/1.1
Host: api.heliosdb.com
Authorization: Bearer {token}

Path Parameters: - id (UUID, required): Tenant identifier - policy_id (string, required): Policy ID in format "{table}_{policy_name}"

Response¶

Success (200 OK):

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_id": "users_tenant_isolation",
  "policy_name": "tenant_isolation",
  "table": "users",
  "message": "RLS policy 'users_tenant_isolation' deleted successfully",
  "deleted_at": "2025-12-09T10:35:00Z"
}

Error Responses: - 404 Not Found: Tenant or policy does not exist - 500 Internal Server Error: Server-side error

Data Models¶

RlsPolicy¶

Field	Type	Description	Constraints
`policy_id`	string	Unique policy identifier	Format: "{table}_{policy_name}"
`name`	string	Policy name	3-128 chars, alphanumeric, _, -
`table`	string	Target table	1-255 chars, alphanumeric, _
`expression`	string	SQL WHERE condition	1-2048 chars, valid SQL
`operations`	array[RlsOperation]	Applicable operations	SELECT, INSERT, UPDATE, DELETE
`allow_superuser_bypass`	boolean	Superuser bypass flag	true/false
`enabled`	boolean	Policy enabled status	true/false
`description`	string	Policy description	max 512 chars

RlsOperation¶

Enum of operations that can have RLS policies:

SELECT: Applied to SELECT queries
INSERT: Applied to INSERT statements
UPDATE: Applied to UPDATE statements
DELETE: Applied to DELETE statements

RLS Expressions¶

Expression Syntax¶

RLS expressions are SQL WHERE clause conditions that are automatically added to queries.

Supported Placeholders: - {tenant_id}: Current tenant's UUID - {user_id}: Current user's identifier - {role}: Current user's role - {timestamp}: Current timestamp

Expression Examples¶

1. Basic Tenant Isolation¶

tenant_id = {tenant_id}

Description: Filters all rows to current tenant only.

Applied Query:

-- Original
SELECT * FROM users WHERE status = 'active';

-- Rewritten
SELECT * FROM users WHERE (tenant_id = '550e8400-e29b-41d4-a716-446655440000') AND status = 'active';

2. User and Tenant Ownership¶

tenant_id = {tenant_id} AND (owner_id = {user_id} OR public = true)

Description: Allow access to tenant's public data or user's owned data.

3. Department-Based Access¶

tenant_id = {tenant_id} AND department_id IN (
  SELECT id FROM departments WHERE tenant_id = {tenant_id}
)

Description: Restrict access based on department membership.

4. Time-Based Access¶

tenant_id = {tenant_id} AND valid_from <= {timestamp} AND valid_until >= {timestamp}

Description: Filter data based on temporal validity.

5. Hierarchical Access¶

tenant_id = {tenant_id} AND (
  created_by = {user_id} OR
  manager_id = {user_id} OR
  {role} = 'admin'
)

Description: Allow access to own records, subordinate records, or all records for admins.

Expression Validation¶

The API validates expressions for:

SQL Injection Prevention: Blocks dangerous keywords (DROP, TRUNCATE, etc.)
Syntax Checking: Ensures valid SQL WHERE clause syntax
Placeholder Presence: Warns if no tenant_id/user_id placeholders found
Length Limits: Maximum 2048 characters
Empty/Whitespace: Rejects empty or whitespace-only expressions

Query Rewriting¶

How It Works¶

When a query is executed with RLS policies enabled:

Policy Lookup: Identify applicable policies for the query's tables
Context Injection: Replace placeholders with actual values
Expression Combination: Combine policy expressions with AND
Query Rewrite: Inject combined expression into WHERE clause
Execution: Execute rewritten query

Rewriting Examples¶

Example 1: Simple SELECT¶

Original Query:

SELECT * FROM users WHERE email LIKE '%@example.com';

RLS Policy:

{
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["SELECT"]
}

Rewritten Query:

SELECT * FROM users
WHERE (tenant_id = '550e8400-e29b-41d4-a716-446655440000')
  AND email LIKE '%@example.com';

Example 2: JOIN Query¶

Original Query:

SELECT u.*, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

RLS Policies: - Users table: tenant_id = {tenant_id} - Orders table: tenant_id = {tenant_id}

Rewritten Query:

SELECT u.*, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE (u.tenant_id = '550e8400-e29b-41d4-a716-446655440000')
  AND (o.tenant_id = '550e8400-e29b-41d4-a716-446655440000')
  AND o.status = 'completed';

Example 3: UPDATE Statement¶

Original Query:

UPDATE users SET last_login = NOW() WHERE id = 123;

RLS Policy:

{
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["UPDATE"]
}

Rewritten Query:

UPDATE users
SET last_login = NOW()
WHERE (tenant_id = '550e8400-e29b-41d4-a716-446655440000')
  AND id = 123;

Examples¶

Example 1: Create Basic Tenant Isolation Policy¶

curl -X POST https://api.heliosdb.com/api/v1/tenants/550e8400-e29b-41d4-a716-446655440000/rls/policies \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "tenant_isolation",
    "table": "products",
    "expression": "tenant_id = {tenant_id}",
    "operations": ["SELECT", "UPDATE", "DELETE"],
    "allow_superuser_bypass": true,
    "description": "Basic tenant isolation for products table"
  }'

Response:

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_id": "products_tenant_isolation",
  "name": "tenant_isolation",
  "table": "products",
  "enabled": true,
  "created_at": "2025-12-09T10:30:00Z",
  "message": "RLS policy created successfully"
}

Example 2: Create Complex Access Control Policy¶

curl -X POST https://api.heliosdb.com/api/v1/tenants/550e8400-e29b-41d4-a716-446655440000/rls/policies \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "document_access",
    "table": "documents",
    "expression": "(tenant_id = {tenant_id} AND status = '\''active'\'') OR owner_id = {user_id}",
    "operations": ["SELECT", "UPDATE"],
    "allow_superuser_bypass": false,
    "description": "Access control for active documents or owned documents"
  }'

Example 3: List Policies for a Table¶

curl -X GET "https://api.heliosdb.com/api/v1/tenants/550e8400-e29b-41d4-a716-446655440000/rls/policies?table=users" \
  -H "Authorization: Bearer ${TOKEN}"

Response:

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policies": [
    {
      "policy_id": "users_tenant_isolation",
      "name": "tenant_isolation",
      "table": "users",
      "expression": "tenant_id = {tenant_id}",
      "operations": ["SELECT", "UPDATE", "DELETE"],
      "allow_superuser_bypass": true,
      "enabled": true,
      "description": "Tenant isolation for users table"
    }
  ],
  "total_count": 1
}

Example 4: Delete an RLS Policy¶

curl -X DELETE https://api.heliosdb.com/api/v1/tenants/550e8400-e29b-41d4-a716-446655440000/rls/policies/products_tenant_isolation \
  -H "Authorization: Bearer ${TOKEN}"

Response:

{
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_id": "products_tenant_isolation",
  "policy_name": "tenant_isolation",
  "table": "products",
  "message": "RLS policy 'products_tenant_isolation' deleted successfully",
  "deleted_at": "2025-12-09T10:35:00Z"
}

Best Practices¶

1. Policy Design¶

DO: - ✅ Create one policy per table for tenant isolation - ✅ Use descriptive policy names - ✅ Include {tenant_id} placeholder in all multi-tenant policies - ✅ Test policies in staging before production - ✅ Document complex expressions

DON'T: - ❌ Create overly complex expressions that hurt performance - ❌ Use subqueries unless necessary - ❌ Duplicate policies across tables (use table inheritance) - ❌ Hard-code values in expressions

2. Security¶

DO: - ✅ Enable RLS on all tables containing tenant data - ✅ Set allow_superuser_bypass = false for sensitive data - ✅ Validate policy expressions before creation - ✅ Regularly audit active policies - ✅ Use parameterized placeholders ({tenant_id}, {user_id})

DON'T: - ❌ Rely solely on application-layer filtering - ❌ Disable RLS without thorough review - ❌ Allow user input in policy expressions - ❌ Create policies that can be circumvented

3. Performance¶

DO: - ✅ Add indexes on columns used in RLS expressions - ✅ Keep expressions simple and efficient - ✅ Monitor query performance after adding policies - ✅ Use covering indexes for common access patterns

DON'T: - ❌ Use functions that prevent index usage - ❌ Create expressions with multiple subqueries - ❌ Apply RLS to tables with billions of rows without proper indexing

4. Operations¶

DO: - ✅ Apply policies to all relevant operations (SELECT, UPDATE, DELETE) - ✅ Test query rewriting in development - ✅ Monitor RLS metrics and performance - ✅ Document policy changes in changelog

DON'T: - ❌ Skip testing after policy modifications - ❌ Change policies in production without validation - ❌ Delete policies without understanding impact

Policy Patterns¶

Pattern 1: Basic Tenant Isolation¶

Use Case: Multi-tenant SaaS with complete data isolation

{
  "name": "tenant_isolation",
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["SELECT", "UPDATE", "DELETE"]
}

Pattern 2: Hierarchical Access¶

Use Case: Organization with departments and teams

{
  "name": "department_access",
  "table": "records",
  "expression": "tenant_id = {tenant_id} AND department_id IN (SELECT id FROM user_departments WHERE user_id = {user_id})",
  "operations": ["SELECT"]
}

Pattern 3: Owner-Based Access¶

Use Case: Users can only access their own data

{
  "name": "owner_access",
  "table": "documents",
  "expression": "tenant_id = {tenant_id} AND (created_by = {user_id} OR shared_with_user_id = {user_id})",
  "operations": ["SELECT", "UPDATE", "DELETE"]
}

Pattern 4: Role-Based Access¶

Use Case: Admins see all data, users see their own

{
  "name": "role_access",
  "table": "transactions",
  "expression": "tenant_id = {tenant_id} AND ({role} = 'admin' OR user_id = {user_id})",
  "operations": ["SELECT"]
}

Pattern 5: Time-Based Access¶

Use Case: Data visible only within validity period

{
  "name": "temporal_access",
  "table": "contracts",
  "expression": "tenant_id = {tenant_id} AND start_date <= {timestamp} AND end_date >= {timestamp}",
  "operations": ["SELECT"]
}

Error Handling¶

Common Error Codes¶

Code	Description	Resolution
400	Bad Request	Check policy validation errors
401	Unauthorized	Verify authentication token
403	Forbidden	Check tenant permissions
404	Not Found	Verify tenant/policy exists
409	Conflict	Policy already exists
429	Too Many Requests	Reduce request rate
500	Internal Server Error	Contact support

Error Response Format¶

{
  "error": "Bad Request",
  "message": "SQL expression contains potentially dangerous keyword: DROP",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_name": "malicious_policy"
}

Validation Error Examples¶

Example 1: Dangerous SQL Keyword

{
  "error": "Bad Request",
  "message": "SQL expression contains potentially dangerous keyword: DROP",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000"
}

Example 2: Empty Expression

{
  "error": "Bad Request",
  "message": "SQL expression cannot be empty",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000"
}

Example 3: Invalid Table Name

{
  "error": "Bad Request",
  "message": "Table name must contain only alphanumeric characters and underscores",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000"
}

Example 4: Policy Already Exists

{
  "error": "Conflict",
  "message": "Policy 'tenant_isolation' already exists for table 'users'",
  "code": 409,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000",
  "policy_name": "tenant_isolation"
}

Performance Considerations¶

Index Requirements¶

For optimal performance, create indexes on columns used in RLS expressions:

-- Index for tenant_id filtering
CREATE INDEX idx_users_tenant_id ON users(tenant_id);

-- Composite index for complex filtering
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

-- Covering index for common queries
CREATE INDEX idx_documents_tenant_owner_status ON documents(tenant_id, owner_id, status);

Query Performance¶

Before RLS: - Simple query: ~5ms - Join query: ~15ms

After RLS (with proper indexes): - Simple query: ~7ms (+2ms overhead) - Join query: ~18ms (+3ms overhead)

Target: <5% query overhead with properly indexed columns

Monitoring¶

Monitor these metrics: - RLS policy application rate - Query rewrite latency - Index usage on RLS columns - Cross-tenant access attempts (should be 0)

Rate Limits¶

Policy Creation: 10 requests/minute per tenant
Policy Listing: 50 requests/minute per tenant
Policy Deletion: 10 requests/minute per tenant

Note: Rate limits are subject to change based on tenant tier.

Support¶

For questions or issues with the RLS Policy API:

Documentation: https://docs.heliosdb.com/api/rls
Support: support@heliosdb.com
Security: security@heliosdb.com

Last Updated: 2025-12-09 API Version: v1.0.0