Re: Proposal: Conflict log history table for Logical Replication

On Mon, May 4, 2026 at 4:59 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sat, May 2, 2026 at 2:40 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >
> > On Fri, May 1, 2026 at 7:16 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > >
> > > 4. pg_conflict is the catalog schema and as Nisha reported,
> > > non-superusers aren't allowed to access the objects within it. Because
> > > of this, SELECT, DELETE, and TRUNCATE are disallowed even for the
> > > subscription owner if that owner is a non-superuser. I am working on
> > > the fix.
> >
> > While analyzing this, I realized that the schema ACL check happens
> > very early in analyze phase [1]. I'm not sure if we can bypass the
> > subscription owner from this check at that stage without implementing
> > a hacky solution. Another option is to remove restrictions from the
> > pg_conflict schema for all users and keep only table-level
> > restrictions within that schema. I am exploring how to implement this.
> >
>
> How about if we grant usage privilege on pg_conflict schema to
> pg_create_subscription role and then allow only select, delete,
> truncate to table_owners on tables in pg_conflict schema? Internally
> the apply_worker can still make inserts to clt table in pg_conflict
> schema similar to what we do for toast tables.

I am still testing, but I quickly prototyped this approach and basic
things seem to be working.

<Test case Start>
dilipkumarb(at)dilipkumarb:~/PG/install$ psql -p 5433
postgres[3614939]=# CREATE USER dilip LOGIN ;
GRANT pg_create_subscription TO dilip;
GRANT ALL ON DATABASE postgres TO dilip;
postgres[3614939]=# \q

-- Connect to nonsuper user--
dilipkumarb(at)dilipkumarb:~/PG/install$ psql -p 5433 -U dilip
postgres[3615002]=> CREATE SUBSCRIPTION regress_clt_perm_test CONNECTION
'dbname=regress_doesnotexist password=pass' PUBLICATION testpub WITH
(connect = false, conflict_log_destination = 'table');

postgres[3615002]=> select * from pg_conflict.pg_conflict_log_164
pg_conflict.pg_conflict_log_16406 pg_conflict.pg_conflict_log_16412
postgres[3615002]=> select * from pg_conflict.pg_conflict_log_16412;
relid | schemaname | relname | conflict_type | remote_xid |
remote_commit_lsn | remote_commit_ts | remote_origin |
replica_identity | remote_tuple | local
_conflicts
-------+------------+---------+---------------+------------+-------------------+------------------+---------------+------------------+--------------+------
-----------
(0 rows)

postgres[3615002]=> delete from pg_conflict.pg_conflict_log_16412;
DELETE 0
postgres[3615002]=> TRUNCATE pg_conflict.pg_conflict_log_16412;
TRUNCATE TABLE
postgres[3615002]=> \q
dilipkumarb(at)dilipkumarb:~/PG/install$ psql -p 5433
psql (19devel)
Type "help" for help.

--Create another user to test non subscription owner which has
pg_create_subscription role granted do not have access on another
subscription's conflict log tables
postgres[3615293]=# CREATE USER dilip1 LOGIN;
GRANT pg_create_subscription TO dilip1;
GRANT ALL ON DATABASE postgres TO dilip1;
dilipkumarb(at)dilipkumarb:~/PG/install$ psql -p 5433 -U dilip1
psql (19devel)
Type "help" for help.

postgres[3615370]=> select * from pg_conflict.pg_conflict_log_16412;
ERROR: 42501: permission denied for table pg_conflict_log_16412
LOCATION: aclcheck_error, aclchk.c:2813
postgres[3615370]=> delete from pg_conflict.pg_conflict_log_16412;
ERROR: 42501: permission denied for table pg_conflict_log_16412
LOCATION: aclcheck_error, aclchk.c:2813

<Test Case Ends>

PFA, poc patch for the same.

--
Regards,
Dilip Kumar
Google