Subject: Add pg_ownerships and pg_privileges system views

From: "Joel Jacobson" <[email-masked]>
Date: Sun, 20 Oct 2024 08:19:39 +0200

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

---

From: Alvaro Herrera <[email-masked]>
Date: Sun, 20 Oct 2024 12:14:10 +0200

Hello,

> for a while, thanks to pg_get_acl() function that was committed in

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

---

From: "Joel Jacobson" <[email-masked]>
Date: Sun, 20 Oct 2024 16:52:15 +0200

> 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.

> sure how to do different, other than ditching the use of pg_shdepend

Hmm, yeah that's a bit awkward. Maybe okay if clearly documented.

/Joel