Data Isolation

OmniBase uses PostgreSQL Row-Level Security (RLS) to enforce tenant isolation at the database level. This ensures that tenants can never access each other's data, even if application code has bugs.

How It Works

┌──────────────┐    ┌──────────────┐    ┌──────────────┐
│   Request    │───▶│  JWT Token   │───▶│  RLS Policy  │
│              │    │  tenant_id   │    │   Filters    │
└──────────────┘    └──────────────┘    └──────────────┘

User makes API request with session cookie
API extracts tenant_id from JWT claims
JWT is passed to PostgREST
PostgreSQL RLS policies filter all queries by tenant

JWT Token Structure

OmniBase generates JWT tokens with tenant context:

{
  "user_id": "550e8400-e29b-41d4-a716-446655440000",
  "tenant_id": "7c9e6679-7425-40de-944b-e07fc1f90ae7",
  "user_role": "owner",
  "role": "anon_user",
  "iat": 1699900000,
  "exp": 1699986400
}

RLS Helper Functions

OmniBase provides SQL helper functions to access JWT claims:

-- Get current user ID from JWT
SELECT auth.user_id();
-- Returns: '550e8400-e29b-41d4-a716-446655440000'

-- Get active tenant ID from JWT
SELECT auth.active_tenant_id();
-- Returns: '7c9e6679-7425-40de-944b-e07fc1f90ae7'

-- Get user's role in active tenant
SELECT auth.active_user_role();
-- Returns: 'owner'

-- Get all tenant IDs user belongs to
SELECT auth.user_tenant_ids();
-- Returns: ARRAY['tenant-1', 'tenant-2']

Implementation

CREATE OR REPLACE FUNCTION auth.user_id()
RETURNS text AS $$
  SELECT current_setting('request.jwt.claims', true)::json->>'user_id'
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION auth.active_tenant_id()
RETURNS text AS $$
  SELECT current_setting('request.jwt.claims', true)::json->>'tenant_id'
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION auth.active_user_role()
RETURNS text AS $$
  SELECT current_setting('request.jwt.claims', true)::json->>'user_role'
$$ LANGUAGE sql STABLE;

Creating Tenant-Scoped Tables

Basic Pattern

-- Create a tenant-scoped table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create isolation policy
CREATE POLICY tenant_isolation ON projects
  FOR ALL
  USING (tenant_id = auth.active_tenant_id()::uuid)
  WITH CHECK (tenant_id = auth.active_tenant_id()::uuid);

-- Grant access to the anon_user role
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO anon_user;

Policy Breakdown

Clause	Purpose
`FOR ALL`	Applies to SELECT, INSERT, UPDATE, DELETE
`USING`	Filter condition for SELECT, UPDATE, DELETE
`WITH CHECK`	Validation for INSERT, UPDATE

Separate Policies Pattern

For more granular control:

-- Read policy
CREATE POLICY projects_select ON projects
  FOR SELECT
  USING (tenant_id = auth.active_tenant_id()::uuid);

-- Insert policy (ensures tenant_id is set correctly)
CREATE POLICY projects_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = auth.active_tenant_id()::uuid);

-- Update policy
CREATE POLICY projects_update ON projects
  FOR UPDATE
  USING (tenant_id = auth.active_tenant_id()::uuid)
  WITH CHECK (tenant_id = auth.active_tenant_id()::uuid);

-- Delete policy
CREATE POLICY projects_delete ON projects
  FOR DELETE
  USING (tenant_id = auth.active_tenant_id()::uuid);

Querying with RLS

Automatic Filtering

// Using PostgREST client
const { data: projects } = await db
  .from('projects')
  .select('*')
  .eq('status', 'active');

// Only returns projects where tenant_id = current user's active tenant
// The WHERE tenant_id = '...' is added automatically by RLS

Behind the Scenes

-- Your query:
SELECT * FROM projects WHERE status = 'active';

-- What PostgreSQL actually executes:
SELECT * FROM projects
WHERE status = 'active'
  AND tenant_id = '7c9e6679-7425-40de-944b-e07fc1f90ae7';

