Pglogical superuser requirements

[arinib]

The public documentation of pglogical says that pglogical setup and administration requires superuser privileges. It also mentions that it may later be extended to more granular privileges. We have a usecase where we want to setup replication as a regular user with replication privilege. For security reasons, we do not want to provide give superuser privilege to a replication user. Is there a way to workaround the superuser requirement? Thanks, Arini

[luss]

The replication privilege is pretty darn powerful. If you don't mind me asking... what's the use case where a particlar user must have replication privs but can't have all super user priv's. What specific Super user priv's are you not wanting a "pglogical_superuser" to have?

[srl295]

postgres14 seems to have made some improvements (link if I could find them) where the REPLICATION privilege can perform more of the replication related activities.

However, pglogical as a subscriber insists on SET session_replication_role = "replica"; which seems to be reserved for SUPERUSER.

pglogical is undeterred even when I ALTER ROLE myrole SET session_replication_role = "replica"; (i.e. the SET would have no effect)

[tbecks]

In hosted platforms (in this case Azure), the super admin role is restricted and only available to the service, without access to a super user is it possible to setup a subscription?

[srl295]

In hosted platforms (in this case Azure), the super admin role is restricted and only available to the service, without access to a super user is it possible to setup a subscription?

@tbecks do you get REPLICATION access?

[tbecks]

No access to REPLICATION, if i try to grant that role with an admin user I get: "ERROR: only superusers can grant role "replication""

[luss]

In the AWS environment the restrictions are similar, you have access to and RDS super user, who has almost 100% of the privs that you get with the postgres superuser. Ask you Azure tech support, unless they just added pglogical and you are the first to try and use it... they have to give you access to an id on your db that can do the needful.

On Wed, Aug 3, 2022 at 5:59 PM Tyler Becker @.***> wrote:

No access to REPLICATION, if i try to grant that role with an admin user I get: "ERROR: only superusers can grant role "replication""

— Reply to this email directly, view it on GitHub https://github.com/2ndQuadrant/pglogical/issues/218#issuecomment-1204528901, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHQPWLRM72YR3RKHU2TVXLTSZANCNFSM4IWSCVRQ . You are receiving this because you commented.Message ID: @.***>

[tbecks]

The resolution is to alter the user to provide the replication privilege (not grant the role which needs super user). So the command would be: "ALTER ROLE myUser replicaiton".