Update Samples to use PG Direct TLS Negotiation

[rmarrowstone]

DSQL supports Postgres' Direct TLS Negotiation. This is a best practice for performance. The samples should be updated to support this, if they are using a PG 17 compatible client.

For more: https://marc-bowes.com/postgres-direct-tls.html

[danielfrankcom]

I looked into the drivers which we currently have code samples for, and the situation is not very uniform. Direct TLS negotiation is supported natively by some drivers, others require the underlying libpq they use to support it, and others don't support it at all right now.

Disclaimer: I haven't tested any of this so this information is just based on public docs I could find.

Native support

The drivers below have native support in new enough versions.

Language	Client / ORM	Support
C++	libpq	sslnegotiation=direct [ref]
C# (dotnet)	npgsql	SslNegotiation.Direct [ref]
Go	pgx	Config.SSLNegotiation=direct [ref]
Java	pgjdbc	sslNegotiation=direct [ref]

Support depends on libpq

The drivers below require libpq to function. Depending on the provided libpq version, the sslnegotiation configuration could be passed through the driver to libpq. Some drivers (both psycopgs) provide a precompiled binary option which must be built against a new enough libpq.

Without testing it's hard to say if any of these drivers have an allowlist of options, or if they pass all options to libpq. Even with a recent enough libpq version, it's possible some of these drivers would need code changes to allowlist sslnegotiation.

Language	Client / ORM	Parameter location
Python	psycopg	conn_params
Python	psycopg2	conn_params
Python	sqlalchemy (uses Psycopg)	connect_args
Ruby	rails	database.yml
Ruby	ruby-pg	PG.connect

Not currently supported

The drivers below do not currently support sslnegotiation as far as I can tell.

Rust SQLx

There doesn't seem to be an existing issue for this. The closest is launchbadge/sqlx#3264 which mentions the feature is new and not yet supported.

The docs don't list sslnegotiation in this table.

JavaScript Postgres.js

Not supported according to porsager/postgres#993.

JavaScript node-postgres

Not supported according to brianc/node-postgres#3346.

Provides a pg-native binding which is backed by libpq. This may allow for sslnegotiation usage, but it's unclear to me how many node-postgres users would be able to do this, or if there would be a performance impact.

Typescript Sequelize

Uses node-postgres, and delegates configuration there. Seems to support pg-native so may be an option if that works for node-postgres.

From here:

Sequelize uses the pg package to connect to PostgreSQL. Most of the above options are provided as-is to the pg package, and you can find more information about them in the pg documentation.

native: If true, Sequelize will use the pg-native package instead of the pg package. pg-native must be installed separately.

Typescript TypeORM

Uses node-postgres, and delegates configuration there. May be able to use pg-native if that works for node-postgres.

See ssl config here.

[danielfrankcom]

There are now PRs for each of the drivers which currently support direct TLS negotiation.

I created an issue (launchbadge/sqlx#3880) and PR (launchbadge/sqlx#3879) for SQLx. We should be able to update the sample if that change is accepted by the maintainers.

At the time of writing, the various JavaScript drivers are the only ones which we need upstream support for.

[rmarrowstone]

Awesome! Thank you.

[wcmjunior]

@rmarrowstone I asked @danielfrankcom to close this ticket once he finishes the changes for those drivers that currently support the direct TLS negotiation and open individual tickets for those that don't support. This will make it easier to track and fix in the future, otherwise this ticket will hang indefinitely.

@danielfrankcom I kindly ask you to relate all of the to this ticket so this can be referred in the future. Thanks!