Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cannot delete role because it depends on "default privileges"

Cannot delete role because it depends on "default privileges"

From
Durumdara
Date:
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:
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  
---------------------------

How to we remove the default privileges from role_x to we can drop it?

Thank you!

    dd

Re: Cannot delete role because it depends on "default privileges"

From
Tom Lane
Date:
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

Re: Cannot delete role because it depends on "default privileges"

From
Durumdara
Date:
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

Re: Cannot delete role because it depends on "default privileges"

From
Tom Lane
Date:
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

Re: Cannot delete role because it depends on "default privileges"

From
Durumdara
Date:
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

Re: Cannot delete role because it depends on "default privileges"

From
Durumdara
Date:

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... :-(

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