path: root/weed/server/postgres/DESIGN.md

# PostgreSQL Wire Protocol Support for SeaweedFS

## Overview

This design adds native PostgreSQL wire protocol support to SeaweedFS, enabling compatibility with all PostgreSQL clients, tools, and drivers without requiring custom implementations.

## Benefits

### Universal Compatibility
- **Standard PostgreSQL Clients**: psql, pgAdmin, Adminer, etc.
- **JDBC/ODBC Drivers**: Use standard PostgreSQL drivers
- **BI Tools**: Tableau, Power BI, Grafana, Superset with native PostgreSQL connectors
- **ORMs**: Hibernate, ActiveRecord, Django ORM, etc.
- **Programming Languages**: Native PostgreSQL libraries in Python (psycopg2), Node.js (pg), Go (lib/pq), etc.

### Enterprise Integration
- **Existing Infrastructure**: Drop-in replacement for PostgreSQL in read-only scenarios
- **Migration Path**: Easy transition from PostgreSQL-based analytics
- **Tool Ecosystem**: Leverage entire PostgreSQL ecosystem

## Architecture

```
┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│   PostgreSQL    │    │   PostgreSQL     │    │   SeaweedFS     │
│   Clients       │◄──►│   Protocol       │◄──►│   SQL Engine    │
│   (psql, etc.)  │    │   Server         │    │                 │
└─────────────────┘    └──────────────────┘    └─────────────────┘
                              │
                              ▼
                       ┌──────────────────┐
                       │   Authentication │
                       │   & Session Mgmt │
                       └──────────────────┘
```

## Core Components

### 1. PostgreSQL Wire Protocol Handler

```go
// PostgreSQL message types
const (
    PG_MSG_STARTUP         = 0x00  // Startup message
    PG_MSG_QUERY           = 'Q'   // Simple query
    PG_MSG_PARSE           = 'P'   // Parse (prepared statement)
    PG_MSG_BIND            = 'B'   // Bind parameters
    PG_MSG_EXECUTE         = 'E'   // Execute prepared statement
    PG_MSG_DESCRIBE        = 'D'   // Describe statement/portal
    PG_MSG_CLOSE           = 'C'   // Close statement/portal
    PG_MSG_FLUSH           = 'H'   // Flush
    PG_MSG_SYNC            = 'S'   // Sync
    PG_MSG_TERMINATE       = 'X'   // Terminate connection
    PG_MSG_PASSWORD        = 'p'   // Password message
)

// PostgreSQL response types
const (
    PG_RESP_AUTH_OK        = 'R'   // Authentication OK
    PG_RESP_AUTH_REQ       = 'R'   // Authentication request
    PG_RESP_BACKEND_KEY    = 'K'   // Backend key data
    PG_RESP_PARAMETER      = 'S'   // Parameter status
    PG_RESP_READY          = 'Z'   // Ready for query
    PG_RESP_COMMAND        = 'C'   // Command complete
    PG_RESP_DATA_ROW       = 'D'   // Data row
    PG_RESP_ROW_DESC       = 'T'   // Row description
    PG_RESP_PARSE_COMPLETE = '1'   // Parse complete
    PG_RESP_BIND_COMPLETE  = '2'   // Bind complete
    PG_RESP_CLOSE_COMPLETE = '3'   // Close complete
    PG_RESP_ERROR          = 'E'   // Error response
    PG_RESP_NOTICE         = 'N'   // Notice response
)
```

### 2. Session Management

```go
type PostgreSQLSession struct {
    conn             net.Conn
    reader           *bufio.Reader
    writer           *bufio.Writer
    authenticated    bool
    username         string
    database         string
    parameters       map[string]string
    preparedStmts    map[string]*PreparedStatement
    portals          map[string]*Portal
    transactionState TransactionState
    processID        uint32
    secretKey        uint32
}

type PreparedStatement struct {
    name       string
    query      string
    paramTypes []uint32
    fields     []FieldDescription
}

type Portal struct {
    name       string
    statement  string
    parameters [][]byte
    suspended  bool
}
```

### 3. SQL Translation Layer

