crossplane-contrib / crossplane-contrib/provider-sql

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

Open
#344 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Go
Stars
154
Forks
119
Avg merge
6d 17h
Merged PRs (30d)
8

Description

### 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

```yaml
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:

```text
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:

```text
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

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.