27

Where are roles stored in a cluster, and how do I dump them?

I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:

psql:mydump.sql:3621: ERROR:  role "myrole" does not exist 

So apparently the dump of my db does not include roles. I tried dumping the 'postgres' db, but I don't see the roles there either.

Do I need to use pg_dumpall --roles-only ?

Postgresql versions 8.4.8 and 9.1.4 OS: Ubuntu 11.04 Natty

37

From the CREATE ROLE documentation:

Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

Since pg_dump dumps a single database, you can't extract roles with that utility. The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only desired roles will be created in the new cluster.

Roles are stored in the pg_authid catalog, which is physically stored in the data/global/ subfolder of a PostgreSQL installation, together with the other cluster-wide tables. You can query the contents of pg_authid through the pg_roles view.

NOTE: you will need superuser rights to dump the roles. Otherwise, you'd get a permission denied on SELECT on pg_authid - and even when a superuser grants SELECT rights, you'd get the same error. In this case, however, you can list the roles by querying pg_authid directly, COPY it to a file and roll some magic to create the necessary CREATE ROLE and ALTER ROLE statements.

  • Can you add how to restore all roles (i.e. no filtering needed)? – Ethan Furman Oct 15 '16 at 16:52
  • 1
    to restore all roles, just copy and paste the output of pg_dumpall --roles-only into the desired psql shell. Or specific CREATE ROLE and ALTER ROLE lines – Fernando Fabreti Aug 7 '18 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.