Skip to content

authentication.md

Latest commit

 

History

History
308 lines (251 loc) · 13.8 KB

authentication.md

File metadata and controls

308 lines (251 loc) · 13.8 KB
Raw

PostgreSQL Proxy Authentication Design

Overview

The proxy implements pass-through authentication, where client credentials are validated against the real PostgreSQL backend. This maintains PostgreSQL's native security model while adding query analysis capabilities.

Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                              Client (psql)                                  │
│                         user: alice, pass: ****                             │
└─────────────────────────────────┬───────────────────────────────────────────┘
                                  │
                                  │ 1. Connect with credentials
                                  ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                           PROXY (:5432)                                     │
│  ┌───────────────────────────────────────────────────────────────────────┐  │
│  │                     Authentication Handler                            │  │
│  │                                                                       │  │
│  │  1. Receive username/password from client                             │  │
│  │  2. Attempt connection to real PostgreSQL with those credentials      │  │
│  │  3. If connection succeeds → auth passes, store connection            │  │
│  │  4. If connection fails → reject client with auth error               │  │
│  └───────────────────────────────────────────────────────────────────────┘  │
│                                  │                                          │
│                                  │ 2. Validate credentials                  │
│                                  ▼                                          │
│  ┌───────────────────────────────────────────────────────────────────────┐  │
│  │                     Connection Pool (per user)                        │  │
│  │                                                                       │  │
│  │  alice@db1 ──────► [conn1, conn2, conn3]                              │  │
│  │  bob@db1   ──────► [conn1, conn2]                                     │  │
│  │  alice@db2 ──────► [conn1]                                            │  │
│  └───────────────────────────────────────────────────────────────────────┘  │
│                                  │                                          │
│                                  │ 3. Use pooled connection                 │
│                                  ▼                                          │
│  ┌───────────────────────────────────────────────────────────────────────┐  │
│  │                     Query Handler                                     │  │
│  │                                                                       │  │
│  │  1. Check approval cache (keyed by query fingerprint)                 │  │
│  │  2. If not cached → send to LLM for analysis                          │  │
│  │  3. Execute approved queries using user's pooled connection           │  │
│  │  4. Return results to client                                          │  │
│  └───────────────────────────────────────────────────────────────────────┘  │
└─────────────────────────────────┬───────────────────────────────────────────┘
                                  │
                                  │ 4. Query execution
                                  ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                        Real PostgreSQL (:5433)                              │
│                                                                             │
│  Users: alice, bob, carol                                                   │
│  Databases: db1, db2                                                        │
│  Permissions: Standard PostgreSQL GRANT/REVOKE                              │
└─────────────────────────────────────────────────────────────────────────────┘

Authentication Flow

1. Initial Connection

Client                          Proxy                         PostgreSQL
  │                               │                               │
  │──── StartupMessage ──────────►│                               │
  │     (user, database)          │                               │
  │                               │                               │
  │◄─── AuthenticationMD5 ────────│                               │
  │     (request password)        │                               │
  │                               │                               │
  │──── PasswordMessage ─────────►│                               │
  │     (hashed password)         │                               │
  │                               │──── Connect ─────────────────►│
  │                               │     (user, pass, database)    │
  │                               │                               │
  │                               │◄─── AuthenticationOk ─────────│
  │                               │     (credentials valid)       │
  │                               │                               │
  │◄─── AuthenticationOk ─────────│                               │
  │     (proxy accepts client)    │                               │
  │                               │                               │
  │◄─── ReadyForQuery ────────────│                               │
  │                               │                               │

2. Failed Authentication

Client                          Proxy                         PostgreSQL
  │                               │                               │
  │──── StartupMessage ──────────►│                               │
  │──── PasswordMessage ─────────►│                               │
  │                               │──── Connect ─────────────────►│
  │                               │                               │
  │                               │◄─── ErrorResponse ────────────│
  │                               │     (invalid credentials)     │
  │                               │                               │
  │◄─── ErrorResponse ────────────│                               │
  │     "authentication failed"   │                               │
  │                               │                               │

Security Properties

What's Preserved

Property Description
User isolation Each client authenticates as themselves; queries run with their permissions
Permission model PostgreSQL's GRANT/REVOKE system remains authoritative
Password security Proxy never stores passwords; only validates them once
Database access Users can only access databases they have permission for
Audit trail PostgreSQL logs show the actual user executing queries

