Thread: pg_replication_origin_session_setup and superuser

pg_replication_origin_session_setup and superuser

From
Zohar Gofer
Date:

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

Re: pg_replication_origin_session_setup and superuser

From
Michael Paquier
Date:
On Mon, Feb 15, 2021 at 09:37:53AM +0000, Zohar Gofer wrote:
> 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.

PostgreSQL 14 will remove those hardcoded superuser checks.  Please
see this thread:
https://www.postgresql.org/message-id/CAPdiE1xJMZOKQL3dgHMUrPqysZkgwzSMXETfKkHYnBAB7-0VRQ@mail.gmail.com
And its related commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cc072641d41c55c6aa24a331fc1f8029e0a8d799

While the default is still superuser-only, it becomes possible to
grant access to this stuff to other roles that have no need to be
superusers.
--
Michael

Attachment

RE: pg_replication_origin_session_setup and superuser

From
Zohar Gofer
Date:
Thanks. This seems to be the fix we need.
Would it be possible to push it to previous versions? 12 or 13?

Zohar

-----Original Message-----
From: Michael Paquier <michael@paquier.xyz>
Sent: Tuesday, February 16, 2021 2:52 AM
To: Zohar Gofer <Zohar.Gofer@amdocs.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pg_replication_origin_session_setup and superuser

On Mon, Feb 15, 2021 at 09:37:53AM +0000, Zohar Gofer wrote:
> 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.

PostgreSQL 14 will remove those hardcoded superuser checks.  Please see this thread:
https://www.postgresql.org/message-id/CAPdiE1xJMZOKQL3dgHMUrPqysZkgwzSMXETfKkHYnBAB7-0VRQ@mail.gmail.com
And its related commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cc072641d41c55c6aa24a331fc1f8029e0a8d799

While the default is still superuser-only, it becomes possible to grant access to this stuff to other roles that have
noneed to be superusers. 
--
Michael
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms
ofService, which you may review at https://www.amdocs.com/about/email-terms-of-service
<https://www.amdocs.com/about/email-terms-of-service>




Re: pg_replication_origin_session_setup and superuser

From
Michael Paquier
Date:
On Tue, Feb 16, 2021 at 07:54:32AM +0000, Zohar Gofer wrote:
> Thanks. This seems to be the fix we need.
> Would it be possible to push it to previous versions? 12 or 13?

New features don't go into stable branches, only bug fixes do.  And
this is not a bug fix, but a feature.
--
Michael

Attachment