(Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT

[alereca]

What happened?

When creating or updating a grant sql provider always try to grant it for <selected-username>@%, instead of <selected-username>@<selected-host>. Which results in an Error 1410: You are not allowed to create a user with GRANT, as <selected-username>@% does not exists

2023-02-06T17:43:35.445964Z     24753 Connect   root@minimal-cluster-haproxy-0.minimal-cluster-haproxy.mysql.svc.cluster.local on  using SSL/TLS
2023-02-06T17:43:35.446276Z     24753 Query     SHOW GRANTS FOR 'user-sample'@'localhost'
2023-02-06T17:43:35.446645Z     24753 Quit
2023-02-06T17:43:35.450194Z     24754 Connect   root@minimal-cluster-haproxy-0.minimal-cluster-haproxy.mysql.svc.cluster.local on  using SSL/TLS
2023-02-06T17:43:35.450413Z     24754 Query     REVOKE ALL ON *.* FROM 'user-sample'@'localhost'
2023-02-06T17:43:35.459298Z     24754 Quit
2023-02-06T17:43:35.461147Z     24755 Connect   root@minimal-cluster-haproxy-0.minimal-cluster-haproxy.mysql.svc.cluster.local on  using SSL/TLS
2023-02-06T17:43:35.461270Z     24755 Query     GRANT SELECT ON *.* TO 'user-sample'@'%'

This bug seems to be caused by a variable resignation (username) in https://github.com/crossplane-contrib/provider-sql/blob/master/pkg/controller/mysql/grant/reconciler.go#L272, Update method

        username := *cr.Spec.ForProvider.User
	dbname := defaultIdentifier(cr.Spec.ForProvider.Database)
	table := defaultIdentifier(cr.Spec.ForProvider.Table)

	privileges := strings.Join(cr.Spec.ForProvider.Privileges.ToStringSlice(), ", ")
	username, host := mysql.SplitUserHost(username) // username is now missing the host part

	query := fmt.Sprintf("REVOKE ALL ON %s.%s FROM %s@%s",
		dbname,
		table,
		mysql.QuoteValue(username),
		mysql.QuoteValue(host),
	)
	if err := c.db.Exec(ctx, xsql.Query{String: query}); err != nil {
		return managed.ExternalUpdate{}, errors.Wrap(err, errRevokeGrant)
	}

	query = createGrantQuery(privileges, dbname, username, table)
	if err := c.db.Exec(ctx, xsql.Query{String: query}); err != nil {
		return managed.ExternalUpdate{}, err
	}

Then in createGrantQuery function

func createGrantQuery(privileges, dbname, username string, table string) string {
	username, host := mysql.SplitUserHost(username) // so here it will be defaulted to '%' instead of the requested host
	result := fmt.Sprintf("GRANT %s ON %s.%s TO %s@%s",
		privileges,
		dbname,
		table,
		mysql.QuoteValue(username),
		mysql.QuoteValue(host),
	)

	return result
}

How can we reproduce it?

Create a user with host: localhost (mind the annotation crossplane.io/external-name: user-sample@localhost)

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: User
metadata:
  annotations:
    crossplane.io/external-name: user-sample@localhost
  creationTimestamp: '2023-02-03T23:57:40Z'
  name: user-sample
spec:
  deletionPolicy: Delete
  forProvider:
    passwordSecretRef:
      key: password
      name: user-sample
      namespace: mysql
    resourceOptions: {}

Then create a grant for that user

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: grant-sample
spec:
  deletionPolicy: Delete
  forProvider:
    privileges:
      - SELECT
    user: user-sample@localhost
    userRef:
      name: user-sample

What environment did it happen in?

crossplane-1.9.1 crossplane/provider-sql:v0.6.0

I would like to work in a pr

[Duologic]

Will this be covered in #136?

[alereca]

It's covered by https://github.com/crossplane-contrib/provider-sql/pull/129, but I can merge the changes in https://github.com/crossplane-contrib/provider-sql/pull/136 and the rest of the feature prs if requested

[Duologic]

I'll try to review #136 soon, them circle back to this.