Thread: postgres_fdw and Kerberos authentication

Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Jean-Marc Lessard <Jean-Marc.Lessard@ultra-ft.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
youshould 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?

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

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Jean-Marc Lessard <Jean-Marc.Lessard@ultra-ft.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
authenticationyou 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

Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Jean-Marc,

* Jean-Marc Lessard (Jean-Marc.Lessard@ultra-ft.com) wrote:
> Stephen Frost [sfrost@snowman.net]  wrote:
> > The database owner operating system user has to be trusted, along with any superusers in the database, but if you
assumethose, 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.
>
> True, in such a case (single sign on) the user should not specify a user in the user mapping, so that its own
Kerberosticket be used to authenticate. 

I don't know that it's actually an issue for the user to specify the
mapping- either it'll be allowed or not, based on the credentials in the
Kerberos cache and pg_ident mappings.  What we can't do is allow the
user to control which cache they are able to use.

In other words, there should be one credential cache per backend process
and that holds exactly the credentials which are forwarded from the
client system.

> > Having to trust the OS user and superusers with those credentials isn't any different from using passwords with
postgres_fdw.
>
> OS user and superusers, should not have access and allowed to manage the credential files.

This isn't possible with traditional Unix permissions.  Perhaps
something could be done with SELinux, but we're not going to depend on
that.

Ultimately, the credential cache must be available to the backend
process, which runs as the OS user.  The PG superuser can execute
arbitrary commands as the OS user, so there isn't any distinction
between the OS user and the PG superuser.

As mentioned up-thread, this is exactly the same as Apache, except that
Apache happens to run as root whereas we run as a non-root user.

> For example, in a secure environment with separation of duties at the organization level (tier1, tier3, superuser,
sysadmins, etc), the tier1 DB users cannot connect onto the DB server (as OS user), but may move data form one database
toanother. 

Sure, I assumed that we were discussing a case where DB users connect to
the database, not log on to the DB server as an OS user.

> I agree that tier1 users cannot query the catalog and see other user password, but a superuser can, which is
considereda security breach by auditors. 
> Storing a password in plain text even for a short period of time is unfortunately not authorized.

Agreed.  This isn't the same as a Kerberos credential cache, but it's
not as far different as one might assume either.  The superuser will be
able to access the credential cache of anyone who has forwarded their
Kerberos ticket to the server, which is the same for any environment
that allows Kerberos credential proxying.

Thanks!

Stephen