Delete User and Grant after Database Deletion

[uluzox]

What problem are you facing?

I am using FluxCD to reconcile my crossplane resources in my k8s cluster. I provision a database on AWS RDS with

apiVersion: v1
kind: Namespace
metadata:
  name: db-deletion-test
---
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Database
metadata:
  name: db-deletion-test-db
spec:
  providerConfigRef:
    name: default
  deletionPolicy: Delete
---
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: User
metadata:
  name: db-deletion-test-user
spec:
  deletionPolicy: Delete
  providerConfigRef:
    name: default
  forProvider: {}
  writeConnectionSecretToRef:
    name: db-deletion-test-db-conn
    namespace: db-deletion-test
---
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: db-deletion-test-db
spec:
  deletionPolicy: Delete
  providerConfigRef:
    name: default
  forProvider:
    privileges:
      - ALL
    userRef:
      name: db-deletion-test-user
    databaseRef:
      name: db-deletion-test

If I delete the Kustomization that includes the resources above. Flux tries to remove the namespace, user, grant and database all at once. My observation is that while namespace, user and grant are deleted, the database remains as k8s resource in the cluster and in RDS. However this does only happen if the database is not empty. Therefore I assume that if the database is somewhat filled, the user, grant and namespace (and with that the connection secret) is deleted before the database is successfully dropped.

How could Crossplane help solve your problem?

Implement a deletion order via finalizers? Can I somehow mark the namespace to wait for those cluster scoped resources (user, grant, database) before it deletes the connection secret?

[acelinkio]

One solution is database to have an ownerRef field. This would create a link between the role/user and dictate the order of operations: Role/User -> Database -> Grant. That does does not exist currently. https://doc.crds.dev/github.com/crossplane-contrib/provider-sql/postgresql.sql.crossplane.io/Database/v1alpha1@v0.6.0

Example of what the desired configuration would be:

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Role
metadata:
  name: testdb-owner
spec:
  forProvider: {}
  providerConfigRef:
    name: dbms00-superuser
---
apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Database
metadata:
  name: testdb
spec:
  forProvider:
    ownerRef: testdb-owner
  providerConfigRef:
    name: dbms00-superuser