2

I am using Pulumi GO SDK: When I try to destroy stack where I provisioned a new SQL DatabaseInstance, Database, password and user I get this error message:

21:00:33  [2022-07-05T18:00:33.872Z] Diagnostics:
21:00:33  [2022-07-05T18:00:33.874Z]   gcp:sql:User (gcp-test02-user:myuser):
21:00:33  [2022-07-05T18:00:33.875Z]     error: deleting urn:pulumi:us-east4-gcp-test02::cluster::gcp:myuser/sql:Database$gcp:sql/user:User::gcp-test02-user:myuser: 1 error occurred:
21:00:33  [2022-07-05T18:00:33.876Z]        * Error, failed to deleteuser myuser in instance gcp-test02-1b95d9a: googleapi: Error 400: Invalid request: failed to delete user myuser: . role "myuser" cannot be dropped because some objects depend on it Details: 640 objects in database mydatabases., invalid
21:00:33  [2022-07-05T18:00:33.877Z]  
21:00:33  [2022-07-05T18:00:33.877Z]   gcp:sql:Database (gcp-test02-db:mydatabases):
21:00:33  [2022-07-05T18:00:33.879Z]     error: deleting urn:pulumi:us-east4-auto-mgmt-console-gcp-test02::cluster::gcp:myuser/sql:Database$gcp:sql/database:Database::gcp-test02-db:mydatabases: 1 error occurred:
21:00:33  [2022-07-05T18:00:33.880Z]        * Error when reading or editing Database: googleapi: Error 400: Invalid request: failed to delete database "sentinellabs". Detail: pq: database "sentinellabs" is being accessed by other users. (Please use psql client to delete database that is not owned by "cloudsqlsuperuser")., invalid
21:00:33  [2022-07-05T18:00:33.881Z]  
21:00:33  [2022-07-05T18:00:33.881Z]   pulumi:pulumi:Stack (cluster-us-east4-auto-mgmt-console-gcp-test02):
21:00:33  [2022-07-05T18:00:33.882Z]     error: update failed
Chanan Berler
  • 69
  • 3
  • if this or any answer has solved your question please consider [accepting it](https://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. Another option is to [upvote the answer](https://stackoverflow.com/help/someone-answers) if you feel it is useful for you There is no obligation to do this. – Hector Martinez Rodriguez Jul 11 '22 at 15:25
  • There should be a more Pulumi way to drop user. I found that I can use dependsON SQL on my app virtual instances running services accessing the DB. It resolve issue where DB is being used by service. As for user I tried setting user dependsON Database and DatabaseInstance but it still tries to delete user while Database is still up (deleting Database will also delete User) - I also added RetainOnDelete to user options where it says it will not delete user from cloud but only from state - deleting Database will also delete the user - this option also failed – Chanan Berler Aug 03 '22 at 09:29

2 Answers2

1

[...] failed to delete user myuser: . role "myuser" cannot be dropped because some objects depend on it [...]

DROP USER(or DROP ROLE) cannot proceed while the role still owns anything or has any granted privileges on other objects.

In the GCP Console, in your Cloud SQL instance, you should get rid of all privileges with DROP OWNED (which isn't obvious). The manual:

[...] Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.

So the sequence of commands to drop a role should be:

REASSIGN OWNED BY myuser TO postgres;  
DROP OWNED BY myuser;

Run both commands in every database of the same cluster where the role owns anything or has any privileges. And then:

DROP USER myuser;
  • REASSIGN OWNED changes ownership for all objects currently owned by the role.
  • DROP OWNED then only revokes privileges (ownerships out of the way).

Try again pulumi destroy.

Finally, you should run ‘pulumi refresh’, and then the CLI should detect that it was deleted and remove it from the stack.

Recommended:

Andrés
  • 487
  • 1
  • 12
  • There should be Pulumi way to delete Database and user. I did found that deleting the SQL Database will delete user. But when asking to destroy pulumi resources it tries to delete user with SQL - and using user dependsON database fails - still pulumi tries to delete user before/during SQL database still alive – Chanan Berler Aug 03 '22 at 09:31
-1

It looks like there might be an additional database added to that sql instance that is being accessed and locked. You might have to login to the sql instance and drop that db first, then run pulumi refresh, and pulumi destroy. The 400 error is being returned from Google.

Richard Shade
  • 1