Re: askpass program for libpq

On Fri, May 17, 2013 at 2:03 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Wed, Jan 9, 2013 at 5:17 AM, Peter Eisentraut <peter_e(at)gmx(dot)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 despairingly often
today.

* Client-side Proxies: pgbouncer comes to mind, a variation being used
on production applications right now that uses full-blown
preprocessing of the user environment (only possible in a
environment with certain assumptions like Heroku)
https://github.com/gregburek/heroku-buildpack-pgbouncer seems very
promising and effective, but it'd be nice to confer the same
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 dynamic binding DNS
or Virtual IP (sometimes), but these both are pretty complicated and
carry baggage and pitfalls, but as long 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 Postgres work
cross-server (older features like dblink, but now also new ones like
FDWs and Writable FDWs) make complex interconnection between servers
more likely and problematic, especially if one has standbys where
there is a delay in catalog propagation 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.