Cannot delete role because it depends on "default privileges"

Durumdara <durumdara@gmail.com> writes:

We have a ROLE_MAIN.
This gave default privileges to all next objects in DB_X to ROLE_X.
Somebody dropped DB_X, and later he tried to drop ROLE_X.
But he got errors in PGAdmin.
ERROR: role "role_x" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new types belonging to role
role_main

See DROP OWNED BY.

https://www.postgresql.org/docs/9.6/static/role-removal.html

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

Dear Tom!

The DB_X dropped, so I can't choose it as "actual database".

I tried this in a neutral database:

drop owned by role_x;

But nothing happened, the error is same.

As I read it have "CASCADE" mode, but I'm afraid to start it, because I
don't know what will happen.

It is a really used server, so if I do something wrong, I will killed by my
boss... :-(

To complicate the things my coll. renamed this role to "role_x_to_delete",
and created a new role_x, and a new db_x database.

So if the PG uses "names" and not identifiers in the background, I will
be in trouble...

Thanks

2016-10-21 15:27 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

Durumdara <durumdara@gmail.com> writes:

The DB_X dropped, so I can't choose it as "actual database".
I tried this in a neutral database:
drop owned by role_x;
But nothing happened, the error is same.

The error you are reporting is describing default privileges that
exist in the *current* database. You need to do DROP OWNED BY
in that database (and maybe other ones, but start there).

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

Dear Tom!

Is there any tool what can show me the dependents or dependencies?

In PGAdmin I don't see any dependencies or dependents... :-(

Thanks
dd

2016-10-21 16:08 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

Wooooooooooooooooooooow... I found it. The postgres database contained more
default privs. But PGAdmin III nothing say about dependents in it's reports.

Thanks!

2016-10-21 16:19 GMT+02:00 Durumdara <durumdara@gmail.com>: