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
alter table ... owner to postgres;
manually for those tables?SELECT * FROM pg_tables;
beforehand the tableowner column for each table in question was already the postgres user.revoke all on ... from user1;
for each of these tables?REVOKE ALL ON ALL TABLES IN SCHEMA public from user1;
worked like a dream. User is deleted! Answer it and I'll accept the answer.REVOKE ALL ON DATABASE mydb from user1;
in addition to the other command. 🤷♂️. Now comes the fun part of figuring out how the DB got in this state to begin with