Обсуждение: 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
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
Вложения
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>
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