Thread: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
aditya desai
Date:
Hi,
Is there any way to achieve autonomous transactions in Postgres? There is a way to do it by using DB Link. However DB Link involves user name and password which is a bit of a security threat.
How to use certificate based authentication in DB Links instead of hardcoding user name and password in it?
Regards,
Aditya.
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Jonathan Katz
Date:
On Dec 17, 2021, at 10:43 AM, aditya desai <admad123@gmail.com> wrote:Hi,Is there any way to achieve autonomous transactions in Postgres? There is a way to do it by using DB Link. However DB Link involves user name and password which is a bit of a security threat.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].
Jonathan
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Tom Lane
Date:
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
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Jonathan Katz
Date:
> 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
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Tom Lane
Date:
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
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Thomas Kellerer
Date:
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
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Steve Midgley
Date:
On Fri, Dec 17, 2021 at 9:07 AM Thomas Kellerer <shammat@gmx.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
Re: Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links
From
Tom Lane
Date:
Thomas Kellerer <shammat@gmx.net> writes:
> 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.
> What about using a .pgpass file?
Still amounts to impersonating the server (and yeah, we do prevent
that if you're not superuser).
It might make sense to have a superuser-owned SECURITY DEFINER
function that's responsible for creating the desired connection,
and could make use of the server's .pgpass credentials. As long
as you restrict what that function is willing to do, and restrict
who can execute it, this'd probably be adequately secure.
regards, tom lane