Best way to list a role’s owned objects?
Hi all,
What is the best way to list a role�s owned objects in any database?
My problem is that I need to be able to �FORCE DROP ROLE� by taking any
objects that that role owns, in any database, and reassigning them to
the �postgres� user.
Even if REASSIGN OWNED BY worked for this purpose (it only works on the
current database), I would still need a way to display to the API caller
which objects were reassigned.
Oh, and this preferably needs to work all the way back to PostgreSQL
8.1. :-<
Any ideas?
Thanks!
-Felipe Gasper
Houston, TX
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 ?
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
-F
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Felipe Gasper <felipe@felipegasper.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.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jerry Sievers <gsievers19@comcast.net> writes:
Felipe Gasper <felipe@felipegasper.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.
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@comcast.net
p: 312.241.7800
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Jerry Sievers <gsievers19@comcast.net> writes:
Felipe Gasper <felipe@felipegasper.com> writes:
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.
Yeah. You can identify the kind of object represented by each entry,
since classid values are common to all databases; but you can't resolve
any more information than that unless you connect to the DB in question.
This is because you can only "see" a given DB's system catalogs when
connected to that DB.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general