Re: pgpass (in)flexibility

On 9/15/15 12:48 AM, Ben Chobot wrote:
> We're in a situation where we would like to take advantage of the pgpass hostname field to determine which password gets used. For example:
>
> psql -h prod-server -d foo # should use the prod password
> psql -h beta-server -d foo # should use the beta password
>
> This would *seem* to be simple, just put "prod-server" or "beta-server" into the hostname field of .pgpass. But if somebody uses the FQDN of those hosts, then the line does not match. If somebody uses the IP address of those hosts, again, no match. It seems that the hostname must match the hostname *exactly* - or match any host ("*"), which does not work for our use case.
>
> This seems to make the hostname field unnecessarily inflexible. Has anybody else experienced - and hopefully overcome - this pain? Maybe I'm just going about it all wrong.

I don't know of a way around that, but you might be better off using SSL
certs to authenticate. I believe there's even something similar to
ssh-keychain that would allow you not to store the passphrase on-disk
(though you would have to enter it manually on reboot).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com