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@xxxxxxxxx
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
-- ES: Consulta que lista los oid de los roles de los cuales depende la base de datos, así como los roles que dependen de éstos primeros.
-- EN: Query that lists the oids of the roles in which the database deppends on as well as those roles which descend from them.
WITH RECURSIVE deps(rol) AS (
SELECT DISTINCT roles.oid
FROM
pg_catalog.pg_database db INNER JOIN
pg_catalog.pg_shdepend dep ON (db.oid = dep.dbid) INNER JOIN
pg_catalog.pg_roles roles ON (dep.refobjid = roles.oid)
WHERE
dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND
ARRAY[db.datname] <@ $1
UNION ALL
SELECT am.member AS rol
FROM
pg_catalog.pg_auth_members am INNER JOIN
deps ON (am.roleid = deps.rol)
)
SELECT * FROM deps
-- ES: La siguiente consulta genera el dump de los roles. La columna 'orden' establece el orden en que deben ejecutarse las sentencias.
-- EN: This query generates the dump of the roles. The column 'orden' sets the order in which the sentencies are to be executed.
SELECT 1::SMALLINT AS orden, 'CREATE ROLE "' || rolname || '" '
CASE WHEN NOT rolsuper THEN 'NO' ELSE '' END || 'SUPERUSER ' || -- 'NOSUPERUSER ' ||
CASE WHEN NOT rolinherit THEN 'NO' ELSE '' END || 'INHERIT ' ||
CASE WHEN NOT rolcreaterole THEN 'NO' ELSE '' END || 'CREATEROLE ' || -- 'NOCREATEROLE ' ||
CASE WHEN NOT rolcreatedb THEN 'NO' ELSE '' END || 'CREATEDB ' || -- 'NOCREATEDB ' ||
CASE WHEN NOT rolcanlogin THEN 'NO' ELSE '' END || 'LOGIN ' ||
'CONNECTION LIMIT ' || rolconnlimit ||
CASE WHEN rolvaliduntil IS NULL THEN '' ELSE E' VALID UNTIL \'' || rolvaliduntil::text || E'\'' END ||
';' AS sentencia
FROM pg_catalog.pg_roles
--WHERE ARRAY[oid] <@ $1
--WHERE oid IN (<LISTA_OID_ROLES>)
UNION
SELECT 2::SMALLINT AS orden, 'ALTER ROLE "' || rolname || '" SET ' || array_to_string(
rolconfig, ';ALTER ROLE "' || rolname || '" SET '
) || ';' AS sentencia
FROM pg_catalog.pg_roles
WHERE rolconfig IS NOT NULL
--AND ARRAY[oid] <@ $1
--AND oid IN (<LISTA_OID_ROLES>)
UNION
SELECT 2::SMALLINT AS orden, 'GRANT "' || rol.rolname || '" TO "' || member.rolname || '"' ||
CASE WHEN am.admin_option THEN 'WITH ADMIN OPTION' ELSE '' END ||
';' AS sentencia
FROM
pg_catalog.pg_auth_members am INNER JOIN
pg_catalog.pg_roles rol ON (am.roleid = rol.oid) INNER JOIN
pg_catalog.pg_roles member ON (am.member = member.oid)
--WHERE rol.oid IN (<LISTA_OID_ROLES>)
ORDER BY orden, sentencia ASC;
-- ES: La siguiente función combina las dos consultas anteriores, y además agrega la contraseña (debe ser ejecutado con un superusuario)
-- EN: This function combines the above queries and adds the password. Requires superuser privileges.
CREATE OR REPLACE FUNCTION dump_db_roles(VARIADIC name[])
RETURNS TABLE(orden smallint, sentencia text) AS
$BODY$
WITH RECURSIVE deps(rol) AS (
SELECT DISTINCT roles.oid
FROM
pg_catalog.pg_database db INNER JOIN
pg_catalog.pg_shdepend dep ON (db.oid = dep.dbid) INNER JOIN
pg_catalog.pg_authid roles ON (dep.refobjid = roles.oid)
WHERE
dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND
ARRAY[db.datname] <@ $1
UNION ALL
SELECT am.member AS rol
FROM
pg_catalog.pg_auth_members am INNER JOIN
deps ON (am.roleid = deps.rol)
)
SELECT 1::SMALLINT AS orden, 'CREATE ROLE "' || r.rolname || '" WITH ' ||
CASE WHEN r.rolpassword IS NOT NULL THEN E'ENCRYPTED PASSWORD \'' || r.rolpassword || E'\'' ELSE '' END ||
CASE WHEN NOT r.rolsuper THEN 'NO' ELSE '' END || 'SUPERUSER ' ||
CASE WHEN NOT r.rolinherit THEN 'NO' ELSE '' END || 'INHERIT ' ||
CASE WHEN NOT r.rolcreaterole THEN 'NO' ELSE '' END || 'CREATEROLE ' ||
CASE WHEN NOT r.rolcreatedb THEN 'NO' ELSE '' END || 'CREATEDB ' ||
CASE WHEN NOT r.rolcanlogin THEN 'NO' ELSE '' END || 'LOGIN ' ||
'CONNECTION LIMIT ' || r.rolconnlimit ||
CASE WHEN r.rolvaliduntil IS NULL THEN '' ELSE E' VALID UNTIL \'' || rolvaliduntil::text || E'\'' END ||
';' AS sentencia
FROM
pg_catalog.pg_authid r INNER JOIN
deps on (r.oid = deps.rol)
UNION
SELECT 2::SMALLINT AS orden, 'ALTER ROLE "' || r.rolname || '" SET ' || array_to_string(
r.rolconfig, ';ALTER ROLE "' || r.rolname || '" SET '
) || ';' AS sentencia
FROM
pg_catalog.pg_authid r INNER JOIN
deps on (r.oid = deps.rol)
WHERE rolconfig IS NOT NULL
UNION
SELECT 2::SMALLINT AS orden, 'GRANT "' || rol.rolname || '" TO "' || member.rolname || '"' ||
CASE WHEN am.admin_option THEN 'WITH ADMIN OPTION' ELSE '' END ||
';' AS sentencia
FROM
pg_catalog.pg_auth_members am INNER JOIN
pg_catalog.pg_authid rol ON (am.roleid = rol.oid) INNER JOIN
pg_catalog.pg_authid member ON (am.member = member.oid) INNER JOIN
deps on (rol.oid = deps.rol)
ORDER BY orden, sentencia ASC;
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION dump_db_roles(name[]) OWNER TO postgres;
-- ES: La siguiente función es igual que la anterior, pero no muestra las contraseñas y puede ser ejecutado por cualquier rol.
-- EN: This function is the same as the previous one except that it doesn't show the passwords and thus can be executed by any role.
CREATE OR REPLACE FUNCTION unpriv_dump_db_roles(VARIADIC name[])
RETURNS TABLE(orden smallint, sentencia text) AS
$BODY$
WITH RECURSIVE deps(rol) AS (
SELECT DISTINCT roles.oid
FROM
pg_catalog.pg_database db INNER JOIN
pg_catalog.pg_shdepend dep ON (db.oid = dep.dbid) INNER JOIN
pg_catalog.pg_roles roles ON (dep.refobjid = roles.oid)
WHERE
dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND
ARRAY[db.datname] <@ $1
UNION ALL
SELECT am.member AS rol
FROM
pg_catalog.pg_auth_members am INNER JOIN
deps ON (am.roleid = deps.rol)
)
SELECT 1::SMALLINT AS orden, 'CREATE ROLE "' || r.rolname || '" '
CASE WHEN NOT r.rolsuper THEN 'NO' ELSE '' END || 'SUPERUSER ' ||
CASE WHEN NOT r.rolinherit THEN 'NO' ELSE '' END || 'INHERIT ' ||
CASE WHEN NOT r.rolcreaterole THEN 'NO' ELSE '' END || 'CREATEROLE ' ||
CASE WHEN NOT r.rolcreatedb THEN 'NO' ELSE '' END || 'CREATEDB ' ||
CASE WHEN NOT r.rolcanlogin THEN 'NO' ELSE '' END || 'LOGIN ' ||
'CONNECTION LIMIT ' || r.rolconnlimit ||
CASE WHEN r.rolvaliduntil IS NULL THEN '' ELSE E' VALID UNTIL \'' || rolvaliduntil::text || E'\'' END ||
';' AS sentencia
FROM
pg_catalog.pg_roles r INNER JOIN
deps on (r.oid = deps.rol)
UNION
SELECT 2::SMALLINT AS orden, 'ALTER ROLE "' || r.rolname || '" SET ' || array_to_string(
r.rolconfig, ';ALTER ROLE "' || r.rolname || '" SET '
) || ';' AS sentencia
FROM
pg_catalog.pg_roles r INNER JOIN
deps on (r.oid = deps.rol)
WHERE rolconfig IS NOT NULL
UNION
SELECT 2::SMALLINT AS orden, 'GRANT "' || rol.rolname || '" TO "' || member.rolname || '"' ||
CASE WHEN am.admin_option THEN 'WITH ADMIN OPTION' ELSE '' END ||
';' AS sentencia
FROM
pg_catalog.pg_auth_members am INNER JOIN
pg_catalog.pg_roles rol ON (am.roleid = rol.oid) INNER JOIN
pg_catalog.pg_roles member ON (am.member = member.oid) INNER JOIN
deps on (rol.oid = deps.rol)
ORDER BY orden, sentencia ASC;
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION unpriv_dump_db_roles(name[]) OWNER TO postgres;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
