Re: Non-superuser subscription owners
От | Robert Haas |
---|---|
Тема | Re: Non-superuser subscription owners |
Дата | |
Msg-id | CA+TgmoaoNNW_dWMydF2B5a-3RsrgHuu+ZdcsU2OXiy3s7ExNkg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Non-superuser subscription owners (Mark Dilger <mark.dilger@enterprisedb.com>) |
Ответы |
Re: Non-superuser subscription owners
(Mark Dilger <mark.dilger@enterprisedb.com>)
Re: Non-superuser subscription owners (Mark Dilger <mark.dilger@enterprisedb.com>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: João Paulo Labegalini de CarvalhoДата:
Сообщение: Re: Optimizing PostgreSQL with LLVM's PGO+LTO
Следующее
От: Andres FreundДата:
Сообщение: Re: meson: Optionally disable installation of test modules