Hello, We're using the Azure Database Migration service to migrate our Azure PostgreSQL Single server to a Flexible server. It fails with the following message: "Failed to execute query `ALTER ROLE "dummyAdmin" WITH BYPASSRLS;` with error : 42501: must be superuser to change bypassrls attribute" What is happening here? Any insights? Shouldn't the migration service avoid re-creating / altering the "dummyAdmin"? How can we resolve this issue? Important: we performed similar and even identical (test) migrations a few days ago, and the service did not fail whatsoever (see screenshot) Some more details: Offline method Tried via the portal and via the cli The migration consists of multiple databases, but the postgres database is never selected. We've created the Flexible server via an ARM template (with "dummyAdmin" as the administratorLogin ), and it gets created successfully (beforehand). "dummyAdmin" exists on both the single server as on the flexible server (via administratorLogin created). However, on the single server, "dummyAdmin" has NOBYPASSRLS configured, while on the flexible server "dummyAdmin" has BYPASSRLS configured. We get the error for each database we migrate (so the error message contains the error n-times, for each database once), so it seems to try and alter the role for each database?

Hi Neils, I think the changes made was reverted. Please try to reproduce the issue from your end again.

PostgreSQL Single server to Flexible server fails on BYPASSRLS

Niels Delestinne 25

Hello,

We're using the Azure Database Migration service to migrate our Azure PostgreSQL Single server to a Flexible server.

It fails with the following message: "Failed to execute query `ALTER ROLE "dummyAdmin" WITH BYPASSRLS;` with error : 42501: must be superuser to change bypassrls attribute"

What is happening here? Any insights?

Shouldn't the migration service avoid re-creating / altering the "dummyAdmin"?
How can we resolve this issue?
Important: we performed similar and even identical (test) migrations a few days ago, and the service did not fail whatsoever (see screenshot)

azure_migration1

azure_migration2

Some more details:

Offline method
Tried via the portal and via the cli
The migration consists of multiple databases, but the postgres database is never selected.
We've created the Flexible server via an ARM template (with "dummyAdmin" as the administratorLogin), and it gets created successfully (beforehand).
"dummyAdmin" exists on both the single server as on the flexible server (via administratorLogin created). However, on the single server, "dummyAdmin" has NOBYPASSRLS configured, while on the flexible server "dummyAdmin" has BYPASSRLS configured.
We get the error for each database we migrate (so the error message contains the error n-times, for each database once), so it seems to try and alter the role for each database?

Oury Ba-MSFT 19,826 Reputation points Microsoft Employee

2024-10-16T19:14:33.11+00:00
@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;
Akeem Gbadebo 90 Reputation points

2024-10-16T20:15:10.9033333+00:00

ERROR: must be superuser to alter superusers
Akeem Gbadebo 90 Reputation points

2024-10-16T20:15:56.1966667+00:00

Was there a recent change to the migration tool?
Niels Delestinne 25 Reputation points

2024-10-17T07:24:26.82+00:00
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://learn.microsoft.com/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://learn.microsoft.com/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');
Oury Ba-MSFT 19,826 Reputation points Microsoft Employee

2024-10-17T21:37:06.91+00:00

@Niels Delestinne

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

See: https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-security
Niels Delestinne 25 Reputation points

2024-10-18T07:29:28.0966667+00:00
Thanks, that's some interesting additional information.

Good to know though, today the database migration service is again able to successfully perform the migrations, so there must have been a change that is now reverted again. Do you have any insights on that? Are there "release notes" available for the database migration service?

Accepted answer

Akeem Gbadebo 90 Reputation points

2024-10-17T17:19:20.5+00:00

Hi Neils,

I think the changes made was reverted. Please try to reproduce the issue from your end again.
Please sign in to rate this answer.

1 person found this answer helpful.
Niels Delestinne 25 Reputation points

2024-10-18T07:30:31.8133333+00:00

The exact same migrations that previously worked, then failed are indeed now working again.

So indeed, something must have been reverted / changed again.

Niels Delestinne 25 Reputation points

2024-10-18T07:31:25.8633333+00:00

Thanks for informing us.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

PostgreSQL Single server to Flexible server fails on BYPASSRLS

0 additional answers

Your answer