Search Postgresql Archives

Re: Best way to list a roles owned objects?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Jerry Sievers <gsievers19@xxxxxxxxxxx> writes:

> Felipe Gasper <felipe@xxxxxxxxxxxxxxxx> 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. 

Minor clarification...

You will get only OID data from pg_shdepend.  Assuming that this isn't
helpful for a human making a decision whether or not to drop a role and
all it's dependencies and/or reassign  them...

Symbolic information  will only be found in the related DB.

Furthermore,  you need to visit the related DB anyhow to run either of
DROP OWNED BY or REASSIGN OWNED BY.

HTH

>
> 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.consulting@xxxxxxxxxxx
> p: 312.241.7800

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux