Support for Clients Using the `PostgreSQL` Wire Protocol

[mamcx]

Some considerations and decision we need in the short-term:

Authentication Options

Using SSL/Certs

PostgreSQL supports certificate-based authentication over SSL. A typical psql connection looks like:

psql "port=5432 host=localhost user=postgres sslcert=${STDB_PATH}/id_ecdsa.crt sslkey=${STDB_PATH}/id_ecdsa sslrootcert=${STDB_PATH}/root.crt sslmode=require"

To generate the required certificate:

openssl req -new -x509 \
    -key "${STDB_PATH}/id_ecdsa" \
    -out "${STDB_PATH}/id_ecdsa.crt" \
    -days 365

chmod 0600 "${STDB_PATH}/id_ecdsa"
chmod 0644 "${STDB_PATH}/id_ecdsa.crt"

To encode identity, we can either use the Common Name (CN) or embed it via Subject Alternative Name (SAN):

openssl ... -subj "/CN=$IDENTITY"
# or
openssl ... -subj "/CN=postgres" \
  -reqexts SAN -config <(cat /etc/ssl/openssl.cnf \
  <(printf "\n[SAN]\nsubjectAltName=URI:https://my-server.example.com,URI:identity-token:$IDENTITY"))

And parse the identity from the CN or SAN field.

Alternatively, the server could derive the IDENTITY from the certificate itself, though this requires deeper inspection of the cert chain.

Compatibility

Some tools (like database explorers, ORMs, etc) may lack support for client certs, and I think is rare for users to use this setup.

Hijacking username/password

To support simpler auth flows, we can overload username or password fields to carry the IDENTITY. Using password is preferable for the possibility the tool hide it in logs,
despite username being more semantically correct.

The advantage is that is simpler, but could mean the end user embed the IDENTITY in code.

Unsupported features that affect the usability of using the protocol.

Many tools expect certain PostgreSQL behaviors. For instance, psql uses queries against system views (e.g., \d) to introspect schemas.

To support these, we should:

• Implement the expected system views (maybe making a custom module for this, or doing in-memory rewrites to mimic the pg_catalog schema).
• And extend our SQL dialect to support more clauses (e.g., ORDER BY, CASE, etc.) that are emitted by the tools.

Without this, compatibility with standard clients will be severely limited.

The most simple utility to support is psql, as it is the most common tool used by developers, and maybe restrict it to the most common commands (e.g., \d).

[mamcx]

After talk with @joshua-spacetime

• Auth is done passing the token as the password field using Clear Text Protocol. The connection is set using ssl so the communication is secure
• I'm working with upstream crate to see if we can use cert auth too