Add pg_ownerships and pg_privileges system views
Started by Joel Jacobsonabout 1 year ago8 messages
Hi hackers,
Here is an attempt to revive this patch from 2021-2022, that has been ready now
for a while, thanks to pg_get_acl() function that was committed in
4564f1c and d898665.
I've renamed the $subject of the email thread, to match the commitfest entry:
https://commitfest.postgresql.org/50/5033/
---
Add pg_ownerships and pg_privileges system views.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
Recap:
During the work on this, the need for a pg_get_acl() function was identified.
Thanks to feedback from Peter Eisentraut, the view "pg_permissions" was renamed
to "pg_privileges", since "permissions" is not an SQL term.
David Fetter:
+1 for both this and the ownerships view.
Joe Conway:
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.
Chapman Flack expressed interest in reviewing the patch, but at that time
the pg_get_acl() had not yet been committed and the view not been renamed.
Michael Paquier alerted me CF bot had been red, and the patch was rebased.
/Joel
Attachments:
v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?v2-0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.p?= =?UTF-8?Q?atch?="Download
From 0cf4d0a5fc756f7d98fbefbd5cf32d9cae97f516 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.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
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
Re: Add pg_ownerships and pg_privileges system views
Hello,
On 2024-Oct-20, Joel Jacobson wrote:
Here is an attempt to revive this patch from 2021-2022, that has been ready now
for a while, thanks to pg_get_acl() function that was committed in
4564f1c and d898665.
Nice.
I think the function calls should be in the FROM clause, and restrict the
pg_shdepend rows to only the ones in the current database:
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,
identify.*,
aclexplode.*
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,
LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
FROM pg_database pg_database_1
WHERE pg_database_1.datname = current_database()))
) AS a ;
Now, depending on pg_shdepend for this means that you don't report
anything for an object until a GRANT to another user has been executed.
For example if you REVOKE some priv from the object owner, nothing is
shown until a GRANT is done for another user (and at that point onwards,
privs by the owner are shown). This seems less than ideal, but I'm not
sure how to do different, other than ditching the use of pg_shdepend
entirely.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL. This is by far the most pleasant management experience of
any database I've worked on." (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php
Re: Add pg_ownerships and pg_privileges system views
On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:
I think the function calls should be in the FROM clause, and restrict the
pg_shdepend rows to only the ones in the current database:
Cool. I assume pg_ownerships should be changed in the same way?
New patch attached.
Now, depending on pg_shdepend for this means that you don't report
anything for an object until a GRANT to another user has been executed.
For example if you REVOKE some priv from the object owner, nothing is
shown until a GRANT is done for another user (and at that point onwards,
privs by the owner are shown). This seems less than ideal, but I'm not
sure how to do different, other than ditching the use of pg_shdepend
entirely.
Hmm, yeah that's a bit awkward. Maybe okay if clearly documented.
/Joel
Attachments:
v3-0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?v3-0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.p?= =?UTF-8?Q?atch?="Download
From 58736a64e9f5b50b461f10ca89a45a7573f643b7 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.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
doc/src/sgml/system-views.sgml | 260 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 47 +++++
src/test/regress/expected/rules.out | 38 ++++
3 files changed, 345 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..3e85db1f7b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -601,6 +601,53 @@ 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,
+ identify.*,
+ aclexplode.*
+ 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,
+ LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
+ LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
+ WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()))
+ ) AS a ;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ identify.*,
+ 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,
+ LATERAL pg_catalog.pg_identify_object(a.classid, a.objid, a.objsubid) AS identify
+ WHERE a.deptype = 'o' AND a.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()
+ ));
+
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
Re: Add pg_ownerships and pg_privileges system views
On Sun, Oct 20, 2024, at 16:52, Joel Jacobson wrote:
On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:
I think the function calls should be in the FROM clause, and restrict the
pg_shdepend rows to only the ones in the current database:Cool. I assume pg_ownerships should be changed in the same way?
New patch attached.Now, depending on pg_shdepend for this means that you don't report
anything for an object until a GRANT to another user has been executed.
For example if you REVOKE some priv from the object owner, nothing is
shown until a GRANT is done for another user (and at that point onwards,
privs by the owner are shown). This seems less than ideal, but I'm not
sure how to do different, other than ditching the use of pg_shdepend
entirely.Hmm, yeah that's a bit awkward. Maybe okay if clearly documented.
I've tried to explain this behavior in the docs like this:
<note>
<para>
This view reports privileges only when they have been explicitly granted
to a role other than the object owner. By default, the object owner has all
privileges on the object, but these default privileges are not displayed
in this view until a privilege is granted to another role. For example,
if you revoke some privileges from the object owner, nothing is shown in
this view until a privilege is granted to another role, after which the
owner's privileges are also displayed.
</para>
</note>
/Joel
Attachments:
v4-0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?v4-0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.p?= =?UTF-8?Q?atch?="Download
From 358886eb598c94967db76f30dcf8280d016e2b1e 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.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
doc/src/sgml/system-views.sgml | 272 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 47 +++++
src/test/regress/expected/rules.out | 38 ++++
3 files changed, 357 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 61d28e701f..2bd8b1589e 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,268 @@ 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>.
+
+ <note>
+ <para>
+ This view reports privileges only when they have been explicitly granted
+ to a role other than the object owner. By default, the object owner has all
+ privileges on the object, but these default privileges are not displayed
+ in this view until a privilege is granted to another role. For example,
+ if you revoke some privileges from the object owner, nothing is shown in
+ this view until a privilege is granted to another role, after which the
+ owner's privileges are also displayed.
+ </para>
+ </note>
+ </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..3e85db1f7b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -601,6 +601,53 @@ 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,
+ identify.*,
+ aclexplode.*
+ 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,
+ LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
+ LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
+ WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()))
+ ) AS a ;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ identify.*,
+ 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,
+ LATERAL pg_catalog.pg_identify_object(a.classid, a.objid, a.objsubid) AS identify
+ WHERE a.deptype = 'o' AND a.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()
+ ));
+
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
Re: Add pg_ownerships and pg_privileges system views
On Sun, Oct 20, 2024, at 23:03, Joel Jacobson wrote:
On Sun, Oct 20, 2024, at 16:52, Joel Jacobson wrote:
On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:
I think the function calls should be in the FROM clause, and restrict the
pg_shdepend rows to only the ones in the current database:Cool. I assume pg_ownerships should be changed in the same way?
New patch attached.Now, depending on pg_shdepend for this means that you don't report
anything for an object until a GRANT to another user has been executed.
For example if you REVOKE some priv from the object owner, nothing is
shown until a GRANT is done for another user (and at that point onwards,
privs by the owner are shown). This seems less than ideal, but I'm not
sure how to do different, other than ditching the use of pg_shdepend
entirely.Hmm, yeah that's a bit awkward. Maybe okay if clearly documented.
I've tried to explain this behavior in the docs like this:
<note>
<para>
This view reports privileges only when they have been explicitly granted
to a role other than the object owner. By default, the object owner has all
privileges on the object, but these default privileges are not displayed
in this view until a privilege is granted to another role. For example,
if you revoke some privileges from the object owner, nothing is shown in
this view until a privilege is granted to another role, after which the
owner's privileges are also displayed.
</para>
</note>
Ops, sorry, forgot to update expected/rules.out, fixed.
/Joel
Attachments:
v5-0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?v5-0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.p?= =?UTF-8?Q?atch?="Download
From 7c4f9c7bf9416dcb4ca13cedbd2a35cebce4b1a1 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.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
doc/src/sgml/system-views.sgml | 272 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 47 +++++
src/test/regress/expected/rules.out | 45 +++++
3 files changed, 364 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 61d28e701f..2bd8b1589e 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,268 @@ 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>.
+
+ <note>
+ <para>
+ This view reports privileges only when they have been explicitly granted
+ to a role other than the object owner. By default, the object owner has all
+ privileges on the object, but these default privileges are not displayed
+ in this view until a privilege is granted to another role. For example,
+ if you revoke some privileges from the object owner, nothing is shown in
+ this view until a privilege is granted to another role, after which the
+ owner's privileges are also displayed.
+ </para>
+ </note>
+ </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..3e85db1f7b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -601,6 +601,53 @@ 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,
+ identify.*,
+ aclexplode.*
+ 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,
+ LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
+ LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
+ WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()))
+ ) AS a ;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ identify.*,
+ 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,
+ LATERAL pg_catalog.pg_identify_object(a.classid, a.objid, a.objsubid) AS identify
+ WHERE a.deptype = 'o' AND a.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()
+ ));
+
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..a32381f1ab 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1398,6 +1398,21 @@ 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,
+ identify.type,
+ identify.schema,
+ identify.name,
+ identify.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)))),
+ LATERAL pg_identify_object(a.classid, a.objid, a.objsubid) identify(type, schema, name, identity)
+ WHERE ((a.deptype = 'o'::"char") AND (a.dbid = ( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE (pg_database_1.datname = current_database()))));
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
@@ -1442,6 +1457,36 @@ 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,
+ identify.type,
+ identify.schema,
+ identify.name,
+ identify.identity,
+ aclexplode.grantor,
+ aclexplode.grantee,
+ aclexplode.privilege_type,
+ aclexplode.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)))),
+ LATERAL pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid) identify(type, schema, name, identity),
+ LATERAL aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)) aclexplode(grantor, grantee, privilege_type, is_grantable)
+ WHERE ((pg_shdepend.deptype = 'a'::"char") AND (pg_shdepend.dbid = ( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE (pg_database_1.datname = current_database()))))) a;
pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
--
2.45.1
Re: Add pg_ownerships and pg_privileges system views
On 2024-Oct-20, Alvaro Herrera wrote:
SELECT
pg_shdepend.classid,
pg_shdepend.objid,
pg_shdepend.objsubid,
identify.*,
aclexplode.*
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,
LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
FROM pg_database pg_database_1
WHERE pg_database_1.datname = current_database()))
) AS a ;
... actually, the "AND pg_shdepend.dbid = ( SELECT pg_database_1.oid
...)" part of this is useless, because you already had that in the ON
condition of the original join to pg_database. So, apologies for the
noise there. TBH I don't see why you put that in the JOIN ON condition
instead of WHERE, but anyway you don't need to add a new condition for
it. I guess I'd do it like this for clarity:
FROM pg_catalog.pg_shdepend
JOIN pg_catalog.pg_database ON pg_database.oid = pg_shdepend.dbid
JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid
LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid) AS identify,
LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)) AS aclexplode
WHERE pg_shdepend.deptype = 'a' AND
pg_shdepend.refclassid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND
pg_database.datname = pg_catalog.current_database()
... but since these are inner joins, this might be a matter of style.
(I did add a couple of schema-qualifications there.)
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Debido a que la velocidad de la luz es mucho mayor que la del sonido,
algunas personas nos parecen brillantes un minuto antes
de escuchar las pelotudeces que dicen." (Roberto Fontanarrosa)
Re: Add pg_ownerships and pg_privileges system views
On Mon, Oct 21, 2024, at 11:42, Alvaro Herrera wrote:
... actually, the "AND pg_shdepend.dbid = ( SELECT pg_database_1.oid
...)" part of this is useless, because you already had that in the ON
condition of the original join to pg_database. So, apologies for the
noise there. TBH I don't see why you put that in the JOIN ON condition
instead of WHERE, but anyway you don't need to add a new condition for
it. I guess I'd do it like this for clarity:FROM pg_catalog.pg_shdepend
JOIN pg_catalog.pg_database ON pg_database.oid =
pg_shdepend.dbid
JOIN pg_catalog.pg_authid ON pg_authid.oid =
pg_shdepend.refobjid
LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,
pg_shdepend.objid, pg_shdepend.objsubid) AS identify,
LATERAL
pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,
pg_shdepend.objid, pg_shdepend.objsubid)) AS aclexplode
WHERE pg_shdepend.deptype = 'a' AND
pg_shdepend.refclassid = 'pg_catalog.pg_authid'::pg_catalog.regclass
AND
pg_database.datname = pg_catalog.current_database()... but since these are inner joins, this might be a matter of style.
(I did add a couple of schema-qualifications there.)
Ahh, right, that's nicer, thanks for fixing.
New patch attached.
I also fixed pg_ownerships in the same way, moving the `pg_catalog.pg_database.datname = pg_catalog.current_database()` to the WHERE clause instead.
/Joel
Attachments:
v5-0001-Add-pg_ownerships-and-pg_privileges-system-views.patchapplication/octet-stream; name="=?UTF-8?Q?v5-0001-Add-pg=5Fownerships-and-pg=5Fprivileges-system-views.p?= =?UTF-8?Q?atch?="Download
From 7c4f9c7bf9416dcb4ca13cedbd2a35cebce4b1a1 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.
These new views provide a more accessible and user-friendly way to retrieve
information about object ownerships and privileges.
The view pg_ownerships provides access to information about object ownerships.
The view pg_privileges provides access to information about explicitly
granted privileges on database objects. The special grantee value "-" means
the privilege is granted to PUBLIC.
Example usage:
CREATE ROLE alice;
CREATE ROLE bob;
CREATE ROLE carol;
CREATE TABLE alice_table ();
ALTER TABLE alice_table OWNER TO alice;
REVOKE ALL ON alice_table FROM alice;
GRANT SELECT ON alice_table TO bob;
CREATE TABLE bob_table ();
ALTER TABLE bob_table OWNER TO bob;
REVOKE ALL ON bob_table FROM bob;
GRANT SELECT, UPDATE ON bob_table TO carol;
SELECT * FROM pg_ownerships ORDER BY owner;
classid | objid | objsubid | type | schema | name | identity | owner
----------+-------+----------+-------+--------+-------------+--------------------+-------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob
(2 rows)
SELECT * FROM pg_privileges ORDER BY grantee;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+--------------
pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f
pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f
(3 rows)
---
doc/src/sgml/system-views.sgml | 272 +++++++++++++++++++++++++++
src/backend/catalog/system_views.sql | 47 +++++
src/test/regress/expected/rules.out | 45 +++++
3 files changed, 364 insertions(+)
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 61d28e701f..2bd8b1589e 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,268 @@ 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>.
+
+ <note>
+ <para>
+ This view reports privileges only when they have been explicitly granted
+ to a role other than the object owner. By default, the object owner has all
+ privileges on the object, but these default privileges are not displayed
+ in this view until a privilege is granted to another role. For example,
+ if you revoke some privileges from the object owner, nothing is shown in
+ this view until a privilege is granted to another role, after which the
+ owner's privileges are also displayed.
+ </para>
+ </note>
+ </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..3e85db1f7b 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -601,6 +601,53 @@ 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,
+ identify.*,
+ aclexplode.*
+ 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,
+ LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify,
+ LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode
+ WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()))
+ ) AS a ;
+
+CREATE VIEW pg_ownerships AS
+ SELECT
+ a.classid::regclass,
+ a.objid,
+ a.objsubid,
+ identify.*,
+ 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,
+ LATERAL pg_catalog.pg_identify_object(a.classid, a.objid, a.objsubid) AS identify
+ WHERE a.deptype = 'o' AND a.dbid = (( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE pg_database_1.datname = current_database()
+ ));
+
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..a32381f1ab 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1398,6 +1398,21 @@ 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,
+ identify.type,
+ identify.schema,
+ identify.name,
+ identify.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)))),
+ LATERAL pg_identify_object(a.classid, a.objid, a.objsubid) identify(type, schema, name, identity)
+ WHERE ((a.deptype = 'o'::"char") AND (a.dbid = ( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE (pg_database_1.datname = current_database()))));
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
@@ -1442,6 +1457,36 @@ 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,
+ identify.type,
+ identify.schema,
+ identify.name,
+ identify.identity,
+ aclexplode.grantor,
+ aclexplode.grantee,
+ aclexplode.privilege_type,
+ aclexplode.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)))),
+ LATERAL pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid) identify(type, schema, name, identity),
+ LATERAL aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)) aclexplode(grantor, grantee, privilege_type, is_grantable)
+ WHERE ((pg_shdepend.deptype = 'a'::"char") AND (pg_shdepend.dbid = ( SELECT pg_database_1.oid
+ FROM pg_database pg_database_1
+ WHERE (pg_database_1.datname = current_database()))))) a;
pg_publication_tables| SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename,
--
2.45.1
Re: Add pg_ownerships and pg_privileges system views
Hi hackers,
As noted in previous feedback, relying on pg_shdepend leads to a less-than-ideal
behavior: no privileges are displayed for an object until at least one privilege
is granted to a role other than the owner, as explained by Alvaro:
On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:
Now, depending on pg_shdepend for this means that you don't report
anything for an object until a GRANT to another user has been executed.
For example if you REVOKE some priv from the object owner, nothing is
shown until a GRANT is done for another user (and at that point onwards,
privs by the owner are shown). This seems less than ideal, but I'm not
sure how to do different, other than ditching the use of pg_shdepend
entirely.
I'm considering withdrawing this patch, due to this awkwardness.
Do we still want pg_ownerships? If so, I can submit it as a separate patch.
Are there any idea on how to address the underlying dependency issue so that
pg_privileges can report owner privileges consistently from the start,
without requiring a subsequent grant to another user?
/Joel