Support kerberos authentication for postgres_fdw

Peifeng Qiu <peifengq@vmware.com> writes:

I'd like to add kerberos authentication support for postgres_fdw by adding two
options to user mapping: krb_client_keyfile and gssencmode.

As you note, this'd have to be restricted to superusers, which makes it
seem like a pretty bad idea. We really don't want to be in a situation
of pushing people to run day-to-day stuff as superuser. Yeah, having
access to kerberos auth sounds good on the surface, but it seems like
it would be a net loss in security because of that.

Is there some other way?

regards, tom lane

On Fri, Jul 9, 2021 at 3:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peifeng Qiu <peifengq@vmware.com> writes:

I'd like to add kerberos authentication support for postgres_fdw by adding two
options to user mapping: krb_client_keyfile and gssencmode.

As you note, this'd have to be restricted to superusers, which makes it
seem like a pretty bad idea. We really don't want to be in a situation
of pushing people to run day-to-day stuff as superuser. Yeah, having
access to kerberos auth sounds good on the surface, but it seems like
it would be a net loss in security because of that.

Is there some other way?

ISTM the right way to do this would be using Kerberos delegation. That
is, the system would be set up so that the postgres service principal
is trusted for kerberos delegation and it would then pass through the
actual Kerberos authentication from the client.

At least at a quick glance this does not look like what this patch is
doing, sadly.

What does kerberos auth with a fixed key on the client (being the
postgres server in this auth step) actually help with?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Sorry I have sent a duplicate email. I will first continue discussion
in the other thread and then submit it after we have a conclusion.
Thanks.

Peifeng

As you note, this'd have to be restricted to superusers, which makes it
seem like a pretty bad idea. We really don't want to be in a situation
of pushing people to run day-to-day stuff as superuser. Yeah, having
access to kerberos auth sounds good on the surface, but it seems like
it would be a net loss in security because of that.

I can imagine the use case would be a superuser creates the user
mapping and foreign table, then grants access of foreign table to
a normal user. This way the normal user can execute queries on the
foreign table but can't access sensitive information in user mapping.

The main purpose of this patch is to provide a simple way to do
kerberos authentication with the least modification possible.

ISTM the right way to do this would be using Kerberos delegation. That
is, the system would be set up so that the postgres service principal
is trusted for kerberos delegation and it would then pass through the
actual Kerberos authentication from the client.

I agree this sounds like the ideal solution. If I understand it correctly,
this approach requires both postgres servers to use same kerberos
settings(kdc, realm, etc), and the FDW server can just "forward"
necessary information to authenticate on behalf of the same user.
I will spend some time to investigate it and reach out later.

Best regards,
Peifeng

On Mon, Jul 12, 2021 at 5:43 AM Peifeng Qiu <peifengq@vmware.com> wrote:

As you note, this'd have to be restricted to superusers, which makes it
seem like a pretty bad idea. We really don't want to be in a situation
of pushing people to run day-to-day stuff as superuser. Yeah, having
access to kerberos auth sounds good on the surface, but it seems like
it would be a net loss in security because of that.

I can imagine the use case would be a superuser creates the user
mapping and foreign table, then grants access of foreign table to
a normal user. This way the normal user can execute queries on the
foreign table but can't access sensitive information in user mapping.

The main purpose of this patch is to provide a simple way to do
kerberos authentication with the least modification possible.

But in this case, what dose Kerberos give over just using a password
based solution? It adds complexity, but what's teh actual gain?

ISTM the right way to do this would be using Kerberos delegation. That
is, the system would be set up so that the postgres service principal
is trusted for kerberos delegation and it would then pass through the
actual Kerberos authentication from the client.

I agree this sounds like the ideal solution. If I understand it correctly,
this approach requires both postgres servers to use same kerberos
settings(kdc, realm, etc), and the FDW server can just "forward"
necessary information to authenticate on behalf of the same user.
I will spend some time to investigate it and reach out later.

I don't actually know if they have to be in the same realm, I *think*
kerberos delegations work across trusted realms, but I'm not sure
about that.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

But in this case, what dose Kerberos give over just using a password
based solution? It adds complexity, but what's teh actual gain?

That's due to policy of some customers. They require all login to be kerberos
based and password-less. I suppose this way they don't need to maintain
passwords in each database and the same keytab file may be used in
connections to multiple databases.
If we can do the delegation approach right, it's clearly a superior solution
since keytab file management is also quite heavy burden.

Greetings,

* Magnus Hagander (magnus@hagander.net) wrote:

On Mon, Jul 12, 2021 at 5:43 AM Peifeng Qiu <peifengq@vmware.com> wrote:

As you note, this'd have to be restricted to superusers, which makes it
seem like a pretty bad idea. We really don't want to be in a situation
of pushing people to run day-to-day stuff as superuser. Yeah, having
access to kerberos auth sounds good on the surface, but it seems like
it would be a net loss in security because of that.

I can imagine the use case would be a superuser creates the user
mapping and foreign table, then grants access of foreign table to
a normal user. This way the normal user can execute queries on the
foreign table but can't access sensitive information in user mapping.

The main purpose of this patch is to provide a simple way to do
kerberos authentication with the least modification possible.

But in this case, what dose Kerberos give over just using a password
based solution? It adds complexity, but what's teh actual gain?

This is a bad idea.

ISTM the right way to do this would be using Kerberos delegation. That
is, the system would be set up so that the postgres service principal
is trusted for kerberos delegation and it would then pass through the
actual Kerberos authentication from the client.

I agree this sounds like the ideal solution. If I understand it correctly,
this approach requires both postgres servers to use same kerberos
settings(kdc, realm, etc), and the FDW server can just "forward"
necessary information to authenticate on behalf of the same user.
I will spend some time to investigate it and reach out later.

I don't actually know if they have to be in the same realm, I *think*
kerberos delegations work across trusted realms, but I'm not sure
about that.

This is a good idea, and yes, delegation works just fine across realms
if the environment is properly set up for cross-realm trust.

Kerberos delegation is absolutely the way to go here. I don't think we
should even be thinking of accepting something that requires users to
put a bunch of keytab files on the PG server to allow that server to
reach out to other servers...

I'd be happy to work with someone on an effort to support Kerberos
delegated credentials; it's been something that I've wanted to work on
for a long time.

Thanks,

Stephen

Hi all.

I've come up with a proof-of-concept patch using the delegation/proxy approach.

Let's say we have two DB, one for FDW and one for the real server. When client
connects to FDW server using kerberos authentication, we can obtain a "proxy"
credential and store it in the global variable "MyProcPort->gss->proxy". This can
be then passed to gssapi calls during libpq kerberos setup when the foreign table
is queried.

This will mitigate the need for keytab file on FDW server. We will also have to
relax the password requirement for user mapping.

The big problem here is how to pass proxy credential from backend to libpq-fe
safely. Because libpq called in postgres_fdw is compiled as frontend binary, we'd
better not include any backend related stuff in libpq-fe.
In this patch I use a very ugly hack to work around this. First take pointer address
of the variable MyProcPort->gss->proxy, convert it to hex string, and then pass
it as libpq option "gss_proxy_cred". Any idea about how to do this in a more
elegant way?

Best regards,
Peifeng