PG19 Hacktober: 31 Days of New Features: Security and Connectivity: SNI, OAuth Improvements, and libpq Additions Part 1

By /

Welcome back to the PG19 Hacktober series.

PostgreSQL has always taken security seriously, and version 19 raises the bar further. This installment covers five interconnected improvements spanning TLS certificate management, OAuth authentication, large-object permissions, and client connectivity. Whether you manage enterprise database infrastructure, build applications that authenticate against external identity providers, or simply want finer-grained access control, there is something here for you.

Release status: PostgreSQL 19 is currently documented as a release candidate. Check the final release notes before using these features in production.

1. Server-Side SNI Support

What is it?

Server Name Indication (SNI) is a TLS extension that lets a client announce the hostname it intends to connect to before the TLS handshake is complete. Web servers have used SNI for years to serve multiple TLS certificates from a single IP address, the same technique that powers virtual hosting over HTTPS.

Until PostgreSQL 19, the server had no mechanism to inspect this extension. Every incoming connection received the same certificate, regardless of which hostname the client asked for.

2. What Changes in PostgreSQL 19?

In the PostgreSQL 18 environment used for this test, the server does not recognize the ssl_sni and hosts_file configuration parameters.

In my PostgreSQL 19rc1 environment, both parameters are recognized. This provides an initial indication that the tested build exposes the configuration needed to investigate server-side SNI.

The configuration parameters are:

  • ssl_sni — controls whether server-side SNI handling is enabled.
  • hosts_file — identifies the host configuration file, which in my environment is /var/lib/pgsql/19/data/pg_hosts.conf.

The feature is opt-in in the tested rc1 build, so recognizing these settings does not mean that SNI is already enabled or working.

3. PostgreSQL 18.6 vs PostgreSQL-19rc1

PostgreSQL 18.6

I connected to the PostgreSQL 18.6 server and checked its version and SSL settings.

postgres=# SHOW server_version;
SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;
 server_version
----------------
 18.6
(1 row)
 ssl
-----
 off
(1 row)
 ssl_cert_file
---------------
 server.crt
(1 row)
 ssl_key_file
--------------
 server.key
(1 row)

Next, I checked the SNI-related parameters:

postgres=# SHOW ssl_sni;
SHOW hosts_file;
ERROR:  unrecognized configuration parameter "ssl_sni"
ERROR:  unrecognized configuration parameter "hosts_file"

This confirms that these parameters are not available on the PostgreSQL 18.6 server I tested.

pg19:
postgres=# SHOW server_version;
SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;
  server_version
----------------
 19rc1
(1 row)
 ssl
-----
 off
(1 row)
 ssl_cert_file
---------------
 server.crt
(1 row)
 ssl_key_file
--------------
 server.key
(1 row)
postgres=# SHOW ssl_sni;
SHOW hosts_file;
 ssl_sni
---------
 off
(1 row)
              hosts_file
--------------------------------------
 /var/lib/pgsql/19/data/pg_hosts.conf
(1 row)

I have created the two self-signed certificates and their corresponding private keys.

sameer.crt
sameer.key
sam.crt
sam.key

I generated the certificates with OpenSSL:

[root@node3 pg-sni-test]# openssl req -x509 -newkey rsa:2048 -nodes \
  -keyout sameer.key -out sameer.crt \
  -days 30 -subj "/CN=shameer.b@opensource-db.com"
..+.....+...+............+...+.........+.........+.........+......+.+......+.....+...+....+...........+..........+.........+++++++++++++++++++++++++++++++
[root@node3 pg-sni-test]#
openssl req -x509 -newkey rsa:2048 -nodes \
  -keyout sam.key -out sam.crt \
  -days 30 -subj "/CN=sam.corp"
.+...............+.+..+..........+...+.....+..........+..+...+.+.....+..........+.....+......+.......+.....+...............+.+..+++++++++++++++++++++++++++++++++++++++*....+++++++++++++++++++++++++++++++++++++++*..+......+..............+......+.++++++-----
[root@node3 pg-sni-test]# chmod 600 sameer.key sam.key

Install Certificates into the Data Directory

# Install cert files (644) and key files (600) as postgres:postgres
install -o postgres -g postgres -m 644 \
  ~/pg-sni-test/sameer.crt /var/lib/pgsql/19/data/sameer.crt
install -o postgres -g postgres -m 600 \
  ~/pg-sni-test/sameer.key /var/lib/pgsql/19/data/sameer.key
install -o postgres -g postgres -m 644 \
  ~/pg-sni-test/sam.crt /var/lib/pgsql/19/data/sam.crt
install -o postgres -g postgres -m 600 \
  ~/pg-sni-test/sam.key /var/lib/pgsql/19/data/sam.key

The PostgreSQL server process runs as the postgres user, so certificate files must be owned by postgres with correct permissions:

Configuring pg_hosts.conf