```go
type PostgreSQLTranslator struct {
    dialectMap map[string]string
}

// Translates PostgreSQL-specific SQL to SeaweedFS SQL
func (t *PostgreSQLTranslator) TranslateQuery(pgSQL string) (string, error) {
    // Handle PostgreSQL-specific syntax:
    // - SELECT version() -> SELECT 'SeaweedFS 1.0'
    // - SELECT current_database() -> SELECT 'default'
    // - SELECT current_user -> SELECT 'seaweedfs'
    // - \d commands -> SHOW TABLES/DESCRIBE equivalents
    // - PostgreSQL system catalogs -> SeaweedFS equivalents
}
```

### 4. Data Type Mapping

```go
var PostgreSQLTypeMap = map[string]uint32{
    "TEXT":      25,   // PostgreSQL TEXT type
    "VARCHAR":   1043, // PostgreSQL VARCHAR type
    "INTEGER":   23,   // PostgreSQL INTEGER type
    "BIGINT":    20,   // PostgreSQL BIGINT type
    "FLOAT":     701,  // PostgreSQL FLOAT8 type
    "BOOLEAN":   16,   // PostgreSQL BOOLEAN type
    "TIMESTAMP": 1114, // PostgreSQL TIMESTAMP type
    "JSON":      114,  // PostgreSQL JSON type
}

func SeaweedToPostgreSQLType(seaweedType string) uint32 {
    if pgType, exists := PostgreSQLTypeMap[strings.ToUpper(seaweedType)]; exists {
        return pgType
    }
    return 25 // Default to TEXT
}
```

## Protocol Implementation

### 1. Connection Flow

```
Client                          Server
  │                              │
  ├─ StartupMessage ────────────►│
  │                              ├─ AuthenticationOk
  │                              ├─ ParameterStatus (multiple)
  │                              ├─ BackendKeyData
  │                              └─ ReadyForQuery
  │                              │
  ├─ Query('SELECT 1') ─────────►│
  │                              ├─ RowDescription
  │                              ├─ DataRow
  │                              ├─ CommandComplete
  │                              └─ ReadyForQuery
  │                              │
  ├─ Parse('stmt1', 'SELECT $1')►│
  │                              └─ ParseComplete
  ├─ Bind('portal1', 'stmt1')───►│
  │                              └─ BindComplete  
  ├─ Execute('portal1')─────────►│
  │                              ├─ DataRow (multiple)
  │                              └─ CommandComplete
  ├─ Sync ──────────────────────►│
  │                              └─ ReadyForQuery
  │                              │
  ├─ Terminate ─────────────────►│
  │                              └─ [Connection closed]
```

### 2. Authentication

```go
type AuthMethod int

const (
    AuthTrust AuthMethod = iota
    AuthPassword
    AuthMD5
    AuthSASL
)

func (s *PostgreSQLServer) handleAuthentication(session *PostgreSQLSession) error {
    switch s.authMethod {
    case AuthTrust:
        return s.sendAuthenticationOk(session)
    case AuthPassword:
        return s.handlePasswordAuth(session)
    case AuthMD5:
        return s.handleMD5Auth(session)
    default:
        return fmt.Errorf("unsupported auth method")
    }
}
```

### 3. Query Processing

```go
func (s *PostgreSQLServer) handleSimpleQuery(session *PostgreSQLSession, query string) error {
    // 1. Translate PostgreSQL SQL to SeaweedFS SQL
    translatedQuery, err := s.translator.TranslateQuery(query)
    if err != nil {
        return s.sendError(session, err)
    }
    
    // 2. Execute using existing SQL engine
    result, err := s.sqlEngine.ExecuteSQL(context.Background(), translatedQuery)
    if err != nil {
        return s.sendError(session, err)
    }
    
    // 3. Send results in PostgreSQL format
    err = s.sendRowDescription(session, result.Columns)
    if err != nil {
        return err
    }
    
    for _, row := range result.Rows {
        err = s.sendDataRow(session, row)
        if err != nil {
            return err
        }
    }
    
    return s.sendCommandComplete(session, fmt.Sprintf("SELECT %d", len(result.Rows)))
}
```

## System Catalogs Support

PostgreSQL clients expect certain system catalogs. We'll implement views for key ones:

