Subject: Re: Pragma autonomous transactions in Postgres/ Certification based
 authentication in DB Links
To: pgsql-sql@lists.postgresql.org
References: 
 <CAN0SRDEGsXnULFzB+X7FNzwO4iT27G=rDhe9GgvMnhsj98KKPw@mail.gmail.com>
 <A0E82030-D418-4596-A6E5-87016B0CCCE7@excoventures.com>
 <1355149.1639758450@sss.pgh.pa.us>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <b1c37523-c7ec-1643-4958-e7c8f400e3a8@gmx.net>
Date: Fri, 17 Dec 2021 18:07:29 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.21)
 Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
MIME-Version: 1.0
In-Reply-To: <1355149.1639758450@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/b1c37523-c7ec-1643-4958-e7c8f400e3a8%40gmx.net>
Precedence: bulk

Tom Lane schrieb am 17.12.2021 um 17:27:
> 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 :-().

What about using a .pgpass file?

We use that to hide the password for FDW connections on the SQL level.

Regards
Thomas