unionall-repro-c.sql

CREATE ROLE cr_admin;
ALTER ROLE cr_admin WITH NOSUPERUSER INHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE davidj WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE g6a_fixedops_manager_su;
ALTER ROLE g6a_fixedops_manager_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE g6c_service_manager_su;
ALTER ROLE g6c_service_manager_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE g6d_service_advisor_su;
ALTER ROLE g6d_service_advisor_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE g6e_service_tech_su;
ALTER ROLE g6e_service_tech_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE u6_blue_leader_su;
ALTER ROLE u6_blue_leader_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE u6_green_leader_su;
ALTER ROLE u6_green_leader_su WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;

--
-- User Configurations
--


--
-- Role memberships
--

GRANT g6a_fixedops_manager_su TO u6_green_leader_su WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6a_fixedops_manager_su TO u6_blue_leader_su WITH INHERIT TRUE GRANTED BY u6_green_leader_su;
GRANT g6c_service_manager_su TO cr_admin WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6c_service_manager_su TO u6_green_leader_su WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY cr_admin;
GRANT g6c_service_manager_su TO u6_green_leader_su WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6c_service_manager_su TO g6a_fixedops_manager_su WITH INHERIT TRUE GRANTED BY u6_green_leader_su;
GRANT g6d_service_advisor_su TO cr_admin WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6d_service_advisor_su TO g6c_service_manager_su WITH INHERIT TRUE GRANTED BY cr_admin;
GRANT g6d_service_advisor_su TO u6_green_leader_su WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6d_service_advisor_su TO g6c_service_manager_su WITH INHERIT FALSE GRANTED BY u6_green_leader_su;
GRANT g6e_service_tech_su TO cr_admin WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6e_service_tech_su TO g6d_service_advisor_su WITH INHERIT TRUE GRANTED BY cr_admin;
GRANT g6e_service_tech_su TO u6_green_leader_su WITH ADMIN OPTION, INHERIT FALSE, SET FALSE GRANTED BY davidj;
GRANT g6e_service_tech_su TO g6d_service_advisor_su WITH INHERIT TRUE, SET FALSE GRANTED BY u6_green_leader_su;



CREATE VIEW pg_roles_1 AS
    SELECT
        rolname,
        rolsuper,
        rolcreaterole,
        rolcanlogin,
        pg_authid.oid
    FROM pg_authid LEFT JOIN pg_db_role_setting s
    ON (pg_authid.oid = setrole AND setdatabase = 0);


CREATE VIEW role_relationship AS
 WITH RECURSIVE cte_role_relationship AS (
         SELECT r.oid AS leaf_node,
            r.oid AS group_node,
            NULL::oid AS grantor,
            0 AS level,
            ARRAY[]::oid[] AS via,
            ARRAY[]::text[] AS via_names,
            ARRAY[]::text[] AS privs_path,
            ARRAY[]::text[] AS grantor_path,
            NULL::boolean AS grant_is_empty,
            NULL::boolean AS got_set,
            NULL::boolean AS got_inherit,
            NULL::boolean AS got_admin
           FROM pg_roles_1 r
        UNION ALL
         SELECT cb.leaf_node,
            cb.group_node,
            cb.grantor,
            cb.level,
            cb.via,
            cb.via_names,
            cb.privs_path,
            cb.grantor_path,
            cb.grant_is_empty,
            cb.got_set,
            cb.got_inherit,
            cb.got_admin
           FROM ( SELECT a.leaf_node,
                    m.roleid AS group_node,
                    m.grantor,
                    (a.level + 1) AS level,
                    (a.via || m.roleid) AS via,
                    (a.via_names || ((m.roleid)::regrole)::text) AS via_names,
                    (a.privs_path || (((
                        CASE
                            WHEN (NOT (m.set_option OR m.inherit_option OR m.admin_option)) THEN 'e'::text
                            ELSE ''::text
                        END ||
                        CASE
                            WHEN m.admin_option THEN 'a'::text
                            ELSE ''::text
                        END) ||
                        CASE
                            WHEN m.set_option THEN 's'::text
                            ELSE ''::text
                        END) ||
                        CASE
                            WHEN m.inherit_option THEN 'i'::text
                            ELSE ''::text
                        END)) AS privs_path,
                    (a.grantor_path ||
                        CASE
                            WHEN (m.grantor = (10)::oid) THEN 'superuser'::text
                            ELSE ((m.grantor)::regrole)::text
                        END) AS grantor_path,
                    (NOT (m.set_option OR m.inherit_option OR m.admin_option)) AS grant_is_empty,
                    (COALESCE(a.got_set, true) AND m.set_option) AS got_set,
                    (m.inherit_option) AS got_inherit,
                    (COALESCE(m.admin_option, false) AND (((a.level + 1) = 1) OR (a.got_set AND m.set_option) OR (a.got_inherit AND m.inherit_option) OR ((m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND ((NOT m.set_option) AND m.inherit_option))))) AS got_admin,
                    (m.inherit_option OR (COALESCE(a.got_set, false) AND ((NOT m.set_option) AND m.inherit_option))) AS got_inherit_via_set
                   FROM (cte_role_relationship a
                     JOIN pg_auth_members m ON ((m.member = a.group_node)))) cb
          WHERE ((cb.grant_is_empty AND (cb.level <= 1)) OR cb.got_set OR cb.got_inherit OR cb.got_inherit_via_set OR cb.got_admin)
        )
 SELECT leaf_node,
    group_node,
    grantor,
    level,
    via,
    via_names,
    privs_path,
    grantor_path,
    grant_is_empty,
    got_set,
    got_inherit,
    got_admin
   FROM cte_role_relationship;

