Horton
Menu

Re: running logical replication as the subscription owner

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
To: postgres@jeltef.nl
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Date: 2023-03-06T17:10:23Z
Lists: pgsql-hackers 
On Fri, Mar 3, 2023 at 6:57 PM Jelte Fennema <postgres@jeltef.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

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.