/**
 * Creates a view to get information rights on database objects.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_role_permissions AS
WITH rtg AS
	(
		SELECT rtg.grantee AS role_name
			, rtg.table_catalog AS database_name
			, rtg.table_schema  AS schema_name
			, rtg.table_name
			, array_agg(rtg.privilege_type) AS privilege
			, rtg.is_grantable
		FROM information_schema.role_table_grants AS rtg
		GROUP BY rtg.grantee
			, rtg.table_catalog
			, rtg.table_schema
			, rtg.table_name
			, rtg.is_grantable
	)
, rtg_res AS
	(
		SELECT rtg.role_name
			, rtg.database_name
			, rtg.schema_name
			, rtg.table_name AS object_name
			, 'TABLE' AS object_type
			, rtg.privilege
			, 'SELECT' = ANY (rtg.privilege) AS "SELECT"
			, 'INSERT' = ANY (rtg.privilege) AS "INSERT"
			, 'UPDATE' = ANY (rtg.privilege) AS "UPDATE"
			, 'DELETE' = ANY (rtg.privilege) AS "DELETE"
			, 'TRUNCATE' = ANY (rtg.privilege) AS "TRUNCATE"
			, 'REFERENCES' = ANY (rtg.privilege) AS "REFERENCES"
			, 'TRIGGER' = ANY (rtg.privilege) AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rtg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rtg
	)
,rug AS
	(
		SELECT rug.grantee AS role_name
			, rug.udt_catalog AS database_name
			, rug.udt_schema AS schema_name
			, rug.udt_name AS object_name
			, 'USER DEFINED TYPE' AS object_type
			, string_to_array(substring(rug.privilege_type, 6, 5), '.') AS privilege
			, rug.is_grantable
		FROM information_schema.role_udt_grants AS rug
	)
, rug_res AS
	(
		SELECT rug.role_name
			, rug.database_name
			, rug.schema_name
			, rug.object_name
			, rug.object_type
			, rug.privilege
			, NULL::boolean AS "SELECT"
			, NULL::boolean AS "INSERT"
			, NULL::boolean AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, NULL::boolean AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, 'USAGE' = ANY (rug.privilege) AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rug.is_grantable = 'YES' AS "GRANTABLE"
		FROM rug
	)
, rrg AS
	(
		SELECT rrg.grantee AS role_name
			, rrg.routine_catalog AS database_name
			, rrg.routine_schema AS schema_name
			, rrg.routine_name AS object_name
			, 'FUNCTION/PROCEDURE' AS object_type
			, string_to_array(rrg.privilege_type, '.') AS privilege
			, rrg.is_grantable
		FROM information_schema.role_routine_grants AS rrg
	)
, rrg_res AS
	(
		SELECT rrg.role_name
			, rrg.database_name
			, rrg.schema_name
			, rrg.object_name
			, rrg.object_type
			, rrg.privilege
			, NULL::boolean AS "SELECT"
			, NULL::boolean AS "INSERT"
			, NULL::boolean AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, NULL::boolean AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, 'EXECUTE' = ANY (rrg.privilege) AS "EXECUTE"
			, rrg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rrg
	)
, rcg AS
	(
		SELECT rcg.grantee AS role_name
			, rcg.table_catalog AS database_name
			, rcg.table_schema AS schema_name
			, rcg.table_name || '.' || rcg.column_name AS object_name
			, 'TABLE COLUMN' AS object_type
			, array_agg(rcg.privilege_type) AS privilege
			, rcg.is_grantable
		FROM information_schema.role_column_grants AS rcg
		GROUP BY rcg.grantee
			, rcg.table_catalog
			, rcg.table_schema
			, rcg.table_name
			, rcg.column_name
			, rcg.is_grantable
	)
, rcg_res AS
	(
		SELECT rcg.role_name
			, rcg.database_name
			, rcg.schema_name
			, rcg.object_name
			, 'TABLE' AS object_type
			, rcg.privilege
			, 'SELECT' = ANY (rcg.privilege) AS "SELECT"
			, 'INSERT' = ANY (rcg.privilege) AS "INSERT"
			, 'UPDATE' = ANY (rcg.privilege) AS "UPDATE"
			, NULL::boolean AS "DELETE"
			, NULL::boolean AS "TRUNCATE"
			, 'REFERENCES' = ANY (rcg.privilege) AS "REFERENCES"
			, NULL::boolean AS "TRIGGER"
			, NULL::boolean AS "USAGE"
			, NULL::boolean AS "EXECUTE"
			, rcg.is_grantable = 'YES' AS "GRANTABLE"
		FROM rcg
	)
, res AS
	(
		SELECT *
		FROM rtg_res
		UNION ALL
		SELECT *
		FROM rug_res
		UNION ALL
		SELECT *
		FROM rrg_res
		UNION ALL
		SELECT *
		FROM rcg_res
	)
SELECT *
FROM res
WHERE res.schema_name NOT IN
	(
		'information_schema',
		'pg_catalog'
	)
;

COMMENT ON VIEW pg_role_permissions IS 'Information about rights of roles on database objects.';
COMMENT ON COLUMN pg_role_permissions.role_name IS 'The name of the role.';
COMMENT ON COLUMN pg_role_permissions.database_name IS 'The name of the database.';
COMMENT ON COLUMN pg_role_permissions.schema_name IS 'The name of the schema.';
COMMENT ON COLUMN pg_role_permissions.object_name IS 'The name of the object.';
COMMENT ON COLUMN pg_role_permissions.object_type IS 'The type of the object, can be one of TABLE, FUNCTION/PROCEDURE, USER DEFINED TYPE, or TABLE COLUMN.';
COMMENT ON COLUMN pg_role_permissions.privilege IS 'All privileges as an array.';
COMMENT ON COLUMN pg_role_permissions."SELECT" IS 'SELECT right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."INSERT" IS 'INSERT right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."UPDATE" IS 'UPDATE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."DELETE" IS 'DELETE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."TRUNCATE" IS 'TRUNCATE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."REFERENCES" IS 'REFERENCES right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."TRIGGER" IS 'TRIGGER right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."USAGE" IS 'USAGE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."EXECUTE" IS 'EXECUTE right, NULL if not supported by the object.';
COMMENT ON COLUMN pg_role_permissions."GRANTABLE" IS 'GRANTABLE right, role can grant rights on this object.';