RLS policies are enforced at the database level. Even direct SQL queries through PostgREST are filtered. There's no way for application code to bypass this protection.

Database Roles

OmniBase uses two PostgreSQL roles:

anon_user

Used for all user requests via PostgREST
RLS policies are enforced
Limited permissions per table

super_user

Used for administrative operations
Bypasses RLS policies
Used by OmniBase API internally

-- Create roles
CREATE ROLE anon_user NOLOGIN;
CREATE ROLE super_user NOLOGIN;

-- anon_user has RLS enforced
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO anon_user;

-- super_user bypasses RLS
ALTER ROLE super_user SET row_security TO off;

Database Schema

Core Tables

tenants

tenant_users

tenant_settings

tenant_invites

identities

Schema Reference

Cross-Tenant Queries

Never Allowed via PostgREST

User requests through PostgREST can never access other tenants' data:

// Even if you somehow know another tenant's ID,
// RLS will filter it out
const { data } = await db
  .from('projects')
  .select('*')
  .eq('tenant_id', 'other-tenant-id');

// Returns: [] (empty array)

Service-Level Access

For administrative operations, use service key authentication:

import { Configuration, V1TenantsApi } from '@omnibase/core-js';

const config = new Configuration({
  basePath: process.env.OMNIBASE_API_URL,
  headers: {
    'X-Service-Key': process.env.OMNIBASE_SERVICE_KEY,
    'X-Tenant-Id': targetTenantId,  // Explicit tenant context
  },
});

Cascade Deletes

When a tenant is deleted, all related data is automatically cleaned up via foreign key cascades:

-- When auth.tenants row is deleted:
-- ├── auth.tenant_users (CASCADE)
-- ├── auth.tenant_settings (CASCADE)
-- ├── auth.tenant_invites (CASCADE)
-- ├── permissions.roles (CASCADE)
-- ├── storage.objects (CASCADE)
-- └── All custom tables with tenant_id FK (CASCADE)

Always use ON DELETE CASCADE for the tenant_id foreign key to ensure proper cleanup when tenants are deleted.

Multi-Tenant Indexes

For optimal query performance, include tenant_id in indexes:

-- Bad: Index without tenant_id
CREATE INDEX idx_projects_status ON projects(status);

-- Good: Composite index with tenant_id first
CREATE INDEX idx_projects_tenant_status ON projects(tenant_id, status);

-- For unique constraints, include tenant_id
CREATE UNIQUE INDEX idx_projects_tenant_name
  ON projects(tenant_id, name);

Why Tenant-First Indexes?

RLS adds tenant_id = ? to every query. Having tenant_id as the leading column in indexes allows PostgreSQL to efficiently filter rows before applying other conditions.

Testing Data Isolation

k6 Integration Tests

OmniBase includes tests that verify tenant isolation:

// Create two separate users in separate tenants
const user1 = await createUser({ email: 'user1@test.com' });
const tenant1 = await createTenant({ name: 'Tenant 1' });

const user2 = await createUser({ email: 'user2@test.com' });
const tenant2 = await createTenant({ name: 'Tenant 2' });

// Create data in tenant1
await createProject({ name: 'Secret Project', tenantContext: tenant1 });

// Try to access from tenant2 context
const projects = await listProjects({ tenantContext: tenant2 });

// Verify: tenant2 cannot see tenant1's project
assert(projects.length === 0);

Manual Verification

-- Connect as anon_user with tenant1 JWT
SET request.jwt.claims = '{"tenant_id": "tenant-1-id"}';

SELECT * FROM projects;
-- Returns only tenant-1's projects

-- Switch to tenant2 context
SET request.jwt.claims = '{"tenant_id": "tenant-2-id"}';

SELECT * FROM projects;
-- Returns only tenant-2's projects (tenant-1's are invisible)

Common Patterns

Tenant Lifecycle — Create and manage tenants
Roles & Permissions — Configure access control
Permissions Concept — How ReBAC works

Data Isolation

auth.tenants

auth.tenant_users

auth.tenant_invites

permissions.roles

storage.objects

User-Owned Data Within Tenant

Role-Based Data Access

Shared Reference Data

On this page