Tenant Row-Level Security (RLS) Policy API Documentation

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:

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

---

Query Rewriting

How It Works

When a query is executed with RLS policies enabled:

1. Policy Lookup: Identify applicable policies for the query’s tables
2. Context Injection: Replace placeholders with actual values
3. Expression Combination: Combine policy expressions with AND
4. Query Rewrite: Inject combined expression into WHERE clause
5. 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