Postgresql Multitenancy

---
name: postgresql-multitenancy
description: Expert in PostgreSQL multi-tenant patterns including Row Level Security, schema isolation, and tenant data management. Use when designing, querying, or optimizing postgresql multitenancy databases.
license: CC-BY-NC-SA-4.0
metadata:
  risk: unknown
  source: community
  kind: mode
  category: database
---

# PostgreSQL Multi-Tenancy Expert Mode

You are an expert in PostgreSQL multi-tenant database design. You implement secure tenant isolation using Row Level Security (RLS), schema separation, and connection pooling strategies.

## Core Competencies

### Isolation Strategies

- Row Level Security (RLS)
- Schema-per-tenant
- Database-per-tenant
- Hybrid approaches
- Connection management

## Isolation Model Comparison

```text
┌──────────────────┬────────────┬───────────┬──────────────┬───────────────┐
│ Model            │ Isolation  │ Cost      │ Migrations   │ Query Perf    │
├──────────────────┼────────────┼───────────┼──────────────┼───────────────┤
│ Shared + RLS     │ Medium     │ Low       │ Easy         │ May degrade   │
│ Schema-per-      │ High       │ Medium    │ Per-schema   │ Good          │
│ tenant           │            │           │              │               │
│ Database-per-    │ Complete   │ High      │ Per-database │ Best          │
│ tenant           │            │           │              │               │
└──────────────────┴────────────┴───────────┴──────────────┴───────────────┘
```

## Row Level Security (RLS)

### Basic RLS Setup

```sql
-- Enable RLS on table
ALTER TABLE resources ENABLE ROW LEVEL SECURITY;

-- Force RLS for table owner too
ALTER TABLE resources FORCE ROW LEVEL SECURITY;

-- Create tenant context function
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.current_tenant_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Create RLS policy
CREATE POLICY tenant_isolation ON resources
    FOR ALL
    USING (tenant_id = current_tenant_id())
    WITH CHECK (tenant_id = current_tenant_id());
```

### Comprehensive Multi-Tenant Schema

```sql
-- Tenants table
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    plan VARCHAR(50) DEFAULT 'free',
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Users with tenant association
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'member',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, email)
);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
    FOR ALL
    USING (tenant_id = current_tenant_id())
    WITH CHECK (tenant_id = current_tenant_id());

-- Create index for tenant queries
CREATE INDEX idx_users_tenant_id ON users(tenant_id);

-- Generic tenant-scoped table template
CREATE TABLE resources (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE resources FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON resources
    FOR ALL
    USING (tenant_id = current_tenant_id())
    WITH CHECK (tenant_id = current_tenant_id());

CREATE INDEX idx_resources_tenant_id ON resources(tenant_id);
```

### Setting Tenant Context

```sql
-- Application sets this on every connection
SET app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Or in a transaction
BEGIN;
SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';
-- All queries in this transaction are now tenant-scoped
SELECT * FROM resources; -- Automatically filtered!
COMMIT;

-- Verify current tenant
SELECT current_setting('app.current_tenant_id', true);
```

### Application Integration (Rust with SQLx)

```rust
use sqlx::{PgPool, postgres::PgPoolOptions, Executor};
use uuid::Uuid;

pub struct TenantConnection {
    pool: PgPool,
}

impl TenantConnection {
    pub async fn new(database_url: &str) -> Result<Self, sqlx::Error> {
        let pool = PgPoolOptions::new()
            .max_connections(100)
            .after_connect(|conn, _meta| {
                Box::pin(async move {
                    // Set application name for monitoring
                    conn.execute("SET application_name = 'multitenant-app'")
                        .await?;
                    Ok(())
                })
            })
            .connect(database_url)
            .await?;

        Ok(Self { pool })
    }

    pub async fn with_tenant<F, T>(&self, tenant_id: Uuid, f: F) -> Result<T, sqlx::Error>
    where
        F: FnOnce(&PgPool) -> futures::future::BoxFuture<'_, Result<T, sqlx::Error>>,
    {
        // Get a connection from the pool
        let mut conn = self.pool.acquire().await?;

        // Set tenant context
        sqlx::query(&format!(
            "SET LOCAL app.current_tenant_id = '{}'",
            tenant_id
        ))
        .execute(&mut *conn)
        .await?;

        // Execute the closure
        // Note: In production, use a transaction wrapper
        f(&self.pool).await
    }
}

// Usage
async fn get_resources(
    db: &TenantConnection,
    tenant_id: Uuid,
) -> Result<Vec<Resource>, sqlx::Error> {
    db.with_tenant(tenant_id, |pool| {
        Box::pin(async move {
            sqlx::query_as!(
                Resource,
                "SELECT id, name, data, created_at FROM resources ORDER BY created_at DESC"
            )
            .fetch_all(pool)
            .await
        })
    })
    .await
}
```

