Thread: pgpass (in)flexibility
We're in a situation where we would like to take advantage of the pgpass hostname field to determine which password getsused. 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 somebodyuses 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 workfor our use case. This seems to make the hostname field unnecessarily inflexible. Has anybody else experienced - and hopefully overcome - thispain? Maybe I'm just going about it all wrong.
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 getsused. 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 somebodyuses 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 workfor 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
On Sep 15, 2015, at 12:27 AM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote: > > 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 getsused. 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 somebodyuses 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 workfor 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 evensomething similar to ssh-keychain that would allow you not to store the passphrase on-disk (though you would have toenter it manually on reboot). Does that solve the "different passwords for different servers" problem, or just the "password on disk" problem?
On 9/15/15 10:32 AM, Ben Chobot wrote: > On Sep 15, 2015, at 12:27 AM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote: >> >> 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 getsused. 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 somebodyuses 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 workfor 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 evensomething similar to ssh-keychain that would allow you not to store the passphrase on-disk (though you would have toenter it manually on reboot). > > Does that solve the "different passwords for different servers" problem, or just the "password on disk" problem? You can use different certs for different servers. If there's an SSL equivalent to ssh-keychain, that would solve the password on disk problem. -- 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
On 9/15/15 1: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 getsused. 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 somebodyuses 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 workfor 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. The alternative would be to do a double host name resolution before every connection that asks for a password, which would probably also have some concerns. I note, for example, that the OpenSSH configuration also goes by the host name as you wrote it, and then has additional options to canonicalize host names. That might be something to look into.