Question 344831 in PostgreSQL Question posted in
The official documentation can be found here.

What roles does a postgresql database make use of?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search