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 protected]> a écrit : > 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 > > >
