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
El día lunes, junio 22, 2026 a las 07:56:39 +0200, Matthias Apitz escribió:
>
>
> 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 protected], http://www.unixarea.de/ +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
>
--
Matthias Apitz, ✉ [email protected], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
