search

berenddeboer / cdk-rds-sql

A CDK construct that allows creating roles and databases an on Aurora Serverless Postgresql cluster.
Apache License 2.0
23 stars 11 forks source link

Dropping a role failes if objects were created #39

Open stephanpelikan opened 4 weeks ago

stephanpelikan commented 4 weeks ago

As part of my CDK script I deploy applications which creates tables, etc. For each application I've created a separate role. Undeploying the application does not remove those tables. However, on destroying via CDK the RDS cluster, the role needs to be deleted as well which causes this error which prevents from destroying the cluster:

Could not drop the role. ERROR: role "[username]" cannot be dropped because some objects depend on it DETAIL: [#] objects in database [dbname]

I had to connect to each database using the respective role created and run the command

drop owned by "[username]";

After doing so, the role could be deleted by CDK. I think it would be great if cdk-rds-sql executes this command as part of destroying the Role.

berenddeboer commented 3 weeks ago

I think that could be a significant surprise :-) What about attaching some sql to a role which gets run on delete or the role? Then you can include this behaviour.

stephanpelikan commented 3 weeks ago

You're right. If one has more than one role or wants to keep data, then this is a bad idea.

What about attaching some sql to a role which gets run on delete or the role?

Yes and no. I'm not able to guess any quotas, but I think may use-case is not a rare one. How many people like me will run into the same problem since they are not aware of that situation? For important use-cases I would prefer a separate property like onDeleteDropObjectsOwnedByRole. If there is only a property sqlToRunOnDelete someone might not think that they will encounter a problem when deleting. On the other hand, there cannot be a separate property for each use-case. Maybe, if the documentation lists examples for typical use-cases (as mine) then it is fine. How do you rate the importance of this use case?