Changing database external/name does not trigger update of granted privileges

[jabbrwcky]

What happened?

I have the use case that BI need to have read privileges across several databases. To configure this independently of individual application deployments, we define the used database as Observe with an crossplane.io/external-name annotation to attach to the database.

We then create the user and grant privileges as usual.

Now one team renamed their DB in our staging system, so I updated the external name, which had no effect, although crossplane shows everything to be in sync.

Observations:

1. Altering the external-name annotation does not trigger an update of grants
2. Deleting the Database CR and redeploying with the new external-name creates the new grant, BUT it does not delete the old grant.

IMO the provider should reconcile changes caused by the external-name annotation and also either revoke grants if the corresponding DB CR is deleted (or block the deletion, but preferring deleting the grant and moving the grant object in an unreconciled state).

How can we reproduce it?

Initial DB-Definition

apiVersion: mysql.sql.m.crossplane.io/v1alpha1
kind: Database
metadata:
  name: et
  annotations:
    crossplane.io/external-name: et_staging
spec:
  managementPolicies:
    - Observe
  forProvider: {}
  providerConfigRef:
    kind: ClusterProviderConfig
    name: default
---
apiVersion: mysql.sql.m.crossplane.io/v1alpha1
kind: User
metadata:
  name: bi
spec:
  managementPolicies:
    - Observe
    - Create
    - Update
  forProvider:
    resourceOptions:
      maxUserConnections: 4
  providerConfigRef:
    kind: ClusterProviderConfig
    name: default
  writeConnectionSecretToRef:
    name: bi
---
apiVersion: mysql.sql.m.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: bi-et
spec:
  managementPolicies:
    - Observe
    - Create
    - Update
  forProvider:
    userRef:
      name: bi
    databaseRef:
      name: et
    privileges:
      - SELECT
  providerConfigRef:
    kind: ClusterProviderConfig
    name: default

Assigned grants:

mysql> show grants for 'bi';
+--------------------------------------------+
| Grants for bi@%                            |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `bi`@`%`             |
| GRANT SELECT ON `et_staging`.* TO `bi`@`%` |
+--------------------------------------------+
2 rows in set (0.002 sec)

The DB definition changed to:

apiVersion: mysql.sql.m.crossplane.io/v1alpha1
kind: Database
metadata:
  name: et
  annotations:
    crossplane.io/external-name: et-staging-db
spec:
  managementPolicies:
    - Observe
  forProvider: {}
  providerConfigRef:
    kind: ClusterProviderConfig
    name: default

After triggering the reconciliation by deleting the DB and recreating it, I get the following grants:

mysql> show grants for 'bi';
+-----------------------------------------------+
| Grants for bi@%                               |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `bi`@`%`                |
| GRANT SELECT ON `et-staging-db`.* TO `bi`@`%` |
| GRANT SELECT ON `et_staging`.* TO `bi`@`%`    |
+-----------------------------------------------+
3 rows in set (0.001 sec)

What environment did it happen in?

Crossplane version: 2.1.0
provider-sql: 0.13
Database: Percona PXC DB Cluster