cat > /var/lib/pgsql/19/data/pg_hosts.conf <<'EOF' 
#hostname     ssl_cert_file            ssl_key_file
sameer.local  /var/../data/sameer.crt  /var/../data/sameer.key
sam.local     /var/../sam.crt          /var/..../sam.key
*             /var/../server.crt       /var/.../server.key
EOF
chown postgres:postgres /var/lib/pgsql/19/data/pg_hosts.conf
chmod 600 /var/lib/pgsql/19/data/pg_hosts.conf

The * entry acts as a wildcard fallback for any hostname not explicitly listed, including clients that do not send an SNI extension at all.

[root@node3 postgresql-19rc1]# ./configure \
  --prefix=/usr/local/pgsql-19 \
  --with-ssl=openssl \
  --with-readline \
  --with-zlib
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for gcc... gcc
checking whether the C compiler works... yes
checking whether gcc supports -fexcess-precision=standard, for CFLAGS... yes
checking whether g++ supports -fexcess-precision=standard, for CXXFLAGS... no
checking whether gcc supports -Wunused-command-line-argument, for NOT_THE_CFLAGS... no
..........
checking zlib.h usability... yes
checking zlib.h presence... yes
checking for zlib.h... yes
checking for lz4... /usr/bin/lz4
checking for zstd... /usr/bin/zstd
checking for openssl... /usr/bin/openssl
configure: using openssl: OpenSSL 3.5.8 25 Aug 2026 (Library: OpenSSL 3.5.8 25 Aug 2026)
checking openssl/ssl.h usability... yes
checking openssl/ssl.h presence... yes
checking for openssl/ssl.h... yes
checking openssl/err.h usability... yes
checking openssl/err.h presence... yes
checking for openssl/err.h... yes
..............
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: src/interfaces/ecpg/include/ecpg_config.h is unchanged
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

The configuration process successfully detected OpenSSL, including the ClientHello callback required for the SNI implementation being investigated.

I verified the new build configuration:

[root@node3 postgresql-19rc1]# grep '^with_ssl' src/Makefile.global
with_ssl        = openssl

This confirms that the source tree is now configured to build with OpenSSL support. Compilation, installation, server startup, and the actual SNI handshake test remain the next steps.

Enable SSL
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_sni = on;
SELECT pg_reload_conf();
ALTER SYSTEM
ALTER SYSTEM
 pg_reload_conf
----------------
 t
(1 row)

Verify:

postgres=# SHOW server_version;
SHOW ssl;
SHOW ssl_sni;
SHOW hosts_file;
 server_version
----------------
 19rc1
(1 row)
 ssl
-----
 on
(1 row)
 ssl_sni
---------
 on
(1 row)
              hosts_file
--------------------------------------
 /var/lib/pgsql/19/data/pg_hosts.conf
(1 row)
Test the SNI Handshake

With SSL and SNI active and pg_hosts.conf mapped, verify that the server serves distinct certificates based on the client’s SNI hostname using openssl s_client.

Test 1: Requesting sameer.local

[root@node3 postgresql-19rc1]# openssl s_client -starttls postgres \
  -connect 127.0.0.1:5419 \
  -servername sameer.local </dev/null 2>/dev/null \
  | openssl x509 -noout -subject
subject=CN=shameer.b@opensource-db.com

Test 2: Requesting sam.local

[root@node3 postgresql-19rc1]# openssl s_client -starttls postgres \
  -connect 127.0.0.1:5419 \
  -servername sam.local </dev/null 2>/dev/null \
  | openssl x509 -noout -subject
subject=CN=sam.corp

Test 3: No SNI Provided (-noservername Fallback)

[root@node3 postgresql-19rc1]# openssl s_client -starttls postgres \
  -connect 127.0.0.1:5419 \
  -noservername \
  </dev/null 2>/dev/null \
  | openssl x509 -noout -subject
subject=CN=*.opensource-db.com

(Note: Replace CN=*.opensource-db.com with CN=*.local or the exact CN you assigned when generating your fallback wildcard certificate).

2. Custom pg_hba.conf Options for OAuth Validators

PostgreSQL’s pg_hba.conf has long been the single configuration point for connection authentication policies. When the oauth authentication method was introduced, it brought with it a fixed set of known options. OAuth deployments are diverse, however — different authorization servers, different scopes, different token introspection endpoints — and a one-size-fits-all option set quickly becomes limiting.

What Changed

PostgreSQL 19 allows OAuth validator modules to register their own custom authentication options that are accepted inside pg_hba.conf entries.

A pg_hba.conf record using a custom option might look like this:

# TYPE  DATABASE  USER      ADDRESS     METHOD  OPTIONS
host    all       all       0.0.0.0/0   oauth   issuer="https://idp.example.com"
                                                scope="openid profile email"
                                                audience="pgdb-prod"
                                                require_group="db-admins"

require_group could be a custom option registered by the validator module, not something PostgreSQL itself would know about.

