PostgreSQL Wire Protocol Implementation

Overview

HeliosDB Lite implements the PostgreSQL wire protocol v3, enabling compatibility with PostgreSQL clients including psql, pgAdmin, and various ORMs.

Architecture

┌─────────────┐
│ psql/Client │
└──────┬──────┘
       │ TCP Connection
       │ Port 5432
┌──────▼──────────────────┐
│   Network Server        │
│  - TCP Listener         │
│  - Session Management   │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   Session Handler       │
│  - Authentication       │
│  - Message Decoder      │
│  - Message Encoder      │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   SQL Engine            │
│  - Parser               │
│  - Planner              │
│  - Executor             │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   Storage Engine        │
│  - Storage backend      │
│  - MVCC                 │
│  - WAL                  │
└─────────────────────────┘

Components

1. Protocol Messages

Implements message encoding/decoding for the PostgreSQL wire protocol.

Key Features:

• Message parsing (Frontend messages from client)
• Message encoding (Backend messages to client)
• Type OID mapping (PostgreSQL data types)
• Field descriptions for result sets

Supported Messages:

Frontend (Client → Server):

• Startup - Initial connection setup
• Query - Simple query protocol
• Parse - Prepare statement (extended protocol)
• Bind - Bind parameters to statement
• Execute - Execute prepared statement
• Describe - Get statement/portal metadata
• Close - Close statement/portal
• Sync - End of extended query
• Terminate - Close connection

Backend (Server → Client):

• Authentication* - Authentication challenges
• ReadyForQuery - Server ready for next command
• RowDescription - Column metadata for results
• DataRow - Result row data
• CommandComplete - Command execution complete
• ErrorResponse - Error occurred
• ParameterStatus - Server parameters
• BackendKeyData - Cancellation key

2. Authentication

Implements authentication mechanisms.

Supported Methods:

• Cleartext Password (for testing)
• SCRAM-SHA-256

SCRAM-SHA-256 Flow:

1. Client sends initial message with username and nonce
2. Server responds with salt, iteration count, and combined nonce
3. Client sends proof of password knowledge
4. Server verifies proof and sends signature
5. Authentication complete

3. Sessions

Manages individual client connections.

Responsibilities:

• Connection lifecycle management
• Authentication state machine
• Message routing
• Query execution coordination
• Transaction state tracking
• Prepared statement cache
• Portal (bound statement) cache

Session State:

• Unauthenticated
• Authenticating (SCRAM in progress)
• Authenticated
• Transaction (Idle/InTransaction/Failed)

4. Server

Asynchronous TCP server.

Features:

• Asynchronous I/O
• Concurrent session handling
• Graceful shutdown support
• Session ID management

Protocol Details

Connection Flow

1. Client Connects

   Client → Server: TCP Connection

2. Startup Message

   Client → Server: StartupMessage {
       protocol_version: 196608 (3.0)
       user: "postgres"
       database: "heliosdb"
   }

3. Authentication

   Server → Client: Authentication(CleartextPassword)
   Client → Server: PasswordMessage("password")
   Server → Client: Authentication(Ok)

4. Parameter Exchange

   Server → Client: ParameterStatus("server_version", "17.0 (HeliosDB Lite)")
   Server → Client: ParameterStatus("client_encoding", "UTF8")
   Server → Client: BackendKeyData { process_id, secret_key }
   Server → Client: ReadyForQuery(Idle)

5. Query Execution

   Client → Server: Query("SELECT * FROM users")
   Server → Client: RowDescription([field1, field2,...])
   Server → Client: DataRow([value1, value2,...])
   Server → Client: DataRow([value1, value2,...])
   Server → Client: CommandComplete("SELECT 2")
   Server → Client: ReadyForQuery(Idle)

Simple Query Protocol

Used by default for interactive queries.

Flow:

1. Client sends Query message
2. Server parses and executes
3. Server sends results:
   • RowDescription (for SELECT)
   • DataRow (one per result row)
   • CommandComplete
4. Server sends ReadyForQuery

Example:

-- Client sends
Query("SELECT id, name FROM users WHERE id = 1")

-- Server responds
RowDescription([
    Field { name: "id", type_oid: INT4, ... },
    Field { name: "name", type_oid: TEXT, ... }
])
DataRow([b"1", b"Alice"])
CommandComplete("SELECT 1")
ReadyForQuery(Idle)

Extended Query Protocol

Used for prepared statements and parameter binding.

Flow:

1. Parse - Prepare statement
2. Bind - Bind parameters
3. Describe - Get metadata (optional)
4. Execute - Run query
5. Sync - Commit

Example:

-- Prepare
Parse("stmt1", "SELECT * FROM users WHERE id = $1", [INT4])
ParseComplete

-- Bind
Bind("portal1", "stmt1", params=[b"42"])
BindComplete

