Re: running logical replication as the subscription owner

On Fri, Mar 3, 2023 at 6:57 PM Jelte Fennema <postgres(at)jeltef(dot)nl> wrote:
> I'm definitely in favor of making it easier to use logical replication
> in a safe manner.

Cool.

> In Citus we need to logically replicate and we're
> currently using quite some nasty and undocumented hacks to do so:
> We're creating a subscription per table owner, where each subscription
> is owned by a temporary user that has the same permissions as the
> table owner. These temporary users were originally superusers, because
> otherwise we cannot make them subscription owners, but once assigning
> a subscription to them we take away the superuser permissions from
> them[1]. And we also need to hook into ALTER/DELETE subscription
> commands to make sure that these temporary owners cannot edit their
> own subscription[2].
>
> Getting this right was not easy. And even it has the serious downside
> that we need multiple subscriptions/replication slots which causes
> extra complexity in various ways and it eats much more aggressively
> into the replication slot limits than we'd like. Having one
> subscription that could apply into tables that were owned by multiple
> users in a safe way would make this sooo much easier.

Yeah. As Andres pointed out somewhere or other, that also means you're
decoding the WAL once per user instead of just once. I'm surprised
that hasn't been cost-prohibitive.

--
Robert Haas
EDB: http://www.enterprisedb.com