--
-- Name: role_graph_detail; Type: VIEW; Schema: rolegraph; Owner: davidj
--

CREATE VIEW role_graph_detail AS
 SELECT r.oid,
    r.rolname,
        CASE
            WHEN r.rolcanlogin THEN 'User'::text
            ELSE 'Group'::text
        END AS role_type,
    mou.memberof_users,
    mog.memberof_groups,
    mu.member_users,
    mg.member_groups,
    r.rolsuper,
    r.rolcreaterole
   FROM ((((pg_roles_1 r
     JOIN LATERAL ( SELECT array_agg(DISTINCT a.group_node ORDER BY a.group_node) AS array_agg
           FROM (role_relationship a
             JOIN pg_roles_1 u ON (((u.oid = a.group_node) AND u.rolcanlogin)))
          WHERE ((a.leaf_node = r.oid) AND (r.oid <> a.group_node))) mou(memberof_users) ON (true))
     JOIN LATERAL ( SELECT array_agg(DISTINCT a.group_node ORDER BY a.group_node) AS array_agg
           FROM (role_relationship a
             JOIN pg_roles_1 u ON (((u.oid = a.group_node) AND (NOT u.rolcanlogin))))
          WHERE ((a.leaf_node = r.oid) AND (r.oid <> a.group_node))) mog(memberof_groups) ON (true))
     JOIN LATERAL ( SELECT array_agg(DISTINCT a.leaf_node ORDER BY a.leaf_node) AS array_agg
           FROM (role_relationship a
             JOIN pg_roles_1 u ON (((u.oid = a.leaf_node) AND u.rolcanlogin)))
          WHERE ((a.group_node = r.oid) AND (r.oid <> a.leaf_node))) mu(member_users) ON (true))
     JOIN LATERAL ( SELECT array_agg(DISTINCT a.leaf_node ORDER BY a.leaf_node) AS array_agg
           FROM (role_relationship a
             JOIN pg_roles_1 u ON (((u.oid = a.leaf_node) AND (NOT u.rolcanlogin))))
          WHERE ((a.group_node = r.oid) AND (r.oid <> a.leaf_node))) mg(member_groups) ON (true));

SET enable_group_by_reordering=on;
SELECT leaf_role.oid, leaf_role.role_type, leaf_role.rolname, leaf_role.rolsuper,
  array_to_string(ARRAY(
    SELECT ' '::text
      UNION ALL
    SELECT format('%I from %s'::text, other_role.rolname, string_agg(''::text, ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) + 3))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))

    FROM ((((unnest(leaf_role.memberof_groups) other(other)
      JOIN pg_roles_1 other_role ON ((other_role.oid = other.other)))
      JOIN role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
      JOIN pg_roles_1 grant_role ON ((grant_role.oid = grant_instance.grantor)))
      LEFT JOIN pg_roles_1 ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
    WHERE ((grant_instance.got_admin AND (NOT (grant_instance.leaf_node = grant_instance.grantor))) OR grant_instance.grant_is_empty)
    GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS administration
FROM role_graph_detail leaf_role
WHERE (leaf_role.rolname ~ 'u6_green'::text);

SET enable_group_by_reordering=off;
SELECT leaf_role.oid, leaf_role.role_type, leaf_role.rolname, leaf_role.rolsuper,
  array_to_string(ARRAY(
    SELECT ' '::text
      UNION ALL
    SELECT format('%I from %s'::text, other_role.rolname, string_agg(''::text, ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) + 3))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))

    FROM ((((unnest(leaf_role.memberof_groups) other(other)
      JOIN pg_roles_1 other_role ON ((other_role.oid = other.other)))
      JOIN role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
      JOIN pg_roles_1 grant_role ON ((grant_role.oid = grant_instance.grantor)))
      LEFT JOIN pg_roles_1 ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
    WHERE ((grant_instance.got_admin AND (NOT (grant_instance.leaf_node = grant_instance.grantor))) OR grant_instance.grant_is_empty)
    GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS administration
FROM role_graph_detail leaf_role
WHERE (leaf_role.rolname ~ 'u6_green'::text);