Date: Mon, 22 Jun 2026 07:56:39 +0200
From: Matthias Apitz <guru@unixarea.de>
To: pgsql-general@lists.postgresql.org
Subject: PostgreSQL server with SSL
Message-ID: <ajjOl3NzXlWyNJFG@c720-1400094>
Reply-To: Matthias Apitz <guru@unixarea.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ajjOl3NzXlWyNJFG%40c720-1400094>
Precedence: bulk


Hello,


I've enabled SSL in the connection to the PostgreSQL server (16.5).
All details see below. The SSL connection works fine from a remote
host, for example from my MacBook, but does not work on the host
itself via interface 'lo' where it gives the error message:

    FATAL:  no PostgreSQL user name specified in startup packet
    connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port 5432 failed: FATAL:  no PostgreSQL user name specified in startup packet

and psql crashes. Interesting observation with tcpdump is, stat the
above error message is sent in clear over the network.

The same picture is with all C- or Java-written software using an ESQL/C
or JDBC interface.

Any idea on this?

Here are the details


# su - postgres
$ mkdir ca
$ cd ca
$ export LD_LIBRARY_PATH=/usr/local/sisis-pap/lib
$ export OPENSSL=/usr/local/sisis-pap/bin/openssl
$ $OPENSSL version  # just for testing
export OPENSSL_CONFIG='-config /usr/local/sisis-pap/openssl.cnf'
$ /usr/local/sisis-pap/misc/CA.pl -newca
...
$ /usr/local/sisis-pap/misc/CA.pl -newreq
...
$ ls -l newreq.pem newkey.pem
-rw------- 1 postgres postgres 1886 16. Jun 12:40 newkey.pem
-rw-r--r-- 1 postgres postgres 1090 16. Jun 12:42 newreq.pem
$ /usr/local/sisis-pap/misc/CA.pl -sign
...

$ mv newcert.pem pg-server.crt
$ mv newkey.pem pg-server.key

we must remove the passphrase from the key for PostgreSQL to be able to read
and start the PostgreSQL server without user interaction:

$ $OPENSSL rsa -in pg-server.key -out pg-passless-server.key
Enter pass phrase for pg-server.key:
writing RSA key

Enabling SSL in postgresql.conf:

$ vim /data/postgresql165/data/postgresql.conf

# - SSL -

ssl = on
ssl_cert_file = '/home/postgres/ca/pg-server.crt'
ssl_key_file = '/home/postgres/ca/pg-passless-server.key'
ssl_ca_file = '/home/postgres/ca/demoCA/cacert.pem'

$ vim /data/postgresql165/data/pg_hba.conf
changed 'host' to 'hostssl' for the relevant lines

Start of the server:

# /etc/init.d/postgres165 start

Connect from my MacBook to the remote host srap48dxr1.dev.xxxx.org:

$ export PGHOST=srap48dxr1.dev.xxxx.org
$ export PGPORT=5432

$ psql -Usisis sisis
Password for user sisis:
psql (14.15 (Homebrew), server 16.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

sisis=>


Connect on the host itself:

$ export PGHOST=srap48dxr1.dev.xxxx.org
$ export PGPORT=5432

$ /usr/local/sisis-pap/pgsql-16.5/bin/psql -Usisis
psql: Fehler: connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port 5432 failed: FATAL:  no PostgreSQL user name specified in startup packet
connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port 5432 failed: FATAL:  no PostgreSQL user name specified in startup packet
free(): invalid pointer
Abgebrochen (Speicherabzug geschrieben)

$ ldd /usr/local/sisis-pap/pgsql-16.5/bin/psql | egrep 'libssl|crypto'
	libssl.so.3 => /usr/local/sisis-pap/lib/libssl.so.3 (0x00007f9ea38cb000)
	libcrypto.so.3 => /usr/local/sisis-pap/lib/libcrypto.so.3 (0x00007f9ea3000000)

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub