GitHub - openworkers/postgate: Multi-tenant HTTP proxy for PostgreSQL with SQL validation

                                Token: pg_xxx...
┌─────────────────┐                                  ┌──────────────┐
│  Admin Client   │ ────── POST /query ────────────▶ │              │
│                 │   SELECT create_tenant_token()   │   postgate   │
└─────────────────┘                                  │              │
                                                     │   ┌──────┐   │      ┌────────────────┐
                                Token: pg_xxx...     │   │ SQL  │   │      │   PostgreSQL   │
┌─────────────────┐                                  │   │Parser│   │ ───▶ │                │
│  Tenant Client  │ ────── POST /query ────────────▶ │   └──────┘   │      │ ┌────────────┐ │
│                 │   SELECT * FROM my_table         │              │      │ │  Schema A  │ │
└─────────────────┘                                  └──────────────┘      │ ├────────────┤ │
                                                                           │ │  Schema B  │ │
                                                                           │ └────────────┘ │
                                                                           └────────────────┘

# Create the database
createdb postgate

# Create user
psql postgres -c "CREATE USER postgate WITH PASSWORD 'your-password' SUPERUSER"

# Run postgate (migrations run automatically)
DATABASE_URL="postgres://postgate:your-password@localhost/postgate" cargo run

# Generate token for postgate admin (manages tenants)
DATABASE_URL="postgres://postgate:your-password@localhost/postgate" \
  cargo run -- gen-token 00000000-0000-0000-0000-000000000000 admin
# Output: pg_abc123... (SAVE THIS!)

SELECT * FROM create_tenant_token(
    '00000000-0000-0000-0000-000000000000'::uuid,
    'admin',
    ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER', 'DROP']
);

# Health check
curl http://localhost:3000/health

# Create a tenant database (using admin token)
curl -X POST http://localhost:3000/query \
  -H "Authorization: Bearer pg_your_admin_token" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM create_tenant_database($1, $2::integer)", "params": ["my_app", 5000]}'

# Create a token for the tenant
curl -X POST http://localhost:3000/query \
  -H "Authorization: Bearer pg_your_admin_token" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM create_tenant_token($1::uuid, $2, $3::text[])", "params": ["<database-id>", "default", ["SELECT", "INSERT", "UPDATE", "DELETE"]]}'

# Start the server (default)
cargo run

# Create a tenant database (schema-based)
cargo run -- create-db <NAME> [-m <MAX_ROWS>]

# Create a dedicated database (external connection)
cargo run -- create-db <NAME> -d <CONNECTION_STRING>

# Generate a token for a database
cargo run -- gen-token <DATABASE_ID> [NAME] [-p <PERMISSIONS>]

# Show help
cargo run -- --help
cargo run -- create-db --help
cargo run -- gen-token --help

# Create a tenant database with schema isolation
cargo run -- create-db my-app
# Output: <database-uuid>
# Schema: tenant_xxx_my_app

# Create with custom max rows
cargo run -- create-db my-app -m 5000

# Create a dedicated database (external PostgreSQL)
cargo run -- create-db premium-client -d "postgres://user:pass@host/db"

# Generate token with default DML permissions
cargo run -- gen-token <database-uuid> default

# Generate token with full permissions (DML + DDL)
cargo run -- gen-token <database-uuid> admin \
    -p SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP

# Generate read-only token
cargo run -- gen-token <database-uuid> readonly -p SELECT

{
  "sql": "SELECT * FROM users WHERE id = $1::int",
  "params": [1]
}

{
  "rows": [{"id": 1, "name": "Alice", "email": "alice@example.com"}],
  "row_count": 1
}

{
  "error": "Operation DELETE is not allowed",
  "code": "PARSE_ERROR"
}

pg_<64_hex_characters>
│   └─────────────────────── 32 random bytes (hex encoded)
└─────────────────────────── Prefix for identification

-- ✅ Allowed (with SELECT permission)
SELECT * FROM users WHERE id = $1

-- ✅ Allowed (with CREATE permission)
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT)

-- ❌ Blocked: Multiple statements
SELECT 1; DROP TABLE users

-- ❌ Blocked: Schema-qualified name
SELECT * FROM public.users

-- ❌ Blocked: System table access
SELECT * FROM pg_tables

-- ✅ Allowed: postgate_helpers functions
SELECT * FROM postgate_helpers.list_tables()

SELECT * FROM postgate_helpers.list_tables();
-- Returns: { table_name: "users", row_count: 42 }, ...