What's Added

Property Description
Query analysis All queries pass through LLM before execution
Query caching Approved/rejected decisions cached to reduce LLM calls
DDL monitoring Schema changes trigger metadata cache refresh

Connection Pooling

Each authenticated user gets their own connection pool:

type ConnectionPool struct {
    mu    sync.RWMutex
    pools map[string]*UserPool  // key: "user@database"
}

type UserPool struct {
    conns    chan *sql.Conn
    maxConns int
    connStr  string  // Built from user's credentials
}

Pool Key Format

{username}@{database}

Examples:

  • alice@production
  • bob@analytics
  • readonly@production

Connection Lifecycle

  1. First query: Create pool, establish connection
  2. Subsequent queries: Reuse pooled connection
  3. Idle timeout: Close connections after 10 minutes of inactivity
  4. Max connections: Limit per user (default: 5)

Configuration

Environment Variables

# Backend PostgreSQL (only host:port, no credentials)
PG_HOST=localhost
PG_PORT=5433

# Proxy listen address
PROXY_LISTEN=:5432

# Connection pool settings
POOL_MAX_CONNS_PER_USER=5
POOL_IDLE_TIMEOUT=10m

# SSL/TLS (optional)
PROXY_TLS_CERT=/path/to/cert.pem
PROXY_TLS_KEY=/path/to/key.pem

No Hardcoded Credentials

The proxy does NOT have a PG_BACKEND_URL with embedded credentials. Instead:

// OLD (insecure)
connStr = "postgres://admin:secret@localhost:5433/mydb"

// NEW (pass-through)
func buildConnString(user, pass, database string) string {
    return fmt.Sprintf("postgres://%s:%s@%s:%s/%s?sslmode=disable",
        url.QueryEscape(user),
        url.QueryEscape(pass),
        cfg.PGHost,
        cfg.PGPort,
        url.QueryEscape(database),
    )
}

Implementation Notes

psql-wire Authentication Callback

server, err := wire.NewServer(
    wire.Port(5432),
    wire.Auth(wire.CleartextPassword(validateCredentials)),
)

func validateCredentials(ctx context.Context, username, password string) (bool, error) {
    // Get database from startup parameters
    database := wire.DatabaseFromContext(ctx)

    // Try to connect to real PostgreSQL
    connStr := buildConnString(username, password, database)
    conn, err := sql.Open("postgres", connStr)
    if err != nil {
        return false, nil  // Auth failed
    }

    if err := conn.Ping(); err != nil {
        conn.Close()
        return false, nil  // Auth failed
    }

    // Store connection for this session
    pool.Add(username, database, conn)
    return true, nil  // Auth succeeded
}

Session Context

Each client session carries authentication context:

type SessionContext struct {
    Username string
    Database string
    PoolKey  string
    ConnectedAt time.Time
}

Supported Authentication Methods

Method Support Notes
trust Yes No password required
password Yes Cleartext (not recommended)
md5 Yes Hashed password
scram-sha-256 Planned PostgreSQL 10+ default
cert Planned Client certificate
gss No Kerberos not supported
ldap No Use PostgreSQL's native LDAP

Limitations

  1. Single backend: All users connect to the same PostgreSQL instance
  2. No connection multiplexing: Each user gets dedicated connections
  3. Session state: SET commands affect only that user's connections
  4. Prepared statements: Cached per-connection, not globally

Migration from Current Design

Before (No Auth)

// main.go
realDB, err = sql.Open("postgres", os.Getenv("PG_BACKEND_URL"))
wire.ListenAndServe(":5432", handler)

After (Pass-through Auth)

// main.go
server, err := wire.NewServer(
    wire.Port(5432),
    wire.Auth(wire.CleartextPassword(validateCredentials)),
    wire.SimpleQuery(handler),
)
server.ListenAndServe()

Testing Authentication

# Should succeed (valid user)
psql -h localhost -p 5432 -U alice -d mydb
Password: ****
mydb=>

# Should fail (wrong password)
psql -h localhost -p 5432 -U alice -d mydb
Password: wrong
psql: error: connection refused: authentication failed

# Should fail (unknown user)
psql -h localhost -p 5432 -U nobody -d mydb
psql: error: connection refused: authentication failed

# Should fail (no access to database)
psql -h localhost -p 5432 -U alice -d restricted_db
psql: error: connection refused: permission denied for database