Re: Proposal: Conflict log history table for Logical Replication

On Thu, May 7, 2026 at 8:26 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Wed, May 6, 2026 at 7:34 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Wed, May 6, 2026 at 6:28 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > >
> > > On Wed, May 6, 2026 at 4:31 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > >
> > > > On Wed, May 6, 2026 at 3:01 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Wed, May 6, 2026 at 9:24 AM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > Few comments:
> > > > > > 1) Currently we allow renaming of pg_conflict schema, this might be ok
> > > > > > as we allow other sysem schema like pg_catalog and pg_toast also.
> > > > > > postgres=# alter schema pg_conflict rename to test_conflict;
> > > > > > ALTER SCHEMA
> > > > > >
> > > > >
> > > > > I agree that we allow renaming other schemas including pg_toast, but I
> > > > > am not sure if this is consciously made decision, see BUG #18281 ast
> > > > > [1]. I don't favour allowing renaming pg_conflict for 2 reasons:
> > > > >
> > > > > 1) Because Postgres explicitly blocks renaming schemas to a name
> > > > > starting with 'pg_'. If an admin accidentally renames 'pg_conflict' to
> > > > > something else, they are permanently locked out from renaming it back.
> > > > >
> > > > > 2) While the core worker might survive a rename via OID lookups;
> > > > > external scripts, extensions, and monitoring tools will likely
> > > > > hardcode the 'pg_conflict' string. If the schema is renamed, these
> > > > > tools will fail.
> > > > >
> > > >
> > > > I think we shouldn't go out of our way to disallow superusers to
> > > > rename pg_conflict schema similar to other cases. We can try to
> > > > prevent hard-coding schema names where possible but not sure we can
> > > > guarantee that nothing related to pg_conflict schema won't break as
> > > > shown by you in the following similar case for pg_conflict.
> > > >
> > > > > One such example of scripts breaking is present event in Postgres. I
> > > > > did the following, and most of psql commands started failing after
> > > > > that due to hard-coded pg_catalog name in them.
> > > > >
> > > > > postgres=# alter schema pg_catalog rename to catalog_new;
> > > > > ALTER SCHEMA
> > > > >
> > > > > postgres=# \d catalog_new.*
> > > > > ERROR: relation "pg_catalog.pg_class" does not exist
> > > > > LINE 5: FROM pg_catalog.pg_class c
> > > > >
> > > > > [1]: https://www.postgresql.org/message-id/flat/18281-5b1b6c5991d345aa%40postgresql.org
> > >
> > > I can see pg_toast and pg_catalog schema also hard coded in couple of
> > > places e.g.
> > >
> > > listPartitionedTables()
> > > {
> > > if (!pattern)
> > > appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
> > > " AND n.nspname !~ '^pg_toast'\n"
> > > " AND n.nspname <> 'information_schema'\n");
> > > }
> > >
> > > I will analyze which all places we are hardcoding, I think on server
> > > side code we can easily avoid but from client side e.g. describe we
> > > might need to invent a way to identify the schema name, or we might
> > > have to store it somewhere in pg_subscription etc, I don't think we
> > > should go that route.
> >
> > Here is updated patch set
> >
> > Open comments:
> > 1. Analyze and avoid hardcoding the 'pg_conflict' schema name wherever possible
> > 2. change the way we display clt in \dRs+
> > 3. Transfer the clt ownership when subscription ownership has change
> > (Note: I have coded a poc for this but still checking whether it works
> > in all cases)
> >
> > I will send the revised version by end of this week after fixing these
> > open comments as well.
>
> 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. IMO, at best we can document it
well, something like:

Note: Conflict log tables reside in the restricted pg_conflict schema.
To query or truncate these logs, a user must be a superuser or have
the pg_create_subscription privilege. A subscription owner lacking
these privileges will not be able to access or purge conflict log
tables.

Thoughts?

thanks
Shveta