PostgreSQL Multi-Tenant Instance Manager

Dynamic PostgreSQL multi-tenant management system providing complete database isolation by creating dedicated PostgreSQL instances per tenant, with intelligent SNI-based routing via Traefik v3.

📖 For detailed project definition, architecture, and comparison with similar solutions, see docs/PROJECT.md

Quick Start

Initial Setup

pgs setup

This creates the configuration directory at ~/.sysnee-config/ with:

• docker-compose.yml - Container orchestration
• traefik.yml - Traefik static configuration
• dynamic.yml - Traefik dynamic routing configuration
• tenant-access.json - Access control
• certs/ - SSL certificates directory

SSL Certificate Setup

Place your wildcard SSL certificate in the certs directory:

# Using Let's Encrypt
sudo cp /etc/letsencrypt/live/pgs.YOUR-DOMAIN.com/fullchain.pem ~/.sysnee-config/certs/
sudo cp /etc/letsencrypt/live/pgs.YOUR-DOMAIN.com/privkey.pem ~/.sysnee-config/certs/
sudo chown $USER:$USER ~/.sysnee-config/certs/*

DNS Configuration

Add a wildcard DNS record pointing to your server:

*.pgs.YOUR-DOMAIN.com → YOUR_SERVER_IP

Usage

Create a new tenant instance

pgs create <tenant-id> [--password <password>]

Example:

pgs create tenant1
pgs create tenant2 --password mycustompass

New tenants are created with external access enabled by default.

List all tenants

pgs list

Shows all tenants with their hostnames and external access status.

Remove a tenant

pgs remove <tenant-id>

Start services

pgs start                 # Start all services (including Traefik)
pgs start <tenant-id>     # Start specific tenant

Stop services

pgs stop                  # Stop all services
pgs stop <tenant-id>      # Stop specific tenant

Access Control

pgs enable-access <tenant-id>   # Enable external access
pgs disable-access <tenant-id>  # Disable external access

How it works

• Traefik v3 listens on port 5432 with TLS/SSL enabled
• Routes connections based on SNI (Server Name Indication) hostname
• Each tenant connects using their unique hostname (e.g., tenant1-abc123.pgs.domain.com)
• External access is controlled via tenant-access.json
• Tenants are isolated in their own PostgreSQL containers on a Docker bridge network
• Only Traefik has external port mapping; PostgreSQL containers are internal only

Connection

After creating a tenant:

# Using psql
psql "postgresql://postgres:[email protected]:5432/DATABASE?sslmode=require"

# Example
psql "postgresql://postgres:[email protected]:5432/tenant1-abc123?sslmode=require"

DBeaver / GUI Clients

1. Host: tenant-id.pgs.your-domain.com
2. Port: 5432
3. Database: tenant-id
4. Username: postgres
5. Password: (your password)
6. SSL: Enable SSL, set mode to require

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                       External Access                            │
│           (tenant-id.pgs.domain.com:5432 + TLS/SNI)             │
└───────────────────────────┬─────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────────┐
│                      Traefik v3 Proxy                            │
│  ┌───────────────────────────────────────────────────────────┐  │
│  │  EntryPoint: postgres (port 5432)                         │  │
│  │  - TLS termination with wildcard certificate              │  │
│  │  - PostgreSQL STARTTLS protocol support                   │  │
│  │  - SNI-based routing (HostSNI rule)                       │  │
│  │  - Dynamic configuration via dynamic.yml                  │  │
│  └───────────────────────────────────────────────────────────┘  │
└───────────────────────────┬─────────────────────────────────────┘
                            │
         ┌──────────────────┼──────────────────┐
         │                  │                  │
         ▼                  ▼                  ▼
┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│  TCP Router  │   │  TCP Router  │   │  TCP Router  │
│  tenant1     │   │  tenant2     │   │  tenant3     │
│  HostSNI()   │   │  HostSNI()   │   │  HostSNI()   │
└──────┬───────┘   └──────┬───────┘   └──────┬───────┘
       │                  │                  │
       ▼                  ▼                  ▼
┌──────────────┐   ┌──────────────┐   ┌──────────────┐
│ PostgreSQL   │   │ PostgreSQL   │   │ PostgreSQL   │
│ Container 1  │   │ Container 2  │   │ Container 3  │
│              │   │              │   │              │
│ Port: 5432   │   │ Port: 5432   │   │ Port: 5432   │
│ (internal)   │   │ (internal)   │   │ (internal)   │
│              │   │              │   │              │
│ Volume:      │   │ Volume:      │   │ Volume:      │
│ pgdata_1     │   │ pgdata_2     │   │ pgdata_3     │
└──────────────┘   └──────────────┘   └──────────────┘

Technical Implementation

Components

1. Manager Script (manager.js)

   • Node.js CLI tool for tenant lifecycle management
   • Dynamically generates docker-compose.yml entries
   • Manages Traefik dynamic configuration
   • Controls tenant access permissions

2. Traefik v3 Reverse Proxy

   • TCP-level routing with TLS termination
   • Native PostgreSQL STARTTLS protocol support
   • SNI-based tenant routing
   • Dynamic configuration without restarts

3. Docker Infrastructure

   • Separate container per tenant
   • Bridge network for internal communication
   • Persistent volumes for data
   • Isolated execution environments

Connection Flow

1. Client connects to tenant-id.pgs.domain.com:5432 with sslmode=require
2. Traefik receives connection and initiates PostgreSQL STARTTLS handshake
3. Client sends TLS ClientHello with SNI (hostname)
4. Traefik extracts SNI and matches against configured routers
5. If tenant has access enabled, routes to backend pgs_{tenant_id}:5432
6. Connection established with complete isolation

Why Traefik v3?

PostgreSQL uses a non-standard TLS negotiation (STARTTLS):

1. Client sends PostgreSQL SSLRequest packet
2. Server responds 'S' (SSL supported)
3. Client sends TLS ClientHello with SNI
4. TLS handshake completes

Traefik v3 is one of the few proxies that natively understands this PostgreSQL-specific flow, allowing SNI-based routing for PostgreSQL connections.

Comparison with Similar Solutions

Shared Database Architecture

Traditional Multi-Tenant PostgreSQL:

• Single PostgreSQL instance
• Multiple databases/schemas per instance
• Shared processes and memory
• Risk of cross-tenant data access

This Solution:

• Multiple PostgreSQL instances
• One instance per tenant
• Complete process isolation
• Zero risk of cross-tenant access

Similar Open Source Solutions

| Solution | Purpose | Difference | |----------|---------|------------| | PgBouncer | Connection pooling | Pools to single instance; this creates separate instances | | Citus | Distributed PostgreSQL | Shards data; this isolates tenants completely | | Patroni | High availability | Replicates single DB; this creates isolated instances | | RLS | Row-level security | Logic-based separation; this uses infrastructure isolation |

Unique Aspects

1. Instance-per-tenant - Complete process and memory isolation
2. SNI-based routing - Single port, automatic hostname-based routing
3. TLS by default - Secure connections required
4. Dynamic provisioning - Create tenants on-demand via CLI
5. Docker-native - Simple deployment and resource limits per tenant

Use Cases

Ideal For

• SaaS Applications requiring strict tenant data isolation
• Healthcare/Finance applications with compliance requirements
• Multi-tenant platforms needing independent scaling
• Development/Testing environments with isolated databases

Not Ideal For

• Thousands of tenants (resource overhead)
• Simple multi-tenant applications without strict isolation needs
• Environments requiring minimal resource usage

Technology Stack

• Runtime: Node.js (ES Modules)
• Container Orchestration: Docker Compose
• Reverse Proxy: Traefik v3 (PostgreSQL STARTTLS + SNI routing)
• Database: PostgreSQL 18+
• TLS: Wildcard SSL certificate
• Configuration: YAML (docker-compose.yml, traefik.yml, dynamic.yml), JSON (tenant-access.json)

Requirements

• Docker & Docker Compose
• Node.js 18+
• Wildcard SSL certificate for your domain
• Wildcard DNS record pointing to your server

Future Enhancements

• [ ] Health checks and automatic failover
• [ ] Backup/restore automation per tenant
• [ ] Monitoring and metrics collection
• [ ] Tenant migration tools
• [ ] Kubernetes support
• [ ] Connection pooling per tenant
• [ ] Web dashboard

License & Status

This is a custom solution built for specific multi-tenant requirements. It combines open-source tools (Traefik, PostgreSQL, Docker) with SNI-based routing to achieve instance-per-tenant isolation with intelligent connection routing.