PostgreSQL Single server to Flexible server fails on BYPASSRLS

@Niels Delestinne Thank you for reaching out.

BYPASSRLS can only be run by azure_su only.

Please check if dummyAdmin role has superuser privileges on the source database before starting the migration. You can do this by running the following SQL command:

ALTER ROLE dummyAdmin WITH SUPERUSER;

Changing the BYPASSRLS attribute requires superuser privileges, which “dummyAdmin” lacks on the target server.

Make “dummyAdmin” a Superuser:

On the target Flexible server, grant superuser privileges to “dummyAdmin”:

ALTER USER "dummyAdmin" WITH SUPERUSER;

ERROR: must be superuser to alter superusers

Was there a recent change to the migration tool?

Hi @Oury Ba-MSFT

Thanks for reaching out.

I fail to understand how I can make "dummyAdmin" a SUPERUSER, as Azure blocks this off entirely.

• The only superusers that exist are "azure_superuser" on the single server and "azuresu" on the flexible server.
• So, those are the only users / roles who grant SUPERUSER to another user
• However, it's not possible to login as "azure_superuser" or "azuresu", right?
• This is all by design, as Azure does not want to grant superuser access to any users. You will get the "ERROR: must be superuser to alter superusers" when you try, as we're unable to login as a superuser.

I honestly believe there was a recent change to the migration tool, as we have an identical state / scenario / situation which worked before, but no longer works a few days later.

• Can this be confirmed?

For example, when instead of using the migration service, pg_dumpall is used, the documentation clearly describes how NOBYPASSRLS attributes have to be removed manually: https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/how-to-migrate-using-dump-and-restore?tabs=psql#cleaning-up-the-roles-dump

• Removing attributes that can be set only by superusers: If migrating to an environment where you don't have superuser privileges, remove attributes like NOSUPERUSER and NOBYPASSRLS from the roles dump.
• Excluding service-specific users: Exclude Single Server service users, such as azure_superuser or azure_pg_admin. These are specific to the service and will be created automatically in the new environment.

We have no view or impact on how the migration services deals with these attributes. It SEEMS that it handled it differently a few days before than it does now...

Additionally, according to the migration-cli docs, their are requirements specified for the admin user ("dummyAdmin"). It needs to be of the azure_pg_admin group and then some more: https://zcusa.951200.xyz/en-us/azure/postgresql/migrate/migration-service/tutorial-migration-service-single-to-flexible?tabs=cli%2Coffline#create-a-migration-using-the-azure-cli (see the sourceServerUserName and targetServerUserName options)

• sourceServerUserName: [...]This user should have the necessary privileges and ownership of the database objects involved in the migration and should be a member of the azure_pg_admin role.
• targetServerUserName: [...] This user should be a member of azure_pg_admin, pg_read_all_settings, pg_read_all_stats,pg_stat_scan_tables roles and should have the Create role, Create DB attributes.

On the single-server we verified this with the following query, it's all in order.
SELECT r1.rolname AS member, r2.rolname AS roleFROM pg_auth_members mJOIN pg_roles r1 ON m.roleid = r1.oidJOIN pg_roles r2 ON m.member = r2.oidWHERE r2.rolname = 'dummyadmin' AND r1.rolname = 'azure_pg_admin';

On the flexible-server extra requirements were specified, hence the extended query, it's all in order.
SELECT r1.rolname AS member, r2.rolname AS roleFROM pg_auth_members mJOIN pg_roles r1 ON m.roleid = r1.oidJOIN pg_roles r2 ON m.member = r2.oidWHERE r2.rolname = 'dummyadmin'AND r1.rolname IN ('azure_pg_admin', 'pg_read_all_settings', 'pg_read_all_stats', 'pg_stat_scan_tables');

@Niels Delestinne

What version of Azure PostgreSQL are you suing. This might depend on what version of PG you are migrating to.

See: https://zcusa.951200.xyz/azure/postgresql/flexible-server/concepts-security