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