crossplane-contrib / crossplane-contrib/provider-sql
specific list of mysql privileges getting revoked during reconciliation
- Dominant language
- Go
- Stars
- 154
- Forks
- 119
- Avg merge
- 6d 17h
- Merged PRs (30d)
- 8
Description
### 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
Contributor guide
No contributing guide indexed for this repository
Research direction
Reproduce the Grant reconciliation with the supplied MySQL manifests and general-log queries, comparing the full privilege list with SELECT/INSERT and ALL PRIVILEGES. Trace the provider's grant comparison and reconciliation entry point, then verify that an unchanged full privilege list causes no REVOKE or re-GRANT during subsequent reconciles.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 45/100