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

/Joel

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>

/Joel

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

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)

On Mon, Oct 21, 2024, at 11:42, Alvaro Herrera wrote:

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

Ahh, right, that's nicer, thanks for fixing.

New patch attached.

I also fixed pg_ownerships in the same way, moving the `pg_catalog.pg_database.datname = pg_catalog.current_database()` to the WHERE clause instead.

/Joel

Hi hackers,

As noted in previous feedback, relying on pg_shdepend leads to a less-than-ideal
behavior: no privileges are displayed for an object until at least one privilege
is granted to a role other than the owner, as explained by Alvaro:

On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote:

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.

I'm considering withdrawing this patch, due to this awkwardness.

Do we still want pg_ownerships? If so, I can submit it as a separate patch.

Are there any idea on how to address the underlying dependency issue so that
pg_privileges can report owner privileges consistently from the start,
without requiring a subsequent grant to another user?

/Joel