Subject: PostgreSQL server with SSL

From: Matthias Apitz <[email-masked]>
Date: Mon, 22 Jun 2026 07:56:39 +0200

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, ✉ [email-masked], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

---

From: Matthias Apitz <[email-masked]>
Date: Tue, 23 Jun 2026 12:30:59 +0200

I have generated new SSL keys exactly as documented in
https://www.postgresql.org/docs/15/ssl-tcp.html

# su - postgres
$ mkdir canew
$ cd canew
$ export PATH=/usr/local/sisis-pap/bin:$PATH
$ export LD_LIBRARY_PATH=/usr/local/sisis-pap/lib
$ openssl -v
OpenSSL 3.5.7 9 Jun 2026 (Library: OpenSSL 3.5.7 9 Jun 2026)

$ openssl req -new -x509 -days 365 -nodes -text -out server.crt   -keyout server.key -subj "/CN=srap48dxr1.dev.xxxx.org"
$ chmod og-rwx server.key

$ openssl req -new -nodes -text -out root.csr   -keyout root.key -subj "/CN=root.dev.xxxx.org"
$ chmod og-rwx root.key

$ openssl x509 -req -in root.csr -text -days 3650 -extfile /usr/local/sisis-pap/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
$ openssl req -new -nodes -text -out server.csr   -keyout server.key -subj "/CN=srap48dxr1.dev.xxxx.org"
$ chmod og-rwx server.key
$ openssl x509 -req -in server.csr -text -days 365   -CA root.crt -CAkey root.key -CAcreateserial   -out server.crt

$ ls -l
insgesamt 36
-rw-r--r-- 1 postgres postgres 4168 23. Jun 11:27 root.crt
-rw-r--r-- 1 postgres postgres 3377 23. Jun 11:24 root.csr
-rw------- 1 postgres postgres 1704 23. Jun 11:24 root.key
-rw-r--r-- 1 postgres postgres   41 23. Jun 11:28 root.srl
-rw-r--r-- 1 postgres postgres 4087 23. Jun 11:28 server.crt
-rw-r--r-- 1 postgres postgres 3391 23. Jun 11:28 server.csr
-rw------- 1 postgres postgres 1704 23. Jun 11:28 server.key

In postgresql.conf the SSL section is now:

# - SSL -
#
ssl = on
ssl_cert_file = '/home/postgres/canew/server.crt'
ssl_key_file = '/home/postgres/canew/server.key'

and in pg_hba.conf the matching entry for the IP addr of my Mac is:

hostssl    all             all             10.49.210.27/32         password
host       all             all             10.49.210.27/32         password

With the line for 'host' the connect with the psql falls back to non-SSL.

$ psql -Usisis
Password for user sisis:
psql (14.15 (Homebrew), server 15.1)
Type "help" for help.

sisis=#

When I have only the 'hostssl' line for the IP addr 10.49.210.27 it says

psql -Usisis
psql: error: connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port 2345 failed: SSL SYSCALL error: EOF detected
connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port 2345 failed: FATAL:  no pg_hba.conf entry for host "10.49.210.27", user "sisis", database "sisis", no encryption

How could I enable more logging about the SSL session problem?
Thanks

	matthias

---

From: Joan Frey <[email-masked]>
Date: Tue, 23 Jun 2026 13:19:00 +0200

Hi Matthias,

*How could I enable more logging about the SSL session problem?*

You can edit the following parameters in postgresql.conf:
log_connections = on
log_min_messages =  [debug5, debug4, debug3, debug2, debug1, info, notice,
warning, error, log, fatal, panic]

Reload postgres and then check the postgresql logs

You can also force sslmode when you connect using
psql "host=... port=5432  user=sisis dbname=sisis sslmode=require"

Cheers,
Joan

Le mar. 23 juin 2026 à 12:31, Matthias Apitz <[email-masked]> a écrit :

> $ openssl req -new -x509 -days 365 -nodes -text -out server.crt   -keyout
> server.key -subj "/CN=srap48dxr1.dev.xxxx.org"
> $ openssl x509 -req -in root.csr -text -days 3650 -extfile
> /usr/local/sisis-pap/openssl.cnf -extensions v3_ca -signkey root.key -out
> root.crt
> psql: error: connection to server at "srap48dxr1.dev.xxxx.org"
> (10.23.33.57), port 2345 failed: SSL SYSCALL error: EOF detected
> connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57), port
> 2345 failed: FATAL:  no pg_hba.conf entry for host "10.49.210.27", user
> "sisis", database "sisis", no encryption
>
> >     connection to server at "srap48dxr1.dev.xxxx.org" (10.23.33.57),
> port 5432 failed: FATAL:  no PostgreSQL user name specified in startup
> packet
> >
> > 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

