/*
\echo test data for regression test
CREATE ROLE regress_du_role0;
CREATE ROLE regress_du_role1;
CREATE ROLE regress_du_role2 LOGIN CREATEROLE BYPASSRLS REPLICATION CREATEDB NOINHERIT CONNECTION LIMIT 3 VALID UNTIL 'infinity';
CREATE ROLE regress_du_admin LOGIN CREATEROLE;
COMMENT ON ROLE regress_du_role0 IS 'Description for regress_du_role0';
COMMENT ON ROLE regress_du_role1 IS 'Description for regress_du_role1';
COMMENT ON ROLE regress_du_role2 IS 'Description for regress_du_role2';
COMMENT ON ROLE regress_du_admin IS 'Description for regress_du_admin';
GRANT regress_du_role0 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role1 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role2 TO regress_du_admin WITH ADMIN TRUE;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN TRUE,  INHERIT TRUE,  SET TRUE  GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN TRUE,  INHERIT FALSE, SET FALSE GRANTED BY regress_du_admin;
GRANT regress_du_role1 TO regress_du_role2 WITH ADMIN TRUE , INHERIT FALSE, SET TRUE  GRANTED BY regress_du_admin;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT TRUE,  SET FALSE GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT TRUE , SET TRUE  GRANTED BY regress_du_role1;
GRANT regress_du_role0 TO regress_du_role1 WITH ADMIN FALSE, INHERIT FALSE, SET TRUE  GRANTED BY regress_du_role2;
GRANT regress_du_role0 TO regress_du_role2 WITH ADMIN FALSE, INHERIT FALSE, SET FALSE GRANTED BY regress_du_role2;
*/

\echo Tabular form
SELECT r.rolname, 
       pg_catalog.string_agg(m.roleid::regrole::text, E'\n' ORDER BY m.roleid::regrole::text, m.grantor::regrole::text) memberof,
       pg_catalog.string_agg(
         CASE WHEN m.roleid IS NOT NULL THEN
           pg_catalog.regexp_replace(
             pg_catalog.concat_ws(', ',
               CASE WHEN m.admin_option THEN 'admin' END,
               CASE WHEN m.inherit_option THEN 'inherit' END,
               CASE WHEN m.set_option THEN 'set' END),
            '^$', 'empty')
         END
         , E'\n' ORDER BY m.roleid::regrole::text, m.grantor::regrole::text) options,
       pg_catalog.string_agg(m.grantor::regrole::text, E'\n' ORDER BY m.roleid::regrole::text, m.grantor::regrole::text) grantor
FROM pg_catalog.pg_roles r
     LEFT JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid)
WHERE r.rolname !~ '^pg_'
GROUP BY r.rolname
ORDER BY r.rolname;

\echo Sentence form from patch v7
SELECT r.rolname, 
  (SELECT pg_catalog.string_agg(
    pg_catalog.format('%I from %I (%s)',
      b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text,
      pg_catalog.regexp_replace(
        pg_catalog.concat_ws(', ',
          CASE WHEN m.admin_option THEN 'admin' END,
          CASE WHEN m.inherit_option THEN 'inherit' END,
          CASE WHEN m.set_option THEN 'set' END),
      '^$', 'empty')
    ), E'\n'
    ORDER BY b.rolname, m.grantor::pg_catalog.regrole::pg_catalog.text)
  FROM pg_catalog.pg_auth_members m
       JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
  WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

