Re: askpass program for libpq

On Fri, May 17, 2013 at 2:03 PM, Daniel Farina <daniel@heroku.com> wrote:
> On Wed, Jan 9, 2013 at 5:17 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> I would like to have something like ssh-askpass for libpq.  The main
>> reason is that I don't want to have passwords in plain text on disk,
>> even if .pgpass is read protected.  By getting the password from an
>> external program, I can integrate libpq tools with the host system's key
>> chain or wallet thing, which stores passwords encrypted.
>>
>> I'm thinking about adding a new connection option "askpass" with
>> environment variable PGASKPASS.  One thing I haven't quite figured out
>> is how to make this ask for passwords only if needed.  Maybe it needs
>> two connection options, one to say which program to use and one to say
>> whether to use it.
>>
>> Ideas?
>
> Okay, I have a patch that does something *like* (but not the same) as
> this, and whose implementation is totally unreasonable, but it's
> enough to get a sense of how the whole thing feels.  Critically, it
> goes beyond askpass, instead allowing a shell-command based hook for
> arbitrary interpretation and rewriting of connection info...such as
> the 'host' libpq keyword.  I have called it, without much thought, a
> 'resolver'.  In this way, it's closer to the libpq 'service' facility,
> except with addition of complete control of the interpretation of
> user-provided notation.

Hello everyone,

I'm sort of thinking of attacking this problem again, does anyone have
an opinion or any words of (en/dis)couragement to continue?  The
implementation I posted is bogus but is reasonable to feel around
with, but I'm curious besides its obvious defects as to what the
temperature of opinion is.

Most generally, I think the benefits are strongest in dealing with:

* Security: out-of-band secrets will just prevent people from pasting important stuff all over the place, as I see
despairinglyoften today.
 

* Client-side Proxies: pgbouncer comes to mind, a variation being used on production applications right now that uses
full-blownpreprocessing of the user environment (only possible in a environment with certain assumptions like Heroku)
https://github.com/gregburek/heroku-buildpack-pgbouncerseems very promising and effective, but it'd be nice to confer
thesame benefits to everyone else, too.
 

* HA: one of the most annoying problems in HA is naming things.  Yes, this could be solved with other forms of common
dynamicbinding DNS or Virtual IP (sometimes), but these both are pretty complicated and carry baggage and pitfalls, but
aslong as there is dynamic binding of the credentials, I'm thinking it may make sense to have dynamci binding of net
locations,too.
 

* Cross-server references
 This is basically the issues seen in HA and Security, but on (horrible) steroids: the spate of features making
Postgreswork cross-server (older features like dblink, but now also new ones like FDWs and Writable FDWs) make complex
interconnectionbetween servers more likely and problematic, especially if one has standbys where there is a delay in
catalogpropagation from a primary to standby with new connection info.
 
 So, an out of band way where one can adjust the dynamic binding seems useful there.

Knowing those, am I barking up the wrong tree?  Can I do something
else entirely?  I've considered DNS and SSL certs, but these seem
much, much harder and limited, too.