Thanks Tom for giving me the lead. I’ll take a look.
On Fri, Aug 3, 2018 at 10:52 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Tiffany Thang <tiffanyth...@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 >
