(gcloud.sql.users.delete) INTERNAL_ERROR deleting postgres user

[Pilar Gomez]

I have been terraforming the dev environment, and I have been seeing an intermittent but rather persistent (8 times out of 10 or so) error when deleting databse users - Once I get in that state, there is no way back to normal.

The setup is rather simple, it is a postgres database. We have the default postgres user, and another user we create, called "proxyuser", that we use from the sidecar to connect to the DB. 

Right now, it is an empty database I terraformed a couple of hours ago. 

It has:

* One database: postgres

* Two users: postgres and proxyuser

I am trying to delete proxyuser. There are no open connections because the container cluster has been deleted. If I try to delete the user:

$ gcloud sql users delete proxyuser --instance  dev-instance-XXX --host <host>

prox...@35.234.129.60 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

                  

I have tried restarting the database, hoping to kill any zombie open connections (is there such a thing?) there may be.

I have tried doing it from the cloud UI, with the same results (same unknown error). Does anyone have any idea of what it may be going on?

I know that if I want to delete the database anyway, a workaround would be to delete the database straightaway, but sadly, that is the order terraform will try to do thing. And it should be a valid use case, right? Deleting a database user without deleting the whole database should be possible (there still will be the postgres user anyway... So it should be doable).

Thanks a bunch!

[David (Google Cloud Support)]

Hello Pilar,

The possible cause of this error is that you are trying to delete a user that has been granted privileges on other objects. Therefore, you need to:

1-Connect to your PostgreSQL instance with a user account with admin privileges:

gcloud sql connect [YourInstance] --user=[YourUser]

2-Revoke all privileges from the user you want to delete

REVOKE ALL PRIVILEGES ON DATABASE [DBname] from [UserToDelete];

3-Run this command to delete the user:

gcloud sql users delete [USER_NAME] --host=[HOST] --instance=[INSTANCE_NAME]