Обсуждение: Cannot delete role because it depends on "default privileges"
Hello!
We created a DB named DB_X, and a role ROLE_X.
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.
---------------------------
pgAdmin III
---------------------------
An error has occurred:
pgAdmin III
---------------------------
An error has occurred:
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
privileges for default privileges on new functions belonging to role role_main
privileges for default privileges on new sequences belonging to role role_main
privileges for default privileges on new relations belonging to role role_main
privileges for default privileges on new types belonging to role postgres
privileges for default privileges on new functions belonging to role postgres
privileges for default privileges on new sequences belonging to role postgres
privileges for default privileges on new relations belonging to role postgres
---------------------------
OK
---------------------------
DETAIL: privileges for default privileges on new types belonging to role role_main
privileges for default privileges on new functions belonging to role role_main
privileges for default privileges on new sequences belonging to role role_main
privileges for default privileges on new relations belonging to role role_main
privileges for default privileges on new types belonging to role postgres
privileges for default privileges on new functions belonging to role postgres
privileges for default privileges on new sequences belonging to role postgres
privileges for default privileges on new relations belonging to role postgres
---------------------------
OK
---------------------------
How to we remove the default privileges from role_x to we can drop it?
Thank you!
dd
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
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:
> 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
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
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>:
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
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>:
Dear Tom!Is there any tool what can show me the dependents or dependencies?In PGAdmin I don't see any dependencies or dependents... :-(Thanksdd2016-10-21 16:08 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