---
title: "Tenant Row-Level Security (RLS) Policy API Documentation"
---

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

:::caution[Not served by the HTTP gateway]
The `heliosdb-full` server's HTTP gateway (port 8443) does not serve these routes in
the current release; a request to them returns 404. The routes the gateway serves are
listed in the [REST API overview](/docs/full/api/rest/readme/). Use SQL from a
PostgreSQL client for administration, and contact
[support@heliosdb.com](mailto:support@heliosdb.com) if you need this API.
:::

**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](#endpoints)
  - [POST /tenants/{id}/rls/policies](#post-tenantsidrlspolicies)
  - [GET /tenants/{id}/rls/policies](#get-tenantsidrlspolicies)
  - [DELETE /tenants/{id}/rls/policies/{policy_id}](#delete-tenantsidrlspoliciespolicy_id)
- [Data Models](#data-models)
- [RLS Expressions](#rls-expressions)
- [Query Rewriting](#query-rewriting)
- [Examples](#examples)
- [Best Practices](#best-practices)
- [Error Handling](#error-handling)

---

## Endpoints

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

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

#### Request

```http
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):**

```json
{
  "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

```http
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):**

```json
{
  "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

```http
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):**

```json
{
  "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

```sql
tenant_id = {tenant_id}
```

**Description**: Filters all rows to current tenant only.

**Applied Query:**
```sql
-- 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

```sql
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

```sql
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

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

**Description**: Filter data based on temporal validity.

#### 5. Hierarchical Access

```sql
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:**
```sql
SELECT * FROM users WHERE email LIKE '%@example.com';
```

**RLS Policy:**
```json
{
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["SELECT"]
}
```

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

#### Example 2: JOIN Query

**Original Query:**
```sql
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:**
```sql
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:**
```sql
UPDATE users SET last_login = NOW() WHERE id = 123;
```

**RLS Policy:**
```json
{
  "table": "users",
  "expression": "tenant_id = {tenant_id}",
  "operations": ["UPDATE"]
}
```

**Rewritten Query:**
```sql
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

```bash
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:**
```json
{
  "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

```bash
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

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

**Response:**
```json
{
  "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

```bash
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:**
```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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

```json
{
  "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**
```json
{
  "error": "Bad Request",
  "message": "SQL expression contains potentially dangerous keyword: DROP",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000"
}
```

**Example 2: Empty Expression**
```json
{
  "error": "Bad Request",
  "message": "SQL expression cannot be empty",
  "code": 400,
  "tenant_id": "550e8400-e29b-41d4-a716-446655440000"
}
```

**Example 3: Invalid Table Name**
```json
{
  "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**
```json
{
  "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:

```sql
-- 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://www.heliosdb.com/docs/full/
- **Support**: support@heliosdb.com
- **Security**: security@heliosdb.com

---

**API Version**: v1.0.0