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
0003-pg_permissions-and-pg_ownerships.patch
Description: Binary data
