Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links

Jonathan Katz <jonathan(dot)katz(at)excoventures(dot)com> writes:
>> On Dec 17, 2021, at 11:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The problem is
>> that making use of any credentials stored in the server's filesystem
>> amounts to impersonating the OS user that's running the server. It'd
>> be nice to find a less confining solution, but I'm not sure what one
>> would look like.

> Even stepping back and just looking at what prompted the question,
> i.e. “hardcoding the username/password”, if there was a way we could
> allow for the injection of the credentials when we’re trying to establish
> the connection, that may be one way forward, but I see that also
> opening up a bunch more problems we would need to consider.

One approach that's available now is to have dblink use a foreign
server/foreign user mapping definition. Then the secret is stored
in pg_user_mapping rather than in the SQL text, which is an
improvement anyway. (If you want to complain about that, you have
to be a little more specific about what your threat model is.
Somebody who can peek into pg_user_mapping can probably get hold
of credentials in the server's filesystem, too.)

regards, tom lane