In Part 1, we explored five PostgreSQL 19 improvements focused on security and connectivity, including server-side SNI support, extensible OAuth authentication options, large-object permissions, and enhancements to libpq’s OAuth capabilities.
In Part 2, we shift our focus to the client side of PostgreSQL, exploring five additional areas that matter to database administrators, application developers, and contributors working with PostgreSQL’s client libraries and connection protocols.
This installment examines how to make libpq connection configuration more flexible, how to investigate protocol compatibility, how OAuth debugging can help troubleshoot authentication issues, and how thread-locking APIs support multithreaded applications. We will also explore PostgreSQL’s restrictions on carriage return and line feed characters in database object names and their implications for compatibility.
PostgreSQL 19: Introducing the ‘servicefile’ Connection Option & psql: SERVICEFILE
Connection service files (pg_service.conf) are one of PostgreSQL’s most convenient features. They let you store host, port, database, user, and SSL options in organized INI-style sections so you can connect using service=sales_db.
However, before PostgreSQL 19, there was an annoying limitation: you could only choose which service file to load by setting the OS environment variable PGSERVICEFILE. It could never be specified directly inside a connection string or connection URI.
In PostgreSQL 19 (committed via 6b1c4d326b06 and 092f3c63efc6), libpq officially gains the servicefile connection parameter and a matching :SERVICEFILE variable in psql.
What Changed in PostgreSQL 19?
| Feature | PostgreSQL 18 & Earlier | PostgreSQL 19+ |
|---|---|---|
service in connection string | Supported (service=app1) | Supported (service=app1) |
servicefile in connection string | Not supported | Supported (servicefile=/path/to/file) |
| URI parameter | postgresql:///?service=app1 | postgresql:///?service=app1&servicefile=/path |
| Order of Precedence | Environment variable PGSERVICEFILE -> Default paths | Connection param servicefile -> PGSERVICEFILE -> Default paths |
psql Inspection Variable | :SERVICE | :SERVICE and :SERVICEFILE |
libpq C API | PQservice(conn) | PQservice(conn) and PQserviceFile(conn) |
Hands-On Test Cases: Testing PG18 vs PG19
Setup Custom Service Files
Create a temporary working directory on both the nodes :
$ mkdir -p /tmp/pg_servicesCreate /tmp/pg_services/prod.conf 18&19:
[prod_service]
host=localhost
port=<port#>
dbname=<db_bame>Create /tmp/pg_services/analytics.conf18&19:
[analytics_service]
host=localhost
port=<port#>
dbname=<db_bame>Test Case 1: Behavior on PostgreSQL 18 (The Old Limitation)
Let’s attempt to pass servicefile to the PostgreSQL 18 client:
[root@node1 ~]# sudo -iu postgres /usr/pgsql-18/bin/psql "service=prod_service servicefile=/tmp/pg_services/prod.conf"
psql: error: invalid connection option "servicefile"Explanation: PostgreSQL 18 libpq rejects servicefile because it is not recognized as a valid connection parameter keyword. You had to resort to PGSERVICEFILE=/tmp/pg_services/prod.conf psql service=prod_service.
Test Case 2: Using servicefile in PostgreSQL 19rc1
Run the exact same command using PostgreSQL 19’s psql:
[root@node2 ~]# sudo -iu postgres /usr/local/pgsql/bin/psql "service=prod_service servicefile=/tmp/pg_services/prod.conf"
psql (19rc1)
Type "help" for help.
postgres=#It immediately connects using the settings defined inside /tmp/pg_services/prod.conf without touching any environment variables.
Test Case 3: Verify the New psql Variable :SERVICEFILE
PostgreSQL 19 automatically populates both :SERVICE and :SERVICEFILE upon connection:
postgres=# \echo 'Active Service: ' :SERVICE
Active Service: prod_service
postgres=# \echo 'Service File Path: ' :SERVICEFILE
Service File Path: /tmp/pg_services/prod.confYou can even switch connections inside psql seamlessly:
postgres=# \c "service=analytics_service servicefile=/tmp/pg_services/analytics.conf"
You are now connected to database "postgres" as user "postgres".
postgres=# \echo 'New Service File: ' :SERVICEFILE
New Service File: /tmp/pg_services/analytics.confTest Case 4: PostgreSQL Connection URI Format
You can also use this in standard PostgreSQL connection URIs:
[root@node2 ~]# sudo -iu postgres /usr/local/pgsql/bin/psql -p 5419 "postgresql:///?service=prod_service&servicefile=%2Ftmp%2Fp
g_services%2Fprod.conf" -c "SELECT current_database(), inet_server_port();"
current_database | inet_server_port
------------------+------------------
postgres | 5419
(1 row)Test Case 5: Protection Against Nested Service Files
To avoid recursive loops, PostgreSQL 19 enforces that a service file cannot define service or servicefile options inside itself.
Let’s test this error protection. Add an invalid nested line to /tmp/pg_services/invalid.conf:
[root@node2 pg_services]# cat invalid.conf
[invalid_service]
servicefile=/tmp/pg_services/prod.conf
host=localhost
port=<port#>[root@node2 pg_services]# sudo -iu postgres /usr/local/pgsql/bin/psql -p 5419 "service=invalid_service servicefile=/tmp/pg_serv
ices/invalid.conf"
psql: error: nested "servicefile" specifications not supported in service file "/tmp/pg_services/invalid.conf", line 2PostgreSQL cleanly catches the nesting and aborts with a clear error message.
Understanding libpq Protocol GREASE in PostgreSQL 19: Testing the Ecosystem
When PostgreSQL modernizes its wire protocol, a silent obstacle always stands in the way: connection poolers, proxies, and middleware.
Many intermediate proxies (such as older versions of PgBouncer, custom cloud proxies, or firewalls) were hardcoded to expect wire protocol version 3.0. If a newer client requested version 3.1 or 3.2, instead of cleanly negotiating backward, these proxies would crash or abruptly terminate the connection with errors like:
FATAL: bad packet header: '000000710003270f'To break this chicken-and-egg deadlock during the PostgreSQL 19 development cycle, the community implemented a technique known as Protocol GREASE.
1. What is Protocol GREASE?
Borrowed from TLS (RFC 8701 – Generate Random Extensions and Sustain Extensibility), GREASE is a design principle where systems intentionally inject reserved, unknown protocol values to ensure that intermediaries don’t ossify around fixed versions.
In PostgreSQL 19:
- Reserved Grease Version: Protocol version
3.9999(0x0003270F). - Reserved Extension Parameter:
_pq_.test_protocol_negotiation. - Development Behavior: During master and early beta testing,
libpqdefaulted to requesting3.9999. Compliant servers negotiated down viaNegotiateProtocolVersion, while broken proxies failed visibly so maintainers could fix them before PG19 GA. - The Workaround Parameter: PostgreSQL introduced connection parameter
max_protocol_version(max_protocol_version=3.0), allowing users to bypass uncooperative intermediaries. - Release Candidate (19rc1 / GA): As planned, the automatic greasing default was disabled before the release candidate, leaving the stable protocol negotiation mechanism in place.
2. Hands-On Test Cases
Let’s test connection behavior across PostgreSQL 18 and PostgreSQL 19.
Environment
- Instance 1: PostgreSQL 18.6
- Instance 2: PostgreSQL 19rc1 (Port 5419)
Test Case 1: Check Connection & Protocol Version with PG19rc1 & 18.6
Connect to your PostgreSQL instance using the psql 18 client:
[postgres@node1 ~]$ psql -d postgres -p 5432 -c "SELECT version();"
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)Verify the active protocol version using PQfullProtocolVersion equivalent via SQL:
postgres=# SELECT inet_server_port(), current_setting('server_version');
inet_server_port | current_setting
------------------+-----------------
| 18.6
(1 row)Connect to your PostgreSQL 19rc1 instance using the psql 19 client:
[root@node2 ~]# sudo -iu postgres /usr/local/pgsql/bin/psql -d postgres -p 5419 -c "SELECT version();"
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 19rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)Verify the active protocol version using PQfullProtocolVersion equivalent via SQL:
postgres=# SELECT inet_server_port(), current_setting('server_version');
inet_server_port | current_setting
------------------+-----------------
| 19rc1
(1 row)Test Case 2: Inspecting and Controlling max_protocol_version
You can explicitly control the maximum protocol version libpq requests using connection strings or URI parameters:
A. Force Protocol 3.0 (Legacy compatibility mode)
If you ever encounter an intermediary proxy that does not support modern protocol versions, you can pin the protocol to 3.0:
[postgres@node2 ~]$ /usr/local/pgsql/bin/psql "host=localhost port=5419 dbname=postgres max_protocol_version=3.0" -c "SELECT 'Connected via Protocol 3.0' AS
status;"
status
----------------------------
Connected via Protocol 3.0
(1 row)
B. Request Future Protocol Version (Simulate negotiation or check unsupported behavior)
If you intentionally pass an unsupported version
[postgres@node2 ~]$ /usr/local/pgsql/bin/psql "host=localhost port=5419 dbname=postgres max_protocol_version=3.9999" -c "SELECT 'Negotiation Successful' AS status;"
psql: error: invalid max_protocol_version value: "3.9999"Test connecting from the new PostgreSQL 19 client to a PostgreSQL 18 server:
[root@node2 pg_services]# sudo -iu postgres /usr/local/pgsql/bin/psql -h 127.0.0.1 -p 5432 -d postgres -c "SELECT version();"
Password for user postgres:
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
(1 row)Verify that backward compatibility works out-of-the-box and that connections smoothly negotiate without requiring manual intervention.
PostgreSQL 19 OAuth Hardening: Custom CA Support & Granular PGOAUTHDEBUG
With modern identity platforms (such as Keycloak, Okta, Authentik, and Entra ID) powering database authentication, PostgreSQL’s frontend library (libpq) added native OAuth 2.0 device flow support via libpq-oauth.
However, early iterations had a significant friction point for DevOps, CI pipelines, and enterprise environments: specifying a custom Certificate Authority (CA) for the identity provider required turning on full PGOAUTHDEBUG=UNSAFE mode. This flooded console logs with sensitive token traces and internal curl headers.
In PostgreSQL 19, the community addressed this with two major improvements:
- Promoted
oauth_ca_file&PGOAUTHCAFILEto first-class production features. - Split
PGOAUTHDEBUGinto granular, safe vs. unsafe debug flags.
1. First-Class Custom CA Support: oauth_ca_file
In enterprise Kubernetes clusters (.svc.cluster.local), air-gapped corporate intranets, or local development environments, OAuth identity providers often use private enterprise CAs or self-signed certificates.
Before (PostgreSQL Early OAuth Builds):
To trust an internal CA, you were forced to enable debug mode:
[root@node2 ~]# export PGOAUTHDEBUG=UNSAFE
export PGOAUTHCAFILE=/etc/ssl/certs/internal-ca.pem
[root@node2 ~]# sudo -iu postgres /usr/local/pgsql/bin/psql \
-h localhost -p 5419 -d postgres
psql (19rc1)
Type "help" for help.
postgres=#The PostgreSQL 19 RC1 client accepted the connection parameter and established a database connection.
Important: This test verifies connection-option recognition. It does not prove that the CA certificate is valid or that OAuth TLS verification works, because the connection did not necessarily invoke OAuth authentication.
Option B: Environment Variable
The following were created during testing:
[postgres@node2 ~]$ export PGOAUTHCAFILE=/etc/ssl/certs/internal-ca.pem
[postgres@node2 ~]$ /usr/local/pgsql/bin/psql -h localhost -p 5419 -d postgres
psql (19rc1)
Type "help" for help.
postgres=#1. Generating a Valid CA Certificate for Testing
Create a genuine test CA certificate file in /tmp/internal-ca.pem:
openssl req -new -x509 -days 365 -nodes \
-out /tmp/internal-ca.pem \
-keyout /tmp/internal-ca.key \
-subj "/CN=Internal Test CA"Verify it:
openssl x509 -in /tmp/internal-ca.pem -noout -subject -dates subject=CN = Internal Test CA notBefore=Oct 30 09:25:00 2026 GMTnotAfter=Oct 30 09:25:00 2027 GMT
2. Testing oauth_ca_file Connection Parameter in PostgreSQL 19
Connect using /usr/local/pgsql/bin/psql with user postgres (or as root with -U postgres):
/usr/local/pgsql/bin/psql "host=localhost port=5419 dbname=postgres user=postgres oauth_ca_file=/tmp/internal-ca.pem" -c "SELECT 'oauth_ca_file accepted successfully' AS status;"
status
--------------------------------------
oauth_ca_file accepted successfully
(1 row)
(Notice that unlike PostgreSQL 18, PostgreSQL 19 cleanly accepts the oauth_ca_file parameter without erroring out)
3. Comparing with PostgreSQL 18 (Negative Test)
Run the exact same command against the PostgreSQL 18 client (/usr/bin/psql):
/usr/pgsql-18/bin/psql "host=localhost port=5432 dbname=postgres user=postgres oauth_ca_file=/tmp/internal-ca.pem"
psql: error: invalid connection option "oauth_ca_file"4. Testing PGOAUTHCAFILE Environment Variable
Test setting PGOAUTHCAFILE as an environment variable:
sudo -iu postgres bash -c '
export PGOAUTHCAFILE=/tmp/internal-ca.pem;
/usr/local/pgsql/bin/psql -h localhost -p 5419 -d postgres -c "SELECT current_database(), current_user;"
'
current_database | current_user
------------------+--------------
postgres | postgres
(1 row)
5. Testing Safe and Granular PGOAUTHDEBUG Flags
A. Safe Diagnostic Flags (plugin-errors, call-count)
Safe diagnostic options can be enabled directly without requiring unsafe escalation:
sudo -iu postgres bash -c '
export PGOAUTHDEBUG=plugin-errors,call-count;
/usr/local/pgsql/bin/psql -h localhost -p 5419 -d postgres -c "SELECT 1 AS safe_debug_test;"
'
safe_debug_test
-----------------
1
(1 row)B. Unsafe Options (UNSAFE:http)
When testing against an unencrypted local mock OAuth provider (HTTP instead of HTTPS), pass the explicit UNSAFE: prefix:
sudo -iu postgres bash -c '
export PGOAUTHDEBUG=UNSAFE:http;
/usr/local/pgsql/bin/psql -h localhost -p 5419 -d postgres -c "SELECT 1 AS unsafe_http_test;"
'
unsafe_http_test
------------------
1
(1 row)C. Legacy Mode (PGOAUTHDEBUG=UNSAFE)
Enables full legacy debugging across all subsystems:
sudo -iu postgres bash -c '
export PGOAUTHDEBUG=UNSAFE;
export PGOAUTHCAFILE=/tmp/internal-ca.pem;
/usr/local/pgsql/bin/psql -h localhost -p 5419 -d postgres -c "SELECT 1 AS legacy_unsafe_test;"
'
legacy_unsafe_test
--------------------
1
(1 row)
PostgreSQL 19 Security Hardening: Banning Newlines & Carriage Returns in Database and Role Names
PostgreSQL has always been generous with identifiers enclosed in double quotes. In earlier versions, you could legally create a database or role whose name spanned multiple lines:
postgres=# CREATE DATABASE "sales_prod
backup";
ERROR: database name "sales_prod
backup" contains a newline or carriage return characterIn PostgreSQL 19, the community resolved this long-standing flaw by strictly forbidding line feed (\n) and carriage return (\r) characters across databases, roles, and tablespaces, while adding proactive detection in pg_upgrade --check.
1. Why Were Newlines a Problem?
Under the hood, utility tools like pg_dumpall construct shell commands to invoke pg_dump for each database.
Following a security fix in 2016 (CVE-2016-5424), PostgreSQL hardened libpq and client tools so that shell-escaped command strings reject \r or \n to prevent shell injection attacks on Windows and Unix systems.
This created an architectural inconsistency:
- The server permitted creating database and role names with
\nand\r. - The client utilities (
pg_dumpall,pg_upgrade) refused to process them, halting full cluster backups.
2. What Changes in PostgreSQL 19?
| Operation | PostgreSQL 18 & Earlier | PostgreSQL 19+ |
|---|---|---|
CREATE DATABASE "name\nwith\nnewline" | Allowed | Rejected with ERROR |
CREATE ROLE "user\nwith\nnewline" | Allowed | Rejected with ERROR |
CREATE TABLESPACE "ts\nwith\nnewline" | Allowed | Rejected with ERROR |
ALTER ... RENAME TO "name\nwith\nnewline" | Allowed | Rejected with ERROR |
pg_dumpall Backup Behavior | Fails and aborts mid-dump | Consistently succeeds (names guaranteed clean) |
pg_upgrade --check Validation | Silently passes, but upgrade crashes later | Explicitly detects & lists offending names |
3. Hands-On Test Cases (PG 18 vs PG 19)
Let’s test this behavior across PostgreSQL 18.6 and PostgreSQL 19rc1.
Test Case 1: Behavior in PostgreSQL 18.6 (Allowed, but breaks tools)
Connect to PostgreSQL 18.6:
postgres=# CREATE DATABASE "test_db
multi_line";
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
test_db +| postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
multi_line | | | | | | | |
(4 rows)
Now try running pg_dumpall against this cluster:
[root@node2 ~]# sudo -iu postgres /usr/pgsql-18/bin/pg_dumpall -p 5432 > /tmp/backup.sql
shell command argument contains a newline or carriage return: "port=5432 dbname='test_db
multi_line'"The backup fails completely
Test Case 2: Attempting Creation in PostgreSQL 19rc1 (Strictly Blocked)
Connect to PostgreSQL 19rc1:
postgres=# CREATE DATABASE "test_db
multi_line";
ERROR: database name "test_db
multi_line" contains a newline or carriage return character
postgres=#B. Attempting CREATE ROLE with a newline:
postgres=# CREATE ROLE "bad_user
name" LOGIN;
ERROR: role name "bad_user
name" contains a newline or carriage return characterC. Attempting ALTER DATABASE … RENAME:
postgres=# CREATE DATABASE test_clean;
CREATE DATABASE
postgres=# ALTER DATABASE test_clean RENAME TO "test_clean
newline";
ERROR: database name "test_clean
newline" contains a newline or carriage return characterTest Case 3: Upgrading Older Clusters (pg_upgrade –check)
If you are upgrading from an older PostgreSQL version that already contains a database or role with newlines, PostgreSQL 19’s pg_upgrade –check now proactively intercepts it:
[postgres@node2 ~]$ /usr/local/pgsql/bin/pg_upgrade --check \
--old-datadir=/var/lib/pgsql/18/data \
--new-datadir=/var/lib/pgsql/19rc1/data \
--old-bindir=/usr/pgsql-18/bin \
--new-bindir=/usr/local/pgsql/bin
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking for unsupported encodings ok
Checking names of databases, roles, and tablespaces fatal
Your installation contains databases, roles, or tablespaces with names
with invalid characters (newline or carriage return). To fix this,
rename these objects.
A list of all objects with invalid names is in the file:
/var/lib/pgsql/19rc1/data/pg_upgrade_output.d/20261030T180311.436/db_role_tablespace_invalid_names.txt
Failure, exitingSummary:
PostgreSQL 19 brings essential quality-of-life enhancements, enterprise-grade authentication refinements, and critical security hardening to libpq and client tooling:
- Flexible Service Configuration (
servicefile&:SERVICEFILE): Developers and operators are no longer forced to set OS-levelPGSERVICEFILEenvironment variables. Custom service definition files can now be passed directly inside connection strings and URIs, with runtime visibility provided viapsql’s:SERVICEFILEvariable. - Ecosystem Resilience (Protocol GREASE): The community intentionally injected reserved protocol numbers (
3.9999) during early development to surface interoperability issues in third-party proxies and poolers, ensuring broad compatibility with protocol negotiation ahead of the general release. - Enterprise OAuth Hardening:
libpq-oauthnow supportsoauth_ca_fileandPGOAUTHCAFILEwithout requiring noisy debug modes, making private CA verification seamless in Kubernetes and air-gapped environments. Furthermore,PGOAUTHDEBUGhas been decomposed into granular safe diagnostic flags (plugin-errors,call-count) and strictly guarded unsafe flags (UNSAFE:http,UNSAFE:trace). - Identifier Sanitization & Safe Upgrades: ASCII newline (
\n) and carriage return (\r) characters are now strictly prohibited in names of databases, roles, and tablespaces. This closes a long-standing vulnerability where multiline object names haltedpg_dumpallbackups, whilepg_upgrade --checknow proactively identifies offending identifiers before migrations begin.
Together, these changes make PostgreSQL 19 client connections more configurable, resilient, and enterprise-ready.