PGSync failed to start on Azure PostgreSQL single sever/flexible server due to Superuser permission

[vishal-kadam12]

PGSync version: 2.2.1

Postgres version: 11

Elasticsearch version: 7+

Redis version:

Python version: 3.10.4

Problem Description:

We are able to run PGSync with local PostgreSQL database but when we are trying to run it with Azure PostgreSQL (Single server/Flexible) server its failing with error - "pgsync.exc.SuperUserError: 'PG_USER "XXXXXXX" needs to be superuser or have replication role permission to perform this action. Ensure usesuper or userepl is True in pg_user'".

We don't get superuser permission on Azure database because the PostgreSQL superuser attribute is assigned to the azure_superuser, which belongs to the managed service hence do not have access to this role. But we could see Replication(userepl) permission is enabled for our user.

Error Message (if any):

DEBUG:pgsync.plugin: Reloading plugins from package: plugins
Traceback (most recent call last):
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\Scripts\bootstrap", line 70, in <module>
    main()
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\click\core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\click\core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\click\core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\click\core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\Scripts\bootstrap", line 59, in main
    sync: Sync = Sync(
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\pgsync\sync.py", line 94, in __init__
    self.validate(repl_slots=repl_slots)
  File "C:\Users\XXXXX\AppData\Roaming\Python\Python310\site-packages\pgsync\sync.py", line 144, in validate
    raise SuperUserError(
pgsync.exc.SuperUserError: 'PG_USER "XXXXXXX" needs to be superuser or have replication role permission to perform this action. Ensure usesuper or userepl is True in pg_user'

[toluaina]

Does the Postgres user have a Replication role or super user permission? Can you debug this further and confirm. I don't have access to an Azure PostgreSQL database.

[toluaina]

If this is still an issue. I think the azure postgres user name format needs to be of the form

PG_USER=user@host

[vishal-kadam12]

Does the Postgres user have a Replication role or super user permission? Can you debug this further and confirm. I don't have access to an Azure PostgreSQL database.

I have checked but only replication role is available, Azure don't provide superuser access on managed database servers - reference link

https://docs.microsoft.com/en-us/azure/postgresql/single-server/concepts-servers

[vishal-kadam12]

If this is still an issue. I think the azure postgres user name format needs to be of the form

PG_USER=user@host

Issue is not with the configuration details, its related to Superuser permissions on the Azure managed PostgreSQL databases.

I have commented below lines in "sync.py" file to run the PGSync for the raised issue but in this case I had to manually create replication slot, materialized view and triggers.

You can reproduce this issue by disabling superuser role of the PostgreSQL user. It would be great if you could bypass superuser permissions for the azure managed PostgreSQL.

else: if not self.has_permissions( self.engine.url.username, ["usesuper", "userepl"], ): raise SuperUserError( f'PG_USER "{self.engine.url.username}" needs to be ' f"superuser or have replication role permission to " f"perform this action. " f"Ensure usesuper or userepl is True in pg_user" )

self.create_replication_slot(self.__name)

self.drop_replication_slot(self.__name)

[toluaina]

@vishal-kadam12 are you able to provide me with temp access to an Azure instance to investigate further?

[toluaina]

You no longer need to have super user permissions as this is restricted by most cloud service providers in one ways or the other.

You do however need to have permission to:

• create/drop replication slots/
• create/drop triggers on the target database.

[toluaina]

This has been resolved. Its been replaced with a function that actually setsup and tears down a temp replication slot.