Re: 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