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

> On Dec 17, 2021, at 11:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Jonathan Katz <jonathan.katz@excoventures.com> writes:
>>> On Dec 17, 2021, at 10:43 AM, aditya desai <admad123@gmail.com> wrote:
>>> How to use certificate based authentication in DB Links instead of hardcoding user name and password in it?
>
>> dblink lets you pass in a PostgreSQL connection string[1].
>> From there you can reference parameters to use certificates[2].
>
> No, that won't help.  Like postgres_fdw, dblink will only let you use
> non-password auth methods if you're superuser [1][2].

Oops, I should have TIAS’d.

>  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.
>
> Maybe "use server's FDW credentials" could be associated with a
> grantable role?  That's still an awfully coarse-grained approach
> though.  I thought for a moment about putting an SSL cert right
> into the connection string; but you'd have to put the SSL private
> key in there too, making it just as much of a security problem as
> putting a password there (but about 100 times more verbose :-().

From my experience in container world, that’s somewhat less of an
issue if you’re injecting those items via a secret management
mechanism, though typically you’re handling that reference via an
environmental variable. That said there are some cases I have
wanted to pass in the key/cert directly  just from a libpq perspective, vs.
having to access the key/cert from the filesystem.

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.

Jonathan