Building a tool to fetch PostgreSQL query metadata without execution

Introduction

When writing code generators or similar tools, there are many situations where you might want to retrieve metadata for SQL queries.
By "SQL query metadata" here, I mean things like the types and number of bound variables, and the names and types of the result columns.

In Node.js, node-postgres cannot retrieve metadata for result columns without executing the query.
Since a standard query execution (SELECT) is actually evaluated by the server, side effects and execution costs occur.
Therefore, generating code for input and output data models from PostgreSQL queries in Node.js required considering various factors.

However, by using "Describe" in the PostgreSQL communication protocol (hereafter "PostgreSQL Wire Protocol"), it is possible to obtain only the information from the analysis stage (metadata) without executing the query.
I have written a tool called pgpd that simply outputs this information.
This article aims to record the knowledge that was necessary for writing this tool.

Please note that I am not an expert on PostgreSQL. I would appreciate it if you could point out any errors.

Introduction to pgpd

First, let me briefly introduce the tool I wrote, pgpd.

It can be used via CLI / API. The CLI usage is as follows:

$ deno run -A jsr:@pgpd/pgpd/cli.ts 'postgres://postgres:password@localhost:5432/postgres?sslmode=disable' << EOF | jq
VALUES(\$1::text)
EOF
{
  "parameters": [
    {
      "type": {
        "oid": 25,
        "schema": "pg_catalog",
        "name": "text",
        "sqlType": "text"
      }
    }
  ],
  "rows": [
    {
      "name": "column1",
      "type": {
        "oid": 25,
        "schema": "pg_catalog",
        "name": "text",
        "sqlType": "text"
      },
      "format": "text"
    }
  ]
}

The arguments are specified in the order of the connection target and the file containing the query (standard input if omitted), and the results are output to standard output.

It is published on npm - pgpd and jsr - @pgpd/pgpd, and it works with Node.js / Deno / Bun.
(Note that Deno was used for the development itself.)

---

Message-based Protocol Used for Communication Between PostgreSQL Client and Server

I will explain the message-based protocol used for communication between the PostgreSQL client and server (hereafter referred to as the PostgreSQL Wire Protocol).

Overview

If you want to know the details of the protocol, please refer to the following:
https://www.postgresql.jp/docs/17/protocol.html

Messages from the client to the server and from the server to the client are in the same format.
The first byte contains a tag indicating the message type, and the next 4 bytes store the message length.
After that, the format is determined according to the message type.

| 1    | 2 | 3 | 4 | 5 | ... | N - 4 + 1 |
|------|---|---|---|---|-----|-----------|
| Type | Length N (including self)   | Depends on type|

Except for part of the initial handshake such as TLS (SSL) start requests and startup requests, messages are exchanged in almost the same format.

For example, a simple query without bindings follows a flow like this:

• To prioritize clarity, some messages are omitted, so the description is inaccurate.

1. C -> S ... Query
   • Includes the query string like Query(SELECT 1, 2)
2. S -> C ... RowDescription
   • Returns metadata such as the names and types of the result columns
3. S -> C ... DataRow
   • Includes column values for each row, like DataRow(1, 2)
4. S -> C ... ReadyForQuery
   • The response to the inquiry is complete, and the server is now in a state where it can accept commands from the client.

Extended Query

The previous Query was a simple query with an easy-to-understand format. In contrast, for issuing queries that use placeholders and bind values separately from the query statement for reuse, the extended query is used.

A typical flow is as follows:

• To prioritize clarity, some messages are omitted, making the description slightly inaccurate. Also, names of portals and prepared statements will not be discussed.

1. C -> S Parse
   • A request to parse a SQL statement with placeholders, such as Parse(SELECT $1, $2).
   • It is not executed at the time this message is sent; it is executed by sending a subsequent Sync message.
2. C -> S Describe
   • A request for metadata of the Parsed SQL statement.
   • This message is optional.
   • It is not executed at the time this message is sent; it is executed by sending a subsequent Sync message.
3. C -> S Bind
   • Includes values to be bound to placeholders, such as Bind(1, 2).
   • It is not executed at the time this message is sent; it is executed by sending a subsequent Sync message.
4. C -> S Execute
   • A request to execute the query using the parsed SQL statement and bound values.
   • It is not executed at the time this message is sent; it is executed by sending a subsequent Sync message.
