crossplane-contrib / crossplane-contrib/provider-sql

specific list of mysql privileges getting revoked during reconciliation

Open
#162 3 comments 0 reactions 0 assignees View on GitHub
bug help wanted
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.