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

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

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