Re: Non-superuser subscription owners

On Mon, Jan 30, 2023 at 11:11 AM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> > On Jan 30, 2023, at 7:44 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> >
> > And if we suppose that
> > that already works and is safe, well then what's the case where I do
> > need a run-as user?
>
> A) Alice publishes tables, and occasionally adds new tables to existing publications.
>
> B) Bob manages subscriptions, and periodically runs "refresh publication".  Bob also creates new subscriptions for
peoplewhen a row is inserted into the "please create a subscription for me" table which Bob owns, using a trigger that
Bobcreated on that table. 
>
> C) Alice creates a "please create a subscription for me" table on the publishing database, adds lots of malicious
requests,and adds that table to the publication. 
>
> D) Bob replicates the table, fires the trigger, creates the malicious subscriptions, and starts replicating all that
stuff,too. 
>
> I think that having Charlie, not Bob, as the "run-as" user helps somewhere right around (D).

I suppose it does, but I have some complaints.

First, it doesn't seem to make a lot of sense to have one person
managing the publications and someone else managing the subscriptions,
and especially if those parties are mutually untrusting. I can't think
of any real reason to set things up that way. Sure, you could, but why
would you? You could, equally, decide that one member of your
household was going to decide what's for dinner every night, and some
other member of your household was going to decide what gets purchased
at the grocery store each week. If those two people exercise their
responsibilities without tight coordination, or with hostile intent
toward each other, things are going to go badly, but that's not an
argument for putting a combination lock on the flour canister. It's an
argument for getting along better, or not having such a dumb system in
the first place. I don't quite see how the situation you postulate in
(A) and (B) is any different. Publications and subscriptions are as
closely connected as food purchases and meals. The point of a
publication is for it to connect up to a subscription. In what
circumstances would be it be reasonable to give responsibility for
those objects to different and especially mutually untrusting users?

Second, in step (B), we may ask why Bob is doing this with a trigger.
If he's willing to create any subscription for which Alice asks, we
could have just given Alice the authority to do those actions herself.
Presumably, therefore, Bob is willing to create some subscriptions for
which Alice may ask and not others. Perhaps this whole arrangement is
just a workaround for the lack of a sensible system for controlling
which connection strings Alice can use, in which case what is really
needed here might be something like the separate connection object
which Jeff postulated or my idea of a reverse pg_hba.conf. That kind
of approach would give a better user interface to Alice, who wouldn't
have to rephrase all of her CREATE SUBSCRIPTION commands as insert
statements. Conversely, if Alice and Bob are truly dedicated to this
convoluted system of creating subscriptions, then Bob needs to put
logic into his trigger that's smart enough to block any malicious
requests that Alice may make. He really brought this problem on
himself by not doing that.

Third, in step (C), it seems to me that whoever set up Alice's
permissions has really messed up. Either the schema Bob is using for
his create-me-a-subscription table exists on the primary and Alice has
permission to create tables in that schema, or else that schema does
not exist on the primary and Alice has permission to create it. Either
way, that's a bad setup. Bob's table should be located in a schema for
which Alice has only USAGE permissions and shouldn't have excess
permissions on the table, either. Then this step can't happen. This
step could also be blocked if, instead of using a table with a
trigger, Bob wrote a security definer function or procedure and
granted EXECUTE permission on that function or procedure to Alice.
He's still going to need sanity checks, though, and if the function or
procedure inserts into a logging table or something, he'd better make
sure that table is adequately secured rather than being, say, a table
owned by Alice with malicious triggers on it.

So basically this doesn't really feel like a valid scenario to me.
We're supposed to believe that Alice is hostile to Bob, but the
superuser doesn't seem to have thought very carefully about how Bob is
supposed to defend himself against Alice, and Bob doesn't even seem to
be trying. Maybe we should rename the users to Samson and Delilah? :-)

--
Robert Haas
EDB: http://www.enterprisedb.com