Re: postgres_fdw and Kerberos authentication

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Jean-Marc Lessard <Jean-Marc(dot)Lessard(at)ultra-ft(dot)com> writes:
> > A nice way to meet security requirements would be to provide single sign on support for the postgres_fdw.
> > As long as you have defined a user in the source and destination databases, and configure the Kerberos authentication you should be able to use postgres_fdw.
>
> It's not really that easy, because postgres_fdw (like the server in
> general) is running as the database-owner operating system user.
> How will you associate a Postgres role that's responsible for a
> particular connection request with some Kerberos credentials,
> while keeping it away from credentials that belong to other roles?

That's actually not that difficult and is something which Apache and
mod_auth_kerb has been doing for a very long time.

> This is certainly something that'd be useful to have, but it's not
> clear how to do it in a secure fashion.

The database owner operating system user has to be trusted, along with
any superusers in the database, but if you assume those, then having PG
manage the different Kerberos cache files (one for each backend which
has authenticated via Kerberos and passed through delegation
credentials) should work. Clearly, we can't give the user control over
which credential cache to use.

Having to trust the OS user and superusers with those credentials isn't
any different from using passwords with postgres_fdw.

Thanks!

Stephen