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 diegoaugustomol...@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
dump_roles.sql
Description: Binary data
-- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin