PostgreSQL Multi-Tenant Instance Manager

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

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

Usage

Create a new tenant instance

npm run create <tenant-id> [--password <password>]

Example:

npm run create tenant1
npm run create tenant2 --password mycustompass

New tenants are created with external access disabled by default.

List all tenants

npm run list

Shows all tenants with their external access status.

Remove a tenant

npm run remove <tenant-id>

Start services

npm run start                      # Start all services (including HAProxy)
npm run start -- --tenant tenant1  # Start specific tenant

Stop services

npm run stop                       # Stop all services
npm run stop -- --tenant tenant1   # Stop specific tenant

How it works

• HAProxy listens on port 5432 and routes connections based on the PostgreSQL username
• Each tenant connects using their tenant ID as the username (e.g., tecnolab)
• External access is controlled via tenant-access.json
• Tenants are isolated in their own PostgreSQL containers on a Docker bridge network
• Only HAProxy has external port mapping; PostgreSQL containers are internal only

Connection

After creating a tenant and enabling external access:

psql -h localhost -p 5432 -U <tenant-id> -d <tenant-id>

Project Definition

What It Is

A dynamic PostgreSQL multi-tenant management system that provides complete database isolation by creating dedicated PostgreSQL instances per tenant. The system uses HAProxy with custom PostgreSQL protocol parsing to route connections intelligently while maintaining complete tenant isolation at the database server level.

Core Concept

Instead of sharing a single PostgreSQL instance with multiple databases (shared database architecture), this system creates one PostgreSQL container per tenant, ensuring:

• Complete Data Isolation: Each tenant has its own PostgreSQL process and data directory
• Independent Scaling: Resources can be allocated per tenant
• Enhanced Security: No risk of cross-tenant data access
• Simplified Operations: Each tenant can be managed independently

Key Features

1. Dynamic Tenant Provisioning

   • Create new PostgreSQL instances on-demand
   • Automatic volume and network configuration
   • Custom initialization scripts per tenant

2. Intelligent Routing

   • HAProxy parses PostgreSQL protocol to extract username
   • Routes connections to correct tenant backend automatically
   • Single external port (5432) for all tenants

3. Access Control

   • Per-tenant external access enable/disable
   • Secure by default (access disabled on creation)
   • Runtime access control without service restart

4. Complete Isolation

   • Separate Docker containers per tenant
   • Isolated volumes for data persistence
   • Network isolation via Docker bridge network
   • No shared processes or memory

5. Zero-Downtime Operations

   • Graceful HAProxy reloads
   • Independent tenant management
   • No impact on other tenants during operations

Architecture

┌─────────────────────────────────────────────────────────┐
│                    External Access                       │
│                  (localhost:5432)                        │
└──────────────────────┬──────────────────────────────────┘
                       │
                       ▼
┌─────────────────────────────────────────────────────────┐
│                    HAProxy Proxy                         │
│  ┌──────────────────────────────────────────────────┐   │
│  │  Frontend: postgres_frontend                     │   │
│  │  - Listens on port 5432                          │   │
│  │  - Parses PostgreSQL protocol (Lua script)       │   │
│  │  - Extracts username from startup packet         │   │
│  │  - Checks tenant-access.json for permissions     │   │
│  └──────────────────────────────────────────────────┘   │
└──────────────────────┬──────────────────────────────────┘
                       │
        ┌──────────────┼──────────────┐
        │              │              │
        ▼              ▼              ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│  Backend     │ │  Backend     │ │  Backend     │
│ pgs_tenant1  │ │ pgs_tenant2  │ │ pgs_tenant3  │
└──────┬───────┘ └──────┬───────┘ └──────┬───────┘
       │                │                │
       ▼                ▼                ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ 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 HAProxy configuration
   • Controls tenant access permissions

2. HAProxy Reverse Proxy

   • TCP-level load balancer and router
   • Custom Lua script for PostgreSQL protocol parsing
   • Routes based on extracted username
   • Per-tenant access control

3. PostgreSQL Protocol Parser (pg-route.lua)

   • Parses binary PostgreSQL startup packet
   • Extracts username and connection parameters
   • Handles SSL negotiation
   • Enforces access control policies

4. Docker Infrastructure

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

Connection Flow

1. Client connects to localhost:5432 with username tenant_id
2. HAProxy receives connection and invokes Lua script
3. Script parses PostgreSQL startup packet and extracts username
4. Script checks tenant-access.json for permission
5. If allowed, routes to backend pgs_{tenant_id}
6. Backend forwards to PostgreSQL container on internal network
7. Connection established with complete isolation

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
• Less isolation

This Solution:

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

Similar Open Source Solutions

1. PgBouncer

• Purpose: Connection pooling, not tenant isolation
• Difference: Pools connections to single instance; this creates separate instances
• Use Case: Different - PgBouncer optimizes connections; this isolates tenants

2. Citus

• Purpose: PostgreSQL extension for distributed PostgreSQL
• Difference: Shards data across nodes; this creates separate instances per tenant
• Use Case: Horizontal scaling vs. tenant isolation

3. Patroni + HAProxy

• Purpose: High availability and load balancing
• Difference: Replicates single database; this creates isolated instances
• Use Case: HA for single database vs. multi-tenant isolation

4. Schema-based Multi-tenancy

• Purpose: Share database, separate schemas
• Difference: Shared instance; this uses separate instances
• Use Case: Resource efficiency vs. complete isolation

5. Row-level Security (RLS)

• Purpose: Application-level tenant isolation
• Difference: Logic-based separation; this uses infrastructure isolation
• Use Case: Application isolation vs. infrastructure isolation

Unique Aspects of This Solution

1. Instance-per-tenant at infrastructure level

   • Not just database or schema separation
   • Complete process and memory isolation

2. Dynamic provisioning with single external port

   • No need for port management
   • Automatic routing based on connection parameters

3. Protocol-aware routing

   • Parses PostgreSQL binary protocol
   • Routes before connection completion
   • Handles SSL negotiation

4. Runtime access control

   • Enable/disable tenant access without restart
   • No downtime for access changes

5. Docker-native architecture

   • Leverages container isolation
   • Simple deployment and scaling
   • 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
• Legacy application migration requiring tenant separation

Not Ideal For

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

Advantages

✅ Maximum Isolation: Complete process and data separation ✅ Security: Zero risk of cross-tenant data access ✅ Flexibility: Independent scaling and management per tenant ✅ Simplicity: Single external port, automatic routing ✅ Compliance: Easier to meet regulatory requirements ✅ Debugging: Isolated environments simplify troubleshooting

Trade-offs

⚠️ Resource Usage: Higher memory/CPU per tenant ⚠️ Management Overhead: More containers to manage ⚠️ Scaling Limits: Practical limit on number of tenants per host ⚠️ Backup Complexity: Need to backup multiple instances

Technology Stack

• Runtime: Node.js (ES Modules)
• Container Orchestration: Docker Compose
• Reverse Proxy: HAProxy with Lua scripting
• Database: PostgreSQL 18+
• Protocol Parsing: Custom Lua script
• Configuration: YAML (docker-compose.yml), JSON (tenant-access.json)

Future Enhancements

• [ ] Health checks and automatic failover
• [ ] Backup/restore automation per tenant
• [ ] Resource limits (CPU/memory) per tenant
• [ ] Monitoring and metrics collection
• [ ] Tenant migration tools
• [ ] Kubernetes support
• [ ] Connection pooling per tenant
• [ ] SSL/TLS termination

License & Status

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