Re: Allow superuser to grant passwordless connection rights on postgres_fdw

On Mon, 4 Nov 2019 at 12:20, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Andrew Dunstan (andrew(dot)dunstan(at)2ndquadrant(dot)com) wrote:
> > On 11/1/19 12:58 PM, Robert Haas wrote:
> > > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan
> > > <andrew(dot)dunstan(at)2ndquadrant(dot)com> wrote:
> > >> This patch allows the superuser to grant passwordless connection
> rights
> > >> in postgres_fdw user mappings.
> > > This is clearly something that we need, as the current code seems
> > > woefully ignorant of the fact that passwords are not the only
> > > authentication method supported by PostgreSQL, nor even the most
> > > secure.
> > >
> > > But, I do wonder a bit if we ought to think harder about the overall
> > > authentication model for FDW. Like, maybe we'd take a different view
> > > of how to solve this particular piece of the problem if we were
> > > thinking about how FDWs could do LDAP authentication, SSL
> > > authentication, credentials forwarding...
> >
> > I'm certainly open to alternatives.
>
> 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".

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.

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

>
> To be specific, there would be some kind of 'trust' established between
> the servers and only if there is some kind of server-level
> authentication, eg: dual TLS auth, or dual GSSAPI auth; and then, a
> mapping is defined for that server, which specifies what remote user is
> allowed to log in as what local user.
>
> This would be a server-to-server auth arrangement, and is quite
> different from credential forwarding, or similar. I am certainly also a
> huge fan of the idea that we support Kerberos/GSSAPI credential
> forwarding / delegation, where a client willingly forwards to the PG
> server a set of credentials which then allow the PG server to
> authenticate as that user to another system (eg: through an FDW to
> another PG server).
>
> Of course, as long as we're talking pie-in-the-sky ideas, I would
> certainly be entirely for supporting both. ;)
>
> Thanks,
>
> Stephen
>

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise