0

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       10.155.5.100/32      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=10.5.1.57 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 10.5.1.57

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=10.5.1.57
  • 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.

8
  • 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 127.0.0.1/32 md5 host all postgres 127.0.0.1/32 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

0

Your Answer

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