Re: [PATCH] pg_permissions

On Thu, Mar 25, 2021, at 16:16, Alvaro Herrera wrote:
> On 2021-Mar-25, Joel Jacobson wrote:
>
> > pg_shdepend doesn't contain the aclitem info though,
> > so it won't work for pg_permissions if we want to expose
> > privilege_type, is_grantable and grantor.
>
> Ah, of course -- the only way to obtain the acl columns is by going
> through the catalogs individually, so it won't be possible. I think
> this could be fixed with some very simple, quick function pg_get_acl()
> that takes a catalog OID and object OID and returns the ACL; then
> use aclexplode() to obtain all those details.

+1 for adding pg_get_acl().
Do you want to write a patch for that?
I could try implementing it otherwise, but would be good with buy-in
from some more hackers on if we want these system views at all first.

Maybe we can try to decide on that first,
i.e. if we want them and what they should return?

In the meantime, if people want to try out the views,
I've modified the patch to use pg_shdepend for pg_ownerships,
while pg_permissions is still UNION ALL.

Both views now also use pg_identify_object().

Example usage:

CREATE ROLE test_user;
CREATE ROLE test_group;
CREATE ROLE test_owner;
CREATE SCHEMA test AUTHORIZATION test_owner;
GRANT ALL ON SCHEMA test TO test_group;
GRANT test_group TO test_user;

SELECT * FROM pg_permissions WHERE grantor = 'test_owner'::regrole;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+--------+--------+------+----------+------------+------------+----------------+--------------
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_owner | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_owner | CREATE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | CREATE | f
(4 rows)

SET ROLE TO test_user;
CREATE TABLE test.a ();
RESET ROLE;
ALTER TABLE test.a OWNER TO test_owner;

SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | type | schema | name | identity | owner
---------+-------+----------+--------+--------+------+----------+------------
1259 | 37129 | 0 | table | test | a | test.a | test_owner
2615 | 37128 | 0 | schema | | test | test | test_owner
(2 rows)

GRANT INSERT ON test.a TO test_group;

SELECT * FROM pg_permissions WHERE grantee = 'test_group'::regrole;
classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+--------+--------+------+----------+------------+------------+----------------+--------------
pg_class | 37129 | 0 | table | test | a | test.a | test_owner | test_group | INSERT | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | USAGE | f
pg_namespace | 37128 | 0 | schema | | test | test | test_owner | test_group | CREATE | f
(3 rows)

Looks good or can we improve them further?

>
> > The semantics will not be entirely the same,
> > since internal objects are not tracked in pg_shdepend,
> > but I think this is an improvement.
>
> I just realized that pg_shdepend will not show anything for pinned users
> (the bootstrap superuser). I *think* this is not a problem.

I also think it's not a problem.

Doing a "SELECT * FROM pg_ownerships" would be very noisy
if such objects would be included, as all pre-installed catalog objects would show up,
but by excluding them, the user will only see relevant ownerships explicitly owned by "real" roles.

We would get the same improvement for pg_permissions if pg_shdepend would be use there as well.
Right now it's very noisy, as all permissions also for the bootstrap superuser are included.

/Joel