From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jonathan Katz <jonathan.katz@excoventures.com>
cc: aditya desai <admad123@gmail.com>,
        pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Pragma autonomous transactions in Postgres/ Certification based
 authentication in DB Links
In-reply-to: <52F9312D-4173-495B-B208-51CFA6331E00@excoventures.com>
References: 
 <CAN0SRDEGsXnULFzB+X7FNzwO4iT27G=rDhe9GgvMnhsj98KKPw@mail.gmail.com>
 <A0E82030-D418-4596-A6E5-87016B0CCCE7@excoventures.com>
 <1355149.1639758450@sss.pgh.pa.us>
 <52F9312D-4173-495B-B208-51CFA6331E00@excoventures.com>
Comments: In-reply-to Jonathan Katz <jonathan.katz@excoventures.com>
	message dated "Fri, 17 Dec 2021 11:42:32 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1358971.1639760673.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 17 Dec 2021 12:04:33 -0500
Message-ID: <1358972.1639760673@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1358972.1639760673%40sss.pgh.pa.us>
Precedence: bulk

Jonathan Katz <jonathan.katz@excoventures.com> writes:
>> On Dec 17, 2021, at 11:27 AM, Tom Lane <tgl@sss.pgh.pa.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