  -- 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;

  -- 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 of 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;

/*
  -- ES: La creación de las tablas debe hacerse en un clúster compatible con el código (p. ej. 8.4)
  -- EN: the creation of the tables must be done in a compatible cluster (e.g. 8.4)
CREATE TABLE public.pg_roles(
  rolname name NOT NULL,
  rolsuper boolean NOT NULL,
  rolinherit boolean NOT NULL,
  rolcreaterole boolean NOT NULL,
  rolcreatedb boolean NOT NULL,
  rolcanlogin boolean NOT NULL,
  rolconnlimit integer NOT NULL,
  rolvaliduntil timestamp with time zone,
  rolconfig text[],
  oid oid not null
)WITH (OIDS=FALSE);

CREATE TABLE public.pg_auth_members(
  roleid oid NOT NULL,
  member oid NOT NULL,
  admin_option boolean NOT NULL
)WITH (OIDS=FALSE);

CREATE TABLE public.pg_database(
  datname name NOT NULL,
  oid oid not null
)WITH (OIDS=FALSE);

CREATE TABLE public.pg_shdepend(
  dbid oid NOT NULL,
  refclassid oid NOT NULL,
  refobjid oid NOT NULL
)WITH (OIDS=FALSE);

  -- ES: Esta consulta genera un dump en cualquier servidor PG de las tablas del sistema necesarias, conectado como cualquier rol. Devuelve una sola columna, donde cada tupla es un INSERT en alguna de las tablas de arriba. Estos INSERTs deben ejecutarse en la base de datos que alberga las tablas de arriba (p. ej. 8.4).
  -- EN: This query generates a dump of the required system tables in any PG cluster, logged in as any role. There's only one column, where each tuple is an INSERT into any of the above tables. These INSERTs shall be executed in the database holding the above tables.
select E'INSERT INTO public.pg_roles (rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin,rolconnlimit,rolvaliduntil,rolconfig,oid) VALUES (\'' ||
    rolname || E'\', ' ||
    case when rolsuper then E'\'t\'' else E'\'f\'' end || ', ' ||
    case when rolinherit then E'\'t\'' else E'\'f\'' end || ', ' ||
    case when rolcreaterole then E'\'t\'' else E'\'f\'' end || ', ' ||
    case when rolcreatedb then E'\'t\'' else E'\'f\'' end || ', ' ||
    case when rolcanlogin then E'\'t\'' else E'\'f\'' end || ', ' ||
    case when rolconnlimit is not null then rolconnlimit::text else 'NULL' end || ', ' ||
    case when rolvaliduntil is not null then E'\'' || rolvaliduntil::text || E'\'' else 'NULL' end || ', ' ||
    case when rolconfig is not null then E'\'{' || array_to_string(rolconfig, ',') || E'}\'' else 'NULL' end || ', ' ||
    oid ||
    ');'
  from pg_catalog.pg_roles
union
select 'INSERT INTO public.pg_auth_members (roleid,member,admin_option) VALUES (' ||
    roleid::TEXT || ',' || member::TEXT || ',' || case when admin_option then E'\'t\'' else E'\'f\'' end || ');'
  from pg_catalog.pg_auth_members
union
select E'INSERT INTO public.pg_database (datname,oid) VALUES (\'' || datname || E'\',' || oid::TEXT || ');'
  from pg_catalog.pg_database
union
select 'INSERT INTO public.pg_shdepend (dbid,refclassid,refobjid) VALUES (' || dbid::TEXT || ',' || refclassid::TEXT || ',' || refobjid || ');'
  from pg_catalog.pg_shdepend
  where refclassid = 'pg_catalog.pg_authid'::REGCLASS
 ;
  -- ES: Para ejecutar bien la función no privilegiada, debe reemplazarse la cadena 'pg_catalog' por 'public' y comentar la siguiente línea dentro de la parte no recursiva de la consulta WITH:
  -- EN: In order to properly execute the unprivileged function, the string 'pg_catalog' has to be replaced with 'public' and the following line must be commented out (it's in the non-recursive part of the WITH query):
  dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND
*/