PostgreSQL Client Plugin for Workflow

A PostgreSQL protocol client implemented as an independent plugin for the Sogou Workflow C++ async framework. No modification to the workflow source code is required.

Features

• Full async I/O — built on Workflow's WFComplexClientTask template
• Simple Query protocol — Q message for query execution
• Authentication — Trust, Cleartext, MD5, and SCRAM-SHA-256 (SASL)
• SSL/TLS — direct SSL via TT_TCP_SSL (like HTTPS)
• Result cursor — field metadata, row iteration, type-aware cell access
• Connection keep-alive — automatic connection reuse across queries

Architecture

postgres_client/
├── CMakeLists.txt
├── example.cc                  # Standalone example program
├── src/
│   ├── postgres_types.h        # Protocol enums, type OIDs
│   ├── postgres_byteorder.h/c  # Big-endian (network byte order) helpers
│   ├── postgres_stream.h/c     # Message framing (type + length + payload)
│   ├── postgres_parser.h/c     # C-language backend message parser
│   ├── scram_sha256.h/cc       # SCRAM-SHA-256 authentication (RFC 5802)
│   ├── PostgresMessage.h/inl/cc  # C++ message classes (req/resp/startup)
│   ├── PostgresResult.h/inl/cc   # Result cursor, field, cell classes
│   ├── PostgresTaskImpl.cc     # Client task (handshake + query execution)
│   └── WFPostgresConnection.h  # Public API header
├── test/
│   └── postgres_cli.cc         # Interactive CLI client
└── bin/                        # Built binaries (after make)

The implementation follows the same layered design as Workflow's MySQL client:

Layer	MySQL equivalent	PostgreSQL equivalent
Types	mysql_types.h	postgres_types.h
Byte order	mysql_byteorder.h/c	postgres_byteorder.h/c
Stream	mysql_stream.h/c	postgres_stream.h/c
Parser	mysql_parser.h/c	postgres_parser.h/c
Message	MySQLMessage.h/inl/cc	PostgresMessage.h/inl/cc
Result	MySQLResult.h/inl/cc	PostgresResult.h/inl/cc
Task	MySQLTaskImpl.cc	PostgresTaskImpl.cc

Build

Prerequisites

1. Workflow library — build first:

   cd workflow
   mkdir build && cd build
   cmake .. -DCMAKE_BUILD_TYPE=Release
   make -j$(nproc)
   # Produces _lib/libworkflow.a and _include/workflow/

2. OpenSSL — required for SSL and SCRAM-SHA-256

3. PostgreSQL server (for testing) — version 10+

Build the plugin

cd postgres_client
mkdir build && cd build
cmake ..
make -j$(nproc)

This produces:

• libpostgres_client.a — static library
• bin/postgres_cli — interactive CLI client
• bin/postgres_example — example program

Quick Start

#include "WFPostgresConnection.h"
#include "PostgresResult.h"

void query_callback(WFPostgresTask *task)
{
    if (task->get_state() != WFT_STATE_SUCCESS) return;

    PostgresResponse *resp = task->get_resp();
    PostgresResultCursor cursor(resp);

    if (cursor.get_cursor_status() == POSTGRES_STATUS_GET_RESULT)
    {
        std::vector<std::vector<PostgresCell>> rows;
        cursor.fetch_all(rows);

        for (auto& row : rows)
        {
            for (auto& cell : row)
                printf("%s ", cell.as_string().c_str());
            printf("\n");
        }
    }
}

int main()
{
    auto *task = create_postgres_task(
        "postgres://user:pass@127.0.0.1:5432/mydb",
        0,  // retry_max
        query_callback);

    task->get_req()->set_query("SELECT 1");
    task->start();

    // ... wait for callback (use WFFacilities::WaitGroup)
    return 0;
}

URL Format

postgres://user:password@host:port/database?application_name=app
postgresql://user:password@host:port/database
postgresqls://user:password@host:port/database

Scheme	Transport	Description
postgres://	TCP	Plaintext connection
postgresql://	TCP	Plaintext connection (alias)
postgresqls://	TCP+SSL	SSL/TLS encrypted (like HTTPS)

Parameters:

• application_name — sets the startup application_name parameter

Default port: 5432 (registered for all three schemes)

Authentication

The handshake uses CommMessageIn::feedback() to send authentication responses during the receive phase. The entire startup (handshake + authentication) is a single request — no extra message_out/message_in cycles.

Method	Code	Description
Trust	AUTH_OK (0)	No password required
Cleartext	AUTH_CLEARTEXT_PASSWORD (3)	Password sent as-is
MD5	AUTH_MD5_PASSWORD (5)	MD5 hash with server salt
SCRAM-SHA-256	AUTH_SASL (10/11/12)	RFC 5802 / RFC 7677

SCRAM-SHA-256 is the default in PostgreSQL 10+ and is fully supported. The implementation uses PBKDF2-HMAC-SHA256, HMAC-SHA256, and verifies the server signature.

