Hi, I've been searching a way to fully restore a database with it's
roles. I've got a cluster with many db's in which I don't have many
privileges in addition. I need to migrate that database to a cluster
of our possesion. I've been reading the docs in the section of the
catalogs to figure out how all this works. I came to the reasoning
that the roles needed for a database to properly work are the
following:

1) Those who are explicitly part of the database through ownership or acl.
2) In the membership graph, those who descend into membership (members
of roles in (1) plus the members of these and so on).

Why not include the upper part of the graph (parent roles)? well,
unless any of these are superusers any other permission is irrelevant
for the purpose of that single database. In fact, that's a TODO which
is not needed for my particular case. So my solution consists of the
following pseudocode:

1) Get the roles explicitly involved in the database.
2) Recursively search for members of these.
3) Dump them all ordered ("CREATE ROLE" before "ALTER ROLE" and before "GRANT").

I wrote two functions for this: the first one is a privileged one and
the second is not. I did the second because I can't access to the
passwords of the users in the server I'm migrating and asking the
admins to do this is such a pain. After the restoration I will reset
the application users' passwords and securely transmit them to them.
Additionally, the functions can dump any number of databases.
In the attachment you'll find two queries which are later combined and
slightly modified to compose the functions. One of the main queries is
a "WITH RECURSIVE ..." query, so the PostgreSQL version should be 8.3
or above I guess. I'm using 8.4. The cluster which I'm migrating from
is 8.1, so I had to take a workaround in order for this to work: dump
the needed catalog tables (pg_auth_members, pg_authid, pg_database and
pg_shdepend. Change pg_authid for pg_roles for the unprivileged case);
restore the tables in some schema of some database in an 8.4 cluster;
and finally modify the functions to query the specified schema instead
of pg_catalog.

I provide this code in the hope that it will be useful to someone, but
I also expect some comments and/or corrections.

--
Diego Augusto Molina
diegoaugustomolina(at)gmail(dot)com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html