5. C -> S Sync
   • Sending this message triggers the execution of all messages from Parse up to this point.
6. C -> S ParseComplete
   • Indicates that the parsing of the SQL statement is complete.
7. C -> S ParameterDescription
   • Metadata returned because Describe was sent, showing the type information of the placeholders in the SQL statement.
8. C -> S RowDescription
   • Metadata such as the names and type information of the result columns.
9. C -> S DataRow
   • Same as simple queries.
10. C -> S ReadyForQuery
    • Same as simple queries.

One thing to note here is that this is significantly more stateful than simple queries.
The design is such that no inquiries for other purposes are accepted from the issuance of Parse until ReadyForQuery arrives, and unintended messages in unintended states are not accepted.
State management must be handled on the client side. For example, even if processing is interrupted due to a client-side error, the client still needs to receive the final ReadyForQuery message.

Authentication

PostgreSQL supports various authentication methods. Among password-based and basic authentication, there are the following:

• trust ... No authentication
• plain ... Sends the password in plain text
• md5 ... Sends the password hashed with MD5. This was the default until recently.
• scram-sha-256 ... An authentication method involving SHA256 and salt

SCRAM-SHA-256

This is a method using the Salted Challenge Response Authentication Mechanism (SCRAM) with the SHA-256 hash function. According to English Wikipedia:

Although all clients and servers have to support the SHA-1 hashing algorithm, SCRAM is, unlike CRAM-MD5 or DIGEST-MD5, independent from the underlying hash function.[4] Any hash function defined by the IANA can be used instead.[5] As mentioned in the Motivation section, SCRAM uses the PBKDF2 mechanism, which increases the strength against brute-force attacks, when a data leak has happened on the server. Let H be the selected hash function, given by the name of the algorithm advertised by the server and chosen by the client. 'SCRAM-SHA-1' for instance, uses SHA-1 as hash function.

For those who want to know more, please refer to RFC 5802 for the SCRAM specification itself, and RFC 7677 for SCRAM-SHA-256.

In PostgreSQL, the flow is as follows (this is an overview and may not be completely accurate; please refer to the RFCs for details):

1. S -> C ... AuthenticationSASL
   • SCRAM-SHA-256 authentication might be included in the SASL mechanism.
2. C -> S ... SASLInitialResponse
   • When continuing the authentication flow with SCRAM-SHA-256:
     • Indicates it is SCRAM-SHA-256
     • Uses a format like n,,n=user,r=rOprNGfwEbeRWgbNEkqO as data (quoted from the RFC example)
       • n ... Username to authenticate
       • r ... Client nonce
3. S -> C ... AuthenticationSASLContinue
   • Contains data like the following (quoted from the RFC example):
   • r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,s=W22ZaJ0SNY7soEsUEjb6gQ==,i=4096
     • r ... Server nonce. Since it starts with the client's nonce, verification is possible (and should be done)
     • s ... Salt
     • i ... Iterations
4. C -> S ... SASLResponse
   • Outline of the steps to generate the response message:
     1. Generate a derived key using PBKDF2 (SHA256) with the password to verify, the salt, and the iteration count.
     2. Generate a client key from the derived key and "Client Key". A server key can be similarly generated with "Server Key".
     3. Generate a stored key by hashing the client key with SHA256.
     4. Apply HMAC to the concatenation of the following (separated by ,) using the stored key:
        • The data from SASLInitialResponse starting after n,,
        • The data received in AuthenticationSASLContinue
        • c=biws,r=<server nonce>
     5. Generate the client proof by XORing the client key with the HMAC value calculated above.
     6. Send c=biws,r=<server nonce>,p=<client proof>
   • Example of data to send:
     • c=biws,r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,p=dHzbZapWIk4jUhN+Ute9ytag9zjfMHgsqmmiz7AndVQ= (quoted from the RFC example)
5. S -> C ... AuthenticationSASLFinal
   • Contains data like the following (quoted from the RFC example):
   • v=6rriTRBi23WpRR/wtup+mMhUZUn/dB5nLTJRsjl95G4=
     • v ... Verification is possible (and should be done) to see if it matches the one generated from the server key, similar to the client proof.

Future Outlook

I want to extract this so it can run on WASM / WASI, removing the JavaScript dependency.