ERROR: role "user1" cannot be dropped because some objects depend on it

Question

On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that says "User deleted", then upon further inspection I see that the operation failed with an "Unknown error". The instance's error logs show the following error:

06:42:36.347 UTC [199]: [1-1] db=cloudsqladmin,user=cloudsqlagent ERROR:  role "user1" cannot be dropped because some objects depend on it
06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL:  privileges for database mydb
24 objects in database mydb

I am able to see the 24 objects by running

SELECT distinct table_name
FROM information_schema.role_table_grants
WHERE grantee = 'user1'

Following the advice at https://cloud.google.com/sql/docs/postgres/create-manage-users#deleting_users and I know that I must reassign the ownership to another role (postgres) before dropping the user, but when I attempt to run the following script (from https://stackoverflow.com/questions/51256454/cannot-drop-postgresql-role-error-cannot-be-dropped-because-some-objects-depe):

REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;
DROP USER user1;

I am presented with a permissions error on the REASSIGN command (ERROR: permission denied to reassign objects) due to the fact that Cloud SQL doesn't give my user SUPERUSER status (https://cloud.google.com/sql/docs/postgres/users#superuser_restrictions). I am also unsure of how to grant SUPERUSER status to a user in a Cloud SQL database instance; my assumption is that I cannot.

This issue is also occurring when using the gcloud command of the Cloud Shell. The output is similar:

$ gcloud sql users delete user1 --instance=<INSTANCE_ID_REDACTED>
user1@None will be deleted. New connections can no longer be made
using this user. Existing connections are not affected.

Do you want to continue (Y/n)? Y

Deleting Cloud SQL user...failed.
ERROR: (gcloud.sql.users.delete) [INTERNAL_ERROR]

Important information:

• Postgres Version 9.6
• A read replica is attached to this instance
• Yes, I have tried turning it off and on again. No, the restart of both the main instance and the read replica did not solve this issue.
• All database schema migrations and data migrations on this instance have been accomplished using Flask-Alembic with a very standard configuration
• I can verify that no users have altered the schema outside of the Alembic process in our Cloud Build configuration
• The user was created in the GCP console
• No manual alterations have been made to the user, privileges, or object ownership

Answer 1

06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent DETAIL: privileges for database mydb 24 objects in database mydb

Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL will see some dependencies with the specified pg_catalog.pg_shdepend.dbid, but will not be able to query the system catalog of that database to determine which objects they are. So, postgresql does hint only database names in error details for other databases. (this is also a reason why PostgreSQL has no drop user ... cascade - we can't modify or even read objects description from another database)

But if you connect to the database specified in the details and try to delete a user, you will get a much more detailed description:

postgres=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  3 objects in database m2
postgres=# \connect m2 
You are now connected to database "m2" as user "postgres".
m2=# drop user user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table foo
privileges for table foofk
owner of table test

In this case user1 is owner for one table (REASSIGN OWNED will solve this easily) and some priviilegies on another two tables (REASSIGN OWNED will not touch this).

Certain commands are required for different objects (revoke for permissions, alter default privileges for default privileges and so on), unfortunately, there is no omnipotent ring to manage all dependencies even in one database.