0

Legacy database to me, pg version 9.6.12 running on a linux ami.

I've a user ('example_user') that I would like to drop. I've confirmed that 'example_user' owns no objects. However, when I run DROP USER 'example_user'; I get this (I've truncated for brevity) error:

exampledb=# drop user 'example_user';
ERROR:  role 'example_user' cannot be dropped because some objects depend on it
DETAIL:  privileges for materialized view "example-prod".view_foo
privileges for materialized view "example-prod".view_bar
privileges for materialized view "example-dev".view_foo
privileges for materialized view "example-dev".view_bar

I've tried about 15 different revocation statements to attempt to kill the privileges, and in some cases Postgres doesn't even complain. For example:

exampledb=# revoke all privileges on all tables in schema public from "example_user";
REVOKE

# OR

revoke all privileges on "example-prod".view_foo from "example_user";
REVOKE

I've tried countless different revokes on the separate schemas, views, databases and nothing seems to work. The privileges are not removed and I I get the same errors when I attempt to drop the user. Not sure if it's in any way related, but pg complains if I don't double quote the user.

How can these specific, or indeed all privileges, be removed from this user? Are there any other strategies for dropping this user if I don't need to worry about preserving object or privileges?

Thanks!

1

revoke all privileges on all tables in schema public from "example_user";

This drops privileges from objects in the schema named "public". It does not drop them from objects in other schemas, such as "example-prod".

revoke all privileges on "example-prod".view_foo from "example_user";

Works for me. It still fails due to the other 3 MV, but no longer for that specific one.

  • Yes, the first example was just a hail mary. The second, surprisingly, returns "REVOKE" but does not appear to remove the permission. Thank you for the reply. – RangerRanger Jun 24 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.