How to connect to amazon server using .pem key file

[Armour]

Hi all, I have a identify file .pem which is used to ssh to my AWS, I try to use the Postico ssh config in different ways to connect to AWS but they all failed, so now I'm wondering if there is someone who know how to config this part to make it work.
Thanks in advance :P

[jakob]

Hi!

.pem files usually contain keys and/or certificates used by TLS (SSL), so it sounds like you are trying to connect to PostgreSQL using TLS, not SSH.

Postico supports authentication using client TLS certificates. However, Postico expects two files: a .key file containing your private key, and a .crt file containing the corresponding client certificate.

Try opening the .pem file in a text editor, and look at the contents. You should see both a key and a certificate. Copy the key part in a file with the extension .key, and the certificate in a file with the extension .crt.

Then, when creating a favorite in Postico, click on the Options button and select "Use TLS client certificate..."

Hope this helps! I really wish this was easier to set up; I'll have to see if I can make Postico accept .pem files directly.

[jakob]

I found out that libpq can also use .pem files directly. So I've changed Postico to let you use PEM files for TLS certificates.

Here are the simpler instructions:

1. Download the latest nightly build from here: https://eggerapps-downloads.s3-eu-west-1.amazonaws.com/postico-1475.zip
2. Create a new favorite, enter all the connection parameters (Tip: to avoid all the typing, copy the jdbc URL from the AWS console, then click "New Favorite" and Postico will take all the params from the URL in the clipboard)
3. Then click the options button, select "Use TLS certificate...", and then just select the .pem file.

[Armour]

Thanks! Hope we can put the .pem support for TLS on next release :P
Love Postico! Cheers!

[jakob]

.pem support is now in Postico 1.0.9, released earlier today (should hit the app store soon)

[joshmosh]

I'm not able to use a .pem file when trying to connect with Postico version 1.1.2. I attached a screenshot of the error I'm seeing.

[jakob]

It sounds like the pem file you are using doesn’t include a key. Open the file in a text editor — if there is a key it should start with the line -----BEGIN RSA PRIVATE KEY-----

Make sure to set OpenSSL flags correctly when exporting pem files, the default settings might not export the key.

[joshmosh]

@jakob Thanks for the response. I'm afraid I don't know how to grab the pem file with that information. The pem file I'm using is directly from Amazon for use with RDS. I'm grabbing the cert from here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

[jakob]

@joshmosh ok, I see. The .pem file you are using contains certificates only. It doesn't contain a key. So you don't use it for client authentication, but you use it for verification of the server instead.

Postico uses the macOS keychain for verifying SSL certs, so you should add the cert to the keychain instead. Then, when connecting in Postico, you'll get a dialog that tells you the server cert is not trusted. Check if the cert is the one you added, then check the "Always trust this cert" checkbox.

[joshmosh]

@jakob Perfect. Adding it to my keychain worked great! Thank you so much for the help!