```sql
-- pg_tables equivalent
SELECT 
    'default' as schemaname,
    table_name as tablename,
    'seaweedfs' as tableowner,
    NULL as tablespace,
    false as hasindexes,
    false as hasrules,
    false as hastriggers
FROM information_schema.tables;

-- pg_database equivalent  
SELECT 
    database_name as datname,
    'seaweedfs' as datdba,
    'UTF8' as encoding,
    'C' as datcollate,
    'C' as datctype
FROM information_schema.schemata;

-- pg_version equivalent
SELECT 'SeaweedFS 1.0 (PostgreSQL 14.0 compatible)' as version;
```

## Configuration

### Server Configuration
```go
type PostgreSQLServerConfig struct {
    Host         string
    Port         int
    Database     string
    AuthMethod   AuthMethod
    Users        map[string]string // username -> password
    TLSConfig    *tls.Config
    MaxConns     int
    IdleTimeout  time.Duration
}
```

### Client Connection String
```bash
# Standard PostgreSQL connection strings work
psql "host=localhost port=5432 dbname=default user=seaweedfs"
PGPASSWORD=secret psql -h localhost -p 5432 -U seaweedfs -d default

# JDBC URL
jdbc:postgresql://localhost:5432/default?user=seaweedfs&password=secret
```

## Command Line Interface

```bash
# Start PostgreSQL protocol server
weed db -port=5432 -auth=trust
weed db -port=5432 -auth=password -users="admin:secret;readonly:pass"
weed db -port=5432 -tls-cert=server.crt -tls-key=server.key

# Configuration options
-host=localhost              # Listen host
-port=5432                   # PostgreSQL standard port
-auth=trust|password|md5     # Authentication method
-users=user:pass;user2:pass2 # User credentials (password/md5 auth) - use semicolons to separate users
-database=default            # Default database name
-max-connections=100         # Maximum concurrent connections
-idle-timeout=1h             # Connection idle timeout
-tls-cert=""                 # TLS certificate file
-tls-key=""                  # TLS private key file
```

## Client Compatibility Testing

### Essential Clients
- **psql**: PostgreSQL command line client
- **pgAdmin**: Web-based administration tool  
- **DBeaver**: Universal database tool
- **DataGrip**: JetBrains database IDE

### Programming Language Drivers
- **Python**: psycopg2, asyncpg
- **Java**: PostgreSQL JDBC driver
- **Node.js**: pg, node-postgres
- **Go**: lib/pq, pgx
- **.NET**: Npgsql

### BI Tools
- **Grafana**: PostgreSQL data source
- **Superset**: PostgreSQL connector
- **Tableau**: PostgreSQL native connector
- **Power BI**: PostgreSQL connector

## Implementation Plan

1. **Phase 1**: Basic wire protocol and simple queries
2. **Phase 2**: Extended query protocol (prepared statements)
3. **Phase 3**: System catalog views
4. **Phase 4**: Advanced features (transactions, notifications)
5. **Phase 5**: Performance optimization and caching

## Limitations

### Read-Only Access
- INSERT/UPDATE/DELETE operations not supported
- Returns appropriate error messages for write operations

### Partial SQL Compatibility
- Subset of PostgreSQL SQL features
- SeaweedFS-specific limitations apply

### System Features
- No stored procedures/functions
- No triggers or constraints
- No user-defined types
- Limited transaction support (mostly no-op)

## Security Considerations

### Authentication
- Support for trust, password, and MD5 authentication
- TLS encryption support
- User access control

### SQL Injection Prevention
- Prepared statements with parameter binding
- Input validation and sanitization
- Query complexity limits

## Performance Optimizations

### Connection Pooling
- Configurable maximum connections
- Connection reuse and idle timeout
- Memory efficient session management

### Query Caching  
- Prepared statement caching
- Result set caching for repeated queries
- Metadata caching

### Protocol Efficiency
- Binary result format support
- Batch query processing
- Streaming large result sets

This design provides a comprehensive PostgreSQL wire protocol implementation that makes SeaweedFS accessible to the entire PostgreSQL ecosystem while maintaining compatibility and performance.