test_inh.sql

application/sql

Download

CREATE ROLE init_1_noinherit NOINHERIT;
CREATE ROLE init_2_inherit INHERIT;
CREATE ROLE init_3_noinherit NOINHERIT;
CREATE ROLE init_4_inherit INHERIT;
CREATE ROLE user_1_INHERIT ROLE init_1_noinherit IN ROLE init_3_noinherit INHERIT;
CREATE ROLE user_2_NOINHERIT ROLE init_1_noinherit IN ROLE init_3_noinherit NOINHERIT;
CREATE ROLE user_3_INHERIT ROLE init_1_noinherit IN ROLE init_4_inherit INHERIT;
CREATE ROLE user_4_NOINHERIT ROLE init_1_noinherit IN ROLE init_4_inherit NOINHERIT;
CREATE ROLE user_5_INHERIT ROLE init_2_inherit IN ROLE init_3_noinherit INHERIT;
CREATE ROLE user_6_NOINHERIT ROLE init_2_inherit IN ROLE init_3_noinherit NOINHERIT;
CREATE ROLE user_7_INHERIT ROLE init_2_inherit IN ROLE init_4_inherit INHERIT;
CREATE ROLE user_8_NOINHERIT ROLE init_2_inherit IN ROLE init_4_inherit NOINHERIT;
SELECT rolname, roleid::regrole AS "Is member of role", inherit_option
FROM  pg_roles, pg_auth_members
WHERE member = pg_roles.oid AND
      rolname NOT LIKE 'pg_%'
ORDER BY 1, 2;