12 KiB

Raw Permalink Blame History

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

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

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

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

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

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

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:

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

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

# 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

# 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

Phase 1: Basic wire protocol and simple queries
Phase 2: Extended query protocol (prepared statements)
Phase 3: System catalog views
Phase 4: Advanced features (transactions, notifications)
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.

12 KiB Raw Permalink Blame History