Re: Allow superuser to grant passwordless connection rights on postgres_fdw

On Sun, Nov 10, 2019 at 4:35 AM Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>
> 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.
>
>

Yeah, I agree. Does anyone else want to weigh in on this? If nobody
objects I'd like to tidy this up and get it committed so we can add
support for client certs in postgres_fdw, which is the real business
at hand, and which I know from various offline comments a number of
people are keen to have.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services