RE: Unable to connect with password protected ssl key file - Mailing list pgadmin-support

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

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.abbas@enterprisedb.com>
Sent: Montag, 16. Mai 2022 10:37
To: Merkel, Christian <Christian.Merkel@allegion.com>
Cc: 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.Merkel@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