-- Execute
Execute("portal1", max_rows=0)
RowDescription([...])
DataRow([...])
CommandComplete("SELECT 1")

-- Sync
Sync
ReadyForQuery(Idle)

Data Type Mapping

PostgreSQL OID	Type Name	HeliosDB DataType
16	BOOL	Boolean
20	INT8	BigInt
21	INT2	SmallInt
23	INT4	Integer
25	TEXT	Text
700	FLOAT4	Real
701	FLOAT8	Double
1043	VARCHAR	Varchar
1114	TIMESTAMP	Timestamp
1184	TIMESTAMPTZ	TimestampTz
2950	UUID	Uuid
3802	JSONB	Jsonb

Usage

Starting the Server

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --memory
# or, with persistent storage
heliosdb-lite start --data-dir ./mydb

Connecting with psql

# Connect to server
psql -h localhost -p 5432 -U postgres

# Enter password: postgres

# Run queries
SELECT * FROM users;
INSERT INTO users VALUES (1, 'Alice');
UPDATE users SET name = 'Bob' WHERE id = 1;

Connecting with ORMs

SQLAlchemy (Python):

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/heliosdb')

Diesel (Rust):

let database_url = "postgres://postgres:postgres@localhost:5432/heliosdb";
let connection = PgConnection::establish(&database_url)?;

Configuration

Server configuration options:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Listen on all interfaces (use password authentication and TLS)
heliosdb-lite start --data-dir ./mydb --listen 0.0.0.0 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"

# IPv6 localhost
heliosdb-lite start --data-dir ./mydb --listen ::1

# Custom port
heliosdb-lite start --data-dir ./mydb --port 54321

The same settings are available in config.toml under [server] (listen_addr, port, max_connections). See the CLI Reference.

Security

Authentication

• Trust and Cleartext Password: Useful for local development and controlled networks
• MD5 and SCRAM-SHA-256: Password-based authentication (--auth md5 or --auth scram-sha-256)
• SSL/TLS: Available when the server is started with --tls-cert and --tls-key

Best Practices

1. Always use SCRAM-SHA-256 in production
2. Enable SSL/TLS for network encryption
3. Use strong passwords
4. Limit network exposure (bind to localhost or firewall)
5. Record changes to sensitive tables with triggers (built-in audit logging is planned)

Performance

Benchmarks

Figures below are indicative, measured on representative fixtures; reproduce on your own hardware.

Connection handling:

• 1,000+ concurrent connections supported
• Sub-millisecond query latency for simple queries
• Async I/O prevents blocking

Query execution:

• Uses existing SQL engine
• Performance matches embedded mode
• No overhead from protocol translation

Tuning

Internal buffers size themselves. For many short-lived clients, pool connections on the client side or put HeliosProxy in front of the server.

Limitations

Current implementation limitations:

1. Protocol coverage: Lite targets common PostgreSQL client workflows, but it is not a byte-for-byte PostgreSQL server clone.

2. Catalog coverage: pg_catalog and information_schema support common introspection paths, not every PostgreSQL catalog edge case.

3. Binary result formats: Result format preferences are tracked by portals, but most query paths still emit text-encoded row values.

4. Cancellation: Query cancellation requests are not part of the common execution path.

Troubleshooting

Connection Refused

psql: error: connection to server at "localhost", port 5432 failed:
Connection refused

Solutions:

• Verify server is running
• Check bind address (use 0.0.0.0 for all interfaces)
• Check firewall rules

Authentication Failed

psql: error: FATAL:  password authentication failed for user "postgres"

Solutions:

• Verify username/password
• Check authentication configuration
• Review server logs

Protocol Errors

ERROR:  Protocol error: Invalid message type

Solutions:

• Ensure client is using PostgreSQL protocol
• Check for version mismatch
• Review server debug logs

Query Errors

ERROR:  Query execution error: Table not found

Solutions:

• Verify table exists: \dt
• Check SQL syntax
• Review query execution logs

Compatibility

PostgreSQL Compatibility

HeliosDB Lite implements PostgreSQL wire protocol v3:

• Compatible with PostgreSQL 8.0+
• Tested with psql 14, 15, 16, 17
• Works with most ORMs and drivers

Client Compatibility

Tested clients:

• psql (PostgreSQL command-line)
• pgAdmin 4
• DBeaver
• DataGrip
• psycopg2 (Python)
• pg (Node.js)
• sqlx (Rust)
• Diesel (Rust)

References

• PostgreSQL Wire Protocol Documentation
• SCRAM-SHA-256 RFC 7677
• PostgreSQL Message Formats
• PostgreSQL Error Codes

Contributing

When contributing to the network protocol implementation:

1. Follow PostgreSQL wire protocol specification exactly
2. Test with real PostgreSQL clients
3. Add error handling
4. Update documentation
5. Add tests for new features