.pgpass support

[chytreg]

Hi,

would be great if postico support to read credentials from ~/.pgpass http://www.postgresql.org/docs/9.4/static/libpq-pgpass.html I use it on daily basis and looking forward to see this in postico.

[jakob]

Storing passwords in clear text is very risky. I don't want to encourage unsafe practices in Postico.

If you store passwords in clear text, any software on your Mac can read them. This means that if a vulnerability in an application allows an attacker to read files in your home directory, they will also be able to read your stored passwords.

To avoid this, Postico stores passwords in the OS X keychain. They are stored in an encrypted form, and the security framework makes sure only authorised applications can read them.

For these reasons I'm not planning to add support for reading credentials from the .pgpass file.

[wilsonjackson]

@jakob I know this is a very old issue, but I have a use case I'd like you to consider. At my org, we use IAM authentication to connect to RDS PostgreSQL databases. IAM authentication allows engineers to generate short-lived, auto-expiring (15m) passwords. To streamline this process, we have tooling to write these credentials directly to pgpass, so most of the authentication process can be scripted. This works great for psql and DataGrip, but not so much for Postico. Having to manually copy and paste generated passwords on each connection is burdensome, and doesn't add any security value.

Hoping you can reconsider support in light of this, as a number of our users are Postico fans. pgpass can definitely be used in a secure way, and it's unfortunate that the tool prevents us from doing so.

[tasdflkjweio]

@wilsonjackson @jakob i came across for this issue for this exact reason. i bought postico but may have to switch away because it's too cumbersome now that we're using jwt's.

[jakob]

This does seem like a good reason to support .pgpass files. (I've also changed my mind in the recent years and no longer feel that I should try to enforce security best practices, because there often is a good reason to deviate from them)

So I looked into it, and found out that the only thing I need to do is add an entitlement that allows Postico to access ~/.pgpass.

I can't guarantee that Apple will allow that on the Mac App Store, but it will work with the direct download version of Postico.

Please download Postico Build 9641 or newer from this page and let me know it works!

[bdlindsay]

How is it expected to have a server entry setup in the app to use .pgpass instead of the placeholder defaults?

[cryptic-mystic]

@bdlindsay I've found success by just omitting the password field altogether. I'm still able to authenticate assuming .pgpass is populated.

Thanks for adding this support!

[ybryan]

I also got .pgpass but had to fill out the DB Name, Host name, and User name.

Is there a way to support username lookup with .pgpass with by just referencing the DB name rather than defaulting the username to OS user?

[jakob]

@ybryan Right now Postico doesn't parse the .pgpass file; libpq does. And as far as I understand, libpq only takes the password from the .pgpass file. Since this behavior is built into libpq, all the PostgreSQL tools behave in this way: psql, pg_dump, createdb, etc.

It's theoretically possible to have Postico parse the .pgpass file and behave differently, but I'm not sure I want Postico's behavior to differ from what people are used to from other tools.

[ybryan]

Thanks for the clarification. Makes sense that if libpq doesn't do it, Postico should not.