List of objects owned by a schema/owner

Tiffany Thang <tiffanythang@gmail.com> writes:

Does anyone have a query that will list all the objects (composite types,
sequences, tables, triggers, functions, indices, etc) owned by a schema or
owner? I find fragments of information here and there that query on
pg_tables, pg_views, etc.

Well, you could attack it the hard way:

select relname from pg_class where relowner = [oid of role of interest]
union all
... similar select from every other catalog that has an owner column ...

I don't know if anyone's built such a query before, but a little quality
time with the system catalog documentation would get you there:
https://www.postgresql.org/docs/current/static/catalogs.html

Another idea is to rely on owner dependencies recorded in pg_shdepend,
along the lines of

select pg_describe_object(classid,objid,objsubid)
from pg_shdepend where deptype = 'o' and
refobjid = [oid of role of interest] and
dbid = [oid of current database];

That won't work for objects owned by the bootstrap superuser, and
I think there are some other omissions --- for instance, it looks
like we only record an owner dependency for a table, not for its
indexes.

Or, if you're feeling truly lazy, you can do

begin;
drop user joe;
-- read the error message whining about what joe owns
rollback;

That's largely a hacky way to get the same info as the pg_shdepend
query I sketched before, since the error message is derived from
exactly that info.

You have the same three options for schemas, though the details of each
are a bit different (in particular, schema dependencies would be found
in pg_depend not pg_shdepend).

regards, tom lane

Thanks Tom for giving me the lead. I’ll take a look.

On Fri, Aug 3, 2018 at 10:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: