Documentation for connecting to a postgresql instance with ssl enabled and intermediate certificates present: In some cases, the client certificate might be signed by an 'intermediate' certificate authority, rather than one that is directly trusted by the server. How to specify a client certificate to psql? Ask Question 8. Browse other questions tagged ssl-certificate postgresql psql or ask your own question. Configuring the Client. There are a number of connection parameters for configuring the client for SSL. See SSL Connection parameters. The simplest being ssl=true, passing this into the driver will cause the driver to validate both the SSL certificate and verify the hostname (same as verify-full).

• Postgresql Ssl Client Certificate Template
• Postgres Ssl Client Certificate
• Postgresql Ssl Client Certificate Authentication

Obviously, for this tutorial we’ll use an environment with PostgreSQL database inside - you can easily create such if you haven’t done this yet.

1. To start with, connect to your database server via Jelastic SSH Gate.

Tip: In case you haven’t performed similar operation before, you need to:

• generate an SSH keypair
• add your public SSH key to the dashboard
• access your account via SSH protocol

• server.key - private key
• server.crt - server certificate

• root.crt - trusted root certificate

Within this tutorial, we’ll briefly consider how you can generate them by yourselves.

Tips:

• we won’t explain commands parameters in details here, but if you’d like to know more, just refer to the Self-Signed Custom SSL page in our documentation or check the official OpenSSL site for the full list of available actions
• you can also use custom SSL certificate similarly to the described below (follow the Generate a Custom SSL Certificate section of the linked guide to get such). In this latter case, you can skip the generation instruction and jump directly to the 6th step of this instruction

So, navigate to the mentioned folder and proceed with steps below.

3. First of all, let’s create the first file - private key:

• execute the next commands:

  cd /var/lib/pgsql/data
  openssl genrsa -des3 -out server.key 1024

  During the server.key generation, you’ll be asked for a pass phrase - specify any and confirm it to finish creation.

• Now, in order to work with this key further, it’s required remove the pass phrase you’ve added previously. Execute the following command for this:

  Re-enter pass phrase one more time for confirmation.

• Set the appropriate permission and ownership rights for your private key file with the next commands:

  chmod 400 server.key
  chown postgres.postgres server.key

4. Now, you need to create server certificate based on your server.key file, e.g.:

Note: It’s required to set your personal data for subj parameter if the certificate is intended to be used in production:

Postgresql Ssl Client Certificate Template

5. Since we are going to sign certs by ourselves, the generated server certificate can be used as a trusted root certificate as well, so just make its copy with the appropriate name:

Now, as you have all three certificate files, you can proceed to PostgreSQL database configurations, required for SSL activation and usage.

6. Open the pg_hba.conf file, located in the same folder, for editing with any preferable terminal editor (vim for example) or directly via dashboard.

Replace its default content with the following lines:

host all all 127.0.0.1/32 trust

Postgres Ssl Client Certificate

Save the updated file.

7. To finish configurations, you need apply some more changes to the postgresql.conf file.

Navigate to its Security and Authentication section (approximately at the 80th line) and activate SSL usage itself, through uncommenting the same-named setting and changing its value to “on”. Also, add the new ssl_ca_file parameter below:

Don’t forget to save these changes.

8. Lastly, restart your PostgreSQL container in order to apply new settings:

I am trying to connect to my PostgreSQL server but psql is complaining that I don't have a valid client certificate. Here is how I create my certificates:

Self-signed server certificate:

Client certificate:

After copying the necessary files (client.crt, client.key, root.crt) onto the client machine and changing permission (i.e., chmod og-rwx client.key), I do the following:

and then I get:

Am I doing the client certificate signing process wrong?

Thanks,

#Edit

I tried:

and I get:

Using Wireshark, here is the capture I got for the communication between the client (192.168.0.103) and the server (192.168.0.100):

Do you know how to make sense of this?

#Edit 2

Okay, I did what you said, and it seems like the server does not send the CertificateRequest message to the client. as you can see below:

but this is weird because in pg_hba.conf, I have:

What do you think?

#Edit3 (SOLVED!)

I changed the pg_hba.conf to contain:

Postgresql Ssl Client Certificate Authentication

and changed postgresql.conf to add in the 'Security and Authentication' section:

AND IT WORKED! Thank you so much!

Cara membuka file excel yg lupa password. Kemudian copy-kan syntax VBA berikut ini ke dalam Module tersebut:Public Sub AllInternalPasswords' Breaks worksheet and workbook structure passwords.

1 Answer

In this situation I tend to pull out Wireshark and snoop the SSL negotiation to make sure the client certificate is really being offered by the client.

I suggest using openssl to verify the client->root signing link, too.

Edit: It's necessary to specify clientcert=1 even when cert authentication is chosen. Yes, that's weird.

Not the answer you're looking for? Browse other questions tagged postgresqlsslopensslpsql or ask your own question.