What roles does a postgresql database make use of?

Question

I've created a backup of a database on one computer

postgres@machine1$ pg_dump mydb > mydb.dump.sql

and then, from a second computer, having created a mydb database, I restore it from the dump

postgres@machine2$ psql -d mydb -f mydb.dump.sql 

The problem is that the restoration throws an error when it encounters a role that doesn't already exist on the target system.

Is there some way I can get a list of roles, on the source system, that mydb make some use of? Then, if it's not too many, I'll manually create any missing ones on the target before restoring the database.

I don't want all the roles on the source system as available via psql \du, pg_roles or pg_dumpall -r.The source system will potentially contain many more roles than mydb makes use of. I only want the roles referenced in some way by mydb. Each role has an id, I can see them via pg_roles, and I'm assuming that permissions granted to roles within any particular database reference those ids.

I'm using postgresql 12.

Answer 1

Roles are global to the database cluster you can use:

pg_dumpall

-r --roles-only

Dump only roles, no databases or tablespaces

This will extract a file of roles in a database cluster using a command like:

pg_dumpall -r -f database_roles.sql

You would need to add appropriate host, user and port parameters as needed. The user needs to have sufficient privileges to read the pg_authid which contains passwords.

Answer 2

There is no simple way to determine which role is used in which database.

You could connect to the postgres database and run

BEGIN;  -- transaction
DROP ROLE maxi;
ROLLBACK;  -- undo changes

for each role.

If there is no error, the role doesn't own any objects and has no privileges granted in any database.

Otherwise, the error message will tell you which database have objects with dependencies on the role.