Re: postgres_fdw, dblink, and CREATE SUBSCRIPTION security - Mailing list pgsql-hackers

Greetings,

* Jacob Champion (jchampion@timescale.com) wrote:
> On 3/20/23 09:32, Robert Haas wrote:
> > I think this is the root of our disagreement.
>
> Agreed.

I've read all the way back to the $SUBJECT change to try and get an
understanding of the questions here and it's not been easy, in part, I
think, due to the verbiage but also the perhaps lack of concrete
examples and instead references to other systems and protocols.

> > My understanding of the
> > previous discussion is that people think that the major problem here
> > is the wraparound-to-superuser attack. That is, in general, we expect
> > that when we connect to a database over the network, we expect it to
> > do some kind of active authentication, like asking us for a password,
> > or asking us for an SSL certificate that isn't just lying around for
> > anyone to use. However, in the specific case of a local connection, we
> > have a reliable way of knowing who the remote user is without any kind
> > of active authentication, namely 'peer' authentication or perhaps even
> > 'trust' if we trust all the local users, and so we don't judge it
> > unreasonable to allow local connections without any form of active
> > authentication. There can be some scenarios where even over a network
> > we can know the identity of the person connecting with complete
> > certainty, e.g. if endpoints are locked down such that the source IP
> > address is a reliable indicator of who is initiating the connection,
> > but in general when there's a network involved you don't know who the
> > person making the connection is and need to do something extra to
> > figure it out.
>
> Okay, but this is walking back from the network example you just
> described upthread. Do you still consider that in scope, or...?

The concern around the network certainly needs to be in-scope overall.

> > If you accept this characterization of the problem,
>
> I'm not going to say yes or no just yet, because I don't understand your
> rationale for where to draw the lines.
>
> If you just want the bare minimum thing that will solve the localhost
> case, require_auth landed this week. Login triggers are not yet a thing,
> so `require_auth=password,md5,scram-sha-256` ensures active
> authentication. You don't even have to disallow localhost connections,
> as far as I can tell; they'll work as intended.

I do think require_auth helps us move in a positive direction.  As I
mentioned elsewhere, I don't think we highlight it nearly enough in the
postgres_fdw documentation.  Let's look at that in a bit more depth with
concrete examples and perhaps everyone will be able to get a bit more
understanding of the issues.

Client is psql
Proxy is some PG server that's got postgres_fdw
Target is another PG server, that is being connected to from Proxy
Authentication is via GSS/Kerberos with proxied credentials

What do we want to require the user to configure to make this secure?

Proxy's pg_hba configured to require GSS auth from Client.
Target's pg_hba configured to require GSS auth from Proxy.

Who are we trusting with what?  In particular, I'd argue that the user
who is able to install the postgres_fdw extension and the user who is
able to issue the CREATE SERVER are largely trusted; at least in so far
as the user doing CREATE SERVER is allowed to create the server and
through that allowed to make outbound connections from the Proxy.

Therefore, the Proxy is configured with postgres_fdw and with a trusted
user performing the CREATE SERVER.

What doesn't this handle today?  Connection side-effects are one
problem- once the CREATE SERVER is done, any user with USAGE rights on
the server can create a USER MAPPING for themselves, either with a
password or without one (if they're able to proxy GSS credentials to the
system).  They aren't able to set password_required though, which
defaults to true.  However, without having require_auth set, they're
able to cause the Proxy to reach an authentication stage with the Target
that might not match what credentials they're supposed to be providing.

We attempt to address this by checking post-auth to Target that we used
the credentials to connect that we expected to- if GSS credentials were
proxied, then we expect to use those.  If a password was provided then
we expect to use a password to auth (only checked after we see if GSS
credentials were proxied and used).  The issue here is 'post-auth' bit,
we'd prefer to fail the connection pre-auth if it isn't what we're
expecting.  Should we then explicit set require_auth=gss when GSS
credentials are proxied?  Also, if a password is provided, then
explicitly set require_auth=scram-sha-256?  Or default to these, at
least, and allow the CREATE SERVER user to override our choices?  Or
should it be a USER MAPPING option that's restricted?  Or not?

> > I think that what you're proposing is that B and C can just be allowed
> > to proxy to A and A can say "hey, by the way, I'm just gonna let you
> > in without asking for anything else" and B and C can, when proxying,
> > react to that by disconnecting before the connection actually goes
> > through. That's simpler, in a sense. It doesn't require us to set up
> > the proxy configuration on B and C in a way that matches what
> > pg_hba.conf allows on A. Instead, B and C can automatically deduce
> > what connections they should refuse to proxy.
>
> Right. It's meant to take the "localhost/wraparound connection" out of a
> class of special things we have to worry about, and make it completely
> boring.

Again, trying to get at a more concrete example- the concern here is a
user with CREATE SERVER ability could leverage that access to become a
superuser if the system is configured with 'peer' access, right?  A
non-superuser is already prevented from being able to set
"password_required=false", perhaps we shouldn't allow them to set
"require_auth=none" (or have that effect) either?  Perhaps the system
should simply forcibly set require_auth based on the credentials
provided in the USER MAPPING or on the connection and have require_auth
otherwise restricted to superuser (who could override it if they'd
really like to)?  Perhaps if password_required=false we implicitly
un-set require_auth, to avoid having to make superusers change their
existing configurations where they've clearly already accepted that
credential-less connections are allowed.

Automatically setting require_auth and restricting the ability of it to
be set on user mappings to superusers doesn't strike me as terribly
difficult to do and seems like it'd prevent this concern.

Just to make sure I'm following- Robert's up-thread suggestion of an
'outbound pg_hba' would be an additional restriction when it comes to
what a user who can use CREATE SERVER is allowed to do?  I'm not against
the idea of having a way to lock that down.. but it's another level of
complication certainly and I'm not sure that some external config file
or such is the best way to try and deal with that, though I do see how
it can have some appeal for certain environments.  It does overall
strike me as something we've not tried to address in any way thus far
and a pretty large effort that's not likely to make it into PG16, unlike
the possibility of auto-setting require_auth, now that it exists.

Thanks!

Stephen