Re: Proposal: Conflict log history table for Logical Replication

On Sat, Sep 20, 2025 at 4:59 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Thu, Sep 18, 2025 at 11:46 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Thu, Sep 18, 2025 at 1:33 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > >
> > > If we compare conflict_history_table with the slot that gets created
> > > with subscription, one can say the same thing about slots. Users can
> > > drop the slots and whole replication will stop. I think this table
> > > will be created with the same privileges as the owner of a
> > > subscription which can be either a superuser or a user with the
> > > privileges of the pg_create_subscription role, so we can rely on such
> > > users.
> >
> > We might want to consider which role inserts the conflict info into
> > the history table. For example, if any table created by a user can be
> > used as the history table for a subscription and the conflict info
> > insertion is performed by the subscription owner, we would end up
> > having the same security issue that was addressed by the run_as_owner
> > subscription option.
> >
>
> Yeah, I don't think we want to open that door. For user created
> tables, we should perform actions with table_owner's privilege. In
> such a case, if one wants to create a subscription with run_as_owner
> option, she should give DML operation permissions to the subscription
> owner. OTOH, if we create this table internally (via subscription
> owner) then irrespective of run_as_owner, we will always insert as
> subscription_owner.

Agreed.

>
> AFAIR, one open point for internally created tables is whether we
> should skip changes to conflict_history table while replicating
> changes? The table will be considered under for ALL TABLES
> publications, if defined? Ideally, these should behave as catalog
> tables, so one option is to mark them as 'user_catalog_table', or the
> other option is we have some hard-code checks during replication. The
> first option has the advantage that it won't write additional WAL for
> these tables which is otherwise required under wal_level=logical. What
> other options do we have?

I think conflict history information is subscriber local information
so doesn't have to be replicated to another subscriber. Also it could
be problematic in cross-major-version replication cases if we break
the compatibility of history table definition. I would expect that the
history table works as a catalog table in terms of logical
decoding/replication. It would probably make sense to reuse the
user_catalog_table option for that purpose. If we have a history table
for each subscription that wants to record the conflict history (I
believe so), it would be hard to go with the second option (having
hard-code checks).

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com