Configuring PostgreSQL for testing

# Initialize
initdb -D /tmp/pgdata -U postgres --auth-host=md5

# Start
pg_ctl -D /tmp/pgdata -l /tmp/pg.log start

# Set password
psql -h /tmp -U postgres -c "ALTER USER postgres WITH PASSWORD '123456';"

# Create database
psql -h /tmp -U postgres -c "CREATE DATABASE testdb;"

For SCRAM-SHA-256, set password_encryption = scram-sha-256 in postgresql.conf and scram-sha-256 in pg_hba.conf.

For SSL testing, use stunnel as a proxy:

stunnel stunnel.conf  # accept=5433, connect=127.0.0.1:5432

API Reference

Factory Functions

// Create from URL string
WFPostgresTask *create_postgres_task(const std::string& url,
                                     int retry_max,
                                     postgres_callback_t callback);

// Create from parsed URI
WFPostgresTask *create_postgres_task(const ParsedURI& uri,
                                     int retry_max,
                                     postgres_callback_t callback);

PostgresRequest

void set_query(const char *query);
void set_query(const std::string& query);
std::string get_query() const;
bool query_is_unset() const;

PostgresResponse

bool is_ok_packet() const;        // ReadyForQuery received
bool is_error_packet() const;     // ErrorResponse received
int get_packet_type() const;
std::string get_error_msg() const;
std::string get_error_code() const;    // SQLSTATE code
std::string get_command_tag() const;   // e.g. "INSERT 0 1"
unsigned long long get_affected_rows() const;

PostgresResultCursor

PostgresResultCursor(const PostgresResponse *resp);

bool next_result_set();          // advance to next result set
void first_result_set();

// Field metadata
const PostgresField *fetch_field();
const PostgresField *const *fetch_fields() const;
int get_field_count() const;
int get_rows_count() const;

// Row iteration
bool fetch_row(std::vector<PostgresCell>& row_arr);
bool fetch_row(std::map<std::string, PostgresCell>& row_map);
bool fetch_row(std::unordered_map<std::string, PostgresCell>& row_map);
bool fetch_all(std::vector<std::vector<PostgresCell>>& rows);

// Status
int get_cursor_status() const;   // POSTGRES_STATUS_GET_RESULT / OK / END
unsigned long long get_affected_rows() const;
std::string get_command_tag() const;

PostgresCell

uint32_t get_type_oid() const;
bool is_null() const;
bool is_int() const;
bool is_longlong() const;
bool is_bool() const;
bool is_float() const;
bool is_double() const;
bool is_string() const;
bool is_date() const;
bool is_timestamp() const;
bool is_bytea() const;

int as_int() const;
long long as_longlong() const;
bool as_bool() const;
float as_float() const;
double as_double() const;
std::string as_string() const;
std::string as_binary_string() const;

PostgresField

std::string get_name() const;
uint32_t get_type_oid() const;
uint16_t get_column_attr() const;
int16_t get_type_size() const;
int16_t get_format_code() const;

Using in a Workflow Project

Option 1: Link as static library

In your project's CMakeLists.txt:

# Point to the postgres_client and workflow directories
set(PG_CLIENT_DIR /path/to/postgres_client)
set(WORKFLOW_DIR /path/to/workflow)

# Include paths
include_directories(
    ${PG_CLIENT_DIR}/src
    ${WORKFLOW_DIR}/_include
)

# Link
target_link_libraries(your_app
    ${PG_CLIENT_DIR}/build/libpostgres_client.a
    ${WORKFLOW_DIR}/_lib/libworkflow.a
    ssl crypto pthread
)

Option 2: Add as subdirectory

# In your project's CMakeLists.txt
add_subdirectory(postgres_client)
target_link_libraries(your_app postgres_client)

Option 3: Copy source files

Copy the src/ directory into your project and add the source files to your build system. You only need the workflow headers and library.

Design Notes

Handshake via feedback()

Unlike the MySQL client (which uses a state machine with separate message_out/message_in pairs for each authentication step), the PostgreSQL client uses CommMessageIn::feedback() to send authentication responses during the receive phase.

This means the entire startup process is a single request:

1. message_out sends StartupMessage
2. message_in receives AuthenticationRequest → sends password via feedback() → receives AuthenticationOk + ReadyForQuery
3. The user query is the next request

For SCRAM-SHA-256, multiple feedback() calls happen within the same request:

Server: AuthSASL → Client feedback: SASLInitialResponse
Server: AuthSASLContinue → Client feedback: SASLResponse
Server: AuthSASLFinal → (no response needed)
Server: AuthOk + ReadyForQuery

SSL via TT_TCP_SSL

SSL is established at the TCP level by the framework, exactly like HTTPS. The postgresqls:// scheme sets TT_TCP_SSL, and the framework handles the SSL handshake automatically. No protocol-level SSL negotiation code is needed. This works with SSL proxies (stunnel, HAProxy) or any server that accepts direct SSL connections.