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 <noah@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