PG-9.3.6, unable to "drop role because some objects depend on it"

Started by dennis jenkinsalmost 11 years ago3 messagesgeneral
Jump to latest
#1dennis jenkins
dennis.jenkins.75@gmail.com

I am attempting to remove a "role" from Postgresql-9.3.6. I've already
reassigned ownership for the role's tables, functions, sequences, types,
views, etc... However, I am still unable to remove the role. Postgresql
reports that "8 objects in the database 'postgres'" depend on this role.

How do I locate the database objects that depend on the 'DEADUSER' so that
I can remedy the situation?

ps- I've tried "REASSIGN OWNED BY DEADUSER TO pgsql;" with no success.

(env vars set for PGDATABASE and PGUSER, target role name changed to
protect the guilty)

# psql -c"drop role DEADUSER;"
ERROR: role "DEADUSER" cannot be dropped because some objects depend on it
DETAIL: 8 objects in database postgres

# pg_dump --schema-only postgres | grep -i DEADUSER
## No results

#2dennis jenkins
dennis.jenkins.75@gmail.com
In reply to: dennis jenkins (#1)
Re: PG-9.3.6, unable to "drop role because some objects depend on it"

Doh. I found my answer. Tome posted it years ago..
/messages/by-id/18994.1325874130@sss.pgh.pa.us

I have to connect to the offending database and try to "drop role" again to
get the list of actual objects. One database cannot query the catalog of
another.

On Mon, Apr 13, 2015 at 12:05 PM, Dennis Jenkins <
dennis.jenkins.75@gmail.com> wrote:

Show quoted text

I am attempting to remove a "role" from Postgresql-9.3.6. I've already
reassigned ownership for the role's tables, functions, sequences, types,
views, etc... However, I am still unable to remove the role. Postgresql
reports that "8 objects in the database 'postgres'" depend on this role.

How do I locate the database objects that depend on the 'DEADUSER' so that
I can remedy the situation?

ps- I've tried "REASSIGN OWNED BY DEADUSER TO pgsql;" with no success.

(env vars set for PGDATABASE and PGUSER, target role name changed to
protect the guilty)

# psql -c"drop role DEADUSER;"
ERROR: role "DEADUSER" cannot be dropped because some objects depend on it
DETAIL: 8 objects in database postgres

# pg_dump --schema-only postgres | grep -i DEADUSER
## No results

#3dennis jenkins
dennis.jenkins.75@gmail.com
In reply to: dennis jenkins (#2)
Re: PG-9.3.6, unable to "drop role because some objects depend on it"

Apologies for the typo of your name in my last post, Tom.

On Mon, Apr 13, 2015 at 12:16 PM, Dennis Jenkins <
dennis.jenkins.75@gmail.com> wrote:

Show quoted text

Doh. I found my answer. Tome posted it years ago..
/messages/by-id/18994.1325874130@sss.pgh.pa.us

I have to connect to the offending database and try to "drop role" again
to get the list of actual objects. One database cannot query the catalog
of another.

On Mon, Apr 13, 2015 at 12:05 PM, Dennis Jenkins <
dennis.jenkins.75@gmail.com> wrote:

I am attempting to remove a "role" from Postgresql-9.3.6. I've already
reassigned ownership for the role's tables, functions, sequences, types,
views, etc... However, I am still unable to remove the role. Postgresql
reports that "8 objects in the database 'postgres'" depend on this role.

How do I locate the database objects that depend on the 'DEADUSER' so
that I can remedy the situation?

ps- I've tried "REASSIGN OWNED BY DEADUSER TO pgsql;" with no success.

(env vars set for PGDATABASE and PGUSER, target role name changed to
protect the guilty)

# psql -c"drop role DEADUSER;"
ERROR: role "DEADUSER" cannot be dropped because some objects depend on
it
DETAIL: 8 objects in database postgres

# pg_dump --schema-only postgres | grep -i DEADUSER
## No results