Re: Proposal: Conflict log history table for Logical Replication - Mailing list pgsql-hackers

On Thu, Sep 18, 2025 at 1:33 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Sun, Sep 14, 2025 at 12:23 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Sat, Sep 13, 2025 at 6:16 AM Bharath Rupireddy
> > <bharath.rupireddyforpostgres@gmail.com> wrote:
> >
> > Thanks for the feedback Bharath
> >
> > > On Fri, Sep 12, 2025 at 3:13 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > > >
> > > > I was looking into another thread where we provide an error table for
> > > > COPY [1], it requires the user to pre-create the error table. And
> > > > inside the COPY command we will validate the table, validation in that
> > > > context is a one-time process checking for: (1) table existence, (2)
> > > > ability to acquire a sufficient lock, (3) INSERT privileges, and (4)
> > > > matching column names and data types. This approach avoids concerns
> > > > about the user's DROP or ALTER permissions.
> > > >
> > > > Our requirement for the logical replication conflict log table
> > > > differs, as we must validate the target table upon every conflict
> > > > insertion, not just at subscription creation. A more robust
> > > > alternative is to perform validation and acquire a lock on the
> > > > conflict table whenever the subscription worker starts. This prevents
> > > > modifications (like ALTER or DROP) while the worker is active. When
> > > > the worker gets restarted, we can re-validate the table and
> > > > automatically disable the conflict logging feature if validation
> > > > fails.  And this can be enabled by ALTER SUBSCRIPTION by setting the
> > > > option again.
> > >
> > > Having to worry about ALTER/DROP and adding code to protect seems like
> > > an overkill.
> >
> > IMHO eventually if we can control that I feel this is a good goal to
> > have.  So that we can avoid failure during conflict insertion.  We may
> > argue its user's responsibility to not alter the table and we can just
> > check the validity during create/alter subscription.
> >
>
> 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.

Regards,

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