El día martes, junio 23, 2026 a las 01:19:00 +0200, Joan Frey escribió: > 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
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) > > 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 > > > > > > -- Matthias Apitz, ✉ [email protected], http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