SELECT * FROM postgate_helpers.describe_table('users');
-- Returns: { column_name, data_type, is_nullable, column_default, is_primary_key }

PostgreSQL Database
├── public/              ← postgate metadata tables
│   ├── postgate_databases
│   └── postgate_tokens
├── tenant_abc123_myapp/ ← Tenant A's schema
│   ├── users
│   └── orders
└── tenant_def456_other/ ← Tenant B's schema
    ├── products
    └── inventory

INSERT INTO postgate_databases (name, backend_type, connection_string, max_rows)
VALUES ('premium_tenant', 'dedicated', 'postgres://user:pass@host/db', 10000);

SELECT * FROM create_tenant_database(
    'my_app_name',    -- Database name
    5000              -- Max rows per query (optional, default: 1000)
);
-- Returns: { id: "uuid", schema_name: "tenant_xxx_my_app_name" }

SELECT delete_tenant_database('database-uuid'::uuid);
-- Returns: true/false

SELECT * FROM create_tenant_token(
    'database-uuid'::uuid,                              -- Database ID
    'my_token_name',                                    -- Token name (optional)
    ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE']       -- Permissions (optional)
);
-- Returns: { id: "token-uuid", token: "pg_xxx..." }
-- ⚠️ SAVE THE TOKEN! It's only shown once.

SELECT delete_tenant_token('token-uuid'::uuid);
-- Returns: true/false

-- List all tokens for a database (admin only, through /query)
SELECT id, name, token_prefix, created_at, last_used_at
FROM postgate_tokens
WHERE database_id = 'your-database-id'::uuid;

interface PostgateQueryRequest {
  sql: string;
  params?: unknown[];
}

interface PostgateQueryResponse<T = Record<string, unknown>> {
  rows: T[];
  row_count: number;
}

class PostgateClient {
  constructor(private baseUrl: string, private token: string) {}

  async query<T>(sql: string, params?: unknown[]): Promise<PostgateQueryResponse<T>> {
    const response = await fetch(`${this.baseUrl}/query`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'Authorization': `Bearer ${this.token}`
      },
      body: JSON.stringify({ sql, params })
    });

    if (!response.ok) {
      const error = await response.json();
      throw new Error(error.error || `HTTP ${response.status}`);
    }

    return response.json();
  }
}

// Usage
const client = new PostgateClient('http://localhost:3000', 'pg_your_token');
const { rows } = await client.query<{ id: number; name: string }>(
  'SELECT * FROM users WHERE id = $1',
  [1]
);

class PostgateAdminClient extends PostgateClient {
  async createDatabase(name: string, maxRows = 1000) {
    return this.query(
      'SELECT * FROM create_tenant_database($1, $2::integer)',
      [name, maxRows]
    );
  }

  async deleteDatabase(databaseId: string) {
    return this.query(
      'SELECT delete_tenant_database($1::uuid)',
      [databaseId]
    );
  }

  async createToken(
    databaseId: string,
    name = 'default',
    permissions = ['SELECT', 'INSERT', 'UPDATE', 'DELETE']
  ) {
    return this.query(
      'SELECT * FROM create_tenant_token($1::uuid, $2, $3::text[])',
      [databaseId, name, permissions]
    );
  }

  async deleteToken(tokenId: string) {
    return this.query(
      'SELECT delete_tenant_token($1::uuid)',
      [tokenId]
    );
  }
}

# Clone and setup
git clone <repo>
cd postgate

# Setup test database
createdb postgate_test
psql postgres -c "CREATE USER postgate WITH PASSWORD 'password' SUPERUSER"

# Run tests
DATABASE_URL="postgres://postgate:password@localhost/postgate_test" cargo test

# Run server
DATABASE_URL="postgres://postgate:password@localhost/postgate_test" cargo run

# Format code
cargo fmt

postgate/
├── src/
│   ├── main.rs       # Entry point, migrations, server startup
│   ├── lib.rs        # Module exports
│   ├── auth.rs       # Token extraction and validation
│   ├── config.rs     # Configuration types (DatabaseBackend, SqlOperation, etc.)
│   ├── error.rs      # Error types with HTTP response mapping
│   ├── executor.rs   # SQL execution (schema/dedicated backends)
│   ├── parser.rs     # SQL validation (sqlparser)
│   ├── server.rs     # HTTP handlers (actix-web)
│   ├── store.rs      # Database CRUD operations
│   └── token.rs      # Token generation and hashing
├── migrations/
│   └── 001_init.sql  # Schema + PL/pgSQL functions
├── tests/
│   └── integration.rs # Integration tests
├── Cargo.toml
└── README.md