Cannot drop materialized view privileges from user

Question

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!

Answer 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.