Re: [PATCH] pg_permissions

On Tue, Mar 9, 2021, at 04:01, Chapman Flack wrote:
> On Sat, Mar 06, 2021 at 08:03:17PM +0100, Joel Jacobson wrote:
> > regclass | obj_desc | grantor | grantee |
> privilege_type | is_grantable
> >
> --------------+-----------------------------+---------+---------+----------------+--------------
>
> 1. Is there a reason not to make 'grantor' and 'grantee' of type regrole?
> In other words, s/rolname/oid::regrole/ throughout the view definition.
> It looks the same visually, but should be easier to build on in a larger
> query.
>
> Hmm, ok, a grantee of 'public' can't be expressed as a regrole. This
> seems an annoying little corner.[1] It can be represented by 0::regrole,
> but that displays as '-'. Hmm again, you can even '-'::regrole and get 0.
>
>
> 2. Also to facilitate use in a larger query, how about columns for the
> objid and objsubid, in addition to the human-friendly obj_desc?
> And I'm not sure about using pg_attribute as the regclass for
> attributes; it's nice to look at, but could also plant the wrong idea
> that attributes have pg_attribute as their classid, when it's really
> pg_class with an objsubid. Anyway, there's the human-friendly obj_desc
> to tell you it's a column.

Thanks for coming up with these two good ideas. I was wrong, they are great.

Both have now been implemented.

New patch attached.

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 | objdesc | grantor | grantee | privilege_type | is_grantable
--------------+-------+----------+-------------+------------+------------+----------------+--------------
pg_namespace | 16390 | 0 | schema test | test_owner | test_owner | USAGE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_owner | CREATE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | USAGE | f
pg_namespace | 16390 | 0 | schema test | test_owner | test_group | CREATE | f
(4 rows)

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

SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | objdesc | owner
--------------+-------+----------+-------------+------------
pg_namespace | 16390 | 0 | schema test | test_owner
(1 row)

ALTER TABLE test.a OWNER TO test_owner;

SELECT * FROM pg_ownerships WHERE owner = 'test_owner'::regrole;
classid | objid | objsubid | objdesc | owner
--------------+-------+----------+-------------+------------
pg_class | 16391 | 0 | table a | test_owner
pg_namespace | 16390 | 0 | schema test | test_owner
pg_type | 16393 | 0 | type a | test_owner
pg_type | 16392 | 0 | type a[] | test_owner
(4 rows)

GRANT INSERT ON test.a TO test_group;

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

/Joel