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 : [1-1] db=cloudsqladmin,user=cloudsqlagent ERROR: role "user1" cannot be dropped because some objects depend on it 06:42:36.347 UTC : [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]
- 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