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

On Fri, Dec 17, 2021 at 9:07 AM Thomas Kellerer <shammat(at)gmx(dot)net> wrote:

> 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
>
> I haven't looked into this too closely (yet), but if you were deploying in
an AWS environment with RDS, you might be able to use the Secrets Manager
plus VPCs to create a certificate and IAM Role based authentication pathway
and access level to Pg that is quite secure and doesn't involve any
passwords?

More broadly speaking, maybe Vault (and Boundary?) from Hashicorp might be
suitable for this? You'd have access certificates on your client machine,
which you can use to then check out Pg credentials from Vault to connect to
a server, so that Pg passwords are never persisted on any local machine?

Maybe I'm not tracking the problem you're trying to solve but these are
some ideas we've been looking at to solve for the "where to store the
username/password to gain access to Pg" problem.

Steve