Re: Synchronizing slots from primary to standby

Hi,

On Fri, Feb 23, 2024 at 09:43:48AM +0530, shveta malik wrote:
> On Fri, Feb 23, 2024 at 8:35 AM shveta malik  wrote:
> >
> > On Thu, Feb 22, 2024 at 4:35 PM Bertrand Drouvot
> >  wrote:
> > >
> > > Suppose that in synchronize_slots() the query would be:
> > >
> > >     const char *query = "SELECT slot_name, plugin, confirmed_flush_lsn,"
> > >         " restart_lsn, catalog_xmin, two_phase, failover,"
> > >         " database, conflict_reason"
> > >         " FROM pg_catalog.pg_replication_slots"
> > >         " WHERE failover and NOT temporary and 1 = 1";
> > >
> > > Then my comment is to rewrite it to:
> > >
> > >     const char *query = "SELECT slot_name, plugin, confirmed_flush_lsn,"
> > >         " restart_lsn, catalog_xmin, two_phase, failover,"
> > >         " database, conflict_reason"
> > >         " FROM pg_catalog.pg_replication_slots"
> > >         " WHERE failover and NOT temporary and 1 OPERATOR(pg_catalog.=) 1";
> > >
> > > to ensure the operator "=" is coming from the pg_catalog schema.
> > >
> >
> > Thanks for the details, but slot-sync does not use SPI calls, it uses
> > libpqrcv calls. So is this change needed?
> 
> Additionally, I would like to have a better understanding of why it's
> necessary and whether it addresses any potential security risks.

Because one could create say the "=" OPERATOR in their own schema, attach a
function to it doing undesired stuff and change the search_path for the database
the sync slot worker connects to.

Then this new "=" operator would be used (instead of the pg_catalog.= one),
triggering the "undesired" function as superuser.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com