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?