Message-ID: <92c072dd-d979-8c74-d133-cb4a37a44124@timescale.com>
Date: Wed, 12 Apr 2023 11:24:33 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.7.1
Subject: Re: postgres_fdw, dblink, and CREATE SUBSCRIPTION security
Content-Language: en-US
To: Stephen Frost <sfrost@snowman.net>
Cc: Robert Haas <robertmhaas@gmail.com>, Andres Freund <andres@anarazel.de>,
 Mark Dilger <mark.dilger@enterprisedb.com>, Jeff Davis <pgsql@j-davis.com>,
 Amit Kapila <amit.kapila16@gmail.com>, Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <CA+TgmoZvSSRYAdfppHiYC25CB1f2s5fNvSJ=0kMEGdicRdk2dg@mail.gmail.com>
 <cb43e26f-d897-447c-a795-b3954815f371@timescale.com>
 <CA+Tgmobit_-XTGJxZ0kzxJ7KSasQShxJz-oBGJCDbzPjL6s2-g@mail.gmail.com>
 <CAAWbhmg25XaA=s04y7_LK8Q7-GSBw09fH2A5iiFESUbVaFXWqg@mail.gmail.com>
 <CA+TgmoYBF75-MQ02K=4-JA02YvfLA1bsWdbjQP=evfjJGigsbA@mail.gmail.com>
 <CAAWbhmhPFvV=TyGw0GfdLNUR6GMU0Ntcg6fEuhTNcha6f2oGig@mail.gmail.com>
 <CA+TgmoYk5FCnzQFEsYJAUq4PgAkRcTn9Gy1gr28_rkP-FD=kBQ@mail.gmail.com>
 <CAAWbhmhRn1QnV=HiM_5X0s+1Az8DTdgXROwguMj2ZMCvNi1TcA@mail.gmail.com>
 <CA+TgmoZasHd3VdKrhbiJDduUWewVzc_bcM2ZAYg9ePgrbQyZVQ@mail.gmail.com>
 <29c017e5-a84d-3ff7-c049-1cc0c7ccbb4d@timescale.com>
 <ZCXRX4bE1IQQ4otv@tamriel.snowman.net>
From: Jacob Champion <jchampion@timescale.com>
In-Reply-To: <ZCXRX4bE1IQQ4otv@tamriel.snowman.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/92c072dd-d979-8c74-d133-cb4a37a44124%40timescale.com>
Precedence: bulk

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