-- View: sonex.v_group_permissions

-- DROP VIEW sonex.v_group_permissions;

CREATE OR REPLACE VIEW sonex.v_group_permissions
 AS
 SELECT r.oid AS id,
    r.rolname AS name,
    COALESCE(shobj_description(r.oid, 'pg_authid'::name), ''::text) AS description,
    COALESCE(array_agg(parent.rolname ORDER BY parent.rolname) FILTER (WHERE parent.rolname ~~ like_escape('permission\_%'::text, '\'::text)), ARRAY[]::name[])::text[] AS granted_permission
   FROM pg_roles r
     LEFT JOIN pg_auth_members m ON m.member = r.oid
     LEFT JOIN pg_roles parent ON parent.oid = m.roleid
  WHERE r.rolname ~~ like_escape('group\_%'::text, '\'::text)
  GROUP BY r.oid, r.rolname, (shobj_description(r.oid, 'pg_authid'::name));

ALTER TABLE sonex.v_group_permissions
    OWNER TO postgres;

GRANT SELECT ON TABLE sonex.v_group_permissions TO permission_groups_view;
GRANT ALL ON TABLE sonex.v_group_permissions TO postgres;
GRANT INSERT, DELETE, SELECT, UPDATE ON TABLE sonex.v_group_permissions TO sonex;

