How do I configure PostgreSQL's pg_hba.conf to allow connections with a verified certificate bu-CodePudding

I've set up PostgreSQL (v.15) server on my local machine using the PostgreSQL Binaries.

And I've generated certificates using this link.

I've stored the certificates in c:/certs and have also copied them to the data folder of my local PostgreSQL server (the same one where the postgresql.conf file is located). And I've configured postgresql.conf to use SSL and the certificates, as shown on this image.

When I try to connect to the PostgreSQL server using psql, I want it to accept my connection attempt only if it supplies a valid certificate, such as in the format below:

psql 'host=localhost port=5432 dbname=local-db user=admin sslmode=verify-full sslcert=c:/certs/cert.pem sslkey=c:/certs/cert-key.pem sslrootcert=c:/certs/ca.pem'

But I want it to reject my connection attempt when I supply no certificate, such as in the following format:

psql 'host=localhost port=5432 dbname=local-db user=admin'

I assume that I need to configure the PostgreSQL server's pg_hba.conf file in a particular way (it's in the same folder as the certificates and the postgresql.conf file), and I've tried every suggestion I could find on the internet, but I keep ending up with the server either allowing both of the above connection attempts or neither of them, but never one and not the other.

I tried configuring pg_hba.conf as follows:

hostnossl   all   all   0.0.0.0/0   reject
hostssl     all   all   0.0.0.0/0   cert clientcert=verify-full

But then I get the following error for the connection that supplies a certificate:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  no pg_hba.conf entry for host "::1", user "admin", database "local-db", SSL encryption

And I get the following two errors for the connection that doesn't supply a certificate:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  no pg_hba.conf entry for host "::1", user "admin", database "local-db", SSL encryption

connection to server at "localhost" (::1), port 5432 failed: FATAL:  no pg_hba.conf entry for host "::1", user "admin", database "local-db", no encryption

I imagine the reason is that it's trying to connect through IPv6 instead of Ipv4. When I configure pg_hba.conf as follows:

hostnossl   all   all   ::/0   reject
hostssl     all   all   ::/0   cert clientcert=verify-full

Then it seems to block the connection that doesn't supply a certificate (as desired):

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  connection requires a valid client certificate

connection to server at "localhost" (::1), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "::1", user "admin", database "local-db", no encryption

But I also get the following error for the connection that does supply a certificate:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  certificate authentication failed for user "admin"

When I verify the certificate with the following CLI command: openssl verify -CAfile ca.pem -verbose cert.pem. Then it tells me that the certificate is "OK", so I don't know what could be wrong, but I assume it has to do with my pg_hba configuration (and/or my psql connection string).

I've tried changing clientcert=verify-full to clientcert=1, since I've seen multiple people recommend it online, but then my PostgreSQL server refuses to startup again (I restart it whenever I make changes to its .conf files) and registers the following two lines in its logfile:

FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

Please help me discover how to get a PostgreSQL server to accept only connections that supply a certificate.

CodePudding user response：

It seems that my certificate settings weren't set properly. I used this link to generate new certificates (and modified the CN [Common Name] values to match my hostname [localhost] and username [admin], as seen below):

openssl req -new -x509 -days 365 -nodes -out ca.crt -keyout ca.key -subj "/CN=root-ca"

openssl genrsa -des3 -out server.key 2048
openssl rsa -in server.key -out server.key
openssl req -new -nodes -key server.key -out server.csr -subj "/CN=localhost"
openssl x509 -req -in server.csr -days 365 -CA ca.crt -CAkey ca.key - CAcreateserial -out server.crt

openssl genrsa -des3 -out client.key 2048
openssl rsa -in client.key -out client.key
openssl req -new -nodes -key client.key -out client.csr -subj "/CN=admin"
openssl x509 -req -in client.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt

I then copied them to the aforementioned data folder and modified postgresql.conf as follows:

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

In pg_hba.conf, I only had the following two lines set at the bottom (the rest was commented out):

hostnossl   all   all   0.0.0.0/0   reject
hostnossl   all   all   ::/0        reject
hostssl     all   all   0.0.0.0/0   cert clientcert=verify-full
hostssl     all   all   ::/0        cert clientcert=verify-full

With this, it did what I wanted, which is that it blocked the connection that supplied no certificate while allowing the one that did supply it (as follows):

psql 'host=localhost port=5432 dbname=local-db user=admin sslmode=verify-full sslcert=c:/certs/client.crt sslkey=c:/certs/client.key sslrootcert=c:/certs/ca.crt'

Setting the Common Name correctly was the (most) deciding factor between whether a connection attempt was accepted or rejected.