A validator module declares its accepted options through a new registration interface. PostgreSQL validates the option names and types at pg_hba.conf load time, so mistyped option names surface during configuration reload rather than at connection time.

Why It Matters

Before PostgreSQL 19:

  • Validator modules had to smuggle configuration through workarounds (environment variables, side-channel config files, or OPTIONS strings they parsed themselves).

With PostgreSQL 19:

  • Options are first-class citizens in pg_hba.conf.
  • Errors are caught at reload time, not at connection time.
  • The configuration is self-documenting in a single, familiar file.

TIP

If you build or maintain an OAuth validator module for PostgreSQL, review the updated validator API documentation to register your options. This will make your module significantly easier for DBAs to configure correctly.

3. pg_read_all_data and pg_write_all_data Support for Large Objects

What Are Large Objects?

PostgreSQL’s large object (LO) interface stores binary data, documents, images, PDFs, arbitrary binary payloads, using a dedicated API (lo_read, lo_write, lo_open, etc.) backed by the pg_largeobject system catalog. Unlike bytea columns, large objects can be streamed and seeked efficiently.

The Permission Gap

The predefined roles pg_read_all_data and pg_write_all_data were designed to grant broad data access without superuser privileges, handy for backup tools, data pipelines, and monitoring agents. Before PostgreSQL 19, these roles covered ordinary tables and sequences but not large objects, forcing administrators to either grant superuser or craft bespoke permission solutions for large-object access.

What Changed

PostgreSQL 19 extends pg_read_all_data and pg_write_all_data to include large-object access:

RoleLarge Object Capability
pg_read_all_dataSELECT / lo_read on all large objects
pg_write_all_dataUPDATE / lo_write / lo_truncate on all large objects
Why It Matters
  • Backup tools (pg_dump, custom scripts) no longer need superuser just to dump large objects.
  • Migration pipelines that read and replay data across clusters become simpler to permission safely.
  • Permission model consistency: the mental model of “pg_read_all_data means I can read all data” is now actually true.

pg_read_all_data and pg_write_all_data are very broad grants. Any user holding these roles can read or write every large object in every database they connect to, with no per-object restriction. Apply the principle of least privilege: prefer narrow, explicit grants for regular application users.

4. The oauth_ca_file libpq Parameter

When PostgreSQL clients authenticate using OAuth, libpq makes HTTP(S) requests to external authorization server endpoints (token introspection, device authorization, etc.). In environments that use a private or internal Certificate Authority, the system’s default trust store may not include the CA that signs the authorization server’s certificate — causing TLS verification failures for the OAuth flow even when the database connection itself is perfectly configured.

What Changed

PostgreSQL 19 adds a new libpq connection parameter:

oauth_ca_file=/etc/ssl/certs/company-ca.pem

This parameter tells libpq which CA file to use specifically for OAuth-related HTTPS connections, independently of the sslrootcert parameter used for the database TLS connection.

Why does it matter?

  • Custom trust configuration: Specify the CA file required by your environment.
  • Enterprise integration: Support OAuth deployments that use internally managed certificates.
  • Improved security control: Configure certificate validation explicitly for OAuth communication.

Key takeaway: The oauth_ca_file parameter provides more control over certificate trust when integrating PostgreSQL clients with OAuth providers.

5. Improved Custom OAuth Client Flows

PostgreSQL supports OAuth authentication flows that can be customized by client applications.

PostgreSQL 19 improves this area through an updated authentication-data hook type, PQAUTHDATA_OAUTH_BEARER_TOKEN_V2.

The updated interface can provide more detailed error information and allows libpq to precompute the OAuth issuer identifier.

Why does it matter?

Applications implementing custom OAuth authentication often need to handle token acquisition, issuer information, and authentication failures.

Improved error reporting helps developers diagnose problems, while precomputed issuer information can reduce duplicated work in custom implementations.

Benefits

  • More informative authentication failures.
  • Better support for custom OAuth client implementations.
  • Less duplicated work when handling issuer information.

Key takeaway: These improvements make custom OAuth authentication flows easier to integrate and troubleshoot.

Summary

FeatureWho Benefits Most
Server-side SNIDBAs managing multi-hostname or multi-tenant PostgreSQL deployments
Custom HBA options for OAuthTeams building or deploying OAuth validator modules
pg_read/write_all_data for LOsBackup engineers, migration pipeline developers, DBAs
oauth_ca_fileApplication developers integrating with OAuth providers that use custom CAs
PQAUTHDATA_OAUTH_BEARER_TOKEN_V2Developers writing custom OAuth token acquisition flows in libpq applications

These five features share a common theme: reducing the friction of running PostgreSQL securely in complex, modern infrastructure. Whether you’re juggling multiple TLS certificates, connecting through an enterprise SSO system, or building a backup solution that must respect access boundaries, PostgreSQL 19 gives you more precise tools to do it right.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top