Horton

Thread

  1. Pragma autonomous transactions in Postgres/ Certification based authentication in DB Links

    aditya desai <admad123@gmail.com> — 2021-12-17T15:43:32Z

    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.

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

    Jonathan Katz <jonathan.katz@excoventures.com> — 2021-12-17T16:11:55Z

    
> 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

[1] https://www.postgresql.org/docs/current/contrib-dblink-connect.html <https://www.postgresql.org/docs/current/contrib-dblink-connect.html>
[2] https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-12-17T16:27:30Z

    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

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

    Jonathan Katz <jonathan.katz@excoventures.com> — 2021-12-17T16:42:32Z

    
> 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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-12-17T17:04:33Z

    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

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

    shammat@gmx.net — 2021-12-17T17:07:29Z

    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

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

    Steve Midgley <science@misuse.org> — 2021-12-17T17:13:26Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-12-17T17:58:55Z

    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