From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tiffany Thang <tiffanythang(at)gmail(dot)com> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List of objects owned by a schema/owner |
Date: | 2018-08-03 14:52:04 |
Message-ID: | 14401.1533307924@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tiffany Thang <tiffanythang(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Charles Clavadetscher | 2018-08-03 15:32:23 | RE: how to install pgcrypto |
Previous Message | pinker | 2018-08-03 14:26:25 | Re: List user who have access to schema |