Horton
Menu

Re: running logical replication as the subscription owner

Jelte Fennema <postgres@jeltef.nl>

View thread
From: Jelte Fennema <postgres@jeltef.nl>
To: Robert Haas <robertmhaas@gmail.com>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2023-03-03T23:57:15Z
Lists: pgsql-hackers 
I'm definitely in favor of making it easier to use logical replication
in a safe manner. 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.

[1]: https://github.com/citusdata/citus/blob/main/src/backend/distributed/replication/multi_logical_replication.c#L1487-L1573
[2]: https://github.com/citusdata/citus/blob/main/src/backend/distributed/commands/utility_hook.c#L455-L487

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Honor run_as_owner option in tablesync worker.

  2. Document new pg_subscription columns.

  3. Add a run_as_owner option to subscriptions.

  4. Perform logical replication actions as the table owner.

  5. Add new predefined role pg_create_subscription.

  6. Respect permissions within logical replication.

  7. Empty search_path in logical replication apply worker and walsender.

  8. Empty search_path in Autovacuum and non-psql/pgbench clients.