Thread: Allow superuser to grant passwordless connection rights onpostgres_fdw

This patch allows the superuser to grant passwordless connection rights
in postgres_fdw user mappings.


The patch is authored by my colleague Craig Ringer, with slight bitrot
fixed by me.


One use case for this is with passphrase-protected client certificates,
a patch for which will follow shortly.


Here are Craig's remarks on the patch:

  
    postgres_fdw denies a non-superuser the ability to establish a
connection that
    doesn't have a password in the connection string, or one that fails
to actually
    use the password in authentication. This is to stop the unprivileged
user using
    OS-level authentication as the postgres server (peer, ident, trust).
It also
    stops unauthorized use of local credentials like .pgpass, a service
file,
    client certificate files, etc.
   
    Add the ability for a superuser to create user mappings that
override this
    behaviour by setting the passwordless_ok attribute to true in a user
mapping
    for a non-superuser. The non-superuser gains the ability to use the
FDW the
    mapping applies to even if there's no password in their mapping or
in the
    connection string.
   
    This is only safe if the superuser has established that the local
server is
    configured safely. It must be configured not to allow
    trust/peer/ident/sspi/gssapi auth to allow the OS user the postgres
server runs
    as to log in to postgres as a superuser. Client certificate keys can
be used
    too, if accessible. But the superuser can already GRANT superrole TO
    normalrole, so it's not any sort of new power.
   

cheers


andrew



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

On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan
<andrew.dunstan@2ndquadrant.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...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 11/1/19 12:58 PM, Robert Haas wrote:
> On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan
> <andrew.dunstan@2ndquadrant.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.


cheers


andrew


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

Greetings,

* Andrew Dunstan (andrew.dunstan@2ndquadrant.com) wrote:
> On 11/1/19 12:58 PM, Robert Haas wrote:
> > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan
> > <andrew.dunstan@2ndquadrant.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.

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

On Sun, Nov 10, 2019 at 4:35 AM Craig Ringer <craig@2ndquadrant.com> wrote:
>
> On Mon, 4 Nov 2019 at 12:20, Stephen Frost <sfrost@snowman.net> wrote:
>>
>> Greetings,
>>
>> * Andrew Dunstan (andrew.dunstan@2ndquadrant.com) wrote:
>> > On 11/1/19 12:58 PM, Robert Haas wrote:
>> > > On Thu, Oct 31, 2019 at 4:58 PM Andrew Dunstan
>> > > <andrew.dunstan@2ndquadrant.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
owncertificate - there's a separate patch to allow that. So there's no delegation of trust via Kerberos etc in that
particularcase. 
>
> I can see value in using Kerberos etc for that too though, as it separates authorization and authentication in the
samemanner as most sensible systems. You can say "user postgres@foo is trusted to vet users so you can safely hand out
ticketsfor any bar@foo that postgres@foo says is legit". 
>
> I would strongly discourage allowing all users on host A to authenticate as user postgres on host B. But with
appropriateuser-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

Greetings,

* Craig Ringer (craig@2ndquadrant.com) wrote:
> On Mon, 4 Nov 2019 at 12:20, Stephen Frost <sfrost@snowman.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@foo is trusted to vet users so you can
> safely hand out tickets for any bar@foo that postgres@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@foo' to request a
ticket to delegate to 'postgres@foo', which then allows 'postgres@foo'
to connect as if they are 'bar@foo' to some other service (and in some
implementations, I believe it's further possible to say that the ticket
for 'bar@foo' which is delegated to 'postgres@foo' is only allowed to
request tickets for certain specific services, such as 'postgres2@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

On Tue, Dec 3, 2019 at 9:36 AM Stephen Frost <sfrost@snowman.net> wrote:


>
> > 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).
>


It hasn't been done, but I now propose to commit it shortly so other
work can proceed.

cheers

andrew

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