RE: Unable to connect with password protected ssl key file

I included a typo in one command, sorry:
./easyrsa gen-req pguser

This must be: ./easyrsa gen-req pguser nopass

So that the private key is not password protected, by default the tool is asking for a pw.

Best regards,
Christian

From: Fahar Abbas <fahar(dot)abbas(at)enterprisedb(dot)com>
Sent: Montag, 16. Mai 2022 12:03
To: Merkel, Christian <Christian(dot)Merkel(at)allegion(dot)com>
Cc: pgadmin-support(at)lists(dot)postgresql(dot)org
Subject: Re: Unable to connect with password protected ssl key file

Sure, I will try.

Thanks

On Mon, May 16, 2022 at 2:40 PM Merkel, Christian <Christian(dot)Merkel(at)allegion(dot)com<mailto:Christian(dot)Merkel(at)allegion(dot)com>> wrote:
Hi Fahar,

for a proper ssl connection (with verify-full) to a postgres server you need proper certificates.
Get a copy of easy-rsa from here: https://github.com/OpenVPN/easy-rsa

Execute:
./easyrsa init-pki
./easyrsa build-ca nopass
./easyrsa gen-req pguser
./easyrsa gen-req pguser-pw
(when prompted for Common Name enter pguser, without -pw!)

./easyrsa gen-req pgserver
(when prompted for Common Name enter the EXACT same hostname, which is used by your clients to connect)

./easyrsa sign-req client pguser
./easyrsa sign-req client pguser-pw
./easyrsa sign-req server pgserver

Now you have all certificates in a directory named pki, but you need to move them in place.

Edit your postgresql.conf of your server and set:
ssl = on
ssl_ca_file = 'must point to your pki/ca.crt'
ssl_cert_file = ' must point to your pki/issued/pgserver.crt'
ssl_key_file = ' must point to your pki/private/pgserver.key'

Edit and add to your pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all all cert

Open the properties of the connection in pgAdmin4 and set in the SSL tab:
SSL mode: Verify-Full
Client certificate: pki/issued/pgclient.crt
Client certificate key: pki/private/pgclient.key
Root certificate: pki/ca.crt

Make sure a user called pgclient EXISTS on your postgres server, then you should now be able to connect.

And now to the problem, change the following in your connection properties:
Client certificate: pki/issued/pgclient-pw.crt
Client certificate key: pki/private/pgclient-pw.key

And see how you won’t be able to connect to the server and that there is no prompt shown to enter the password for the pgclient-pw.key

Best regards,
Christian

From: Fahar Abbas <fahar(dot)abbas(at)enterprisedb(dot)com<mailto:fahar(dot)abbas(at)enterprisedb(dot)com>>
Sent: Montag, 16. Mai 2022 10:37
To: Merkel, Christian <Christian(dot)Merkel(at)allegion(dot)com<mailto:Christian(dot)Merkel(at)allegion(dot)com>>
Cc: pgadmin-support(at)lists(dot)postgresql(dot)org<mailto:pgadmin-support(at)lists(dot)postgresql(dot)org>
Subject: Re: Unable to connect with password protected ssl key file

EXTERNE MITTEILUNG: Seien sie vorsichtig mit Antworten, Links und Anhängen.
Hi Merkel,

Can you please share the exact steps to reproduce and screenshot?

I am getting the following error message on psql (command-line tool for PostgreSQL) for verify-full option:

psql.bin: root certificate file "/root/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

Can you please try your error message through psql
----
Steps
1.go into PostgreSQL binary path and execute this command

export PGSSLMODE=verify-full
2. no connect with psql
[root(at)localhost bin]# ./psql -U postgres -h localhost -p 5432 -d postgres
psql.bin: root certificate file "/root/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

If you are getting the same error message through psql then it is not an issue with pgadmin4.

On Wed, May 11, 2022 at 4:23 PM Merkel, Christian <Christian(dot)Merkel(at)allegion(dot)com<mailto:Christian(dot)Merkel(at)allegion(dot)com>> wrote:
Hello,

the pgadmin 6.8 software does support SSL mode Verify-Full on connect.
But how is it possible to use a password protected client certificate key file to connect? (without password works)

There is no prompt shown on connect nor could I find any other way to provide the password for the key.
The whole application gets stuck in “connecting” to server for forever, so it’s also kind of a bug here(?)

Best regards,
Christian Merkel

--
Fahar Abbas
pgAdmin4 team
EnterpriseDB Corporation
Mobile: +92-333-5409707
Skype ID: live:fahar.abbas
Website: www.enterprisedb.com<http://www.enterprisedb.com>

--
Fahar Abbas
pgAdmin4 team
EnterpriseDB Corporation
Mobile: +92-333-5409707
Skype ID: live:fahar.abbas
Website: www.enterprisedb.com<http://www.enterprisedb.com>