- * 5.41
- * ROLE_USAGE_GRANTS view
- */
-
-CREATE VIEW role_usage_grants AS
-
- /* foreign-data wrappers */
- SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
- CAST(g_grantee.rolname AS sql_identifier) AS grantee,
- CAST(current_database() AS sql_identifier) AS object_catalog,
- CAST('' AS sql_identifier) AS object_schema,
- CAST(fdw.fdwname AS sql_identifier) AS object_name,
- CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
- CAST('USAGE' AS character_data) AS privilege_type,
- CAST(
- CASE WHEN
- -- object owner always has grant options
- pg_has_role(g_grantee.oid, fdw.fdwowner, 'USAGE')
- OR aclcontains(fdw.fdwacl,
- makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
-
- FROM pg_foreign_data_wrapper fdw,
- pg_authid u_grantor,
- pg_authid g_grantee
-
- WHERE aclcontains(fdw.fdwacl,
- makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
- AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
- OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles))
-
- UNION ALL
-
- /* foreign server */
- SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
- CAST(g_grantee.rolname AS sql_identifier) AS grantee,
- CAST(current_database() AS sql_identifier) AS object_catalog,
- CAST('' AS sql_identifier) AS object_schema,
- CAST(srv.srvname AS sql_identifier) AS object_name,
- CAST('FOREIGN SERVER' AS character_data) AS object_type,
- CAST('USAGE' AS character_data) AS privilege_type,
- CAST(
- CASE WHEN
- -- object owner always has grant options
- pg_has_role(g_grantee.oid, srv.srvowner, 'USAGE')
- OR aclcontains(srv.srvacl,
- makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
-
- FROM pg_foreign_server srv,
- pg_authid u_grantor,
- pg_authid g_grantee
-
- WHERE aclcontains(srv.srvacl,
- makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', false))
- AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
- OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));
-
-GRANT SELECT ON role_usage_grants TO PUBLIC;
-
-
-/*
- * 5.42
- * ROLE_UDT_GRANTS view
- */
-
--- feature not supported
-
-
-/*
- * 5.43
- * ROUTINE_COLUMN_USAGE view
- */
-
--- not tracked by PostgreSQL
-
-
-/*
- * 5.44
- * ROUTINE_PRIVILEGES view
- */
-
-CREATE VIEW routine_privileges AS
- SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
- CAST(grantee.rolname AS sql_identifier) AS grantee,
- CAST(current_database() AS sql_identifier) AS specific_catalog,
- CAST(n.nspname AS sql_identifier) AS specific_schema,
- CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
- CAST(current_database() AS sql_identifier) AS routine_catalog,
- CAST(n.nspname AS sql_identifier) AS routine_schema,
- CAST(p.proname AS sql_identifier) AS routine_name,
- CAST('EXECUTE' AS character_data) AS privilege_type,
- CAST(
- CASE WHEN
- -- object owner always has grant options
- pg_has_role(grantee.oid, p.proowner, 'USAGE')
- OR aclcontains(p.proacl,
- makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
-
- FROM pg_proc p,
- pg_namespace n,
- pg_authid u_grantor,
- (
- SELECT oid, rolname FROM pg_authid
- UNION ALL
- SELECT 0::oid, 'PUBLIC'
- ) AS grantee (oid, rolname)
-
- WHERE p.pronamespace = n.oid
- AND aclcontains(p.proacl,
- makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
- AND (pg_has_role(u_grantor.oid, 'USAGE')
- OR pg_has_role(grantee.oid, 'USAGE')
- OR grantee.rolname = 'PUBLIC');
-
-GRANT SELECT ON routine_privileges TO PUBLIC;
-
-
-/*
- * 5.45
- * ROUTINE_ROUTINE_USAGE view
- */
-
--- not tracked by PostgreSQL
-
-
-/*
- * 5.46