Feedback-ID: ia2694551:Fastmail
Date: Sun, 20 Oct 2024 12:14:10 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Joel Jacobson <joel@compiler.org>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Add pg_ownerships and pg_privileges system views
Message-ID: <202410201014.dcvfvqdncdds@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <bbe7d1cb-0435-4ee6-a9f5-7dbc79ab84b2@app.fastmail.com>
Archived-At: <https://www.postgresql.org/message-id/202410201014.dcvfvqdncdds%40alvherre.pgsql>
Precedence: bulk

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