Re: Proposal: Conflict log history table for Logical Replication

On Thu, May 7, 2026 at 10:01 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Thu, May 7, 2026 at 8:26 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> >
> > So for the ownership change, this simple change[1] is working fine,
> > but there is another issue that currently we can assign subscription
> > nownership to any user even that doesn't have pg_create_subscription
> > maybe that should be fine as it is not creating the subscription but
> > now question is how to manage the permission on the conflict log table
> > see below test[2]
> >
> >
> > [1[]
> > diff --git a/src/backend/commands/subscriptioncmds.c
> > b/src/backend/commands/subscriptioncmds.c
> > index a2de57e17b4..c9fac56714e 100644
> > --- a/src/backend/commands/subscriptioncmds.c
> > +++ b/src/backend/commands/subscriptioncmds.c
> > @@ -2718,6 +2718,10 @@ AlterSubscriptionOwner_internal(Relation rel,
> > HeapTuple tup, Oid newOwnerId)
> > form->subowner = newOwnerId;
> > CatalogTupleUpdate(rel, &tup->t_self, tup);
> > + /* Update owner of the conflict log table if it exists */
> > + if (OidIsValid(form->subconflictlogrelid))
> > + ATExecChangeOwner(form->subconflictlogrelid,
> > newOwnerId, true, AccessExclusiveLock);
> > +
> > /* Update owner dependency reference */
> > changeDependencyOnOwner(SubscriptionRelationId,
> > form->oid,
> >
> > [2]
> > -- test to show the ownership is getting changed for the table, but
> > now this user will have access issue on the pg_conflict_log table as
> > this user do not have pg_create_subscription role, I haven't yet
> > checked whether the problems are only related to clt access or there
> > would be issue for other subcription management as well.
> >
> > postgres[557253]=# SELECT relname, relowner FROM pg_class WHERE
> > relname = 'pg_conflict_log_16406';
> > relname | relowner
> > -----------------------+----------
> > pg_conflict_log_16406 | 10
> > (1 row)
> >
> > postgres[557253]=# CREATE USER test;
> > CREATE ROLE
> > postgres[557253]=# ALTER SUBSCRIPTION sub OWNER TO test;
> > ALTER SUBSCRIPTION
> > postgres[557253]=# SELECT relname, relowner FROM pg_class WHERE
> > relname = 'pg_conflict_log_16406';
> > relname | relowner
> > -----------------------+----------
> > pg_conflict_log_16406 | 16410
> > (1 row)
> >
>
> During my testing, I initally found it strange that user without
> pg_create_subscription is allowed to perform ALTER Sub. But that is
> base/head behaviour. Now coming to our use-case around it.
>
> postgres=# create user user1;
> CREATE ROLE
> postgres=# ALTER SUBSCRIPTION sub1 OWNER TO user1;
> ALTER SUBSCRIPTION
> postgres=# SELECT relowner::regrole FROM pg_class WHERE relname =
> 'pg_conflict_log_16392';
> relowner
> ----------
> user1
>
> As Dilip stated, user1 owns the table but cannot access or truncate it.
>
> postgres=> select * from pg_conflict.pg_conflict_log_16392;
> ERROR: permission denied for schema pg_conflict
>
> postgres=> truncate pg_conflict.pg_conflict_log_16392;
> ERROR: permission denied for schema pg_conflict
>
> It looks weird at first, but I think we have exact same beahviour for
> toast table:
>
> --as superuser:
> postgres=# CREATE TABLE user_data (id int, big_text text);
> CREATE TABLE
>
> postgres=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname
> = 'user_data';
> reltoastrelid
> -------------------------
> pg_toast.pg_toast_16399
>
> postgres=# SELECT * FROM pg_toast.pg_toast_16399;
> chunk_id | chunk_seq | chunk_data
> ----------+-----------+------------
> (0 rows)
>
>
> postgres=# alter table user_data owner to user1;
> ALTER TABLE
>
> --toast table ownership got changed:
> postgres=# \dt+ pg_toast.pg_toast_16399
> Schema | Name | Type | Owner |
> ----------+----------------+-------------+-------+-
> pg_toast | pg_toast_16399 | TOAST table | user1 |
>
> As user1:
> postgres=> SELECT * FROM pg_toast.pg_toast_16399;
> ERROR: permission denied for schema pg_toast
>
> So behaviour is similar to our case.
>

I am not sure the case is the same for CLT tables. For allowing change
to a user as an owner of a subscription that doesn't have
pg_create_subscription privilege, won't that be risky? Because now the
background worker will be able to insert in the CLT table whereas for
regular tables, it will still use table_owner's privilege (who
originally created the table) as run_as_owner is false. So, shouldn't
we disallow changing to an owner who doesn't pg_create_subscrition
privilege when a CLT table is associated with a subscription similar
to what we do for the SERVER case. (See comment: * If the subscription
uses a server, check that the new owner has USAGE... in
AlterSubscriptionOwner_internal())

--
With Regards,
Amit Kapila.