Role migration command does not work

[wilsonhipolito]

When trying to dump the roles as suggested by the documentation using the command below, Postgres (I am using a PostgreSQL flexible server) throws a permission error for the table pg_authid. I also tried adding --role=azure_pg_admin but it also doesn't work.

Broken command:

pg_dumpall -r --host=mySourceServer --port=5432 --username=myUser --database=mySourceDB > roles.sql

Screenshot of the error:

---

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

• ID: b63959af-0e54-22ff-b1cc-d63ee055fddd
• Version Independent ID: bde862d7-793a-a17c-03c6-327e46305535
• Content: Upgrade using dump and restore - Azure Database for PostgreSQL
• Content Source: articles/postgresql/single-server/how-to-upgrade-using-dump-and-restore.md
• Service: postgresql
• Sub-service: single-server
• GitHub Login: @AwdotiaRomanowna
• Microsoft Alias: alkuchar

[Oury-MSFT]

@wilsonhipolito Thank you for your feedback. We are looking into this and will let you know.

[Oury-MSFT]

wilsonhipolito I have tried the same but not able to connect. @AwdotiaRomanowna any input on this ?

[AwdotiaRomanowna]

Hi @wilsonhipolito, thank you for the feedback, indeed in Flexible server access to pg_authid table has been revoked from end users. You will be able to dump roles names only (without passwords). Please add --no-role-passwords option to your pg_dumpall command, like that: pg_dumpall -r --no-role-passwords --host=mySourceServer --port=5432 --username=myUser --database=mySourceDB > roles.sql

I updated the documentation, should be live soon.

@Oury-MSFT please read your error message - you have not specified the database name so psql is trying to connect to database that does not exist. This is simply user error

[AwdotiaRomanowna]

Please close this one, all the changes are in the docs

[wilsonhipolito]

Awesome, thanks @AwdotiaRomanowna