---

From: Matthias Apitz <[email-masked]>
Date: Tue, 23 Jun 2026 14:00:08 +0200

> Reload postgres and then check the postgresql logs

Thanks,

With

log_connections = on
log_min_messages =  debug5

I see in the log file the following messages (without a hint, why it
fails to accept SSL):

2026-06-23 13:41:08.704 CEST [31292] DEBUG:  forked new backend, pid=994 socket=9
2026-06-23 13:41:08.704 CEST [994] LOG:  connection received: host=10.49.210.27 port=50775
2026-06-23 13:41:08.777 CEST [994] DEBUG:  SSL: handshake start: "before SSL initialization"
2026-06-23 13:41:08.777 CEST [994] DEBUG:  SSL: accept loop: "before SSL initialization"
2026-06-23 13:41:08.777 CEST [994] DEBUG:  SSL: accept exit (-1): "before SSL initialization"
2026-06-23 13:41:08.777 CEST [994] LOG:  could not accept SSL connection: Socket operation on non-socket
2026-06-23 13:41:08.777 CEST [994] DEBUG:  SSL connection from DN:"(anonymous)" CN:"(anonymous)"
2026-06-23 13:41:08.777 CEST [994] DEBUG:  shmem_exit(0): 0 before_shmem_exit callbacks to make
2026-06-23 13:41:08.777 CEST [994] DEBUG:  shmem_exit(0): 0 on_shmem_exit callbacks to make
2026-06-23 13:41:08.777 CEST [994] DEBUG:  proc_exit(0): 1 callbacks to make
2026-06-23 13:41:08.777 CEST [994] DEBUG:  exit(0)

---

From: Joan Frey <[email-masked]>
Date: Tue, 23 Jun 2026 14:25:00 +0200

I could only find this error message related to an error with the openssl
version.
This issue was apparently patched with pg15.6 (
https://www.postgresql.org/docs/release/15.6/)

I suggest that you update to the latest minor version and retry.

Le mar. 23 juin 2026 à 14:00, Matthias Apitz <[email-masked]> a écrit :

> > > $ openssl req -new -nodes -text -out server.csr   -keyout server.key
> -subj
> > > "/CN=srap48dxr1.dev.xxxx.org"
> > > $ openssl x509 -req -in server.csr -text -days 365   -CA root.crt
> -CAkey
> > > root.key -CAcreateserial   -out server.crt
> > >
> > > $ ls -l
> > > insgesamt 36
> > > > we must remove the passphrase from the key for PostgreSQL to be able
> to
> > > read
> > > > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> bits:
> > > 256, compression: off)
> > > > Type "help" for help.
> > > >
> > > > sisis=>
> > > >
> > > >
> port

---

From: Matthias Apitz <[email-masked]>
Date: Wed, 24 Jun 2026 12:48:38 +0200

> I suggest that you update to the latest minor version and retry.

Thanks for your helping hand, Joan.

I've setup in a podman container a 18.3 PostgreSQL cluster and with this
SSL works locally and via network. Details are below for documentation
purpose.

Thanks again!

	matthias

SSL version:

c169ecb37a76:# /usr/local/sisis-pap/bin/openssl -v
OpenSSL 3.5.7 9 Jun 2026 (Library: OpenSSL 3.5.7 9 Jun 2026)

/data/postgresql183/data/postgresql.conf

ssl = on

/data/postgresql183/data/pg_hba.conf

# IPv4 local connections:
hostssl    all             all             127.0.0.1/32         password
hostssl    all             all             10.0.2.100/32        password

podman container 'localhost':

c169ecb37a76:# export PGHOST=localhost
c169ecb37a76:# export PGPORT=5432
c169ecb37a76:# /usr/local/sisis-pap/pgsql-18.3/bin/psql -U sisis
Password for user sisis:
psql (18.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

sisis=#

via network from my MacBook:

$ export PGHOST=srap57dxr1.dev.xxxx.org
$ export PGPORT=61443
$ psql -U sisis
Password for user sisis:
psql (14.15 (Homebrew), server 18.3)
WARNING: psql major version 14, server major version 18.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

sisis=#

The port 61443 sounds unusual, but has todo with the network connectos
exposed by the podman container.

---

From: Joan Frey <[email-masked]>
Date: Wed, 24 Jun 2026 14:09:48 +0200

👍

Joan Frey a réagi depuis Gmail
<https://www.google.com/gmail/about/?utm_source=gmail-in-product&utm_medium=et&utm_campaign=emojireactionemail#app>

Le mer. 24 juin 2026 à 12:48, Matthias Apitz <[email-masked]> a écrit :

> compression: off, ALPN: postgresql)
> Type "help" for help.
>
> sisis=#
>