Postgate

Secure HTTP proxy for PostgreSQL with SQL validation, token-based authentication, and multi-tenant support.

Overview

Postgate provides a secure HTTP interface to PostgreSQL with:

Token-based authentication - API tokens (pg_xxx) with SHA-256 hashing
SQL validation - Parses and validates every query before execution
Multi-tenant isolation - Schema-based or dedicated database backends
Fine-grained permissions - Per-token operation control (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP)
PL/pgSQL administration - All tenant/token management via SQL functions

Architecture

 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 │ │
│ └────────────┘ │
└────────────────┘

Endpoints

Postgate exposes only 2 endpoints:

Endpoint	Method	Description
`/health`	GET	Health check
`/query`	POST	Execute SQL query

All administration (creating databases, tokens) is done via SQL functions through /query.

Quick Start

1. Setup Database

# 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

2. Create Admin Token

Use the CLI to generate tokens for the seed databases:

# 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!)

Or via SQL directly:

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

3. Use the API

# 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"]]}'

Configuration

Environment Variable	Default	Description
`DATABASE_URL`	required	PostgreSQL connection string
`POSTGATE_HOST`	`127.0.0.1`	HTTP server bind address
`POSTGATE_PORT`	`3000`	HTTP server port

CLI Commands

# 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

Examples:

# 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

API Reference

POST /query

Execute a SQL query against a tenant database.

Headers:

Authorization: Bearer <token> - API token (format: pg_<64_hex_chars>)
Content-Type: application/json

Request Body:

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

Recommended: always cast parameters explicitly ($1::int, $2::text, $3::uuid, etc.). Postgate routes cast parameters through Postgres' text parser, which is both safer (no ambiguity about column type) and more reliable (it sidesteps a sqlx prepared-statement type-cache issue that can produce intermittent invalid byte sequence for encoding UTF8 errors on numeric params). Uncast parameters still work but rely on Postgres' implicit type inference from context.

Response (success):

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

Response (error):

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

Error Codes:

Code	HTTP Status	Description
`PARSE_ERROR`	400	SQL parsing or validation failed
`ROW_LIMIT_EXCEEDED`	400	Query returned more rows than allowed
`UNAUTHORIZED`	401	Missing or invalid token
`DATABASE_NOT_FOUND`	404	Token's database doesn't exist
`TIMEOUT`	504	Query timed out (default: 30s)
`DATABASE_ERROR`	500	PostgreSQL execution error
`INTERNAL_ERROR`	500	Unexpected server error

GET /health

Health check endpoint.

Response:

{"status": "ok"}

Token System

Token Format

Tokens follow a specific format for security and identification:

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

Example: pg_a1b2c3d4e5f6... (67 characters total)

Token Storage

Tokens are hashed with SHA-256 before storage
Only the hash is stored in postgate_tokens table
A token_prefix (first 8 chars) is stored for identification
The full token is only returned once at creation time

Token Permissions

Each token has an array of allowed SQL operations:

Permission	Description
`SELECT`	Read data
`INSERT`	Create new rows
`UPDATE`	Modify existing rows
`DELETE`	Remove rows
`CREATE`	Create tables, indexes, views
`ALTER`	Modify table structure
`DROP`	Drop tables, truncate

Permission Sets:

Default (SELECT, INSERT, UPDATE, DELETE) - Safe for most applications
Tenant (all 7 permissions) - Full control over schema

SQL Validation

Every query is parsed and validated before execution:

Blocked Patterns

Multiple statements (prevents SQL injection via ;)
Schema-qualified table names (public.users, other_schema.data)
System tables (pg_*, information_schema)
Operations not allowed by token permissions

Examples

-- ✅ Allowed (with SELECT permission)SELECT*FROM users WHERE id = $1-- ✅ Allowed (with CREATE permission)CREATETABLEorders (id SERIALPRIMARY KEY, user_id INT)
-- ❌ Blocked: Multiple statementsSELECT1; DROPTABLEusers-- ❌ Blocked: Schema-qualified nameSELECT*FROMpublic.users-- ❌ Blocked: System table accessSELECT*FROM pg_tables
-- ✅ Allowed: postgate_helpers functionsSELECT*FROMpostgate_helpers.list_tables()

Helper Functions

The postgate_helpers schema provides utility functions accessible to all tenants:

postgate_helpers.list_tables()

List all tables in the current tenant's schema with row counts.

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

postgate_helpers.describe_table(name)

Describe columns of a table in the current tenant's schema.

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

Multi-Tenant Isolation

Schema Backend (Default)

Each tenant gets an isolated PostgreSQL schema:

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

