specific list of mysql privileges getting revoked during reconciliation

[luis-alen]

What happened?

When provider-sql for mysql grants the below specific list of privileges to a user, it revokes all of them and then grants again when reconciling although the grant object has not been changed at all. This causes the user to temporarily lose access to the database.

      - SELECT
      - INSERT
      - UPDATE
      - DELETE
      - CREATE
      - DROP
      - REFERENCES
      - INDEX
      - ALTER
      - CREATE TEMPORARY TABLES
      - LOCK TABLES
      - EXECUTE
      - CREATE VIEW
      - SHOW VIEW
      - CREATE ROUTINE
      - ALTER ROUTINE
      - EVENT
      - TRIGGER

If you grep for the username on mysql general logs, you'll see the following:

2023-11-22T10:51:40.534820Z	   68 Query	SHOW GRANTS FOR 'my-app'@'%'
2023-11-22T10:51:40.542055Z	   69 Query	REVOKE ALL ON `my-app`.* FROM 'my-app'@'%'
2023-11-22T10:51:40.586787Z	   71 Query	GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `my-app`.* TO 'my-app'@'%'

However, if you grant only SELECT and INSERT or ALL PRIVILEGES, for example, this won't happen. In that case crossplane seems to understand there's no diff and doesn't take any action, as expected.

I'd say this is an extension of https://github.com/crossplane-contrib/provider-sql/issues/126, which was fixed by https://github.com/crossplane-contrib/provider-sql/pull/136, but apparently not fully.

How can we reproduce it?

• enable mysql general log
• create a database

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Database
metadata:
  name: my-app
spec:
  providerConfigRef:
    name: mysql8

• create a user

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: User
metadata:
  name: my-app
spec:
  providerConfigRef:
    name: mysql8
  deletionPolicy: Delete
  forProvider: {}
  writeConnectionSecretToRef:
    name: mysql8-my-app
    namespace: crossplane-system

• create a grant with the privileges mentioned above for the previously created user.

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: my-app
spec:
  forProvider:
    privileges:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
      - CREATE
      - DROP
      - REFERENCES
      - INDEX
      - ALTER
      - CREATE TEMPORARY TABLES
      - LOCK TABLES
      - EXECUTE
      - CREATE VIEW
      - SHOW VIEW
      - CREATE ROUTINE
      - ALTER ROUTINE
      - EVENT
      - TRIGGER
    userRef:
      name: my-app
    database: my-app
  providerConfigRef:
    name: mysql8

• grep for the user name on the general log and you should see crossplane showing grants for the user, revoking them and granting again every time it reconciles:

2023-11-22T10:31:40.528165Z	   33 Query	SHOW GRANTS FOR 'my-app'@'%'
2023-11-22T10:31:40.539979Z	   34 Query	REVOKE ALL ON `my-app`.* FROM 'my-app'@'%'
2023-11-22T10:31:40.574634Z	   36 Query	GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `my-app`.* TO 'my-app'@'%'

• create a second user

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: User
metadata:
  name: my-app2
spec:
  providerConfigRef:
    name: mysql8
  deletionPolicy: Delete
  forProvider: {}
  writeConnectionSecretToRef:
    name: mysql8-my-app2
    namespace: crossplane-system

• create the grant for the 2nd user. This time only with INSERT and SELECT or with ALL PRIVILEGES:

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: my-app2
spec:
  forProvider:
    privileges:
      - SELECT
      - INSERT
    userRef:
      name: my-app2
    database: my-app
  providerConfigRef:
    name: mysql8

• grep for this user name on the general log and you shouldn't see what happened with the 1st user. crossplane will understand there's no diff and won't take any action when reconciling.

What environment did it happen in?

Crossplane version: v1.12.2 provider-sql version: v0.7.0

[luis-alen]

I believe the problem is that this specific set of privileges translates to ALL PRIVILEGES for mysql and this is probably what causes the diff.

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: my-app
spec:
  forProvider:
    privileges:
      - SELECT
      - INSERT
      - UPDATE
      - DELETE
      - CREATE
      - DROP
      - REFERENCES
      - INDEX
      - ALTER
      - CREATE TEMPORARY TABLES
      - LOCK TABLES
      - EXECUTE
      - CREATE VIEW
      - SHOW VIEW
      - CREATE ROUTINE
      - ALTER ROUTINE
      - EVENT
      - TRIGGER
    userRef:
      name: my-app
    database: my-app
  providerConfigRef:
    name: mysql8

mysql> show grants for 'my-app'@'%';
+----------------------------------------------------+
| Grants for my-app@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `my-app`@`%`                 |
| GRANT ALL PRIVILEGES ON `my-app`.* TO `my-app`@`%` |
+----------------------------------------------------+

Crossplane then revokes ALL PRIVILEGES and grants the specific set of privileges again:

2023-11-22T10:51:40.534820Z	   68 Query	SHOW GRANTS FOR 'my-app'@'%'
2023-11-22T10:51:40.542055Z	   69 Query	REVOKE ALL ON `my-app`.* FROM 'my-app'@'%'
2023-11-22T10:51:40.586787Z	   71 Query	GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `my-app`.* TO 'my-app'@'%'

[Duologic]

Interesting.

So if the GRANT has a list of all the privileges, then MySQL just translates that as an ALL PRIVILEGES grant? Can you verify by leaving out just one privilege?

[luis-alen]

@Duologic yes. I actually tested that already. At least that's how it worked with mysql 8.