Hopefully someone might be able to shed a light on this problem.

I've configured two servers, both of them have the same problem.

I've implemented SSL configuration, on PostgreSQL 11.18, I have created self-signed certificates, configured the postgresql.conf file with

ssl = on
ssl_ca_file = 'root.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

On the pg_hba.conf file I've added

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl    demo        user_test      md5 clientcert=verify-full

For the client, I've created certificates with the CN=user_test defined, and I can connect with

psql "sslmode=verify-full sslcert=/location/user.crt sslkey=/location/user.key 
sslrootcert=/location/root.crt host= dbname=demo user=user_test"

After I run the command on the client server, it asks for the password and it connects properly. I see the legend

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

and I can confirm I am connected on the desired server, as I can see the expected databases.

The problem is that I can also run on the client server

psql -d demo -U user_test -h

it will ask for the password, and it connects.

The desired behaviour is to only allow connection if the certificates are provided.

  • I'm making the connection between two Linux servers.
  • All of the entries with the same user or ip in the pg_hba.conf are registered with hostssl and md5 clientcert=verify-full
  • My server.crt and therefore my root.crt is defined with CN=
  • The server should still be able to accept other user connections without providing certificates.

Thanks a lot, I appreciate any help or hints on what might be wrong.

  • 1
    1. A rule that you add to pg_hba.conf is ignored when other rules above it do match. This is an IF...ELSIF...ELSIF cascade. It's not apparent what are the other rules in your file. 2. Set log_connections to on and consult the server log file to troubleshoot. Dec 15, 2022 at 22:58
  • The server should still be able to accept other user connections without providing certificates -- perhaps that's the source of your problems, if you added the clientcert=verify-full after those other lines. Also, what's the purpose of asking for the password when using the certificate-based authentication?
    – mustaccio
    Dec 15, 2022 at 23:52
  • @DanielVérité In my pg_hba.conf I have these lines at the begining # local for Unix domain socket connections only local all all trust local all postgres trust # IPv4 local connections: host all all md5 host all postgres password And several others for connections for other users and ips, not related to this client server and all of them without ssl connection. Dec 16, 2022 at 0:01
  • @DanielVérité following that line of thought, I added the line at the very beginning of the pg_hba.conf file, but I still get the same behaviour. Dec 16, 2022 at 0:09
  • 1
    You should hit the edit link on your question to add all the relevant information to it.
    – mustaccio
    Dec 16, 2022 at 1:02


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.