Re: postgres_fdw, dblink, and CREATE SUBSCRIPTION security

On 3/30/23 11:13, Stephen Frost wrote:
>> 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.

Sounds good!

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

Right. Keep in mind that require_auth is post-auth, though; it can't fix
that issue, so it doesn't fix any connection side-effect problems at all.

> 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?
IMO, yes -- whatever credentials the proxy is forwarding from the user,
the proxy should be checking that the server has actually used them. The
person with the ability to create a USER MAPPING should probably not
have the ability to override that check.

>>> 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?

Or 'trust localhost', or 'ident [postgres user]', yes.

> 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?

I think that sounds reasonable.

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

Mm, I think I like the first idea better. If you've set a password,
wouldn't you like to know if the server ignored it? If password_required
is false, *and* you don't have a password, then we can drop require_auth
without issue.

> 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?

Yes. That can provide additional safety in the case where you really
need to take the require_auth checks away for whatever reason. I think
it's just a good in-depth measure, and if we don't extend the protocol
in some way to do a pre-auth check, it's also the way for the DBA to
bless known-good connection paths.

Thanks,
--Jacob