+ <structname>pg_ownerships</structname> Columns
+
+
+
+
+ Column Type
+
+
+ Description
+
+
+
+
+
+
+
+ classidregclass
+ (references pg_class.oid)
+
+
+ The regclass OID of the system catalog the owned object is in
+
+
+
+
+
+ objidoid
+ (references any OID column)
+
+
+ The OID of the specific owned object
+
+
+
+
+
+ objsubidint4
+
+
+ For a table column, this is the column number (the
+ objid and classid refer to the
+ table itself). For all other object types, this column is
+ zero.
+
+
+
+
+
+ typetext
+
+
+ Identifies the type of database object
+
+
+
+
+
+ schematext
+
+
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+
+
+
+
+
+ nametext
+
+
+ The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL
+
+
+
+
+
+ identitytext
+
+
+ The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary
+
+
+
+
+
+ ownerregrole
+ (references pg_authid.rolname)
+
+
+ Owner of the object
+
+
+
+
+
+
+
+
+
+
+ <structname>pg_permissions</structname>
+
+
+ pg_permissions
+
+
+
+ The view pg_permissions provides access to information about explicitly granted privileges on database objects.
+ The special grantee value - means the privilege is granted to PUBLIC.
+
+
+
+ <structname>pg_permissions</structname> Columns
+
+
+
+
+ Column Type
+
+
+ Description
+
+
+
+
+
+
+
+ classidregclass
+ (references pg_class.oid)
+
+
+ The regclass OID of the system catalog the granted object is in
+
+
+
+
+
+ objidoid
+ (references any OID column)
+
+
+ The OID of the specific granted object
+
+
+
+
+
+ objsubidint4
+
+
+ For a table column, this is the column number (the
+ objid and classid refer to the
+ table itself). For all other object types, this column is
+ zero.
+
+
+
+
+
+ typetext
+
+
+ Identifies the type of database object
+
+
+
+
+
+ schematext
+
+
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+
+
+
+
+
+ nametext
+
+
+ The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL
+
+
+
+
+
+ identitytext
+
+
+ The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary
+
+
+
+
+
+ grantorregrole
+ (references pg_authid.rolname)
+
+
+ Role that granted this permission
+
+
+
+
+
+ granteeregrole
+ (references pg_authid.rolname)
+
+
+ Role to whom permission is granted
+
+
+
+
+
+ privilege_typetext
+
+
+ Type of the privilege: SELECT,
+ INSERT, UPDATE, or
+ REFERENCES
+
+
+
+
+
+ is_grantableboolean
+
+
+ true if the privilege is grantable, false if not
+
+
+
+
+
+
+
+
<structname>pg_policies</structname>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0dca65dc7b..9cf1197c93 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,225 @@ FROM
pg_shseclabel l
JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
+CREATE VIEW pg_permissions AS
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_class'::regclass AS classid,
+ aa.attrelid AS objid,
+ aa.attnum AS objsubid,
+ (pg_identify_object('pg_class'::regclass,aa.attrelid,aa.attnum)).*,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_class'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_class'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_server'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_foreign_server'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_language'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_language'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_namespace'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_namespace'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_proc'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_proc'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_tablespace'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_tablespace'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+ ) AS a
+UNION ALL
+ SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_type'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object('pg_type'::regclass,aa.oid,0)).*,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+ ) AS a;
+
+CREATE VIEW pg_ownerships AS
+SELECT
+ classid,
+ objid,
+ objsubid,
+ (pg_identify_object(classid,objid,objsubid)).*,refobjid::regrole AS owner
+FROM pg_catalog.pg_shdepend
+JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = pg_shdepend.dbid
+JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid AND pg_shdepend.refclassid = 'pg_authid'::regclass
+WHERE pg_shdepend.deptype = 'o';
+
CREATE VIEW pg_settings AS
SELECT * FROM pg_show_all_settings() AS A;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 9b12cc122a..6874a7ce49 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1408,6 +1408,233 @@ pg_matviews| SELECT n.nspname AS schemaname,
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
+pg_ownerships| SELECT pg_shdepend.classid,
+ pg_shdepend.objid,
+ pg_shdepend.objsubid,
+ (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).type AS type,
+ (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).schema AS schema,
+ (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).name AS name,
+ (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).identity AS identity,
+ (pg_shdepend.refobjid)::regrole AS owner
+ FROM ((pg_shdepend
+ JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = pg_shdepend.dbid))))
+ JOIN pg_authid ON (((pg_authid.oid = pg_shdepend.refobjid) AND (pg_shdepend.refclassid = ('pg_authid'::regclass)::oid))))
+ WHERE (pg_shdepend.deptype = 'o'::"char");
+pg_permissions| SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_class'::regclass AS classid,
+ aa.attrelid AS objid,
+ aa.attnum AS objsubid,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer)).type AS type,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer)).schema AS schema,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer)).name AS name,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer)).identity AS identity,
+ (aclexplode(aa.attacl)).grantor AS grantor,
+ (aclexplode(aa.attacl)).grantee AS grantee,
+ (aclexplode(aa.attacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.attacl)).is_grantable AS is_grantable
+ FROM pg_attribute aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_class'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_class'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.relacl)).grantor AS grantor,
+ (aclexplode(aa.relacl)).grantee AS grantee,
+ (aclexplode(aa.relacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.relacl)).is_grantable AS is_grantable
+ FROM pg_class aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_foreign_data_wrapper'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.fdwacl)).grantor AS grantor,
+ (aclexplode(aa.fdwacl)).grantee AS grantee,
+ (aclexplode(aa.fdwacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.fdwacl)).is_grantable AS is_grantable
+ FROM pg_foreign_data_wrapper aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_foreign_server'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.srvacl)).grantor AS grantor,
+ (aclexplode(aa.srvacl)).grantee AS grantee,
+ (aclexplode(aa.srvacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.srvacl)).is_grantable AS is_grantable
+ FROM pg_foreign_server aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_language'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_language'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_language'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_language'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_language'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.lanacl)).grantor AS grantor,
+ (aclexplode(aa.lanacl)).grantee AS grantee,
+ (aclexplode(aa.lanacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.lanacl)).is_grantable AS is_grantable
+ FROM pg_language aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_namespace'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_namespace'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_namespace'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_namespace'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_namespace'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.nspacl)).grantor AS grantor,
+ (aclexplode(aa.nspacl)).grantee AS grantee,
+ (aclexplode(aa.nspacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.nspacl)).is_grantable AS is_grantable
+ FROM pg_namespace aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_proc'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_proc'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_proc'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_proc'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_proc'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.proacl)).grantor AS grantor,
+ (aclexplode(aa.proacl)).grantee AS grantee,
+ (aclexplode(aa.proacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.proacl)).is_grantable AS is_grantable
+ FROM pg_proc aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_tablespace'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_tablespace'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_tablespace'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_tablespace'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_tablespace'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.spcacl)).grantor AS grantor,
+ (aclexplode(aa.spcacl)).grantee AS grantee,
+ (aclexplode(aa.spcacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.spcacl)).is_grantable AS is_grantable
+ FROM pg_tablespace aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.type,
+ a.schema,
+ a.name,
+ a.identity,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_type'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ (pg_identify_object(('pg_type'::regclass)::oid, aa.oid, 0)).type AS type,
+ (pg_identify_object(('pg_type'::regclass)::oid, aa.oid, 0)).schema AS schema,
+ (pg_identify_object(('pg_type'::regclass)::oid, aa.oid, 0)).name AS name,
+ (pg_identify_object(('pg_type'::regclass)::oid, aa.oid, 0)).identity AS identity,
+ (aclexplode(aa.typacl)).grantor AS grantor,
+ (aclexplode(aa.typacl)).grantee AS grantee,
+ (aclexplode(aa.typacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.typacl)).is_grantable AS is_grantable
+ FROM pg_type aa) a;
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,