Re: running logical replication as the subscription owner

On Mon, Apr 03, 2023 at 12:05:29PM -0700, Jeff Davis wrote:
> On Sun, 2023-04-02 at 20:21 -0700, Noah Misch wrote:
> > The most-plausible-to-me attack involves an ENABLE ALWAYS trigger
> > that logs
> > CURRENT_USER to an audit table.
>
> How does requiring that the subscription owner have SET ROLE privileges
> on the table owner help that case? As Robert pointed out, users coming
> from v15 will have superuser subscription owners anyway, so the change
> will be silent for them.

For subscriptions upgraded from v15, it doesn't matter. Requiring SET ROLE
prevents this sequence:

- Make a table with such an audit trigger. Grant INSERT and UPDATE to Alice.
- Upgrade to v15.
- Grant pg_create_subscription and database-level CREATE to Alice.
- Alice creates a subscription as a tool to impersonate the table owner,
bypassing audit.

To put it another way, the benefit of the SET ROLE requirement is not really
making subscriptions more secure. The benefit of the requirement is
pg_create_subscription not becoming a tool for bypassing audit.

I gather we agree on what to do for v16, which is good.

> But I feel like we can do better in version 17 when we have time to
> actually work through common use cases and the exceptional cases and
> weight them appropriately. Like, how common is it to want to get the
> user from a trigger on the subscriber side?

Fair. I don't think the community has arrived at a usable approach for
answering questions like that. It would be valuable to have an approach.

> Should that trigger be
> using SESSION_USER instead of CURRENT_USER?

Apart from evaluating the argument of SET ROLE, I've not heard of a valid use
case for SESSION_USER.