Can we authenticate to PostgreSQL with TLS cert AND password for the same connection?

Question

I want to configure my PostgreSQL server to enable authentication with TLS certificate and password at the same time (for the same connection, not just enable the two authentication mechanisms).

Then for a client to authenticate, it must :

• provide a valid certificate with the username set in the common name and signed by a trusted root ca
• provide a password for this specific user

I want to do this to improve safety. This way, even if my client certificate or my root CA are leaked, you still need a password to authenticate.

I can't find out how to do that. In this page of the PostgreSQL documentation, it is said that when you use TLS certificate :

No password prompt will be sent to the client

Then is it even possible to do this and if yes, how ?

Answer 1

You can achieve that by using a hostssl lin in pg_hba.conf that has scram-sha-256 as authentication method with the additional option clientcert=verify-full. For example:

hostssl mydb myuser all scram-sha-256 clientcert=verify-full

Then PostgreSQL will insist on a valid client certificate in addition to asking for a password.

See the documentation:

In addition to the method-specific options listed below, there is a method-independent authentication option clientcert, which can be specified in any hostssl record. This option can be set to verify-ca or verify-full. Both options require the client to present a valid (trusted) SSL certificate, while verify-full additionally enforces that the cn (Common Name) in the certificate matches the username or an applicable mapping. This behavior is similar to the cert authentication method (see Section 20.12) but enables pairing the verification of client certificates with any authentication method that supports hostssl entries.