public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joel Jacobson <[email protected]>
To: [email protected]
Subject: Add pg_ownerships and pg_privileges system views
Date: Sun, 20 Oct 2024 08:19:39 +0200
Message-ID: <[email protected]> (raw)
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:
[application/octet-stream] v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch (16.4K, ../[email protected]/2-v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch)
download | inline diff:
From 0cf4d0a5fc756f7d98fbefbd5cf32d9cae97f516 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 | 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
view thread (4+ 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]
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