Problem with pg_dumpall in PostgreSQL Flexible Server

Question

I have an Azure Database for PostgreSQL flexible server (version 12).

I'm trying to upgrade using the official docs (the goal is to upgrade to 14).

The first step is to dump the roles

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

This fails with

pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
pg_dumpall: error: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

I'm running it under an admin username. What am I doing wrong? How to actually dump the roles so I could create them on another server?

Answer 1

Hi, @Artur Drobinskiy Welcome to the Microsoft Q&A platform, thanks for posting the question.

I understand that you are facing an issue while migrating roles using pg-dump.

You need to exclude the azuresu and create only roles that need to be transferred, please give it a try and also check the best practice document on pg_dump.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-pgdump-restore#best-practices-for-pg_dump

please let me know if you still have any issues.

Regards

Geetha

Answer 2

Sorry I don't completely understand your answer.

You are referring to best practices on pgdump, but I don't see anything there related to pg_dumpall or to the roles.

You need to exclude the azuresu and create only roles that need to be transferred

Could you please help me with exact syntax? Because in the docs for pg_dumpall I don't see anything related to excluding a particular role (azuresu as you are suggesting).

And basically I just took what the official doc says and it doesn't work (so my guess is that official docs should be corrected as well, or at least it should be stated, that the guide doesn't apply to PostgreSQL Flexible Server).

Or are you saying, that I should just manually create the roles that I need in the target server, and also manually transfer the permissions? In my case it's not realistic, since roles are created dynamically, and there's quite a number of them (~1000).

pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

Answer 3

Also having the same issue and the response posted here is as vague as most answers I get when I put in a support ticket. It's pretty simple, we can't do the "backup globals" because we get the errors, how do we remove the azuresu references when it won't even backup. Silly responses MS.

Answer 4

I'm having the same issue aswell that wasn't occuring on postgresql single server.

For information since no answer here actually addresses what happens, the command fails because the admin user on postgresql flexible servers is not allowed to export passwords.

If you don't need to export the passwords, you can add this argument to your pg_dumpall command

--no-role-password

It will then succeed in dumping the roles from postgresql flexible server That being said, it would be nice to get some words from MS on this, since it used to work on postgresql single server. Is it by design that the admin user can no longer dump passwords ? If not are there plans to update the admin user permissions to include this one ? Since we're not super user we can't grant it ourselves.