pg_client

A PostgreSQL driver and implementation of the low-level Postgres wire protocol.

Relevant Documentation

• PostgreSQL message formats: https://www.postgresql.org/docs/current/protocol-message-formats.html
• PostgreSQL protocol flow: https://www.postgresql.org/docs/current/protocol-flow.html
• RFC 5802: Salt‑Challenge‑Response Authentication Mechanism (SCRAM): https://datatracker.ietf.org/doc/html/rfc5802

Project Overview

The project is a low-level PostgreSQL client demonstrating implementation of PostgreSQL's native protocol.

The current implementation focuses on:

• establishing a raw TCP connection to a PostgreSQL server
• negotiating and authenticating using PostgreSQL startup messages and complete implementation of SASL/(SCRAM-SHA-256) authentication mechanism as documented in the corresponding Postgres and RFC documentation (links above)
• query execution using the native PostgreSQL protocol.
• includes a custom shell for issuing queries.

Architecture

The code is organized into an organized set of cooperating components:

main.go

main.go is the driver entrypoint.

• reads command-line flags
• opens a TCP connection to the PostgreSQL server
• initializes a sessionManager
• starts the PostgreSQL session and query loop

session.go

The sessionManager owns the connection lifecycle and the authenticated session state.

Key responsibilities:

• send the PostgreSQL StartupMessage
• handle the initial authentication handshake
• wait for ReadyForQuery before starting interactive queries
• maintain protocol metadata such as parameter statuses and backend key data
• coordinate between authentication and query layers

The session manager currently expects PostgreSQL protocol version 3.0 and only supports a single socket connection.

sasl_auth.go

Authentication is handled by authManager.

Key features:

• constructs a PostgreSQL StartupMessage with user and optional database parameters
• parses a server AuthSASL response and verifies support for SCRAM-SHA-256
• generates a client-first message with SASLprep username normalization and a randomized nonce
• performs SCRAM-SHA-256 proof computation using the cryptographic functions PBKDF2 and HMAC-SHA-256 as defined in the RFC documentation.
• verifies the server final message and reads the final AuthOk response

query.go

The queryManager handles interactive shell and query execution once authentication completes.

Current behavior:

• reads SQL queries from standard input
• sends a query to the server
• parses incoming server messages
• prints command completion and query lifecycle messages

Packet construction

queryManager.sendSimpleQuery() builds a Simple Query message as:

• byte 'Q'
• int32 message length (4 + query length + 1)
• query text bytes
• null terminator

This is the standard PostgreSQL frontend Simple Query wire format.

Server message parsing

The client reads server messages using a 5-byte header:

• 1 byte type identifier
• 4 byte message length

It then reads the message body and dispatches by message type. Important handling includes:

• parseCommandComplete prints the server's completion tag
• parseReadyForQuery prints transaction status and allows the next query
• parseErrorResponse extracts the first message field from PostgreSQL error fields
• parseNotificationResponse prints channel notifications
• parseParamStatusMsg stores runtime parameter values sent by the backend

Roadmap

This project is designed to evolve to a lightweight, usable PostgreSQL client.

Phase 1: Basic data type and protocol support

Focus on building a stable foundation for simple query execution and result decoding.

• support full RowDescription and DataRow parsing
• decode basic PostgreSQL scalar types such as:
  • text, varchar, char
  • int2, int4, int8
  • float4, float8
  • bool
  • timestamp, timestamptz
  • date
  • bytea
• add parameter type awareness for query results
• properly handle ErrorResponse details and server error fields
• improve the interactive query loop to show result rows instead of just command completion

Phase 2: Extended query and usability improvements

Once basic row decoding is complete, add richer PostgreSQL client features.

• support the extended query protocol (Parse, Bind, Execute, Sync)
• implement prepared statements and parameter binding
• add transaction commands support (BEGIN, COMMIT, ROLLBACK)
• support query cancellation and backend key data usage
• improve CLI ergonomics and configuration options

Phase 3: Complex types and PostgreSQL-specific features

After basic scalar support is stable, then possibly expand into more advanced PostgreSQL type handling and protocol capabilities including:

• support complex types such as:
  • arrays (int[], text[])
  • json / jsonb
  • uuid
  • composite types and custom domains
  • interval
  • hstore
  • range types
• support binary protocol transfer modes for better performance
• add support for COPY and streaming result sets
• support logical replication and LISTEN/NOTIFY more robustly
• implement type registry and OID-driven decode logic
• build a minimal API layer for applications to consume typed query results

Running the client

To run the client against a local PostgreSQL server:

go run . --db=<database> --username=<user> --password=<password>

If --db is omitted, PostgreSQL defaults the database name to the username.

Note

This project is intended as an educational low-level client rather than a drop-in replacement for mature database drivers. The current primary goal is to learn and demonstrate PostgreSQL wire protocol handling, authentication, and query dispatch mechanisms.