Обсуждение: List of objects owned by a schema/owner
Hi,
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.
Thanks.
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:
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