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> 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>
> *Sent:* Montag, 16. Mai 2022 10:37
> *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
>
>
>
> *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> 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
>

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