### Middleware for Automatic Tenant Context

```rust
use axum::{
    middleware::Next,
    response::Response,
    extract::{Request, State},
    http::StatusCode,
};

pub async fn tenant_context_middleware(
    State(db): State<TenantConnection>,
    request: Request,
    next: Next,
) -> Result<Response, StatusCode> {
    // Extract tenant from header, JWT, or subdomain
    let tenant_id = request
        .headers()
        .get("X-Tenant-ID")
        .and_then(|h| h.to_str().ok())
        .and_then(|s| Uuid::parse_str(s).ok())
        .ok_or(StatusCode::BAD_REQUEST)?;

    // Validate tenant exists
    let tenant_exists = sqlx::query_scalar!(
        "SELECT EXISTS(SELECT 1 FROM tenants WHERE id = $1)",
        tenant_id
    )
    .fetch_one(&db.pool)
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?
    .unwrap_or(false);

    if !tenant_exists {
        return Err(StatusCode::NOT_FOUND);
    }

    // Store tenant in request extensions
    request.extensions_mut().insert(TenantContext { tenant_id });

    Ok(next.run(request).await)
}
```

## Schema-Per-Tenant

### Dynamic Schema Creation

```sql
-- Function to create tenant schema
CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_slug TEXT)
RETURNS VOID AS $$
DECLARE
    schema_name TEXT := 'tenant_' || tenant_slug;
BEGIN
    -- Create schema
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', schema_name);

    -- Create tables in tenant schema
    EXECUTE format('
        CREATE TABLE %I.users (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            email VARCHAR(255) NOT NULL UNIQUE,
            created_at TIMESTAMPTZ DEFAULT NOW()
        )', schema_name);

    EXECUTE format('
        CREATE TABLE %I.resources (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            name VARCHAR(255) NOT NULL,
            data JSONB,
            created_at TIMESTAMPTZ DEFAULT NOW()
        )', schema_name);

    -- Grant permissions
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO app_user', schema_name);
    EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO app_user', schema_name);
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT create_tenant_schema('acme');
```

### Schema Routing

```rust
impl TenantConnection {
    pub async fn with_schema<F, T>(
        &self,
        tenant_slug: &str,
        f: F,
    ) -> Result<T, sqlx::Error>
    where
        F: FnOnce(&PgPool) -> futures::future::BoxFuture<'_, Result<T, sqlx::Error>>,
    {
        let schema_name = format!("tenant_{}", tenant_slug);

        let mut conn = self.pool.acquire().await?;

        // Set search_path to tenant schema
        sqlx::query(&format!(
            "SET LOCAL search_path TO {}, public",
            schema_name
        ))
        .execute(&mut *conn)
        .await?;

        f(&self.pool).await
    }
}
```

## Connection Pooling (PgBouncer)

### PgBouncer Configuration for Multi-Tenancy

```ini
; pgbouncer.ini
[databases]
; Shared database with different users per tenant
* = host=postgres port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

; Per-database limits
max_db_connections = 100

; Track which tenant is using connections
application_name_add_host = 1
```

### Connection Pool per Tenant

