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

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].  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 :-().

            regards, tom lane

[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/dblink.c;h=d73c616f4f240cf5f33294e61053765375a5bea6;hb=HEAD#l2669
[2]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/dblink/dblink.c;h=d73c616f4f240cf5f33294e61053765375a5bea6;hb=HEAD#l2690