How it works:

Query arrives with token
Postgate validates token, gets database_id
Looks up schema_name from postgate_databases
Executes in transaction with SET LOCAL search_path TO "tenant_xxx"
Tenant can only see their own tables

Dedicated Backend

For premium tenants, use a separate PostgreSQL connection:

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

PL/pgSQL Functions

All administration is done via SQL functions executed through /query:

create_tenant_database

Create a new tenant with isolated schema.

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

delete_tenant_database

Delete a tenant and drop their schema.

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

create_tenant_token

Create an API token for a database.

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.

delete_tenant_token

Delete a token by ID.

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

Querying Tokens (via SQL)

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

Database Schema

postgate_databases

Column	Type	Description
`id`	UUID	Primary key
`name`	VARCHAR(100)	Display name
`backend_type`	VARCHAR(20)	`'schema'` or `'dedicated'`
`schema_name`	VARCHAR(100)	For schema backend
`connection_string`	TEXT	For dedicated backend
`max_rows`	INTEGER	Max rows per query (default: 1000)
`created_at`	TIMESTAMPTZ	Creation timestamp

postgate_tokens

Column	Type	Description
`id`	UUID	Primary key
`database_id`	UUID	FK to postgate_databases
`name`	VARCHAR(100)	Token name
`token_hash`	VARCHAR(64)	SHA-256 hash (hex)
`token_prefix`	VARCHAR(8)	First 8 chars for identification
`allowed_operations`	TEXT[]	Array of permissions
`created_at`	TIMESTAMPTZ	Creation timestamp
`last_used_at`	TIMESTAMPTZ	Last usage timestamp

Seed Data

The migration creates a default admin database:

ID	Name	Backend	Purpose
`00000000-0000-0000-0000-000000000000`	postgate_admin	schema (public)	Admin operations

Client Integration

TypeScript/JavaScript

interfacePostgateQueryRequest{sql: string;params?: unknown[];}interfacePostgateQueryResponse<T=Record<string,unknown>>{rows: T[];row_count: number;}classPostgateClient{constructor(privatebaseUrl: string,privatetoken: string){}asyncquery<T>(sql: string,params?: unknown[]): Promise<PostgateQueryResponse<T>>{constresponse=awaitfetch(`${this.baseUrl}/query`,{method: 'POST',headers: {'Content-Type': 'application/json','Authorization': `Bearer ${this.token}`},body: JSON.stringify({ sql, params })});if(!response.ok){consterror=awaitresponse.json();thrownewError(error.error||`HTTP ${response.status}`);}returnresponse.json();}}// Usageconstclient=newPostgateClient('http://localhost:3000','pg_your_token');const{ rows }=awaitclient.query<{id: number;name: string}>('SELECT * FROM users WHERE id = $1',[1]);

Admin Operations

classPostgateAdminClientextendsPostgateClient{asynccreateDatabase(name: string,maxRows=1000){returnthis.query('SELECT * FROM create_tenant_database($1, $2::integer)',[name,maxRows]);}asyncdeleteDatabase(databaseId: string){returnthis.query('SELECT delete_tenant_database($1::uuid)',[databaseId]);}asynccreateToken(databaseId: string,name='default',permissions=['SELECT','INSERT','UPDATE','DELETE']){returnthis.query('SELECT * FROM create_tenant_token($1::uuid, $2, $3::text[])',[databaseId,name,permissions]);}asyncdeleteToken(tokenId: string){returnthis.query('SELECT delete_tenant_token($1::uuid)',[tokenId]);}}

Security Considerations

Token Security

Tokens are generated with 32 bytes of cryptographic randomness
Only SHA-256 hashes are stored (tokens cannot be recovered)
Tokens should be transmitted over HTTPS only
Rotate tokens periodically

SQL Injection Prevention

All queries are parsed and validated before execution
Multiple statements are blocked
Parameterized queries prevent injection in values
Schema-qualified names are blocked to prevent escaping tenant isolation

Schema Isolation

Each tenant operates in their own PostgreSQL schema
SET LOCAL search_path ensures queries only see tenant tables
System tables (pg_*) access is blocked
Cross-schema references are blocked

Development

# 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

Project Structure

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

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 23 Commits
.github/workflows		.github/workflows
.sqlx		.sqlx
.vscode		.vscode
migrations		migrations
src		src
tests		tests
.dockerignore		.dockerignore
.gitignore		.gitignore
Cargo.lock		Cargo.lock
Cargo.toml		Cargo.toml
Dockerfile		Dockerfile
Dockerfile.multi		Dockerfile.multi
README.md		README.md

Uh oh!