Hi everyone,
I'm completely new to postgresql and I'm struggling with its SSL configuration.
Ubuntu 22.04 LTS
Postgresql 17.5-1.pgdg22.04+1
postgresql-17-citus-13.0 13.0.4.citus-1
The certificate chain has 4 certificates, 1 root, 1 intermediate signed by the root certificate, a second intermediate signed by the first one and a server certificate signed bt the second intermediate certificate. I'll call it server.
I also have a second server certificate also signed by the second intermediate certificate. I'll call it server2.
Postgresql.conf:
port = 9700
max_connections = 100
ssl = on
ssl_ca_file = /data/db/root.crt
ssl_cert_file = /data/db/server.pem # server + intermediate 2 + intermediate 1
ssl_crl_file = /usr/local/share/OCIO_CA6.pem
ssl_key_file = /data/db/server.key
...
shared_preload_libraries = 'citus'
pg_hba.conf:
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
hostssl all ccid all cert map=rafe
pg_ident.conf:
rafe server2 ccid
On the second server:
vbinet@server2:~$ psql "port=9700 host=server user=ccid sslcert=~/.postgresql/server2.pem sslkey=~/.postgresql/server2.key sslrootcert=~/.postgresql/root.crt sslmode=verify_ca"
psql: error: connection to "server" (ip address), port 9700 failed: FATAL: connection requires a valid certificate
server2.pem also includes the intermediate certificates. I tried with the root and the intermediate certificates together in root.pem and just the server certificate in server.crt / server2.crt but that fails with the same message.
Can anyone point me to what is wrong in my configuration?
Thank you,
Valère Binet
