Cannot Connect with TLS Client Certificate

[seenickcode]

You can use this template for reporting bugs. Feel free to remove parts that are not relevant, or just write something free-form if you prefer.

What did you do?

1. I created a Postgres SSL server certificate, client public key and private key, each in .pem format.
2. I created a new Postico connection, setting "Use TLS Client Certificate". I specify my SSL server certificate .pem file.
3. I try to connect and receive an error: could not load private key file "<my server certificate>.pem": no start line

What did you expect to happen?

I expected to see a successful connection, as I can successfully connect via the command line:

psql "sslmode=verify-full sslrootcert=<ssl server certificate>.pem \
  sslcert=<ssl client cert.pem> sslkey=<ssl client key.pem> \
  hostaddr=<myip> \
  host=<myhost> \
  port=5432 \
  user=postgres dbname=<mydb>"

What actually happened?

Cannot connect.

What software versions are you using?

Postico version:
1.3.1 (2153)

macOS version:
10.13.2

PostgreSQL version:
9.6.6

I suspect this is because I need to specify also an SSL cert and key, right?

[jakob]

The "Use TLS Client Certificate..." setting is for CLIENT authentication, so you need to select the Client Key and Certificate instead of the server certificate. If you have a client pem file, it most likely contains the key and the cert, so a single file should suffice.

Verification of the server cert works a bit different in Postico: When the server cert is not in your keychain, Postico displays a warning, where you can click "show certificate". You can then verify that it is the expected cert (check the hash), and then click "always trust this cert when connecting to..."

Hope this helps!

[seenickcode]

Hey @jakob thanks for the reply.

Yeah that's right, I'd choose the client pem file instead (duh). But I have a separate cert and key .pem file. Would I have to combine them somehow? If I try either separately I get that "no start line" error again.

Thanks,

[jakob]

OK, so the way Postico does this is a bit unintuitive.

When that dialog opens, you can select one or two files (hold down command to select multiple files).

When a file has the extension ".pem", Postico assumes that file contains both the key and the cert, and uses that file for both (client cert + client key).

If a file ends with ".crt", it uses that file for the client cert.

If the file ends with ".key", Postico uses that file for the client key.

So in your case, it sounds like you'd need to rename your files to "ssl client cert.crt" and "ssl client key.key".

I thought that ".pem" files always include key+cert, it seems that I was wrong?

Apologies for the inconvenience, this is really something that I need to improve in Postico.

[seenickcode]

Hey Jakob,

Awesome. It works.

So Google (GCP) SQL provides the cert and key in the form of .pem files, therefore renaming them worked. I've seen .pem files have both but I'm not an expert on that regarding what the best practice is. At least GCP does it this way.

No worries. Love the product. I think getting this feature in would be cool because at least for me, I'm trying to move towards more security when connecting to my non-prod databases.

Thanks again!

Nick

On Tue, Feb 27, 2018 at 6:05 AM, Jakob Egger [email protected] wrote:

OK, so the way Postico does this is a bit unintuitive.

When that dialog opens, you can select one or two files (hold down command to select multiple files).

When a file has the extension ".pem", Postico assumes that file contains both the key and the cert, and uses that file for both (client cert + client key).

If a file ends with ".crt", it uses that file for the client cert.

If the file ends with ".key", Postico uses that file for the client key.

So in your case, it sounds like you'd need to rename your files to "ssl client cert.crt" and "ssl client key.key".

I thought that ".pem" files always include key+cert, it seems that I was wrong?

Apologies for the inconvenience, this is really something that I need to improve in Postico.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/jakob/Postico/issues/502#issuecomment-368835689, or mute the thread https://github.com/notifications/unsubscribe-auth/AAEoORAM2J6oPAF-J_XTaBZOc840PvZdks5tY-F0gaJpZM4SJst- .

[SPAHI4]

I agree with seenickcode, I have -key.pem and -cert.pem files in my project and it doesn't work. After changing extension it works fine.

[prabhatjha]

This thread was super helpful. I had to change the extension for it to work as well.

Minor suggestions -- may be add a section about renaming files when working with Postgres on GCP. I am happy to volunteer if you give me access to https://eggerapps.at/postico/docs/v1.0.7/security.html

[jakob]

We're improved handling of TLS certificates in Postico 2: There are now two fields to provide client certificate and key separately. It looks like this:

I hope that this makes connecting with certificates a lot more intuitive.

Postico 2 isn't released yet, but you can download a preview here: https://eggerapps.at/postico2/

[hegelstad]

Looking forward to see Version 2 :) I was quite confused, so this will be a welcome improvement!

[witsch]

Hi Jakob,

We're improved handling of TLS certificates in Postico 2: There are now two fields to provide client certificate and key separately. It looks like this:

Would it be possible to include the certificate and key paths in the JSON properties? We're running quite a number of databases and keep their credentials in Vault, from which it's easy to export the needed properties. However, after importing a .pgfav file one still has to activate TLS manually, including finding and selecting all the necessary files. It'd be great to be able to specify their location so getting access to a database comes down to one click (or a double-click at the most 🙂)…

Alternatively, it'd also be awesome to add support for PostgreSQL's connection service file. At the moment I can put service=foo as the database name, but that'll complain about the missing .pg_service.conf (and copying it into ~/Library/Containers/at.eggerapps.Postico/Data unfortunately doesn't do the trick).

Best wishes and otherwise many thanks for a great tool! 👍

[jakob]

@witsch Unfortunately Postico can't access arbitrary paths due to being sandboxed. That's why it creates bookmarks for file references.

Regarding TLS certificates: Do your users each have their own TLS certs & keys, or does every user use a shared cert & key to connect to the server?

If it's the latter, you could try creating a shared Postico library. You can create one in preferences. Shared libraries can have a master password for syncing encrypted credentials (SSH keys, TLS keys, passwords).

[grzesir]

This is super helpful! Thanks for helping with this, I was stuck trying to figure out how to connect Postico to GCP for HOURS yesterday.