Re: running logical replication as the subscription owner

> As things stand today, if you think somebody's going to send you
> changes to tables other than the ones you intend to replicate, you
> could handle that by making sure that the user that owns the
> subscription only has permission to write to the tables that are
> expected to receive replicated data. It's a bit awkward to set up
> because you have to initially make the subscription owner a superuser
> and then later remove the superuser bit, so I think this is another
> argument for the pg_create_subscription patch posted elsewhere, but if
> you're a superuser yourself, you can do it. However, with this patch,
> that wouldn't work any more, because the permissions checks don't
> happen until after we've switched to the target role.

For my purposes I always trust the publisher, what I don't trust is
the table owners. But I can indeed imagine scenarios where that's the
other way around, and indeed you can protect against that currently,
but not with your new patch. That seems fairly easily solvable though.

+   if (!member_can_set_role(context->save_userid, userid))
+       ereport(ERROR,
+               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                errmsg("role \"%s\" cannot SET ROLE to \"%s\"",
+                       GetUserNameFromId(context->save_userid, false),
+                       GetUserNameFromId(userid, false))));

If we don't throw an error here, but instead simply return, then the
current behaviour is preserved and people can manually configure
permissions to protect against an untrusted publisher. This would
still mean that the table owners can escalate privileges to the
subscription owner, but if that subscription owner actually has fewer
privileges than the table owner then you don't have that issue.