Thank you for this email. It's very helpful to get your opinion on this. On Sun, Apr 2, 2023 at 11:21 PM Noah Misch <n...@leadboat.com> wrote: > On Wed, Mar 29, 2023 at 04:00:45PM -0400, Robert Haas wrote: > > > The dangerous cases seem to be something along the lines of a security- > > > invoker trigger function that builds and executes arbirary SQL based on > > > the row contents. And then the table owner would then still need to set > > > ENABLE ALWAYS TRIGGER. > > > > > > Do we really want to take that case on as our security responsibility? > > > > That's something about which I would like to get more opinions. > > The most-plausible-to-me attack involves an ENABLE ALWAYS trigger that logs > CURRENT_USER to an audit table. The "SQL based on the row contents" scenario > feels remote. Another remotely-possible attack involves a trigger that > internally queries some other table having RLS. (Switching to the table owner > can change the rows visible to that query.)
I had thought of the first of these cases, but not the second one. > If having INSERT/UPDATE privileges on the table were enough to make a > subscription that impersonates the table owner, then relatively-unprivileged > roles could make a subscription to bypass the aforementioned auditing. Commit > c3afe8c has imposed weighty requirements beyond I/U privileges, namely holding > the pg_create_subscription role and database-level CREATE privilege. Since > database-level CREATE is already powerful, it would be plausible to drop the > SET ROLE requirement and add this audit bypass to its powers. The SET ROLE > requirement is nice for keeping the powers disentangled. One drawback is > making people do GRANTs regardless of whether a relevant audit trigger exists. > Another drawback is the subscription role having more privileges than ideally > needed. I do like keeping strong privileges orthogonal, so I lean toward > keeping the SET ROLE requirement. The orthogonality argument weighs extremely heavily with me in this case. As I said to Jeff, I would not mind having a more granular way to control which tables a user can replicate into; e.g. a grantable REPLICAT{E,ION} privilege, or we want something global we could have a predefined role for it, e.g. pg_replicate_into_any_table. But I think any such thing should definitely be separate from pg_create_subscription. I'll fix the typos. Thanks for reporting them. -- Robert Haas EDB: http://www.enterprisedb.com