Re: Best way to list a roles owned objects?

Felipe Gasper <felipe(at)felipegasper(dot)com> writes:

> On 7/1/14 1:13 PM, John R Pierce wrote:
>
>> On 7/1/2014 11:08 AM, Felipe Gasper wrote:
>>> What is the best way to list a role’s owned objects in any database?
>>
>> query pg_class in each database ?
>>
>
> Every database on the cluster, individually, then? Is there no way to
> query all databases at once?
>
> I mean, *something* under the hood must be doing this because DROP
> ROLE bugs out if the role owns anything in any DB.

That is made possible by pg_shdepend catalog which makes note of shared
dependencies however it will *not* inform you of what specific objects
are depending unless you visit each such DB to find out.

As for doing REASSIGN OWNED BY, as you mentioned earlier...

A better practice might be to create a special role on your cluster (say
orphaned_objects) and let this user take ownership of the depending
objects.

This makes possible for you to easily identify such items later rather
then have them mixed up with everything postgres owns.

The assumption is, that many of the things so reassigned are quite
possibly junk, given that the real owner has been dropped from the system.

>
> -F

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800