Short description:
* Need: migration of database, with roles relevant to it.
* "From" server: pg 8.1; no superuser access.
* "To" server: pg 8.4; full access.
* Database in question: depends on few roles, mainly group roles used
to set permission; there are other roles (login roles) which are
members of these and are intended for the users of the system.
* Proposed solution: migrate the database and reset users passwords.
Long description:
I've been searching a way to achieve this. Having not found it, I
proposed the following solution:
1) Get the roles which are explicitly part of the database through
ownership or acl.
2) Get the roles which descend into the membership graph (members of
roles in (1) plus the members of these and so on).
3) Dump them all (which includes the statements "CREATE ROLE", "ALTER
ROLE" and "GRANT"'s referred to role membership).
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.
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 the roles of any number of
databases in the cluster.
In the attachment you'll find two queries which are later combined and
slightly modified to compose the functions. One of the queries is a
"WITH RECURSIVE ..." query, so the PostgreSQL version should be 8.3 or
above I guess. Given the case that the cluster which I'm migrating
from is 8.1, I had to take a workaround in order for this to work:
1) Dump the needed catalog tables: pg_auth_members, pg_authid for
privileged role OR pg_roles for unprivileged role (which is in fact a
view), pg_database and pg_shdepend.
2) Restore the tables in some user-schema of some database in the 8.4 cluster.
3) 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.
NOTICE: the privileged function was already successfully tested in a
production environment between two 8.4 servers.
--
Diego Augusto Molina
diegoaugustomolina@gmail.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