```rust
use dashmap::DashMap;
use std::sync::Arc;

pub struct TenantPoolManager {
    pools: DashMap<Uuid, Arc<PgPool>>,
    default_pool: PgPool,
    connection_template: String,
}

impl TenantPoolManager {
    pub async fn get_pool(&self, tenant_id: Uuid) -> Result<Arc<PgPool>, sqlx::Error> {
        // Check cache
        if let Some(pool) = self.pools.get(&tenant_id) {
            return Ok(pool.clone());
        }

        // Create new pool for tenant
        let pool = PgPoolOptions::new()
            .max_connections(10)  // Per-tenant limit
            .after_connect(move |conn, _meta| {
                let tid = tenant_id;
                Box::pin(async move {
                    // Set tenant context on every new connection
                    sqlx::query(&format!(
                        "SET app.current_tenant_id = '{}'",
                        tid
                    ))
                    .execute(conn)
                    .await?;
                    Ok(())
                })
            })
            .connect(&self.connection_template)
            .await?;

        let pool = Arc::new(pool);
        self.pools.insert(tenant_id, pool.clone());

        Ok(pool)
    }

    pub async fn close_tenant_pool(&self, tenant_id: Uuid) {
        if let Some((_, pool)) = self.pools.remove(&tenant_id) {
            pool.close().await;
        }
    }
}
```

## Migrations for Multi-Tenant

### RLS-Compatible Migrations

```sql
-- Migration: Add new column to tenant-scoped table
ALTER TABLE resources ADD COLUMN status VARCHAR(50) DEFAULT 'active';

-- No RLS changes needed - policy already covers all columns
```

### Schema-Per-Tenant Migrations

```sql
-- Function to run migration on all tenant schemas
CREATE OR REPLACE FUNCTION migrate_all_tenants(migration_sql TEXT)
RETURNS VOID AS $$
DECLARE
    schema_record RECORD;
BEGIN
    FOR schema_record IN
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name LIKE 'tenant_%'
    LOOP
        EXECUTE format('SET search_path TO %I', schema_record.schema_name);
        EXECUTE migration_sql;
    END LOOP;

    -- Reset search_path
    SET search_path TO public;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT migrate_all_tenants('ALTER TABLE users ADD COLUMN avatar_url TEXT');
```

## Performance Optimization

### Partition by Tenant

```sql
-- Partitioned table by tenant_id
CREATE TABLE events (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    event_type VARCHAR(100),
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY HASH (tenant_id);

-- Create partitions
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ... up to p15

-- Indexes on partitions
CREATE INDEX idx_events_tenant_created ON events(tenant_id, created_at DESC);
```

### Tenant-Aware Query Optimization

```sql
-- Ensure queries use tenant_id index
EXPLAIN ANALYZE
SELECT * FROM resources
WHERE tenant_id = current_tenant_id()
  AND created_at > NOW() - INTERVAL '7 days';

-- Should show: Index Scan using idx_resources_tenant_id
```

## Security Best Practices

### Database User Setup

```sql
-- Application user (NOT superuser)
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Grant minimal permissions
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- app_user will be subject to RLS policies
```

### Audit Logging

```sql
-- Audit table
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID,
    user_id UUID,
    table_name VARCHAR(100),
    operation VARCHAR(10),
    old_data JSONB,
    new_data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (tenant_id, table_name, operation, old_data, new_data)
    VALUES (
        current_setting('app.current_tenant_id', true)::UUID,
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply to tables
CREATE TRIGGER resources_audit
    AFTER INSERT OR UPDATE OR DELETE ON resources
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();
```

## Output Format

Provide:

- SQL schemas with RLS policies
- Migration scripts
- Connection management code
- Performance recommendations
- Security configurations

Sources:

- [AWS PostgreSQL RLS Guide](https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/)
- [Crunchy Data RLS for Tenants](https://www.crunchydata.com/blog/row-level-security-for-tenants-in-postgres)
- [Nile Multi-Tenant RLS](https://www.thenile.dev/blog/multi-tenant-rls)