Re: [PATCH] pg_permissions

On Tue, Mar 23, 2021, at 21:39, Alvaro Herrera wrote:
>I wonder if these views should be defined on top of pg_shdepend instead
>of querying every single catalog. That would make for much shorter
>queries.

+1

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.

pg_shdepend should work fine for pg_ownerships though.

The semantics will not be entirely the same,
since internal objects are not tracked in pg_shdepend,
but I think this is an improvement.

Example:

create role baz;
create type foobar as ( foo int, bar boolean );
alter type foobar owner to baz;

-- UNION ALL variant:

select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
----------+--------+----------+-------+----------------+--------+---------+-----------------
pg_class | 407858 | 0 | baz | composite type | public | foobar | public.foobar
pg_type | 407860 | 0 | baz | type | public | foobar | public.foobar
pg_type | 407859 | 0 | baz | type | public | _foobar | public.foobar[]
(3 rows)

-- pg_shdepend variant:

select * from pg_ownerships where owner = 'baz'::regrole;
classid | objid | objsubid | owner | type | schema | name | identity
---------+--------+----------+-------+------+--------+--------+---------------
1247 | 407860 | 0 | baz | type | public | foobar | public.foobar
(1 row)

I'll update the patch.

/Joel