public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joel Jacobson <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: [email protected]
Subject: Re: Add pg_ownerships and pg_privileges system views
Date: Sun, 20 Oct 2024 16:52:15 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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:
[application/octet-stream] v3-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch (16.8K, ../[email protected]/2-v3-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch)
download | inline diff:
From 58736a64e9f5b50b461f10ca89a45a7573f643b7 Mon Sep 17 00:00:00 2001
From: Joel Jakobsson <[email protected]>
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
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Add pg_ownerships and pg_privileges system views
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox