[PATCH] pg_permissions
Hi,
It's easy to answer the question...
- What permissions are there on this specific object?
...but to answer the question...
- What permissions are there for a specific role in the database?
you need to manually query all relevant pg_catalog or information_schema.*_privileges views,
which is a O(n) mental effort, while the first question is mentally O(1).
I think this can be improved by providing humans a single pg_permissions system view
which can be queried to answer the second question. This should save a lot of keyboard punches.
Demo:
SELECT * FROM pg_permissions WHERE 'joel' IN (grantor,grantee);
regclass | obj_desc | grantor | grantee | privilege_type | is_grantable
--------------+-----------------------------+---------+---------+----------------+--------------
pg_namespace | schema foo | joel | joel | USAGE | f
pg_namespace | schema foo | joel | joel | CREATE | f
pg_class | table foo.bar | joel | joel | INSERT | f
pg_class | table foo.bar | joel | joel | SELECT | f
pg_class | table foo.bar | joel | joel | UPDATE | f
pg_class | table foo.bar | joel | joel | DELETE | f
pg_class | table foo.bar | joel | joel | TRUNCATE | f
pg_class | table foo.bar | joel | joel | REFERENCES | f
pg_class | table foo.bar | joel | joel | TRIGGER | f
pg_attribute | column baz of table foo.bar | joel | joel | SELECT | f
pg_attribute | column baz of table foo.bar | joel | joel | UPDATE | f
(11 rows)
All catalogs with _aclitem columns have been included in the view.
I think a similar one for ownerships would be nice too.
But I'll let you digest this one first to see if the concept is fruitful.
/Joel
Attachments:
0001-pg_permissions.patchapplication/octet-stream; name=0001-pg_permissions.patchDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fc94a73a54..52732158db 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,268 @@ 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.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_attribute'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.attrelid,aa.attnum)
+ AS obj_desc,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_class'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_database'::regclass
+ AS regclass,
+ pg_describe_object('pg_database'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.datacl)).*
+ FROM pg_catalog.pg_database AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_default_acl'::regclass
+ AS regclass,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.defaclacl)).*
+ FROM pg_catalog.pg_default_acl AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_server'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_server'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_init_privs'::regclass
+ AS regclass,
+ pg_describe_object(aa.classoid,aa.objoid,aa.objsubid)
+ AS obj_desc,
+ (aclexplode(aa.initprivs)).*
+ FROM pg_catalog.pg_init_privs AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_language'::regclass
+ AS regclass,
+ pg_describe_object('pg_language'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_largeobject_metadata'::regclass
+ AS regclass,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lomacl)).*
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_namespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_namespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_proc'::regclass
+ AS regclass,
+ pg_describe_object('pg_proc'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_tablespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_tablespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_type'::regclass
+ AS regclass,
+ pg_describe_object('pg_type'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+;
+
CREATE VIEW pg_settings AS
SELECT * FROM pg_show_all_settings() AS A;
On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
Hi,
It's easy to answer the question...
- What permissions are there on this specific object?
...but to answer the question...
- What permissions are there for a specific role in the database?
you need to manually query all relevant pg_catalog or information_schema.*_privileges views,
which is a O(n) mental effort, while the first question is mentally O(1).I think this can be improved by providing humans a single pg_permissions system view
which can be queried to answer the second question. This should save a lot of keyboard punches.Demo:
SELECT * FROM pg_permissions WHERE 'joel' IN (grantor,grantee);
regclass | obj_desc | grantor | grantee | privilege_type | is_grantable
--------------+-----------------------------+---------+---------+----------------+--------------
pg_namespace | schema foo | joel | joel | USAGE | f
pg_namespace | schema foo | joel | joel | CREATE | f
pg_class | table foo.bar | joel | joel | INSERT | f
pg_class | table foo.bar | joel | joel | SELECT | f
pg_class | table foo.bar | joel | joel | UPDATE | f
pg_class | table foo.bar | joel | joel | DELETE | f
pg_class | table foo.bar | joel | joel | TRUNCATE | f
pg_class | table foo.bar | joel | joel | REFERENCES | f
pg_class | table foo.bar | joel | joel | TRIGGER | f
pg_attribute | column baz of table foo.bar | joel | joel | SELECT | f
pg_attribute | column baz of table foo.bar | joel | joel | UPDATE | f
(11 rows)All catalogs with _aclitem columns have been included in the view.
I think a similar one for ownerships would be nice too.
But I'll let you digest this one first to see if the concept is fruitful.
+1 for both this and the ownerships view.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Mar 8, 2021, at 02:09, David Fetter wrote:
+1 for both this and the ownerships view.
Best,
David.
I'm glad you like it.
I've put some more effort into this patch, and developed a method to mechanically verify its correctness.
Attached is a new patch with both pg_permissions and pg_ownerships in the same patch,
based on HEAD (8a812e5106c5db50039336288d376a188844e2cc).
I've also added five catalogs to pg_ownerships that were discovered to be missing in the previous version:
pg_catalog.pg_database
pg_catalog.pg_default_acl
pg_catalog.pg_largeobject_metadata
pg_catalog.pg_publication
pg_catalog.pg_subscription
Here is how I've verified correctness of complete coverage:
All catalogs with permissions have an aclitem[] column.
There are totally 13 such catalogs in HEAD:
SELECT COUNT(DISTINCT table_name) FROM information_schema.columns WHERE table_schema = 'pg_catalog' AND udt_name = '_aclitem';
count
-------
13
(1 row)
Expect the same number of rows in the patch:
$ grep "(aclexplode(aa." 0001-pg_permissions-and-pg_ownerships.patch | wc -l
13
Using the new awesome pg_get_catalog_foreign_keys() function in v14,
we can now query what catalogs are referencing pg_authid.oid,
of which all named .*owner are known by convention to
indicate ownership. Let's see what other columns there are
referencing pg_authid.oid that could possibly also indicate ownership:
SELECT
regexp_replace(fkcols[1],'.*owner$','.*owner') AS fkcol,
COUNT(*)
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass
AND pkcols[1] = 'oid'
AND cardinality(fkcols) = 1
GROUP BY 1
ORDER BY 2 DESC;
fkcol | count
------------+-------
.*owner | 21
datdba | 1
defaclrole | 1
grantor | 1
member | 1
polroles | 1
roleid | 1
setrole | 1
umuser | 1
(9 rows)
If we exclude the .*owner and also look at fktable we see:
SELECT *
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_authid'::regclass
AND pkcols[1] = 'oid'
AND cardinality(fkcols) = 1
AND fkcols[1] !~ '.*owner$'
fktable | fkcols | pktable | pkcols | is_array | is_opt
--------------------+--------------+-----------+--------+----------+--------
pg_database | {datdba} | pg_authid | {oid} | f | f
pg_db_role_setting | {setrole} | pg_authid | {oid} | f | t
pg_auth_members | {roleid} | pg_authid | {oid} | f | f
pg_auth_members | {member} | pg_authid | {oid} | f | f
pg_auth_members | {grantor} | pg_authid | {oid} | f | f
pg_user_mapping | {umuser} | pg_authid | {oid} | f | t
pg_policy | {polroles} | pg_authid | {oid} | t | t
pg_default_acl | {defaclrole} | pg_authid | {oid} | f | f
(8 rows)
By reading the documentation for these catalogs,
I've come to the conclusion these columns also indicate ownership:
pg_database.datdba
pg_default_acl.defaclrole
pg_policy.polroles
In total, we should expect 21+3=24 catalogs.
Let's see if this matches the patch:
$ grep "pg_authid.rolname" 0001-pg_permissions-and-pg_ownerships.patch | wc -l
24
All good.
I note it's not very often new catalogs are added,
so hopefully we can have a routine to update these views
when new catalogs with ownership- or permission columns are added.
However, should we ever get out of sync, we can use the method above to sort things out.
/Joel
Attachments:
0001-pg_permissions-and-pg_ownerships.patchapplication/octet-stream; name=0001-pg_permissions-and-pg_ownerships.patchDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fc94a73a54..ca8b88a682 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,484 @@ 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.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_attribute'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.attrelid,aa.attnum)
+ AS obj_desc,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_class'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_database'::regclass
+ AS regclass,
+ pg_describe_object('pg_database'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.datacl)).*
+ FROM pg_catalog.pg_database AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_default_acl'::regclass
+ AS regclass,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.defaclacl)).*
+ FROM pg_catalog.pg_default_acl AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_server'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_server'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_init_privs'::regclass
+ AS regclass,
+ pg_describe_object(aa.classoid,aa.objoid,aa.objsubid)
+ AS obj_desc,
+ (aclexplode(aa.initprivs)).*
+ FROM pg_catalog.pg_init_privs AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_language'::regclass
+ AS regclass,
+ pg_describe_object('pg_language'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_largeobject_metadata'::regclass
+ AS regclass,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lomacl)).*
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_namespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_namespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_proc'::regclass
+ AS regclass,
+ pg_describe_object('pg_proc'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_tablespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_tablespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_type'::regclass
+ AS regclass,
+ pg_describe_object('pg_type'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ 'pg_class'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_class AS aa
+ JOIN pg_catalog.pg_authid ON aa.relowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_collation'::regclass
+ AS regclass,
+ pg_describe_object('pg_collation'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_collation AS aa
+ JOIN pg_catalog.pg_authid ON aa.collowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_conversion'::regclass
+ AS regclass,
+ pg_describe_object('pg_conversion'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_conversion AS aa
+ JOIN pg_catalog.pg_authid ON aa.conowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_database'::regclass
+ AS regclass,
+ pg_describe_object('pg_database'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_database AS aa
+ JOIN pg_catalog.pg_authid ON aa.datdba = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_default_acl'::regclass
+ AS regclass,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_default_acl AS aa
+ JOIN pg_catalog.pg_authid ON aa.defaclrole = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_event_trigger'::regclass
+ AS regclass,
+ pg_describe_object('pg_event_trigger'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_event_trigger AS aa
+ JOIN pg_catalog.pg_authid ON aa.evtowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_extension'::regclass
+ AS regclass,
+ pg_describe_object('pg_extension'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_extension AS aa
+ JOIN pg_catalog.pg_authid ON aa.extowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ JOIN pg_catalog.pg_authid ON aa.fdwowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_foreign_server'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_server'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_foreign_server AS aa
+ JOIN pg_catalog.pg_authid ON aa.srvowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_language'::regclass
+ AS regclass,
+ pg_describe_object('pg_language'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_language AS aa
+ JOIN pg_catalog.pg_authid ON aa.lanowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_largeobject_metadata'::regclass
+ AS regclass,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+ JOIN pg_catalog.pg_authid ON aa.lomowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_namespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_namespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_namespace AS aa
+ JOIN pg_catalog.pg_authid ON aa.nspowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_opclass'::regclass
+ AS regclass,
+ pg_describe_object('pg_opclass'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_opclass AS aa
+ JOIN pg_catalog.pg_authid ON aa.opcowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_operator'::regclass
+ AS regclass,
+ pg_describe_object('pg_operator'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_operator AS aa
+ JOIN pg_catalog.pg_authid ON aa.oprowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_opfamily'::regclass
+ AS regclass,
+ pg_describe_object('pg_opfamily'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_opfamily AS aa
+ JOIN pg_catalog.pg_authid ON aa.opfowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_policy'::regclass
+ AS regclass,
+ pg_describe_object('pg_policy'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_policy AS aa
+ JOIN pg_catalog.pg_authid ON pg_authid.oid = ANY(aa.polroles)
+UNION ALL
+ SELECT
+ 'pg_proc'::regclass
+ AS regclass,
+ pg_describe_object('pg_proc'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_proc AS aa
+ JOIN pg_catalog.pg_authid ON aa.proowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_publication'::regclass
+ AS regclass,
+ pg_describe_object('pg_publication'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_publication AS aa
+ JOIN pg_catalog.pg_authid ON aa.pubowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_statistic_ext'::regclass
+ AS regclass,
+ pg_describe_object('pg_statistic_ext'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_statistic_ext AS aa
+ JOIN pg_catalog.pg_authid ON aa.stxowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_subscription'::regclass
+ AS regclass,
+ pg_describe_object('pg_subscription'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_subscription AS aa
+ JOIN pg_catalog.pg_authid ON aa.subowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_tablespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_tablespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_tablespace AS aa
+ JOIN pg_catalog.pg_authid ON aa.spcowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_ts_config'::regclass
+ AS regclass,
+ pg_describe_object('pg_ts_config'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_ts_config AS aa
+ JOIN pg_catalog.pg_authid ON aa.cfgowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_ts_dict'::regclass
+ AS regclass,
+ pg_describe_object('pg_ts_dict'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_ts_dict AS aa
+ JOIN pg_catalog.pg_authid ON aa.dictowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_type'::regclass
+ AS regclass,
+ pg_describe_object('pg_type'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_type AS aa
+ JOIN pg_catalog.pg_authid ON aa.typowner = pg_authid.oid;
+
CREATE VIEW pg_settings AS
SELECT * FROM pg_show_all_settings() AS A;
On Mon, Mar 8, 2021, at 07:28, Joel Jacobson wrote:
Attached is a new patch with both pg_permissions and pg_ownerships in the same patch,
based on HEAD (8a812e5106c5db50039336288d376a188844e2cc).Attachments:
0001-pg_permissions-and-pg_ownerships.patch
I forgot to update src/test/regress/expected/rules.out.
New patch attached.
/Joel
Attachments:
0002-pg_permissions-and-pg_ownerships.patchapplication/octet-stream; name=0002-pg_permissions-and-pg_ownerships.patchDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index fc94a73a54..ca8b88a682 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,484 @@ 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.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_attribute'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.attrelid,aa.attnum)
+ AS obj_desc,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_class'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_database'::regclass
+ AS regclass,
+ pg_describe_object('pg_database'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.datacl)).*
+ FROM pg_catalog.pg_database AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_default_acl'::regclass
+ AS regclass,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.defaclacl)).*
+ FROM pg_catalog.pg_default_acl AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_foreign_server'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_server'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_init_privs'::regclass
+ AS regclass,
+ pg_describe_object(aa.classoid,aa.objoid,aa.objsubid)
+ AS obj_desc,
+ (aclexplode(aa.initprivs)).*
+ FROM pg_catalog.pg_init_privs AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_language'::regclass
+ AS regclass,
+ pg_describe_object('pg_language'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_largeobject_metadata'::regclass
+ AS regclass,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.lomacl)).*
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_namespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_namespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_proc'::regclass
+ AS regclass,
+ pg_describe_object('pg_proc'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_tablespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_tablespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee
+UNION ALL
+ SELECT
+ a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM
+ (
+ SELECT
+ 'pg_type'::regclass
+ AS regclass,
+ pg_describe_object('pg_type'::regclass,aa.oid,0)
+ AS obj_desc,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+ ) AS a
+ JOIN pg_catalog.pg_authid AS grantor ON grantor.oid = a.grantor
+ JOIN pg_catalog.pg_authid AS grantee ON grantee.oid = a.grantee;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ 'pg_class'::regclass
+ AS regclass,
+ pg_describe_object('pg_class'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_class AS aa
+ JOIN pg_catalog.pg_authid ON aa.relowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_collation'::regclass
+ AS regclass,
+ pg_describe_object('pg_collation'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_collation AS aa
+ JOIN pg_catalog.pg_authid ON aa.collowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_conversion'::regclass
+ AS regclass,
+ pg_describe_object('pg_conversion'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_conversion AS aa
+ JOIN pg_catalog.pg_authid ON aa.conowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_database'::regclass
+ AS regclass,
+ pg_describe_object('pg_database'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_database AS aa
+ JOIN pg_catalog.pg_authid ON aa.datdba = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_default_acl'::regclass
+ AS regclass,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_default_acl AS aa
+ JOIN pg_catalog.pg_authid ON aa.defaclrole = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_event_trigger'::regclass
+ AS regclass,
+ pg_describe_object('pg_event_trigger'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_event_trigger AS aa
+ JOIN pg_catalog.pg_authid ON aa.evtowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_extension'::regclass
+ AS regclass,
+ pg_describe_object('pg_extension'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_extension AS aa
+ JOIN pg_catalog.pg_authid ON aa.extowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+ JOIN pg_catalog.pg_authid ON aa.fdwowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_foreign_server'::regclass
+ AS regclass,
+ pg_describe_object('pg_foreign_server'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_foreign_server AS aa
+ JOIN pg_catalog.pg_authid ON aa.srvowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_language'::regclass
+ AS regclass,
+ pg_describe_object('pg_language'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_language AS aa
+ JOIN pg_catalog.pg_authid ON aa.lanowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_largeobject_metadata'::regclass
+ AS regclass,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+ JOIN pg_catalog.pg_authid ON aa.lomowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_namespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_namespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_namespace AS aa
+ JOIN pg_catalog.pg_authid ON aa.nspowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_opclass'::regclass
+ AS regclass,
+ pg_describe_object('pg_opclass'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_opclass AS aa
+ JOIN pg_catalog.pg_authid ON aa.opcowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_operator'::regclass
+ AS regclass,
+ pg_describe_object('pg_operator'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_operator AS aa
+ JOIN pg_catalog.pg_authid ON aa.oprowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_opfamily'::regclass
+ AS regclass,
+ pg_describe_object('pg_opfamily'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_opfamily AS aa
+ JOIN pg_catalog.pg_authid ON aa.opfowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_policy'::regclass
+ AS regclass,
+ pg_describe_object('pg_policy'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_policy AS aa
+ JOIN pg_catalog.pg_authid ON pg_authid.oid = ANY(aa.polroles)
+UNION ALL
+ SELECT
+ 'pg_proc'::regclass
+ AS regclass,
+ pg_describe_object('pg_proc'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_proc AS aa
+ JOIN pg_catalog.pg_authid ON aa.proowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_publication'::regclass
+ AS regclass,
+ pg_describe_object('pg_publication'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_publication AS aa
+ JOIN pg_catalog.pg_authid ON aa.pubowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_statistic_ext'::regclass
+ AS regclass,
+ pg_describe_object('pg_statistic_ext'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_statistic_ext AS aa
+ JOIN pg_catalog.pg_authid ON aa.stxowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_subscription'::regclass
+ AS regclass,
+ pg_describe_object('pg_subscription'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_subscription AS aa
+ JOIN pg_catalog.pg_authid ON aa.subowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_tablespace'::regclass
+ AS regclass,
+ pg_describe_object('pg_tablespace'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_tablespace AS aa
+ JOIN pg_catalog.pg_authid ON aa.spcowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_ts_config'::regclass
+ AS regclass,
+ pg_describe_object('pg_ts_config'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_ts_config AS aa
+ JOIN pg_catalog.pg_authid ON aa.cfgowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_ts_dict'::regclass
+ AS regclass,
+ pg_describe_object('pg_ts_dict'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_ts_dict AS aa
+ JOIN pg_catalog.pg_authid ON aa.dictowner = pg_authid.oid
+UNION ALL
+ SELECT
+ 'pg_type'::regclass
+ AS regclass,
+ pg_describe_object('pg_type'::regclass,aa.oid,0)
+ AS obj_desc,
+ pg_authid.rolname
+ FROM pg_catalog.pg_type AS aa
+ JOIN pg_catalog.pg_authid ON aa.typowner = pg_authid.oid;
+
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 b1c9b7bdfe..79b1322195 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1408,6 +1408,356 @@ 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_class'::regclass AS regclass,
+ pg_describe_object(('pg_class'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_class aa
+ JOIN pg_authid ON ((aa.relowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_collation'::regclass AS regclass,
+ pg_describe_object(('pg_collation'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_collation aa
+ JOIN pg_authid ON ((aa.collowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_conversion'::regclass AS regclass,
+ pg_describe_object(('pg_conversion'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_conversion aa
+ JOIN pg_authid ON ((aa.conowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_database'::regclass AS regclass,
+ pg_describe_object(('pg_database'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_database aa
+ JOIN pg_authid ON ((aa.datdba = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_default_acl'::regclass AS regclass,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_default_acl aa
+ JOIN pg_authid ON ((aa.defaclrole = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_event_trigger'::regclass AS regclass,
+ pg_describe_object(('pg_event_trigger'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_event_trigger aa
+ JOIN pg_authid ON ((aa.evtowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_extension'::regclass AS regclass,
+ pg_describe_object(('pg_extension'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_extension aa
+ JOIN pg_authid ON ((aa.extowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_foreign_data_wrapper'::regclass AS regclass,
+ pg_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_foreign_data_wrapper aa
+ JOIN pg_authid ON ((aa.fdwowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_foreign_server'::regclass AS regclass,
+ pg_describe_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_foreign_server aa
+ JOIN pg_authid ON ((aa.srvowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_language'::regclass AS regclass,
+ pg_describe_object(('pg_language'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_language aa
+ JOIN pg_authid ON ((aa.lanowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_largeobject_metadata'::regclass AS regclass,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_largeobject_metadata aa
+ JOIN pg_authid ON ((aa.lomowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_namespace'::regclass AS regclass,
+ pg_describe_object(('pg_namespace'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_namespace aa
+ JOIN pg_authid ON ((aa.nspowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_opclass'::regclass AS regclass,
+ pg_describe_object(('pg_opclass'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_opclass aa
+ JOIN pg_authid ON ((aa.opcowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_operator'::regclass AS regclass,
+ pg_describe_object(('pg_operator'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_operator aa
+ JOIN pg_authid ON ((aa.oprowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_opfamily'::regclass AS regclass,
+ pg_describe_object(('pg_opfamily'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_opfamily aa
+ JOIN pg_authid ON ((aa.opfowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_policy'::regclass AS regclass,
+ pg_describe_object(('pg_policy'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_policy aa
+ JOIN pg_authid ON ((pg_authid.oid = ANY (aa.polroles))))
+UNION ALL
+ SELECT 'pg_proc'::regclass AS regclass,
+ pg_describe_object(('pg_proc'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_proc aa
+ JOIN pg_authid ON ((aa.proowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_publication'::regclass AS regclass,
+ pg_describe_object(('pg_publication'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_publication aa
+ JOIN pg_authid ON ((aa.pubowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_statistic_ext'::regclass AS regclass,
+ pg_describe_object(('pg_statistic_ext'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_statistic_ext aa
+ JOIN pg_authid ON ((aa.stxowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_subscription'::regclass AS regclass,
+ pg_describe_object(('pg_subscription'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_subscription aa
+ JOIN pg_authid ON ((aa.subowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_tablespace'::regclass AS regclass,
+ pg_describe_object(('pg_tablespace'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_tablespace aa
+ JOIN pg_authid ON ((aa.spcowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_ts_config'::regclass AS regclass,
+ pg_describe_object(('pg_ts_config'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_ts_config aa
+ JOIN pg_authid ON ((aa.cfgowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_ts_dict'::regclass AS regclass,
+ pg_describe_object(('pg_ts_dict'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_ts_dict aa
+ JOIN pg_authid ON ((aa.dictowner = pg_authid.oid)))
+UNION ALL
+ SELECT 'pg_type'::regclass AS regclass,
+ pg_describe_object(('pg_type'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ pg_authid.rolname
+ FROM (pg_type aa
+ JOIN pg_authid ON ((aa.typowner = pg_authid.oid)));
+pg_permissions| SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_attribute'::regclass AS regclass,
+ pg_describe_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_class'::regclass AS regclass,
+ pg_describe_object(('pg_class'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_database'::regclass AS regclass,
+ pg_describe_object(('pg_database'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (aclexplode(aa.datacl)).grantor AS grantor,
+ (aclexplode(aa.datacl)).grantee AS grantee,
+ (aclexplode(aa.datacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.datacl)).is_grantable AS is_grantable
+ FROM pg_database aa) a
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_default_acl'::regclass AS regclass,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (aclexplode(aa.defaclacl)).grantor AS grantor,
+ (aclexplode(aa.defaclacl)).grantee AS grantee,
+ (aclexplode(aa.defaclacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.defaclacl)).is_grantable AS is_grantable
+ FROM pg_default_acl aa) a
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_foreign_data_wrapper'::regclass AS regclass,
+ pg_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_foreign_server'::regclass AS regclass,
+ pg_describe_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_init_privs'::regclass AS regclass,
+ pg_describe_object(aa.classoid, aa.objoid, aa.objsubid) AS obj_desc,
+ (aclexplode(aa.initprivs)).grantor AS grantor,
+ (aclexplode(aa.initprivs)).grantee AS grantee,
+ (aclexplode(aa.initprivs)).privilege_type AS privilege_type,
+ (aclexplode(aa.initprivs)).is_grantable AS is_grantable
+ FROM pg_init_privs aa) a
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_language'::regclass AS regclass,
+ pg_describe_object(('pg_language'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_largeobject_metadata'::regclass AS regclass,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (aclexplode(aa.lomacl)).grantor AS grantor,
+ (aclexplode(aa.lomacl)).grantee AS grantee,
+ (aclexplode(aa.lomacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.lomacl)).is_grantable AS is_grantable
+ FROM pg_largeobject_metadata aa) a
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_namespace'::regclass AS regclass,
+ pg_describe_object(('pg_namespace'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_proc'::regclass AS regclass,
+ pg_describe_object(('pg_proc'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_tablespace'::regclass AS regclass,
+ pg_describe_object(('pg_tablespace'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)))
+UNION ALL
+ SELECT a.regclass,
+ a.obj_desc,
+ grantor.rolname AS grantor,
+ grantee.rolname AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ((( SELECT 'pg_type'::regclass AS regclass,
+ pg_describe_object(('pg_type'::regclass)::oid, aa.oid, 0) AS obj_desc,
+ (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
+ JOIN pg_authid grantor ON ((grantor.oid = a.grantor)))
+ JOIN pg_authid grantee ON ((grantee.oid = a.grantee)));
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
On 3/6/21 2:03 PM, Joel Jacobson wrote:
...but to answer the question...
- What permissions are there for a specific role in the database?
you need to manually query all relevant pg_catalog or
information_schema.*_privileges views,
which is a O(n) mental effort, while the first question is mentally O(1).I think this can be improved by providing humans a single pg_permissions system view
which can be queried to answer the second question. This should save a lot of
keyboard punches.
While this is interesting and probably useful for troubleshooting, it does not
provide the complete picture if what you care about is something like "what
stuff can joel do in my database".
The reasons for this include default grants to PUBLIC and role membership, and
even that is convoluted by INHERIT/NOINHERIT role attributes.
I won't try to describe all the implications here, but a while back I wrote a
fairly comprehensive blog[1]http://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1 about it.
FWIW in the blog I reference an extension that I wrote to facilitate object and
role privilege inspection[2]https://github.com/CrunchyData/crunchy_check_access. I have toyed with the idea of morphing that into a
feature I can submit for pg15, but I don't want to spend effort on the morphing
unless there is both sufficient interest and lack of conceptual objections to
the feature. I'd love to hear from both sides of that scale.
Joe
[1]: http://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
http://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
[2]: https://github.com/CrunchyData/crunchy_check_access
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
On Mon, Mar 8, 2021, at 15:35, Joe Conway wrote:
While this is interesting and probably useful for troubleshooting, it does not
provide the complete picture if what you care about is something like "what
stuff can joel do in my database".
Good point, I agree.
I think that's a different more complicated use-case though.
Personally, I use these views to resolve errors like this:
$ dropuser test
dropuser: error: removal of role "test" failed: ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: 1 object in database joel
Hmmm. I wonder which 1 object that could be?
$ psql
# SELECT * FROM pg_ownerships WHERE rolname = 'test';
regclass | obj_desc | rolname
----------+----------+---------
pg_class | table t | test
pg_type | type t | test
pg_type | type t[] | test
(3 rows)
It could also be due to permissions, so normally I would check both pg_ownership and pg_permissions at the same time,
since otherwise I could possibly get the same error again:
$ dropuser test
dropuser: error: removal of role "test" failed: ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: 1 object in database joel
# SELECT * FROM pg_permissions WHERE grantee = 'test';
regclass | obj_desc | grantor | grantee | privilege_type | is_grantable
----------+----------+---------+---------+----------------+--------------
pg_class | table t | joel | test | INSERT | f
(1 row)
Now, this situation is probably easiest to quickly resolve using REASSIGN OWNED BY ... TO ...
but I think that command is scary, I would rather prefer to resolve it manually
to not blindly cause problems.
/Joel
On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
regclass | obj_desc | grantor | grantee |
privilege_type | is_grantable
--------------+-----------------------------+---------+---------+----------------+--------------
1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
In other words, s/rolname/oid::regrole/ throughout the view definition.
It looks the same visually, but should be easier to build on in a larger
query.
Hmm, ok, a grantee of 'public' can't be expressed as a regrole. This
seems an annoying little corner.[1] It can be represented by 0::regrole,
but that displays as '-'. Hmm again, you can even '-'::regrole and get 0.
2. Also to facilitate use in a larger query, how about columns for the
objid and objsubid, in addition to the human-friendly obj_desc?
And I'm not sure about using pg_attribute as the regclass for
attributes; it's nice to look at, but could also plant the wrong idea
that attributes have pg_attribute as their classid, when it's really
pg_class with an objsubid. Anyway, there's the human-friendly obj_desc
to tell you it's a column.
On 03/08/21 12:14, Joel Jacobson wrote:
On Mon, Mar 8, 2021, at 15:35, Joe Conway wrote:
While this is interesting and probably useful for troubleshooting, it does not
provide the complete picture if what you care about is something like "what
stuff can joel do in my database".Good point, I agree.
I think that's a different more complicated use-case though.
I could agree that the role membership and inherit/noinherit part is
a more complicated problem that could be solved by a larger query built
over this view (facilitated by giving grantor and grantee regrole type)
and some recursive-CTEness with the roles.
But I think it would be useful for this view to handle the part of the story
that involves acldefault() when the stored aclitem[] is null. I've long
wanted a view that actually shows you all of the permissions that apply
to something, even the ones you're supposed to Just Know, and indeed
I wrote such a thing for $work.
Then you could even query the view for an answer to the question "what
are all the permissions 'public' (or '-') can exercise here?"
On 03/06/21 19:08, Joel Jacobson wrote:
SELECT * FROM ownerships WHERE rolname = 'joel' LIMIT 5;
regclass | obj_desc | rolname
------------------+-----------------------------------+---------
Here again, I'd repeat the suggestions to present the owner as a regrole
(and in this case there is no need to deal with 'public'), and to include
the objid as well as the human-friendly obj_desc.
Regards,
-Chap
On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
regclass | obj_desc | grantor | grantee |
privilege_type | is_grantable
--------------+-----------------------------+---------+---------+----------------+--------------
1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
I considered it, but this view is tailored for human-use,
to be used by experienced as well as beginner users.
In other words, s/rolname/oid::regrole/ throughout the view definition.
It looks the same visually, but should be easier to build on in a larger
query.
If using regrole, the users would need to know they would need to cast it to text, to search for values, e.g.:
SELECT * FROM pg_permissions WHERE grantee = 'foobar';
ERROR: invalid input syntax for type oid: "foobar"
LINE 1: SELECT * FROM pg_permissions WHERE grantee = 'foobar';
SELECT * FROM pg_permissions WHERE grantee LIKE 'foo%';
ERROR: operator does not exist: regrole ~~ unknown
LINE 1: SELECT * FROM pg_permissions WHERE grantee LIKE 'foo%';
2. Also to facilitate use in a larger query, how about columns for the
objid and objsubid, in addition to the human-friendly obj_desc?
I think it's good to prevent users from abusing this view,
by not including oids and other columns needed for proper
integration in larger queries/systems.
Otherwise there is a risk users will be sloppy and just join pg_permissions,
when they really should be joining some specific catalog.
Also, lots of extra columns not needed by humans just makes the view less readable,
since you would more often need to \x when the width of the output does't fit.
Personally, I'm on a 15" MacBook Pro and I usually have two 117x24 terminals next to each other,
in which both pg_permissions and pg_ownerships output usually fits fine.
And I'm not sure about using pg_attribute as the regclass for
attributes; it's nice to look at, but could also plant the wrong idea
that attributes have pg_attribute as their classid, when it's really
pg_class with an objsubid. Anyway, there's the human-friendly obj_desc
to tell you it's a column.
While pg_class is the "origin class", I think we convey more meaningful information,
by using the regclass for the table which stores the aclitem[] column,
in your example, pg_attribute. This makes it more obvious to the user the permission
is on some column, rather than on the table. In the case where you try to drop a user
and don't understand why you can't, and then look in pg_permissions what could be the
reason, it's more helpful to show pg_attribute than pg_class, since you hopefully then
understand you should revoke permissions for some column, and not the table.
You get this information in obj_desc as well, but I think regclass complements it nicely.
And it's also more precise, the permission *is* really on pg_attribute,
it just happens to be that pg_attribute has a multi-key primary key,
where one of the keys is referencing pg_class.oid.
But I think it would be useful for this view to handle the part of the story
that involves acldefault() when the stored aclitem[] is null. I've long
wanted a view that actually shows you all of the permissions that apply
to something, even the ones you're supposed to Just Know, and indeed
I wrote such a thing for $work.
Then you could even query the view for an answer to the question "what
are all the permissions 'public' (or '-') can exercise here?"
Seems useful, but maybe that's a different view/function?
Could it be integrated into these views without increasing complexity?
/Joel
On Tue, Mar 9, 2021, at 07:34, Joel Jacobson wrote:
On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
Having digested your idea, I actually agree with you.
Since we have the regrole-type, I agree we should use it,
even though we need to cast, no biggie.
I realized my arguments were silly since I already exposed the class as regclass,
which has the same problem.
I'll send a new patch soon.
/Joel
On 03/09/21 11:11, Joel Jacobson wrote:
On Tue, Mar 9, 2021, at 07:34, Joel Jacobson wrote:
On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
Having digested your idea, I actually agree with you.
Since we have the regrole-type, I agree we should use it,
even though we need to cast, no biggie.
This does highlight [topicshift] one sort of
inconvenience I've observed before in other settings: how fussy
it may be to write WHERE grantee = 'bob' when there is no user 'bob'.
A simple cast 'bob'::regrole raises undefined_object (in class
"Syntax Error or Access Rule Violation") rather than just returning
no rows because no grantee is bob.
It's a more general issue: I first noticed it when I had proudly
implemented my first PostgreSQL type foo that would only accept
valid foos as values, and the next thing that happened was my
colleague in frontend development wrote mean Python comments about me
because he couldn't simply search for a foo in a table without either
first duplicating the validation of the value or trapping the error
if the user had entered a non-foo to search for.
We could solve that, of course, by implementing = and <> (foo,text)
to simply return false (resp. true) if the text arg isn't castable
to foo.
But the na�ve way of writing such an operator repeats the castability
test for every row compared. If I were to build such an operator now,
I might explore whether a planner support function could be used
to check the castability once, and replace the whole comparison with
constant false if that fails.
And this strikes me as a situation that might be faced often enough
to wonder if some kind of meta-support-function would be worth supplying
that could do that for any type foo.
[/topicshift]
Regards,
-Chap
On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
regclass | obj_desc | grantor | grantee |
privilege_type | is_grantable
--------------+-----------------------------+---------+---------+----------------+--------------
1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
In other words, s/rolname/oid::regrole/ throughout the view definition.
It looks the same visually, but should be easier to build on in a larger
query.Hmm, ok, a grantee of 'public' can't be expressed as a regrole. This
seems an annoying little corner.[1] It can be represented by 0::regrole,
but that displays as '-'. Hmm again, you can even '-'::regrole and get 0.2. Also to facilitate use in a larger query, how about columns for the
objid and objsubid, in addition to the human-friendly obj_desc?
And I'm not sure about using pg_attribute as the regclass for
attributes; it's nice to look at, but could also plant the wrong idea
that attributes have pg_attribute as their classid, when it's really
pg_class with an objsubid. Anyway, there's the human-friendly obj_desc
to tell you it's a column.
Thanks for coming up with these two good ideas. I was wrong, they are great.
Both have now been implemented.
New patch attached.
Example usage:
CREATE ROLE test_user;
CREATE ROLE test_group;
CREATE ROLE test_owner;
CREATE SCHEMA test AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA test TO test_group;
GRANT test_group TO test_user;
SELECT * FROM pg_permissions WHERE grantor = 'test_owner'::regrole;
classid | objid | objsubid | objdesc | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+-------------+------------+------------+----------------+--------------
pg_namespace | 16390 | 0 | schema test | test_owner | test_owner | USAGE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_owner | CREATE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | USAGE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | CREATE | f
(4 rows)
SET ROLE TO test_user;
CREATE TABLE test.a ();
RESET ROLE;
SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | objdesc | owner
--------------+-------+----------+-------------+------------
pg_namespace | 16390 | 0 | schema test | test_owner
(1 row)
ALTER TABLE test.a OWNER TO test_owner;
SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | objdesc | owner
--------------+-------+----------+-------------+------------
pg_class | 16391 | 0 | table a | test_owner
pg_namespace | 16390 | 0 | schema test | test_owner
pg_type | 16393 | 0 | type a | test_owner
pg_type | 16392 | 0 | type a[] | test_owner
(4 rows)
GRANT INSERT ON test.a TO test_group;
SELECT * FROM pg_permissions WHERE grantee = 'test_group'::regrole;
classid | objid | objsubid | objdesc | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+-------------+------------+------------+----------------+--------------
pg_class | 16391 | 0 | table a | test_owner | test_group | INSERT | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | USAGE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | CREATE | f
(3 rows)
/Joel
Attachments:
0003-pg_permissions-and-pg_ownerships.patchapplication/octet-stream; name=0003-pg_permissions-and-pg_ownerships.patchDownload
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0dca65dc7b..fae70488a9 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,471 @@ 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.objdesc,
+ 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_describe_object('pg_class'::regclass,aa.attrelid,aa.attnum) AS objdesc,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_class'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_database'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_database'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.datacl)).*
+ FROM pg_catalog.pg_database AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_default_acl'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.defaclacl)).*
+ FROM pg_catalog.pg_default_acl AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_foreign_server'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ aa.classoid::regclass AS classid,
+ aa.objoid AS objid,
+ aa.objsubid AS objsubid,
+ pg_describe_object(aa.classoid,aa.objoid,aa.objsubid) AS objdesc,
+ (aclexplode(aa.initprivs)).*
+ FROM pg_catalog.pg_init_privs AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_language'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_largeobject_metadata'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.lomacl)).*
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_namespace'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_proc'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_tablespace'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_type'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+) AS a;
+
+CREATE VIEW pg_ownerships AS
+SELECT
+ 'pg_class'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_class'::regclass,a.oid,0)
+ AS objdesc,
+ a.relowner::regrole AS owner
+FROM pg_catalog.pg_class AS a
+UNION ALL
+SELECT
+ 'pg_collation'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_collation'::regclass,a.oid,0)
+ AS objdesc,
+ a.collowner::regrole AS owner
+FROM pg_catalog.pg_collation AS a
+UNION ALL
+SELECT
+ 'pg_conversion'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_conversion'::regclass,a.oid,0)
+ AS objdesc,
+ a.conowner::regrole AS owner
+FROM pg_catalog.pg_conversion AS a
+UNION ALL
+SELECT
+ 'pg_database'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_database'::regclass,a.oid,0)
+ AS objdesc,
+ a.datdba::regrole AS owner
+FROM pg_catalog.pg_database AS a
+UNION ALL
+SELECT
+ 'pg_default_acl'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_default_acl'::regclass,a.oid,0)
+ AS objdesc,
+ a.defaclrole::regrole AS owner
+FROM pg_catalog.pg_default_acl AS a
+UNION ALL
+SELECT
+ 'pg_event_trigger'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_event_trigger'::regclass,a.oid,0)
+ AS objdesc,
+ a.evtowner::regrole AS owner
+FROM pg_catalog.pg_event_trigger AS a
+UNION ALL
+SELECT
+ 'pg_extension'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_extension'::regclass,a.oid,0)
+ AS objdesc,
+ a.extowner::regrole AS owner
+FROM pg_catalog.pg_extension AS a
+UNION ALL
+SELECT
+ 'pg_foreign_data_wrapper'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,a.oid,0)
+ AS objdesc,
+ a.fdwowner::regrole AS owner
+FROM pg_catalog.pg_foreign_data_wrapper AS a
+UNION ALL
+SELECT
+ 'pg_foreign_server'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_foreign_server'::regclass,a.oid,0)
+ AS objdesc,
+ a.srvowner::regrole AS owner
+FROM pg_catalog.pg_foreign_server AS a
+UNION ALL
+SELECT
+ 'pg_language'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_language'::regclass,a.oid,0)
+ AS objdesc,
+ a.lanowner::regrole AS owner
+FROM pg_catalog.pg_language AS a
+UNION ALL
+SELECT
+ 'pg_largeobject_metadata'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_largeobject_metadata'::regclass,a.oid,0)
+ AS objdesc,
+ a.lomowner::regrole AS owner
+FROM pg_catalog.pg_largeobject_metadata AS a
+UNION ALL
+SELECT
+ 'pg_namespace'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_namespace'::regclass,a.oid,0)
+ AS objdesc,
+ a.nspowner::regrole AS owner
+FROM pg_catalog.pg_namespace AS a
+UNION ALL
+SELECT
+ 'pg_opclass'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_opclass'::regclass,a.oid,0)
+ AS objdesc,
+ a.opcowner::regrole AS owner
+FROM pg_catalog.pg_opclass AS a
+UNION ALL
+SELECT
+ 'pg_operator'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_operator'::regclass,a.oid,0)
+ AS objdesc,
+ a.oprowner::regrole AS owner
+FROM pg_catalog.pg_operator AS a
+UNION ALL
+SELECT
+ 'pg_opfamily'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_opfamily'::regclass,a.oid,0)
+ AS objdesc,
+ a.opfowner::regrole AS owner
+FROM pg_catalog.pg_opfamily AS a
+UNION ALL
+SELECT
+ 'pg_policy'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_policy'::regclass,a.oid,0)
+ AS objdesc,
+ unnest(a.polroles)::regrole AS owner
+FROM pg_catalog.pg_policy AS a
+UNION ALL
+SELECT
+ 'pg_proc'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_proc'::regclass,a.oid,0)
+ AS objdesc,
+ a.proowner::regrole AS owner
+FROM pg_catalog.pg_proc AS a
+UNION ALL
+SELECT
+ 'pg_publication'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_publication'::regclass,a.oid,0)
+ AS objdesc,
+ a.pubowner::regrole AS owner
+FROM pg_catalog.pg_publication AS a
+UNION ALL
+SELECT
+ 'pg_statistic_ext'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_statistic_ext'::regclass,a.oid,0)
+ AS objdesc,
+ a.stxowner::regrole AS owner
+FROM pg_catalog.pg_statistic_ext AS a
+UNION ALL
+SELECT
+ 'pg_subscription'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_subscription'::regclass,a.oid,0)
+ AS objdesc,
+ a.subowner::regrole AS owner
+FROM pg_catalog.pg_subscription AS a
+UNION ALL
+SELECT
+ 'pg_tablespace'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_tablespace'::regclass,a.oid,0)
+ AS objdesc,
+ a.spcowner::regrole AS owner
+FROM pg_catalog.pg_tablespace AS a
+UNION ALL
+SELECT
+ 'pg_ts_config'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_ts_config'::regclass,a.oid,0)
+ AS objdesc,
+ a.cfgowner::regrole AS owner
+FROM pg_catalog.pg_ts_config AS a
+UNION ALL
+SELECT
+ 'pg_ts_dict'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_ts_dict'::regclass,a.oid,0)
+ AS objdesc,
+ a.dictowner::regrole AS owner
+FROM pg_catalog.pg_ts_dict AS a
+UNION ALL
+SELECT
+ 'pg_type'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_type'::regclass,a.oid,0)
+ AS objdesc,
+ a.typowner::regrole AS owner
+FROM pg_catalog.pg_type AS a;
+
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..6a85b09675 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1408,6 +1408,406 @@ 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_class'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_class'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.relowner)::regrole AS owner
+ FROM pg_class a
+UNION ALL
+ SELECT 'pg_collation'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_collation'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.collowner)::regrole AS owner
+ FROM pg_collation a
+UNION ALL
+ SELECT 'pg_conversion'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_conversion'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.conowner)::regrole AS owner
+ FROM pg_conversion a
+UNION ALL
+ SELECT 'pg_database'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_database'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.datdba)::regrole AS owner
+ FROM pg_database a
+UNION ALL
+ SELECT 'pg_default_acl'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.defaclrole)::regrole AS owner
+ FROM pg_default_acl a
+UNION ALL
+ SELECT 'pg_event_trigger'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_event_trigger'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.evtowner)::regrole AS owner
+ FROM pg_event_trigger a
+UNION ALL
+ SELECT 'pg_extension'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_extension'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.extowner)::regrole AS owner
+ FROM pg_extension a
+UNION ALL
+ SELECT 'pg_foreign_data_wrapper'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.fdwowner)::regrole AS owner
+ FROM pg_foreign_data_wrapper a
+UNION ALL
+ SELECT 'pg_foreign_server'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_foreign_server'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.srvowner)::regrole AS owner
+ FROM pg_foreign_server a
+UNION ALL
+ SELECT 'pg_language'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_language'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.lanowner)::regrole AS owner
+ FROM pg_language a
+UNION ALL
+ SELECT 'pg_largeobject_metadata'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.lomowner)::regrole AS owner
+ FROM pg_largeobject_metadata a
+UNION ALL
+ SELECT 'pg_namespace'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_namespace'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.nspowner)::regrole AS owner
+ FROM pg_namespace a
+UNION ALL
+ SELECT 'pg_opclass'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_opclass'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.opcowner)::regrole AS owner
+ FROM pg_opclass a
+UNION ALL
+ SELECT 'pg_operator'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_operator'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.oprowner)::regrole AS owner
+ FROM pg_operator a
+UNION ALL
+ SELECT 'pg_opfamily'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_opfamily'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.opfowner)::regrole AS owner
+ FROM pg_opfamily a
+UNION ALL
+ SELECT 'pg_policy'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_policy'::regclass)::oid, a.oid, 0) AS objdesc,
+ (unnest(a.polroles))::regrole AS owner
+ FROM pg_policy a
+UNION ALL
+ SELECT 'pg_proc'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_proc'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.proowner)::regrole AS owner
+ FROM pg_proc a
+UNION ALL
+ SELECT 'pg_publication'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_publication'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.pubowner)::regrole AS owner
+ FROM pg_publication a
+UNION ALL
+ SELECT 'pg_statistic_ext'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_statistic_ext'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.stxowner)::regrole AS owner
+ FROM pg_statistic_ext a
+UNION ALL
+ SELECT 'pg_subscription'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_subscription'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.subowner)::regrole AS owner
+ FROM pg_subscription a
+UNION ALL
+ SELECT 'pg_tablespace'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_tablespace'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.spcowner)::regrole AS owner
+ FROM pg_tablespace a
+UNION ALL
+ SELECT 'pg_ts_config'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_ts_config'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.cfgowner)::regrole AS owner
+ FROM pg_ts_config a
+UNION ALL
+ SELECT 'pg_ts_dict'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_ts_dict'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.dictowner)::regrole AS owner
+ FROM pg_ts_dict a
+UNION ALL
+ SELECT 'pg_type'::regclass AS classid,
+ a.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_type'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.typowner)::regrole AS owner
+ FROM pg_type a;
+pg_permissions| SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_class'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_database'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_database'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.datacl)).grantor AS grantor,
+ (aclexplode(aa.datacl)).grantee AS grantee,
+ (aclexplode(aa.datacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.datacl)).is_grantable AS is_grantable
+ FROM pg_database aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_default_acl'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.defaclacl)).grantor AS grantor,
+ (aclexplode(aa.defaclacl)).grantee AS grantee,
+ (aclexplode(aa.defaclacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.defaclacl)).is_grantable AS is_grantable
+ FROM pg_default_acl aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT (aa.classoid)::regclass AS classid,
+ aa.objoid AS objid,
+ aa.objsubid,
+ pg_describe_object(aa.classoid, aa.objoid, aa.objsubid) AS objdesc,
+ (aclexplode(aa.initprivs)).grantor AS grantor,
+ (aclexplode(aa.initprivs)).grantee AS grantee,
+ (aclexplode(aa.initprivs)).privilege_type AS privilege_type,
+ (aclexplode(aa.initprivs)).is_grantable AS is_grantable
+ FROM pg_init_privs aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_language'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_largeobject_metadata'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.lomacl)).grantor AS grantor,
+ (aclexplode(aa.lomacl)).grantee AS grantee,
+ (aclexplode(aa.lomacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.lomacl)).is_grantable AS is_grantable
+ FROM pg_largeobject_metadata aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_namespace'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_proc'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_tablespace'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_type'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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,
New version attached.
Changes:
* Added documentation in catalogs.sgml
* Dropped "objsubid" from pg_ownerships since columns have no owner, only tables
Do we prefer "pg_permissions" or "pg_privileges"?
I can see "privileges" occur 2325 times in the sources,
while "permissions" occur only 1097 times.
Personally, I would prefer "pg_permissions" since it seems more common in general.
"database permissions" gives 195 000 000 results on Google,
while "database privileges" only gives 46 800 000 Google results.
If we would have consistently used only "privileges" so far,
I would vote for "pg_privileges", but since there is already a mix,
I think "pg_permissions" would be nicer, it's easier to get right typing also.
/Joel
Attachments:
0004-pg_permissions-and-pg_ownerships.patchapplication/octet-stream; name=0004-pg_permissions-and-pg_ownerships.patchDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b1de6d0674..91fee3f7ec 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9337,6 +9337,16 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry>materialized views</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry>
+ <entry>ownerships</entry>
+ </row>
+
+ <row>
+ <entry><link linkend="view-pg-permissions"><structname>pg_permissions</structname></link></entry>
+ <entry>permissions</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry>
<entry>policies</entry>
@@ -10855,6 +10865,189 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-ownerships">
+ <title><structname>pg_ownerships</structname></title>
+
+ <indexterm zone="view-pg-ownerships">
+ <primary>pg_ownerships</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_ownerships</structname> provides access to information about object ownerships.
+ </para>
+
+ <table>
+ <title><structname>pg_ownerships</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the owned object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objdesc</structfield> <type>text</type>
+ </para>
+ <para>
+ Textual description of the owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>owner</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Owner of the object
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-permissions">
+ <title><structname>pg_permissions</structname></title>
+
+ <indexterm zone="view-pg-permissions">
+ <primary>pg_permissions</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_permissions</structname> provides access to information about explicitly granted privileges on database objects.
+ The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>.
+ </para>
+
+ <table>
+ <title><structname>pg_permissions</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the granted object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific granted object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objdesc</structfield> <type>text</type>
+ </para>
+ <para>
+ Textual description of the object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantor</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role that granted this permission
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantee</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role to whom permission is granted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>privilege_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the privilege: <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, or
+ <literal>REFERENCES</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>is_grantable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-policies">
<title><structname>pg_policies</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0dca65dc7b..260137fe91 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -510,6 +510,447 @@ 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.objdesc,
+ 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_describe_object('pg_class'::regclass,aa.attrelid,aa.attnum) AS objdesc,
+ (aclexplode(aa.attacl)).*
+ FROM pg_catalog.pg_attribute AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_class'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.relacl)).*
+ FROM pg_catalog.pg_class AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_database'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_database'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.datacl)).*
+ FROM pg_catalog.pg_database AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_default_acl'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_default_acl'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.defaclacl)).*
+ FROM pg_catalog.pg_default_acl AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_foreign_data_wrapper'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.fdwacl)).*
+ FROM pg_catalog.pg_foreign_data_wrapper AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_foreign_server'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.srvacl)).*
+ FROM pg_catalog.pg_foreign_server AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ aa.classoid::regclass AS classid,
+ aa.objoid AS objid,
+ aa.objsubid AS objsubid,
+ pg_describe_object(aa.classoid,aa.objoid,aa.objsubid) AS objdesc,
+ (aclexplode(aa.initprivs)).*
+ FROM pg_catalog.pg_init_privs AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_language'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.lanacl)).*
+ FROM pg_catalog.pg_language AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ a.grantor::regrole,
+ a.grantee::regrole,
+ a.privilege_type,
+ a.is_grantable
+FROM
+(
+ SELECT
+ 'pg_largeobject_metadata'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object('pg_largeobject_metadata'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.lomacl)).*
+ FROM pg_catalog.pg_largeobject_metadata AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_namespace'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.nspacl)).*
+ FROM pg_catalog.pg_namespace AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_proc'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.proacl)).*
+ FROM pg_catalog.pg_proc AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_tablespace'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.spcacl)).*
+ FROM pg_catalog.pg_tablespace AS aa
+) AS a
+UNION ALL
+SELECT
+ a.classid,
+ a.objid,
+ a.objsubid,a.objdesc,
+ 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_describe_object('pg_type'::regclass,aa.oid,0) AS objdesc,
+ (aclexplode(aa.typacl)).*
+ FROM pg_catalog.pg_type AS aa
+) AS a;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ 'pg_class'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_class'::regclass,a.oid,0)
+ AS objdesc,
+ a.relowner::regrole AS owner
+ FROM pg_catalog.pg_class AS a
+UNION ALL
+ SELECT
+ 'pg_collation'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_collation'::regclass,a.oid,0)
+ AS objdesc,
+ a.collowner::regrole AS owner
+ FROM pg_catalog.pg_collation AS a
+UNION ALL
+ SELECT
+ 'pg_conversion'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_conversion'::regclass,a.oid,0)
+ AS objdesc,
+ a.conowner::regrole AS owner
+ FROM pg_catalog.pg_conversion AS a
+UNION ALL
+ SELECT
+ 'pg_database'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_database'::regclass,a.oid,0)
+ AS objdesc,
+ a.datdba::regrole AS owner
+ FROM pg_catalog.pg_database AS a
+UNION ALL
+ SELECT
+ 'pg_default_acl'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_default_acl'::regclass,a.oid,0)
+ AS objdesc,
+ a.defaclrole::regrole AS owner
+ FROM pg_catalog.pg_default_acl AS a
+UNION ALL
+ SELECT
+ 'pg_event_trigger'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_event_trigger'::regclass,a.oid,0)
+ AS objdesc,
+ a.evtowner::regrole AS owner
+ FROM pg_catalog.pg_event_trigger AS a
+UNION ALL
+ SELECT
+ 'pg_extension'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_extension'::regclass,a.oid,0)
+ AS objdesc,
+ a.extowner::regrole AS owner
+ FROM pg_catalog.pg_extension AS a
+UNION ALL
+ SELECT
+ 'pg_foreign_data_wrapper'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_foreign_data_wrapper'::regclass,a.oid,0)
+ AS objdesc,
+ a.fdwowner::regrole AS owner
+ FROM pg_catalog.pg_foreign_data_wrapper AS a
+UNION ALL
+ SELECT
+ 'pg_foreign_server'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_foreign_server'::regclass,a.oid,0)
+ AS objdesc,
+ a.srvowner::regrole AS owner
+ FROM pg_catalog.pg_foreign_server AS a
+UNION ALL
+ SELECT
+ 'pg_language'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_language'::regclass,a.oid,0)
+ AS objdesc,
+ a.lanowner::regrole AS owner
+ FROM pg_catalog.pg_language AS a
+UNION ALL
+ SELECT
+ 'pg_largeobject_metadata'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_largeobject_metadata'::regclass,a.oid,0)
+ AS objdesc,
+ a.lomowner::regrole AS owner
+ FROM pg_catalog.pg_largeobject_metadata AS a
+UNION ALL
+ SELECT
+ 'pg_namespace'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_namespace'::regclass,a.oid,0)
+ AS objdesc,
+ a.nspowner::regrole AS owner
+ FROM pg_catalog.pg_namespace AS a
+UNION ALL
+ SELECT
+ 'pg_opclass'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_opclass'::regclass,a.oid,0)
+ AS objdesc,
+ a.opcowner::regrole AS owner
+ FROM pg_catalog.pg_opclass AS a
+UNION ALL
+ SELECT
+ 'pg_operator'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_operator'::regclass,a.oid,0)
+ AS objdesc,
+ a.oprowner::regrole AS owner
+ FROM pg_catalog.pg_operator AS a
+UNION ALL
+ SELECT
+ 'pg_opfamily'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_opfamily'::regclass,a.oid,0)
+ AS objdesc,
+ a.opfowner::regrole AS owner
+ FROM pg_catalog.pg_opfamily AS a
+UNION ALL
+ SELECT
+ 'pg_policy'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_policy'::regclass,a.oid,0)
+ AS objdesc,
+ unnest(a.polroles)::regrole AS owner
+ FROM pg_catalog.pg_policy AS a
+UNION ALL
+ SELECT
+ 'pg_proc'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_proc'::regclass,a.oid,0)
+ AS objdesc,
+ a.proowner::regrole AS owner
+ FROM pg_catalog.pg_proc AS a
+UNION ALL
+ SELECT
+ 'pg_publication'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_publication'::regclass,a.oid,0)
+ AS objdesc,
+ a.pubowner::regrole AS owner
+ FROM pg_catalog.pg_publication AS a
+UNION ALL
+ SELECT
+ 'pg_statistic_ext'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_statistic_ext'::regclass,a.oid,0)
+ AS objdesc,
+ a.stxowner::regrole AS owner
+ FROM pg_catalog.pg_statistic_ext AS a
+UNION ALL
+ SELECT
+ 'pg_subscription'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_subscription'::regclass,a.oid,0)
+ AS objdesc,
+ a.subowner::regrole AS owner
+ FROM pg_catalog.pg_subscription AS a
+UNION ALL
+ SELECT
+ 'pg_tablespace'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_tablespace'::regclass,a.oid,0)
+ AS objdesc,
+ a.spcowner::regrole AS owner
+ FROM pg_catalog.pg_tablespace AS a
+UNION ALL
+ SELECT
+ 'pg_ts_config'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_ts_config'::regclass,a.oid,0)
+ AS objdesc,
+ a.cfgowner::regrole AS owner
+ FROM pg_catalog.pg_ts_config AS a
+UNION ALL
+ SELECT
+ 'pg_ts_dict'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_ts_dict'::regclass,a.oid,0)
+ AS objdesc,
+ a.dictowner::regrole AS owner
+ FROM pg_catalog.pg_ts_dict AS a
+UNION ALL
+ SELECT
+ 'pg_type'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object('pg_type'::regclass,a.oid,0)
+ AS objdesc,
+ a.typowner::regrole AS owner
+ FROM pg_catalog.pg_type AS a;
+
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..390ca4ab84 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1408,6 +1408,382 @@ 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_class'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_class'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.relowner)::regrole AS owner
+ FROM pg_class a
+UNION ALL
+ SELECT 'pg_collation'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_collation'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.collowner)::regrole AS owner
+ FROM pg_collation a
+UNION ALL
+ SELECT 'pg_conversion'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_conversion'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.conowner)::regrole AS owner
+ FROM pg_conversion a
+UNION ALL
+ SELECT 'pg_database'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_database'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.datdba)::regrole AS owner
+ FROM pg_database a
+UNION ALL
+ SELECT 'pg_default_acl'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.defaclrole)::regrole AS owner
+ FROM pg_default_acl a
+UNION ALL
+ SELECT 'pg_event_trigger'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_event_trigger'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.evtowner)::regrole AS owner
+ FROM pg_event_trigger a
+UNION ALL
+ SELECT 'pg_extension'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_extension'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.extowner)::regrole AS owner
+ FROM pg_extension a
+UNION ALL
+ SELECT 'pg_foreign_data_wrapper'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.fdwowner)::regrole AS owner
+ FROM pg_foreign_data_wrapper a
+UNION ALL
+ SELECT 'pg_foreign_server'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_foreign_server'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.srvowner)::regrole AS owner
+ FROM pg_foreign_server a
+UNION ALL
+ SELECT 'pg_language'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_language'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.lanowner)::regrole AS owner
+ FROM pg_language a
+UNION ALL
+ SELECT 'pg_largeobject_metadata'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.lomowner)::regrole AS owner
+ FROM pg_largeobject_metadata a
+UNION ALL
+ SELECT 'pg_namespace'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_namespace'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.nspowner)::regrole AS owner
+ FROM pg_namespace a
+UNION ALL
+ SELECT 'pg_opclass'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_opclass'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.opcowner)::regrole AS owner
+ FROM pg_opclass a
+UNION ALL
+ SELECT 'pg_operator'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_operator'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.oprowner)::regrole AS owner
+ FROM pg_operator a
+UNION ALL
+ SELECT 'pg_opfamily'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_opfamily'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.opfowner)::regrole AS owner
+ FROM pg_opfamily a
+UNION ALL
+ SELECT 'pg_policy'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_policy'::regclass)::oid, a.oid, 0) AS objdesc,
+ (unnest(a.polroles))::regrole AS owner
+ FROM pg_policy a
+UNION ALL
+ SELECT 'pg_proc'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_proc'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.proowner)::regrole AS owner
+ FROM pg_proc a
+UNION ALL
+ SELECT 'pg_publication'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_publication'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.pubowner)::regrole AS owner
+ FROM pg_publication a
+UNION ALL
+ SELECT 'pg_statistic_ext'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_statistic_ext'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.stxowner)::regrole AS owner
+ FROM pg_statistic_ext a
+UNION ALL
+ SELECT 'pg_subscription'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_subscription'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.subowner)::regrole AS owner
+ FROM pg_subscription a
+UNION ALL
+ SELECT 'pg_tablespace'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_tablespace'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.spcowner)::regrole AS owner
+ FROM pg_tablespace a
+UNION ALL
+ SELECT 'pg_ts_config'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_ts_config'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.cfgowner)::regrole AS owner
+ FROM pg_ts_config a
+UNION ALL
+ SELECT 'pg_ts_dict'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_ts_dict'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.dictowner)::regrole AS owner
+ FROM pg_ts_dict a
+UNION ALL
+ SELECT 'pg_type'::regclass AS classid,
+ a.oid AS objid,
+ pg_describe_object(('pg_type'::regclass)::oid, a.oid, 0) AS objdesc,
+ (a.typowner)::regrole AS owner
+ FROM pg_type a;
+pg_permissions| SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_class'::regclass)::oid, aa.attrelid, (aa.attnum)::integer) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_class'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_database'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_database'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.datacl)).grantor AS grantor,
+ (aclexplode(aa.datacl)).grantee AS grantee,
+ (aclexplode(aa.datacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.datacl)).is_grantable AS is_grantable
+ FROM pg_database aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_default_acl'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_default_acl'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.defaclacl)).grantor AS grantor,
+ (aclexplode(aa.defaclacl)).grantee AS grantee,
+ (aclexplode(aa.defaclacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.defaclacl)).is_grantable AS is_grantable
+ FROM pg_default_acl aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_foreign_data_wrapper'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_foreign_server'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT (aa.classoid)::regclass AS classid,
+ aa.objoid AS objid,
+ aa.objsubid,
+ pg_describe_object(aa.classoid, aa.objoid, aa.objsubid) AS objdesc,
+ (aclexplode(aa.initprivs)).grantor AS grantor,
+ (aclexplode(aa.initprivs)).grantee AS grantee,
+ (aclexplode(aa.initprivs)).privilege_type AS privilege_type,
+ (aclexplode(aa.initprivs)).is_grantable AS is_grantable
+ FROM pg_init_privs aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_language'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (a.grantor)::regrole AS grantor,
+ (a.grantee)::regrole AS grantee,
+ a.privilege_type,
+ a.is_grantable
+ FROM ( SELECT 'pg_largeobject_metadata'::regclass AS classid,
+ aa.oid AS objid,
+ 0 AS objsubid,
+ pg_describe_object(('pg_largeobject_metadata'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (aclexplode(aa.lomacl)).grantor AS grantor,
+ (aclexplode(aa.lomacl)).grantee AS grantee,
+ (aclexplode(aa.lomacl)).privilege_type AS privilege_type,
+ (aclexplode(aa.lomacl)).is_grantable AS is_grantable
+ FROM pg_largeobject_metadata aa) a
+UNION ALL
+ SELECT a.classid,
+ a.objid,
+ a.objsubid,
+ a.objdesc,
+ (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_describe_object(('pg_namespace'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_proc'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_tablespace'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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.objdesc,
+ (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_describe_object(('pg_type'::regclass)::oid, aa.oid, 0) AS objdesc,
+ (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,
On Thu, Mar 11, 2021, at 08:00, Joel Jacobson wrote:
0004-pg_permissions-and-pg_ownerships.patch
Having gotten some hands-on experience of these views for a while,
I notice I quite often want to check the ownerships/permissions
for some specific type of objects, or in some specific schema.
The current patch returns pg_describe_object() as the "objdesc" column.
Would it be a better idea to instead return the fields from pg_identify_object()?
This would allow specifically filtering on "type", "schema", "name" or "identity"
instead of having to apply a regex/LIKE on the object description.
/Joel
On 2021-Mar-23, Joel Jacobson wrote:
On Thu, Mar 11, 2021, at 08:00, Joel Jacobson wrote:
0004-pg_permissions-and-pg_ownerships.patch
Having gotten some hands-on experience of these views for a while,
I notice I quite often want to check the ownerships/permissions
for some specific type of objects, or in some specific schema.The current patch returns pg_describe_object() as the "objdesc" column.
Would it be a better idea to instead return the fields from pg_identify_object()?
This would allow specifically filtering on "type", "schema", "name" or "identity"
instead of having to apply a regex/LIKE on the object description.
For programmatic use it is certainly better to use the object identity
rather than the description. Particularly because the description gets
translated, and the identity doesn't.
--
�lvaro Herrera Valdivia, Chile
On 2021-Mar-08, Joel Jacobson wrote:
$ dropuser test
dropuser: error: removal of role "test" failed: ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: 1 object in database joelHmmm. I wonder which 1 object that could be?
BTW the easiest way to find out the answer to this question with current
tech is to connect to database joel and attempt "DROP USER joel"; it
will print a list of objects the user owns or has privileges for.
# SELECT * FROM pg_ownerships WHERE rolname = 'test';
# SELECT * FROM pg_permissions WHERE grantee = 'test';
I wonder if these views should be defined on top of pg_shdepend instead
of querying every single catalog. That would make for much shorter
queries.
--
�lvaro Herrera Valdivia, Chile
On Tue, Mar 23, 2021, at 21:39, Alvaro Herrera wrote:
I wonder if these views should be defined on top of pg_shdepend instead
of querying every single catalog. That would make for much shorter
queries.
+1
pg_shdepend doesn't contain the aclitem info though,
so it won't work for pg_permissions if we want to expose
privilege_type, is_grantable and grantor.
pg_shdepend should work fine for pg_ownerships though.
The semantics will not be entirely the same,
since internal objects are not tracked in pg_shdepend,
but I think this is an improvement.
Example:
create role baz;
create type foobar as ( foo int, bar boolean );
alter type foobar owner to baz;
-- UNION ALL variant:
select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
----------+--------+----------+-------+----------------+--------+---------+-----------------
pg_class | 407858 | 0 | baz | composite type | public | foobar | public.foobar
pg_type | 407860 | 0 | baz | type | public | foobar | public.foobar
pg_type | 407859 | 0 | baz | type | public | _foobar | public.foobar[]
(3 rows)
-- pg_shdepend variant:
select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
---------+--------+----------+-------+------+--------+--------+---------------
1247 | 407860 | 0 | baz | type | public | foobar | public.foobar
(1 row)
I'll update the patch.
/Joel
On 2021-Mar-25, Joel Jacobson wrote:
pg_shdepend doesn't contain the aclitem info though,
so it won't work for pg_permissions if we want to expose
privilege_type, is_grantable and grantor.
Ah, of course -- the only way to obtain the acl columns is by going
through the catalogs individually, so it won't be possible. I think
this could be fixed with some very simple, quick function pg_get_acl()
that takes a catalog OID and object OID and returns the ACL; then
use aclexplode() to obtain all those details.
The semantics will not be entirely the same,
since internal objects are not tracked in pg_shdepend,
but I think this is an improvement.
I just realized that pg_shdepend will not show anything for pinned users
(the bootstrap superuser). I *think* this is not a problem.
--
�lvaro Herrera 39�49'30"S 73�17'W
"E pur si muove" (Galileo Galilei)
On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
On 2021-Mar-25, Joel Jacobson wrote:
pg_shdepend doesn't contain the aclitem info though,
so it won't work for pg_permissions if we want to expose
privilege_type, is_grantable and grantor.Ah, of course -- the only way to obtain the acl columns is by going
through the catalogs individually, so it won't be possible. I think
this could be fixed with some very simple, quick function pg_get_acl()
that takes a catalog OID and object OID and returns the ACL; then
use aclexplode() to obtain all those details.
+1 for adding pg_get_acl().
Do you want to write a patch for that?
I could try implementing it otherwise, but would be good with buy-in
from some more hackers on if we want these system views at all first.
Maybe we can try to decide on that first,
i.e. if we want them and what they should return?
In the meantime, if people want to try out the views,
I've modified the patch to use pg_shdepend for pg_ownerships,
while pg_permissions is still UNION ALL.
Both views now also use pg_identify_object().
Example usage:
CREATE ROLE test_user;
CREATE ROLE test_group;
CREATE ROLE test_owner;
CREATE SCHEMA test AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA test TO test_group;
GRANT test_group TO test_user;
SELECT * FROM pg_permissions WHERE grantor = 'test_owner'::regrole;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+--------+--------+------+----------+------------+------------+----------------+--------------
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_owner | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_owner | CREATE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | CREATE | f
(4 rows)
SET ROLE TO test_user;
CREATE TABLE test.a ();
RESET ROLE;
ALTER TABLE test.a OWNER TO test_owner;
SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | type | schema | name | identity | owner
---------+-------+----------+--------+--------+------+----------+------------
1259 | 37129 | 0 | table | test | a | test.a | test_owner
2615 | 37128 | 0 | schema | | test | test | test_owner
(2 rows)
GRANT INSERT ON test.a TO test_group;
SELECT * FROM pg_permissions WHERE grantee = 'test_group'::regrole;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+--------+--------+------+----------+------------+------------+----------------+--------------
pg_class | 37129 | 0 | table | test | a | test.a | test_owner | test_group | INSERT | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | CREATE | f
(3 rows)
Looks good or can we improve them further?
The semantics will not be entirely the same,
since internal objects are not tracked in pg_shdepend,
but I think this is an improvement.I just realized that pg_shdepend will not show anything for pinned users
(the bootstrap superuser). I *think* this is not a problem.
I also think it's not a problem.
Doing a "SELECT * FROM pg_ownerships" would be very noisy
if such objects would be included, as all pre-installed catalog objects would show up,
but by excluding them, the user will only see relevant ownerships explicitly owned by "real" roles.
We would get the same improvement for pg_permissions if pg_shdepend would be use there as well.
Right now it's very noisy, as all permissions also for the bootstrap superuser are included.
/Joel
Attachments:
0005-pg_permissions-and-pg_ownerships.patchapplication/octet-stream; name=0005-pg_permissions-and-pg_ownerships.patchDownload
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index bae4d8cdd3..4751ad872e 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9359,6 +9359,16 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<entry>materialized views</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry>
+ <entry>ownerships</entry>
+ </row>
+
+ <row>
+ <entry><link linkend="view-pg-permissions"><structname>pg_permissions</structname></link></entry>
+ <entry>permissions</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry>
<entry>policies</entry>
@@ -10877,6 +10887,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-ownerships">
+ <title><structname>pg_ownerships</structname></title>
+
+ <indexterm zone="view-pg-ownerships">
+ <primary>pg_ownerships</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_ownerships</structname> provides access to information about object ownerships.
+ </para>
+
+ <table>
+ <title><structname>pg_ownerships</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the owned object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>owner</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Owner of the object
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-permissions">
+ <title><structname>pg_permissions</structname></title>
+
+ <indexterm zone="view-pg-permissions">
+ <primary>pg_permissions</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_permissions</structname> provides access to information about explicitly granted privileges on database objects.
+ The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>.
+ </para>
+
+ <table>
+ <title><structname>pg_permissions</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the granted object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific granted object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantor</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role that granted this permission
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantee</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role to whom permission is granted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>privilege_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the privilege: <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, or
+ <literal>REFERENCES</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>is_grantable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-policies">
<title><structname>pg_policies</structname></title>
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,
"Joel Jacobson" <joel@compiler.org> writes:
On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
Ah, of course -- the only way to obtain the acl columns is by going
through the catalogs individually, so it won't be possible. I think
this could be fixed with some very simple, quick function pg_get_acl()
that takes a catalog OID and object OID and returns the ACL; then
use aclexplode() to obtain all those details.
+1 for adding pg_get_acl().
I wonder what performance will be like with lots o' objects.
regards, tom lane
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
"Joel Jacobson" <joel@compiler.org <mailto:joel%40compiler.org>> writes:
On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
Ah, of course -- the only way to obtain the acl columns is by going
through the catalogs individually, so it won't be possible. I think
this could be fixed with some very simple, quick function pg_get_acl()
that takes a catalog OID and object OID and returns the ACL; then
use aclexplode() to obtain all those details.+1 for adding pg_get_acl().
I wonder what performance will be like with lots o' objects.
I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
Is your performance concern on how such switch statement will be optimized by the C-compiler?
I can see how it would be annoyingly slow if the compiler would pick a branch table or binary search,
instead of producing a O(2) fast jump table.
On the topic of C switch statements:
I think the Clang/GCC-compiler folks (anyone here?) could actually be inspired by PostgreSQL's PerfectHash.pm.
I think the same strategy could be used in C compilers to optimize switch statements with sparse case values,
which currently produce slow binary search code O(log n) while a perfect hash solution would be O(2).
Example showing the unintelligent binary search code produced by GCC: https://godbolt.org/z/1G6G3vcjx (Clang is just as bad.) This is a hypothetical example with sparse case values. This is not the case here, since the classid case values are nicely ordered from OCLASS_CLASS..OCLASS_TRANSFORM (0..37), so they should produce O(2) fast jump tables.
Maybe there is some other performance concern to reason about that I'm missing here?
/Joel
On Fri, Mar 26, 2021, at 07:53, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
"Joel Jacobson" <joel@compiler.org <mailto:joel%40compiler.org>> writes:
On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
Ah, of course -- the only way to obtain the acl columns is by going
through the catalogs individually, so it won't be possible. I think
this could be fixed with some very simple, quick function pg_get_acl()
that takes a catalog OID and object OID and returns the ACL; then
use aclexplode() to obtain all those details.+1 for adding pg_get_acl().
I wonder what performance will be like with lots o' objects.
I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
Is your performance concern on how such switch statement will be optimized by the C-compiler?
...
the classid case values are nicely ordered from OCLASS_CLASS..OCLASS_TRANSFORM (0..37), so they should produce O(2) fast jump tables.Maybe there is some other performance concern to reason about that I'm missing here?
Hmm, I think I understand your performance concern now:
Am I right guessing the problem even with a jump table is going to be branch prediction,
which will be poor due to many classids being common?
Interesting, the long UNION ALL variant does not seem to suffer from this problem,
thanks to explicitly specifying where to find the aclitem/owner-column.
We pay the lookup-cost "compile time" when writing the pg_ownerships/pg_permissions system views,
instead of having to lookup the classids at run-time to go fetch aclitem/owner-info.
The query planner is also smart enough to understand not all the individuals queries
needs to be executed, for the use-case when filtering on a specific classid.
/Joel
On 2021-Mar-26, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
I wonder what performance will be like with lots o' objects.
I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
No, we have a generalized object query mechanism, see objectaddress.c
Is your performance concern on how such switch statement will be optimized by the C-compiler?
I guess he is concerned about the number of catalog accesses.
--
�lvaro Herrera 39�49'30"S 73�17'W
"Saca el libro que tu religi�n considere como el indicado para encontrar la
oraci�n que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Ducl�s)
On Fri, Mar 26, 2021, at 11:30, Alvaro Herrera wrote:
On 2021-Mar-26, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
I wonder what performance will be like with lots o' objects.
I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
No, we have a generalized object query mechanism, see objectaddress.c
That's where I was looking actually and noticed the switch with 36 cases, in the function getObjectDescription().
/Joel
On 2021-Mar-26, Joel Jacobson wrote:
On Fri, Mar 26, 2021, at 11:30, Alvaro Herrera wrote:
On 2021-Mar-26, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
I wonder what performance will be like with lots o' objects.
I guess pg_get_acl() would need to be implemented using a switch(classid) with 36 cases (one for each class)?
No, we have a generalized object query mechanism, see objectaddress.c
That's where I was looking actually and noticed the switch with 36 cases, in the function getObjectDescription().
Ah! well, you don't have to repeat that.
AFAICS the way to do it is like AlterObjectOwner_internal obtains data
-- first do get_catalog_object_by_oid (gives you the HeapTuple that
represents the object), then
heap_getattr( ..., get_object_attnum_acl(), ..), and there you have the
ACL which you can "explode" (or maybe just return as-is).
AFAICS if you do this, it's just one cache lookups per object, or
one indexscan for the cases with no by-OID syscache. It should be much
cheaper than the UNION ALL query. And you use pg_shdepend to guide
this, so you only do it for the objects that you already know are
interesting.
--
�lvaro Herrera Valdivia, Chile
"La conclusi�n que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusi�n de ellos" (Tanenbaum)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
On 2021-Mar-26, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
I wonder what performance will be like with lots o' objects.
I guess he is concerned about the number of catalog accesses.
My concern is basically that you're forcing the join between
pg_shdepend and $everything_else to be done as a nested loop.
It will work well, up to where you have so many objects that
it doesn't ... but the planner will have no way to improve it.
Having said that, I don't really see a better way either.
Materializing $everything_else via a UNION ALL seems like
no fun from a maintenance perspective, plus we're not that
great on optimizing such constructs either.
regards, tom lane
On Fri, Mar 26, 2021, at 14:16, Tom Lane wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org <mailto:alvherre%40alvh.no-ip.org>> writes:
On 2021-Mar-26, Joel Jacobson wrote:
On Thu, Mar 25, 2021, at 17:51, Tom Lane wrote:
I wonder what performance will be like with lots o' objects.I guess he is concerned about the number of catalog accesses.
My concern is basically that you're forcing the join between
pg_shdepend and $everything_else to be done as a nested loop.
It will work well, up to where you have so many objects that
it doesn't ... but the planner will have no way to improve it.
Thanks Alvaro and Tom for explaining.
Having said that, I don't really see a better way either.
Materializing $everything_else via a UNION ALL seems like
no fun from a maintenance perspective, plus we're not that
great on optimizing such constructs either.
I see why pg_shdepend+pg_get_acl() is to prefer.
That said, I think maintenance of UNION ALL would actually not be too bad,
since the system views could initially be generated by a query using information_schema,
and the same query could update them when new catalogs are added.
/Joel
On 11.03.21 08:00, Joel Jacobson wrote:
Do we prefer "pg_permissions" or "pg_privileges"?
pg_privileges would be better. "Permissions" is not an SQL term.
I would be happy to review this patch, but a look through the email leaves me
thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
right? And perhaps a view rename to pg_privileges, following Peter's comment?
On Fri, Feb 25, 2022, at 22:12, Chapman Flack wrote:
I would be happy to review this patch, but a look through the email leaves me
thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
right?
Not sure.
And perhaps a view rename to pg_privileges, following Peter's comment?
+1
/Joel
On 02/26/22 03:27, Joel Jacobson wrote:
On Fri, Feb 25, 2022, at 22:12, Chapman Flack wrote:
I would be happy to review this patch, but a look through the email leaves me
thinking it may still be waiting on a C implementation of pg_get_acl(). Is that
right?Not sure.
It looked to me as if the -hackers messages of 25 and 26 March 2021 had
found a consensus that a pg_get_acl() function would be a good thing,
with the views to be implemented over that.
I'm just not seeing any later patch that adds such a function.
Regards,
-Chap
On Thu, Mar 10, 2022, at 22:02, Chapman Flack wrote:
It looked to me as if the -hackers messages of 25 and 26 March 2021 had
found a consensus that a pg_get_acl() function would be a good thing,
with the views to be implemented over that.I'm just not seeing any later patch that adds such a function.
My apologies for late reply. Here it comes.
Recap: This patch is about adding two new system views: pg_privileges and pg_ownerships.
Changes since patch 0005 from 2021-03-25:
- Implement SQL-callable pg_get_acl()
This is a stripped down version of AlterObjectOwner_internal() from alter.c.
- Rename pg_permissions -> pg_privileges
- Use pg_shdepend + pg_get_acl() in pg_privileges, to avoid slow UNION ALL.
- Fix indentation of the new system views to be consistent with the other views.
- Add documentation of pg_get_acl() to func.sgml
- Move documentation of system views from catalogs.sgml to system-views.sgml
- Much smaller patch, thanks to getting rid of the long UNION ALL view definition:
1 file changed, 195 insertions(+), 460 deletions(-)
/Joel
Attachments:
0006-pg_privileges-and-pg_ownerships.patchapplication/octet-stream; name="=?UTF-8?Q?0006-pg=5Fprivileges-and-pg=5Fownerships.patch?="Download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 17c44bc338..0ae3618f28 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26683,6 +26683,22 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
Undefined objects are identified with <literal>NULL</literal> values.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_acl</primary>
+ </indexterm>
+ <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+ <returnvalue>aclitem[]</returnvalue>
+ </para>
+ <para>
+ Returns the Access Control List (ACL) for a database object,
+ specified by catalog OID and object OID.
+ This function is useful for retrieving and inspecting the privileges associated with database objects.
+ This function returns NULL values for undefined objects.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 8c18bea902..cfc4887a37 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -111,6 +111,16 @@
<entry>materialized views</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry>
+ <entry>ownerships</entry>
+ </row>
+
+ <row>
+ <entry><link linkend="view-pg-privileges"><structname>pg_privileges</structname></link></entry>
+ <entry>privileges</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry>
<entry>policies</entry>
@@ -1801,6 +1811,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-ownerships">
+ <title><structname>pg_ownerships</structname></title>
+
+ <indexterm zone="view-pg-ownerships">
+ <primary>pg_ownerships</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_ownerships</structname> provides access to information about object ownerships.
+ </para>
+
+ <table>
+ <title><structname>pg_ownerships</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the owned object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>owner</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Owner of the object
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-privileges">
+ <title><structname>pg_privileges</structname></title>
+
+ <indexterm zone="view-pg-privileges">
+ <primary>pg_privileges</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_privileges</structname> provides access to information about explicitly granted privileges on database objects.
+ The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>.
+ </para>
+
+ <table>
+ <title><structname>pg_privileges</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the granted object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific granted object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantor</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role that granted this privilege
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantee</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role to whom privilege is granted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>privilege_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the privilege: <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, or
+ <literal>REFERENCES</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>is_grantable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-policies">
<title><structname>pg_policies</structname></title>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..0579b66ab7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,49 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
}
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+ Oid classId = PG_GETARG_OID(0);
+ Oid objectId = PG_GETARG_OID(1);
+ Oid catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+ AttrNumber Anum_oid = get_object_attnum_oid(catalogId);
+ AttrNumber Anum_acl = get_object_attnum_acl(catalogId);
+ Relation rel;
+ HeapTuple tup;
+ Datum datum;
+ bool isnull;
+
+ /* for "pinned" items in pg_depend, return null */
+ if (!OidIsValid(classId) && !OidIsValid(objectId))
+ PG_RETURN_NULL();
+
+ rel = table_open(catalogId, AccessShareLock);
+
+ tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+ if (tup == NULL)
+ elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+ objectId, RelationGetRelationName(rel));
+
+ if (Anum_acl != InvalidAttrNumber)
+ {
+ datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+ if (!isnull)
+ {
+ table_close(rel, AccessShareLock);
+ PG_RETURN_DATUM(datum);
+ }
+ }
+
+ table_close(rel, AccessShareLock);
+ PG_RETURN_NULL();
+}
+
+
/*
* Return a palloc'ed string that describes the type of object that the
* passed address is for.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 53047cab5f..6a575ff03b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -605,6 +605,44 @@ FROM
pg_shseclabel l
JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
+CREATE VIEW pg_privileges AS
+ SELECT
+ a.classid::regclass,
+ 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_shdepend.classid,
+ pg_shdepend.objid,
+ pg_shdepend.objsubid,
+ (pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)).*,
+ (pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid))).*
+ 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 = 'a'
+ ) AS a;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid,a.objid,a.objsubid)).*,a.refobjid::regrole AS owner
+ FROM pg_catalog.pg_shdepend AS a
+ JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = a.dbid
+ JOIN pg_catalog.pg_authid ON pg_authid.oid = a.refobjid AND a.refclassid = 'pg_authid'::regclass
+ WHERE a.deptype = 'o';
+
CREATE VIEW pg_settings AS
SELECT * FROM pg_show_all_settings() AS A;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
+{ oid => '6347', descr => 'get ACL for SQL object',
+ proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+ proargtypes => 'oid oid',
+ proargnames => '{classid,objid}',
+ prosrc => 'pg_get_acl' },
+
{ oid => '3839',
descr => 'get machine-parseable identification of SQL object',
proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ef658ad740..2a82b672db 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1397,6 +1397,18 @@ 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 (a.classid)::regclass AS classid,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).type AS type,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).schema AS schema,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).name AS name,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).identity AS identity,
+ (a.refobjid)::regrole AS owner
+ FROM ((pg_shdepend a
+ JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = a.dbid))))
+ JOIN pg_authid ON (((pg_authid.oid = a.refobjid) AND (a.refclassid = ('pg_authid'::regclass)::oid))))
+ WHERE (a.deptype = 'o'::"char");
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
@@ -1441,6 +1453,32 @@ pg_prepared_xacts| SELECT p.transaction,
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
+pg_privileges| SELECT (classid)::regclass AS classid,
+ objid,
+ objsubid,
+ type,
+ schema,
+ name,
+ identity,
+ (grantor)::regrole AS grantor,
+ (grantee)::regrole AS grantee,
+ privilege_type,
+ is_grantable
+ FROM ( 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,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).grantor AS grantor,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).grantee AS grantee,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).privilege_type AS privilege_type,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).is_grantable AS is_grantable
+ 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 = 'a'::"char")) a;
pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
On Thu, Jun 13, 2024, at 00:14, Joel Jacobson wrote:
Changes since patch 0005 from 2021-03-25:
* 0006-pg_privileges-and-pg_ownerships.patch
- Also much faster now thanks to pg_get_acl():
Test with 100000 tables:
SELECT COUNT(*) FROM pg_permissions_union_all;
Time: 1466.504 ms (00:01.467)
Time: 1435.520 ms (00:01.436)
Time: 1459.396 ms (00:01.459)
SELECT COUNT(*) FROM pg_privileges;
Time: 292.257 ms
Time: 288.406 ms
Time: 294.831 ms
Hmm, strange, the commitfest system didn't pick up the email with patch 0006 for some reason,
with message id 0c5a6b79-408c-4910-9b2e-4aa9a7b30f3a@app.fastmail.com
It's rebased to latest HEAD, so not sure why.
Maybe it got confused when I quickly afterwards sent a new email without a patch?
Here is a new attempt, file content unchanged, just named to 0007 and added "pg_get_acl" to the name.
On Thu, Jun 13, 2024, at 04:00, Joel Jacobson wrote:
On Thu, Jun 13, 2024, at 00:14, Joel Jacobson wrote:
Changes since patch 0005 from 2021-03-25:
* 0006-pg_privileges-and-pg_ownerships.patch- Also much faster now thanks to pg_get_acl():
Test with 100000 tables:
SELECT COUNT(*) FROM pg_permissions_union_all;
Time: 1466.504 ms (00:01.467)
Time: 1435.520 ms (00:01.436)
Time: 1459.396 ms (00:01.459)SELECT COUNT(*) FROM pg_privileges;
Time: 292.257 ms
Time: 288.406 ms
Time: 294.831 ms
--
Kind regards,
Joel
Attachments:
0007-pg_privileges-pg_ownerships-pg_get_acl.patchapplication/octet-stream; name="=?UTF-8?Q?0007-pg=5Fprivileges-pg=5Fownerships-pg=5Fget=5Facl.patch?="Download
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 17c44bc338..0ae3618f28 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26683,6 +26683,22 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
Undefined objects are identified with <literal>NULL</literal> values.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_acl</primary>
+ </indexterm>
+ <function>pg_get_acl</function> ( <parameter>classid</parameter> <type>oid</type>, <parameter>objid</parameter> <type>oid</type> )
+ <returnvalue>aclitem[]</returnvalue>
+ </para>
+ <para>
+ Returns the Access Control List (ACL) for a database object,
+ specified by catalog OID and object OID.
+ This function is useful for retrieving and inspecting the privileges associated with database objects.
+ This function returns NULL values for undefined objects.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 8c18bea902..cfc4887a37 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -111,6 +111,16 @@
<entry>materialized views</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry>
+ <entry>ownerships</entry>
+ </row>
+
+ <row>
+ <entry><link linkend="view-pg-privileges"><structname>pg_privileges</structname></link></entry>
+ <entry>privileges</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry>
<entry>policies</entry>
@@ -1801,6 +1811,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-ownerships">
+ <title><structname>pg_ownerships</structname></title>
+
+ <indexterm zone="view-pg-ownerships">
+ <primary>pg_ownerships</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_ownerships</structname> provides access to information about object ownerships.
+ </para>
+
+ <table>
+ <title><structname>pg_ownerships</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the owned object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>owner</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Owner of the object
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-privileges">
+ <title><structname>pg_privileges</structname></title>
+
+ <indexterm zone="view-pg-privileges">
+ <primary>pg_privileges</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_privileges</structname> provides access to information about explicitly granted privileges on database objects.
+ The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>.
+ </para>
+
+ <table>
+ <title><structname>pg_privileges</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the granted object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific granted object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantor</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role that granted this privilege
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantee</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role to whom privilege is granted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>privilege_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the privilege: <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, or
+ <literal>REFERENCES</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>is_grantable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-policies">
<title><structname>pg_policies</structname></title>
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 7b536ac6fd..0579b66ab7 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -4362,6 +4362,49 @@ pg_identify_object_as_address(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(HeapTupleGetDatum(htup));
}
+/*
+ * SQL-level callable function to obtain the Access Control List (ACL)
+ * of a specified object, given its catalog OID and object OID.
+ */
+Datum
+pg_get_acl(PG_FUNCTION_ARGS)
+{
+ Oid classId = PG_GETARG_OID(0);
+ Oid objectId = PG_GETARG_OID(1);
+ Oid catalogId = (classId == LargeObjectRelationId) ? LargeObjectMetadataRelationId : classId;
+ AttrNumber Anum_oid = get_object_attnum_oid(catalogId);
+ AttrNumber Anum_acl = get_object_attnum_acl(catalogId);
+ Relation rel;
+ HeapTuple tup;
+ Datum datum;
+ bool isnull;
+
+ /* for "pinned" items in pg_depend, return null */
+ if (!OidIsValid(classId) && !OidIsValid(objectId))
+ PG_RETURN_NULL();
+
+ rel = table_open(catalogId, AccessShareLock);
+
+ tup = get_catalog_object_by_oid(rel, Anum_oid, objectId);
+ if (tup == NULL)
+ elog(ERROR, "cache lookup failed for object %u of catalog \"%s\"",
+ objectId, RelationGetRelationName(rel));
+
+ if (Anum_acl != InvalidAttrNumber)
+ {
+ datum = heap_getattr(tup, Anum_acl, RelationGetDescr(rel), &isnull);
+ if (!isnull)
+ {
+ table_close(rel, AccessShareLock);
+ PG_RETURN_DATUM(datum);
+ }
+ }
+
+ table_close(rel, AccessShareLock);
+ PG_RETURN_NULL();
+}
+
+
/*
* Return a palloc'ed string that describes the type of object that the
* passed address is for.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 53047cab5f..6a575ff03b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -605,6 +605,44 @@ FROM
pg_shseclabel l
JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
+CREATE VIEW pg_privileges AS
+ SELECT
+ a.classid::regclass,
+ 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_shdepend.classid,
+ pg_shdepend.objid,
+ pg_shdepend.objsubid,
+ (pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)).*,
+ (pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid))).*
+ 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 = 'a'
+ ) AS a;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid,a.objid,a.objsubid)).*,a.refobjid::regrole AS owner
+ FROM pg_catalog.pg_shdepend AS a
+ JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = a.dbid
+ JOIN pg_catalog.pg_authid ON pg_authid.oid = a.refobjid AND a.refclassid = 'pg_authid'::regclass
+ WHERE a.deptype = 'o';
+
CREATE VIEW pg_settings AS
SELECT * FROM pg_show_all_settings() AS A;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..5ab9b11b47 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
proname => 'pg_describe_object', provolatile => 's', prorettype => 'text',
proargtypes => 'oid oid int4', prosrc => 'pg_describe_object' },
+{ oid => '6347', descr => 'get ACL for SQL object',
+ proname => 'pg_get_acl', provolatile => 's', prorettype => '_aclitem',
+ proargtypes => 'oid oid',
+ proargnames => '{classid,objid}',
+ prosrc => 'pg_get_acl' },
+
{ oid => '3839',
descr => 'get machine-parseable identification of SQL object',
proname => 'pg_identify_object', provolatile => 's', prorettype => 'record',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ef658ad740..2a82b672db 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1397,6 +1397,18 @@ 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 (a.classid)::regclass AS classid,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).type AS type,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).schema AS schema,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).name AS name,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).identity AS identity,
+ (a.refobjid)::regrole AS owner
+ FROM ((pg_shdepend a
+ JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = a.dbid))))
+ JOIN pg_authid ON (((pg_authid.oid = a.refobjid) AND (a.refclassid = ('pg_authid'::regclass)::oid))))
+ WHERE (a.deptype = 'o'::"char");
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
@@ -1441,6 +1453,32 @@ pg_prepared_xacts| SELECT p.transaction,
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
+pg_privileges| SELECT (classid)::regclass AS classid,
+ objid,
+ objsubid,
+ type,
+ schema,
+ name,
+ identity,
+ (grantor)::regrole AS grantor,
+ (grantee)::regrole AS grantee,
+ privilege_type,
+ is_grantable
+ FROM ( 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,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).grantor AS grantor,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).grantee AS grantee,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).privilege_type AS privilege_type,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid))).is_grantable AS is_grantable
+ 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 = 'a'::"char")) a;
pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
On Thu, Jun 13, 2024 at 07:34:30AM +0200, Joel Jacobson wrote:
Hmm, strange, the commitfest system didn't pick up the email with patch 0006 for some reason,
with message id 0c5a6b79-408c-4910-9b2e-4aa9a7b30f3a@app.fastmail.comIt's rebased to latest HEAD, so not sure why.
Maybe it got confused when I quickly afterwards sent a new email without a patch?
Here is a new attempt, file content unchanged, just named to 0007 and added "pg_get_acl" to the name.
The CF bot is red for some time now. Please rebase.
--
Michael
On Tue, Oct 8, 2024, at 03:48, Michael Paquier wrote:
On Thu, Jun 13, 2024 at 07:34:30AM +0200, Joel Jacobson wrote:
Hmm, strange, the commitfest system didn't pick up the email with patch 0006 for some reason,
with message id 0c5a6b79-408c-4910-9b2e-4aa9a7b30f3a@app.fastmail.comIt's rebased to latest HEAD, so not sure why.
Maybe it got confused when I quickly afterwards sent a new email without a patch?
Here is a new attempt, file content unchanged, just named to 0007 and added "pg_get_acl" to the name.
The CF bot is red for some time now. Please rebase.
Thanks for the reminder.
Rebased and removed pg_get_acl from the patch,
and changed the pg_privileges view to use the three parameter
version of pg_get_acl() that eventually got committed,
where the third parameter is the objsubid.
/Joel
Attachments:
0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.patc?= =?UTF-8?Q?h?="Download
From 20f90c4b22cbe8729978dd1db96c231001fdf7ca Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <github@compiler.org>
Date: Tue, 8 Oct 2024 18:39:04 +0200
Subject: [PATCH] Add pg_ownerships and pg_privileges system views
---
doc/src/sgml/system-views.sgml | 260 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 38 ++++
src/test/regress/expected/rules.out | 38 ++++
3 files changed, 336 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 61d28e701f..f043e675de 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -111,6 +111,16 @@
<entry>materialized views</entry>
</row>
+ <row>
+ <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry>
+ <entry>ownerships</entry>
+ </row>
+
+ <row>
+ <entry><link linkend="view-pg-privileges"><structname>pg_privileges</structname></link></entry>
+ <entry>privileges</entry>
+ </row>
+
<row>
<entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry>
<entry>policies</entry>
@@ -1839,6 +1849,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
</sect1>
+ <sect1 id="view-pg-ownerships">
+ <title><structname>pg_ownerships</structname></title>
+
+ <indexterm zone="view-pg-ownerships">
+ <primary>pg_ownerships</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_ownerships</structname> provides access to information about object ownerships.
+ </para>
+
+ <table>
+ <title><structname>pg_ownerships</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the owned object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific owned object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>owner</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Owner of the object
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
+ <sect1 id="view-pg-privileges">
+ <title><structname>pg_privileges</structname></title>
+
+ <indexterm zone="view-pg-privileges">
+ <primary>pg_privileges</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_privileges</structname> provides access to information about explicitly granted privileges on database objects.
+ The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>.
+ </para>
+
+ <table>
+ <title><structname>pg_privileges</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>classid</structfield> <type>regclass</type>
+ (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The <type>regclass</type> OID of the system catalog the granted object is in
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objid</structfield> <type>oid</type>
+ (references any OID column)
+ </para>
+ <para>
+ The OID of the specific granted object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>objsubid</structfield> <type>int4</type>
+ </para>
+ <para>
+ For a table column, this is the column number (the
+ <structfield>objid</structfield> and <structfield>classid</structfield> refer to the
+ table itself). For all other object types, this column is
+ zero.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>type</structfield> <type>text</type>
+ </para>
+ <para>
+ Identifies the type of database object
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>schema</structfield> <type>text</type>
+ </para>
+ <para>
+ The schema name that the object belongs in, or NULL for object types that do not belong to schemas
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>name</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>identity</structfield> <type>text</type>
+ </para>
+ <para>
+ 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
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantor</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role that granted this privilege
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>grantee</structfield> <type>regrole</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>)
+ </para>
+ <para>
+ Role to whom privilege is granted
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>privilege_type</structfield> <type>text</type>
+ </para>
+ <para>
+ Type of the privilege: <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, or
+ <literal>REFERENCES</literal>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>is_grantable</structfield> <type>boolean</type>
+ </para>
+ <para>
+ <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect1>
+
<sect1 id="view-pg-policies">
<title><structname>pg_policies</structname></title>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3456b821bc..327e3163de 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -601,6 +601,44 @@ FROM
pg_shseclabel l
JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid;
+CREATE VIEW pg_privileges AS
+ SELECT
+ a.classid::regclass,
+ 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_shdepend.classid,
+ pg_shdepend.objid,
+ pg_shdepend.objsubid,
+ (pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)).*,
+ (pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid))).*
+ 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 = 'a'
+ ) AS a;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid,a.objid,a.objsubid)).*,a.refobjid::regrole AS owner
+ FROM pg_catalog.pg_shdepend AS a
+ JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = a.dbid
+ JOIN pg_catalog.pg_authid ON pg_authid.oid = a.refobjid AND a.refclassid = 'pg_authid'::regclass
+ WHERE a.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 2b47013f11..69f3f0c597 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1398,6 +1398,18 @@ 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 (a.classid)::regclass AS classid,
+ a.objid,
+ a.objsubid,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).type AS type,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).schema AS schema,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).name AS name,
+ (pg_identify_object(a.classid, a.objid, a.objsubid)).identity AS identity,
+ (a.refobjid)::regrole AS owner
+ FROM ((pg_shdepend a
+ JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = a.dbid))))
+ JOIN pg_authid ON (((pg_authid.oid = a.refobjid) AND (a.refclassid = ('pg_authid'::regclass)::oid))))
+ WHERE (a.deptype = 'o'::"char");
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
@@ -1442,6 +1454,32 @@ pg_prepared_xacts| SELECT p.transaction,
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
+pg_privileges| SELECT (classid)::regclass AS classid,
+ objid,
+ objsubid,
+ type,
+ schema,
+ name,
+ identity,
+ (grantor)::regrole AS grantor,
+ (grantee)::regrole AS grantee,
+ privilege_type,
+ is_grantable
+ FROM ( 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,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantor AS grantor,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantee AS grantee,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).privilege_type AS privilege_type,
+ (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).is_grantable AS is_grantable
+ 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 = 'a'::"char")) a;
pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
--
2.45.1