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.ab...@enterprisedb.com> Sent: Montag, 16. Mai 2022 12:03 To: Merkel, Christian <christian.mer...@allegion.com> Cc: pgadmin-support@lists.postgresql.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.mer...@allegion.com<mailto:christian.mer...@allegion.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.ab...@enterprisedb.com<mailto:fahar.ab...@enterprisedb.com>> Sent: Montag, 16. Mai 2022 10:37 To: Merkel, Christian <christian.mer...@allegion.com<mailto:christian.mer...@allegion.com>> Cc: pgadmin-support@lists.postgresql.org<mailto:pgadmin-support@lists.postgresql.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@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.mer...@allegion.com<mailto:christian.mer...@allegion.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>
