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 23:09:43 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
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:
[application/octet-stream] v5-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch (17.2K, ../[email protected]/2-v5-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch)
download | inline diff:
From 7c4f9c7bf9416dcb4ca13cedbd2a35cebce4b1a1 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 | 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
view thread (3+ messages) latest in thread
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