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

$3

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

$3

Place your wildcard SSL certificate in the certs directory:

`bash


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/*
`

$3

Add a wildcard DNS record pointing to your server:

`
*.pgs.YOUR-DOMAIN.com → YOUR_SERVER_IP
`

Usage

$3

`bash
pgs create [--password ]
`

Example:
`bash
pgs create tenant1
pgs create tenant2 --password mycustompass
`

New tenants are created with external access enabled by default.

$3

`bash
pgs list
`

Shows all tenants with their hostnames and external access status.

$3

`bash
pgs remove
`

$3

`bash
pgs start # Start all services (including Traefik)
pgs start # Start specific tenant
`

$3

`bash
pgs stop # Stop all services
pgs stop # Stop specific tenant
`

$3

`bash
pgs enable-access # Enable external access
pgs disable-access # 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:

`bash


Using psql

psql "postgresql://postgres:PASSWORD@TENANT-ID.pgs.YOUR-DOMAIN.com:5432/DATABASE?sslmode=require"

Example

psql "postgresql://postgres:mypass@tenant1-abc123.pgs.us-central-1.sysnee.com:5432/tenant1-abc123?sslmode=require"
`

$3

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

$3

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

$3

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

$3

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

$3

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

$3

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

$3

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

$3

- 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

$3

- 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.