Re: askpass program for libpq

On Sat, Jun 15, 2013 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniel Farina <daniel@heroku.com> writes:
>>> 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.
>
> TBH, I see no clear reason to think that a connection-string rewriter
> solves any of those problems.  At best it would move them somewhere else.

Yes, that's exactly what I want to achieve: moving them somewhere else
that can be held in common by client applications.

> Nor is it clear that any of this should be libpq's business, as opposed
> to something an application might do before invoking libpq.

Yes, it's unclear.  I have only arrived at seriously exploring this
after trying my very best meditate on other options.  In addition,
sometimes 'the application' is Postgres, and with diverse access paths
like FDWs and dblink, which may not be so easy to adjust, and it would
seem strange to adjust them in a way that can't be shared in common
with regular non-backend-linked client applications.

Also, it seems like a very high bar to set for an application as to
make use of environment keychains or environment-specific high
availability retargeting.  This general approach you mention is used
in Greg Burek's heroku-pgbouncer buildpack, but it took significant
work to iron out (and probably still needs more ironing, although it
seems to work great) and only serves the Heroku-verse.

Basically, the needs seem very similar, so abstracting seems to me
profitable.

This is not that different in principle than pgpass and the services
file in that regard, except taking the final step to delegate their
function...and deliver full control over notation.

Although I don't know much about it, I seem to recall that VMWare felt
inclined to instigate some kind of vaguely related solution to solve a
similar problem in a custom libpq.  After initial recoil and over a
year of contemplation, I think the reasons are more well justified
than I originally thought and it'd be nice to de-weirdify such
approaches.

> Also, I think a facility dependent on invoking a shell command is
> (a) wide open for security problems, and (b) not likely to be
> portable to Windows.

Yeah, those things occurred to me, I think a dlopen based mechanism is
a more likely solution than the shell-command one.  The latter just
let me get started quickly to experiment.  Would a rigorous proposal
about how to do that help the matter?  I mostly wanted to get the
temperature before thinking about Real Mechanisms.