pg_replication_origin_session_setup and superuser

Hi,

Problem description:
While working on a homegrown limited solution to replace (a very limited set of) golden gate capabilities we have created a CDC solution using the WAL capabilities.

The data flows like this:
PG1 --> Debezium(wal2json) --> Kafka1 --> MM2 --> Kafka2 --> Kafka Connect Sink Plugin --> PG2
And we wanted also changes to flow the other direction as well:
PG1 <-- Kafka Connect Sink Plugin <-- Kafka1 <-- MM2 <-- Kafka2 <-- Debezium(wal2json) <-- PG2

Where our homegrown "Kafka Connect Sink Plugin" will do manipulations on replicated data.

How do we prevent cyclic replication in this case?

Looking around I came across this nice explanation:

https://www.highgo.ca/2020/04/18/the-origin-in-postgresql-logical-decoding/

Using the origin to filter records in the wal2json works perfect once we set up an origin.

But, calling pg_replication_origin_session_setup requires superuser privileges. Our intent is to make this call when starting a write session in the "Kafka Connect Sink Plugin" that writes data to PG.

The logical replication is usually done on the replication channel rather than the normal user space session so I see the reason for requiring superuser. This is aligned with the documentation, so this is not a bug per se.

In my mind the requirement for superuser is too strong. I think that requiring privileges of a replication user is more suitable. This way we can require that only a user with replication privileges will actually do replication, even if this is not really a replication.

Taking it one step further, I see no reason why stamping a session with origin requires elevated privileges at all, but don't know enough about this.

Zohar Gofer

This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service <https://www.amdocs.com/about/email-terms-of-service>