Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
alereca opened this issue Feb 6, 2023 · 3 comments · May be fixed by #129 or #205
Open

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

alereca opened this issue Feb 6, 2023 · 3 comments · May be fixed by #129 or #205
Labels
bug Something isn't working

Comments

@alereca
Copy link
Contributor

alereca commented Feb 6, 2023 •
edited
Loading

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
@alereca alereca added the bug Something isn't working label Feb 6, 2023
@alereca alereca changed the title Grant (Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT Feb 6, 2023
@alereca alereca changed the title (Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT (Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT; sqlprovider changes requested host to '%' Feb 6, 2023
@alereca alereca changed the title (Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT; sqlprovider changes requested host to '%' (Grant Error) update failed: Error 1410: You are not allowed to create a user with GRANT Feb 6, 2023
@alereca alereca linked a pull request Feb 14, 2023 that will close this issue
Open
2 tasks
@Duologic
Copy link
Member

Duologic commented Mar 9, 2023

Will this be covered in #136?

@alereca
Copy link
Contributor Author

alereca commented Mar 9, 2023

It's covered by #129, but I can merge the changes in #136 and the rest of the feature prs if requested

@Duologic
Copy link
Member

Duologic commented Mar 9, 2023

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

@Duologic Duologic linked a pull request Nov 29, 2024 that will close this issue
Open
@Duologic Duologic moved this to Backlog in Provider SQL Nov 29, 2024
@Duologic Duologic moved this from Backlog to In progress in Provider SQL Nov 29, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
Provider SQL
Status: In progress
Milestone
No milestone
2 participants
@Duologic @alereca