Thread: 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

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)