Deleting a MySQL User resource and then deleting a MySQL Grant resource linked to it causes the Grant deletion to hang

[danports]

Grant deletion never occurs, and this warning repeats endlessly:

Warning  CannotObserveExternalResource  managed/grant.mysql.sql.crossplane.io  cannot show current grants: Error 1141: There is no such grant defined for user 'test-app' on host '%!'(MISSING)

This is on Crossplane 1.3.0 and MySQL 5.7. Can work on a repro case a bit later. The issue can be worked around by manually editing the Grant resource and removing the Crossplane finalizer.

[negz]

What's the underlying behaviour in MySQL here? Does deleting the user automatically clean up the relevant grants? If so, the grant controller should probably contain logic such that the Delete method succeeds as a no-op if the grant pertains to a user that doesn't exist.

[danports]

Yes, deleting a MySQL user automatically cleans up the relevant grants - I checked just now on both 5.7 and 8.0.

[JGodin-C2C]

Hello ! what is the status ?

[mateusz-lubanski-sinch]

I am facing exactly same issue. I have a composition which creates on AWS RDS Instance (mysql engine):

• mysql.sql.crossplane.io/v1alpha1/databases
• mysql.sql.crossplane.io/v1alpha1/users
• mysql.sql.crossplane.io/v1alpha1/grants

When I delete claim corresponding compositeresource (XR) object can't be deleted due to the problem described by @danports

As a workaround I set deletionPolicy to Orphan...

[danports]

Awesome, thanks @jabbrwcky!

[mateusz-lubanski-sinch]

@Duologic I tested it on v0.6.0 release and problem still exists. When I delete claim which contains database, user and grant, there is below warning on grant which can't be deleted:

Warning  CannotObserveExternalResource  21s (x13 over 86s)  managed/grant.mysql.sql.crossplane.io  cannot show current grants: Error 1141: There is no such grant defined for user 'db3-ro-user' on host '%!'(MISSING)

[Duologic]

If that's the case, let's reopen this.

[jabbrwcky]

Looks like I missed a spot. I also had this in my cluster. I'll take another look.

[acelinkio]

This be resolved by using references instead of direct strings. User would be marked as in use and not deleted until that was cleared.

apiVersion: postgresql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: user01-rolemembership
spec:
  forProvider:
    roleRef:
      name: user01
    memberOfRef:
      name: read-write
  providerConfigRef:
    name: dbms00-superuser

Adding database engine logic should be avoided where possible.

[jabbrwcky]

IMO there is little database logic involved.

When the deletionTimestamp is set on the Grant resource the reconciliation should not fail and prevent removing the finalizer if it is already gone.

Proper dependency management would be complicated because multiple cases would have to be considered.

Two main strategies need to be considered:

• A Grant that is not deleted blocks deleting connected resources, i.e. Users and Databases
• Deleting a User or Database triggers a cascading deletion of all connected Grants

Also reference, finalizer and delete cascade handling has to be implemented by the controller which would increase the complexity significantly.

[jabbrwcky]

The PR is not good-to-go yet, I would first like to verify the approach taken (and I need to find some time to spend on testing it 😬 )

[mzeeshan1]

@mateusz-lubanski-sinch did you set deletion policy to orphan for user as well? I am trying with only grant and it doesn't seem to work for me. I was assuming that when setting the deletion policy to orphan for grant it wouldn't check the database for its existence but on the contrary it does check it.