--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;


--
-- Roles
--

CREATE ROLE cr_admin;
ALTER ROLE cr_admin WITH NOSUPERUSER INHERIT CREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE davidj;
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;






--
-- Databases
--

--
-- Database "template1" dump
--

\connect template1

--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
--

--
-- Database "postgres" dump
--

\connect postgres

--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: rolegraph; Type: SCHEMA; Schema: -; Owner: davidj
--

CREATE SCHEMA rolegraph;


ALTER SCHEMA rolegraph OWNER TO davidj;

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

CREATE VIEW rolegraph.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,
            NULL::boolean AS got_inherit_via_set,
            NULL::text AS privs
           FROM pg_roles 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,
            cb.got_inherit_via_set,
            COALESCE(NULLIF(((((
                CASE
                    WHEN cb.grant_is_empty THEN ''::text
                    ELSE ''::text
                END ||
                CASE
                    WHEN cb.got_inherit_via_set THEN 'i*'::text
                    ELSE ''::text
                END) ||
                CASE
                    WHEN cb.got_set THEN 's'::text
                    ELSE ''::text
                END) ||
                CASE
                    WHEN (cb.got_set AND cb.got_inherit) THEN ','::text
                    ELSE ''::text
                END) ||
                CASE
                    WHEN cb.got_inherit THEN 'i'::text
                    ELSE ''::text
                END), ''::text), 'n/a'::text) AS "coalesce"
           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,
                    (COALESCE(a.got_inherit, true) AND 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 ((COALESCE(a.got_inherit_via_set, false) AND 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,
                    ((COALESCE(a.got_inherit_via_set, false) AND 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_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,
    got_inherit_via_set,
    privs
   FROM cte_role_relationship;


ALTER VIEW rolegraph.role_relationship OWNER TO davidj;

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

CREATE VIEW rolegraph.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 r
     JOIN LATERAL ( SELECT array_agg(DISTINCT a.group_node ORDER BY a.group_node) AS array_agg
           FROM (rolegraph.role_relationship a
             JOIN pg_roles 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 (rolegraph.role_relationship a
             JOIN pg_roles 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 (rolegraph.role_relationship a
             JOIN pg_roles 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 (rolegraph.role_relationship a
             JOIN pg_roles 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));


ALTER VIEW rolegraph.role_graph_detail OWNER TO davidj;

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

CREATE VIEW rolegraph.role_graph AS
 WITH cte_role_graph AS (
         SELECT leaf_role.oid,
            leaf_role.role_type,
            leaf_role.rolname,
            leaf_role.rolsuper,
            array_to_string(ARRAY( SELECT vals.v
                   FROM ( VALUES ('Superuser'::text)) vals(v)
                  WHERE leaf_role.rolsuper
                UNION ALL
                 SELECT vals.v
                   FROM ( VALUES ('Create Role'::text)) vals(v)
                  WHERE leaf_role.rolcreaterole
                UNION ALL
                 SELECT ((
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END || 'of '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.memberof_users) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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
                UNION ALL
                 SELECT ((
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END || 'of '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.memberof_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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
                UNION ALL
                 SELECT ((
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END || 'by '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.member_users) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.group_node = leaf_role.oid) AND (grant_instance.leaf_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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
                UNION ALL
                 SELECT ((
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END || 'by '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.member_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.group_node = leaf_role.oid) AND (grant_instance.leaf_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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,
            array_to_string(ARRAY( SELECT
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END AS format
                   FROM ((((unnest(leaf_role.memberof_users) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
                  WHERE (grant_instance.got_inherit OR grant_instance.got_set OR grant_instance.got_inherit_via_set)
                  GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS memberof_users,
            array_to_string(ARRAY( SELECT
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END AS format
                   FROM ((((unnest(leaf_role.memberof_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
                  WHERE (grant_instance.got_inherit OR grant_instance.got_set OR grant_instance.got_inherit_via_set)
                  GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS memberof_groups,
            array_to_string(ARRAY( SELECT
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END AS format
                   FROM ((((unnest(leaf_role.member_users) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.group_node = leaf_role.oid) AND (grant_instance.leaf_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
                  WHERE (grant_instance.got_inherit OR grant_instance.got_set OR grant_instance.got_inherit_via_set)
                  GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS member_users,
            array_to_string(ARRAY( SELECT
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN false THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN false THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END AS format
                   FROM ((((unnest(leaf_role.member_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.group_node = leaf_role.oid) AND (grant_instance.leaf_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles ancestor_role ON ((ancestor_role.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])))
                  WHERE (grant_instance.got_inherit OR grant_instance.got_set OR grant_instance.got_inherit_via_set)
                  GROUP BY other_role.rolname, grant_instance.via), '
'::text) AS member_groups
           FROM rolegraph.role_graph_detail leaf_role
        )
 SELECT rolname,
    administration,
    memberof_users,
    memberof_groups,
    member_users,
    member_groups,
    role_type,
    oid,
    row_number() OVER (ORDER BY role_type,
        CASE
            WHEN rolsuper THEN (oid)::integer
            ELSE NULL::integer
        END,
        CASE
            WHEN (rolname ~ 'pg_'::text) THEN 0
            ELSE 1
        END, rolname) AS seq
   FROM cte_role_graph
  ORDER BY (row_number() OVER (ORDER BY role_type,
        CASE
            WHEN rolsuper THEN (oid)::integer
            ELSE NULL::integer
        END,
        CASE
            WHEN (rolname ~ 'pg_'::text) THEN 0
            ELSE 1
        END, rolname));


ALTER VIEW rolegraph.role_graph OWNER TO davidj;

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

CREATE VIEW rolegraph.role_graph_broken AS
 WITH cte_role_graph AS (
         SELECT leaf_role.oid,
            leaf_role.role_type,
            leaf_role.rolname,
            leaf_role.rolsuper,
            array_to_string(ARRAY( SELECT ((('mou '::text ||
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END) || 'of '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.memberof_users) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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
                UNION ALL
                 SELECT ((('mog '::text ||
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END) || 'of '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.memberof_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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 rolegraph.role_graph_detail leaf_role
          WHERE (leaf_role.rolname ~ 'u6_green'::text)
        )
 SELECT oid,
    role_type,
    rolname,
    rolsuper,
    administration
   FROM cte_role_graph;


ALTER VIEW rolegraph.role_graph_broken OWNER TO davidj;

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

CREATE VIEW rolegraph.role_graph_working AS
 WITH cte_role_graph AS (
         SELECT leaf_role.oid,
            leaf_role.role_type,
            leaf_role.rolname,
            leaf_role.rolsuper,
            array_to_string(ARRAY( SELECT ((('mog '::text ||
                        CASE
                            WHEN bool_or(grant_instance.grant_is_empty) THEN 'empty '::text
                            ELSE ''::text
                        END) || 'of '::text) ||
                        CASE
                            WHEN (cardinality(grant_instance.via) > 1) THEN format('%I via %s'::text, other_role.rolname, string_agg(((((quote_ident((ancestor_role.rolname)::text) || '/'::text) || quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text)) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END) ||
                            CASE
                                WHEN (grant_instance.level > 2) THEN (('[+'::text || (grant_instance.level - 2)) || ']'::text)
                                ELSE ''::text
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' via '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                            ELSE format('%I from %s'::text, other_role.rolname, string_agg((quote_ident((
                            CASE
                                WHEN (((grant_role.rolname)::regrole)::oid = (10)::oid) THEN 'superuser'::name
                                ELSE grant_role.rolname
                            END)::text) ||
                            CASE
                                WHEN true THEN ''::text
                                ELSE ((' ('::text || grant_instance.privs) || ')'::text)
                            END), ('
'::text || repeat(' '::text, ((length((other_role.rolname)::text) + length(' from '::text)) +
                            CASE
                                WHEN true THEN 3
                                ELSE 0
                            END))) ORDER BY grant_instance.level, grant_instance.grantor, grant_instance.grantor_path))
                        END)
                   FROM ((((unnest(leaf_role.memberof_groups) other(other)
                     JOIN pg_roles other_role ON ((other_role.oid = other.other)))
                     JOIN rolegraph.role_relationship grant_instance ON (((grant_instance.leaf_node = leaf_role.oid) AND (grant_instance.group_node = other.other))))
                     JOIN pg_roles grant_role ON ((grant_role.oid = grant_instance.grantor)))
                     LEFT JOIN pg_roles 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 rolegraph.role_graph_detail leaf_role
          WHERE (leaf_role.rolname ~ 'u6_green'::text)
        )
 SELECT oid,
    role_type,
    rolname,
    rolsuper,
    administration
   FROM cte_role_graph;


ALTER VIEW rolegraph.role_graph_working OWNER TO davidj;

--
-- Name: SCHEMA rolegraph; Type: ACL; Schema: -; Owner: davidj
--

GRANT USAGE ON SCHEMA rolegraph TO cr_admin;


--
-- Name: TABLE role_relationship; Type: ACL; Schema: rolegraph; Owner: davidj
--

GRANT SELECT ON TABLE rolegraph.role_relationship TO cr_admin;


--
-- Name: TABLE role_graph; Type: ACL; Schema: rolegraph; Owner: davidj
--

GRANT SELECT ON TABLE rolegraph.role_graph TO cr_admin;


--
--

--
-- PostgreSQL database cluster dump complete
--

