Re: Allow superuser to grant passwordless connection rights on postgres_fdw

Greetings,

* Craig Ringer (craig(at)2ndquadrant(dot)com) wrote:
> On Mon, 4 Nov 2019 at 12:20, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > I've long felt that the way to handle this kind of requirement is to
> > have a "trusted remote server" kind of option- where the local server
> > authenticates to the remote server as a *server* and then says "this is
> > the user on this server, and this is the user that this user wishes to
> > be" and the remote server is then able to decide if they accept that, or
> > not.
>
> The original use case for the patch was to allow FDWs to use SSL/TLS client
> certificates. Each user-mapping has its own certificate - there's a
> separate patch to allow that. So there's no delegation of trust via
> Kerberos etc in that particular case.
>
> I can see value in using Kerberos etc for that too though, as it separates
> authorization and authentication in the same manner as most sensible
> systems. You can say "user postgres(at)foo is trusted to vet users so you can
> safely hand out tickets for any bar(at)foo that postgres(at)foo says is legit".

So, just to be clear, the way this *actually* works is a bit different
from the way being described above, last time I looked into Kerberos
delegations anyway.

Essentially, the KDC can be set up to allow 'bar(at)foo' to request a
ticket to delegate to 'postgres(at)foo', which then allows 'postgres(at)foo'
to connect as if they are 'bar(at)foo' to some other service (and in some
implementations, I believe it's further possible to say that the ticket
for 'bar(at)foo' which is delegated to 'postgres(at)foo' is only allowed to
request tickets for certain specific services, such as 'postgres2(at)foo'
or what-have-you).

Note that setting this up with an MIT KDC requires configuring it with
an LDAP backend as the traditional KDC database doesn't support this
kind of complex delegation control (again, last time I checked anyway).

> I would strongly discourage allowing all users on host A to authenticate as
> user postgres on host B. But with appropriate user-mappings support, we
> could likely support that sort of model for both SSPI and Kerberos.

Ideally, both sides would get a 'vote' regarding what's allowed, I would
think. That is, the connecting side would have to have a user mapping
that says "this authenticated user is allowed to connect to this remote
server as this user", and the remote server would have something like
"this server that's connecting, validated by the certificate presented
by the server, is allowed to authenticate as this user". I feel like
we're mostly there by allowing the connecting server to use a
certificate to connect to the remote server, while it's also checking
the user mapping, and the remote server's pg_hba.conf being configured
to allow cert-based auth with a CN mapping from the CN of the connecting
server's certificate to authenticate to whatever users the remote server
wants to allow. Is that more-or-less the idea here..?

> A necessary prerequisite is that Pg be able to cope with passwordless
> user-mappings though. Hence this patch.

Sure, that part seems like it makes sense to me (and perhaps has now
been done, just catching up on things after travel and holidays and such
here in the US).

Thanks!

Stephen