GRANTs Failing for Several Grant Types #79

MattMencel · 2022-04-04T03:53:44Z

What happened?

I'm attempting to create several GRANTs in MySQL. A few succeed, but several fail with cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES. It seems to be related to the specific GRANT types I am trying to create.

PROCESS, REPLICATION CLIENT, REPLICATION SLAVE in particular.

I can create the failing GRANTs from the MySQL CLI without any issue, using the same username/password that Crossplane is using.

Other GRANT objects with different sets of GRANT types work correctly and create the grants. e.g. "SELECT","INSERT","UPDATE", "DELETE","EXECUTE","CREATE TEMPORARY TABLES"

NAME                                              READY   SYNCED   AGE    ROLE                DATABASE               PRIVILEGES
grant.mysql.sql.crossplane.io/datadog-perf        True    True     2d6h   datadog             performance_schema.*   ["SELECT"]
grant.mysql.sql.crossplane.io/datadog-repl        False   False    2d6h   datadog             *.*                    ["REPLICATION CLIENT","PROCE
SS"]
grant.mysql.sql.crossplane.io/somegrantfoo     True    True     2d7h   someuser     *.*                    ["SELECT","INSERT","UPDATE",
"DELETE","EXECUTE","CREATE TEMPORARY TABLES"]

How can we reproduce it?

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: datadog-repl
spec:
  providerConfigRef:
    name: foo
  forProvider:
    privileges:
      - REPLICATION CLIENT
      - PROCESS
    userRef:
      name: datadog
    database: "*.*"

What environment did it happen in?

Crossplane version:

Image: crossplane/crossplane:v1.7.0
Image: crossplane/provider-sql-controller:v0.4.1

Cloud: Azure MySQL Flexible Server v8.0
Kubernetes version:

> kubectl version
Client Version: version.Info{Major:"1", Minor:"23", GitVersion:"v1.23.5", GitCommit:"c285e781331a3785a7f436042c65c5641ce8a9e9", GitTreeState:"clean", BuildDate:"2022-03-16T15:51:05Z", GoVersion:"go1.17.8", Compiler:"gc", Platform:"darwin/amd64"}
Server Version: version.Info{Major:"1", Minor:"22", GitVersion:"v1.22.6", GitCommit:"5963a7a5570a1b8c977c31ced503cb20f0ef6e8c", GitTreeState:"clean", BuildDate:"2022-03-10T07:59:42Z", GoVersion:"go1.16.12", Compiler:"gc", Platform:"linux/amd64"}

Kubernetes distribution: Azure AKS
OS (e.g. from /etc/os-release)

> # cat /etc/os-release
NAME="Ubuntu"
VERSION="18.04.6 LTS (Bionic Beaver)"

Kernel (e.g. uname -a)

 uname -a
Linux aks-lab90-23705758-vmss000001 5.4.0-1069-azure #72~18.04.1-Ubuntu SMP Mon Feb 7 11:12:24 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

Debug Log

provider-sql 2022-04-04T03:21:03.853Z	DEBUG	provider-sql	Cannot create external resource	{"controller": "managed/grant.mysql.sql.crossplane.io", "request": "/datadog-repl", "uid": "1a84a73b-e5d0-4958-92b1-07d82e0992b1", "version": "185563119", "external-name": "datadog-repl", "error": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES", "errorVerbose": "Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES\ncannot create grant\ngithub.com/crossplane-contrib/provider-sql/pkg/controller/mysql/grant.(*external).Create\n\t/home/runner/work/provider-sql/provider-sql/pkg/controller/mysql/grant/reconciler.go:249\ngithub.com/crossplane/crossplane-runtime/pkg/reconciler/managed.(*Reconciler).Reconcile\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/github.com/crossplane/crossplane-runtime@v0.13.0/pkg/reconciler/managed/reconciler.go:670\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:293\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:248\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func1.1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.UntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:99\nruntime.goexit\n\t/opt/hostedtoolcache/go/1.17.7/x64/src/runtime/asm_amd64.s:1581"}

The text was updated successfully, but these errors were encountered:

Duologic · 2022-04-05T08:22:16Z

Can you try with database: "*" ?

The code already suffixes .* to the db name: https://github.com/crossplane-contrib/provider-sql/blob/master/pkg/controller/mysql/grant/reconciler.go#L291

MattMencel · 2022-04-05T16:57:11Z

Interesting... I get the same error.

provider-sql 2022-04-05T15:40:59.878Z	DEBUG	provider-sql	Cannot create external resource	{"controller": "managed/grant.mysql.sql.crossplane.io", "request": "/datadog-repl", "uid": "1a84a73b-e5d0-4958-92b1-07d82e0992b1", "version": "187649454", "external-name": "datadog-repl", "error": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES", "errorVerbose": "Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES\ncannot create grant\ngithub.com/crossplane-contrib/provider-sql/pkg/controller/mysql/grant.(*external).Create\n\t/home/runner/work/provider-sql/provider-sql/pkg/controller/mysql/grant/reconciler.go:249\ngithub.com/crossplane/crossplane-runtime/pkg/reconciler/managed.(*Reconciler).Reconcile\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/github.com/crossplane/crossplane-runtime@v0.13.0/pkg/reconciler/managed/reconciler.go:670\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:293\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:248\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func1.1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.UntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:99\nruntime.goexit\n\t/opt/hostedtoolcache/go/1.17.7/x64/src/runtime/asm_amd64.s:1581"}
provider-sql 2022-04-05T15:40:59.879Z	DEBUG	controller-runtime.manager.events	Warning	{"object": {"kind":"Grant","name":"datadog-repl","uid":"1a84a73b-e5d0-4958-92b1-07d82e0992b1","apiVersion":"mysql.sql.crossplane.io/v1alpha1","resourceVersion":"187649454"}, "reason": "CannotCreateExternalResource", "message": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES"}

NAME                READY   SYNCED   AGE     ROLE                DATABASE             PRIVILEGES
datadog-perf        True    True     3d18h   datadog             performance_schema   ["SELECT"]
datadog-repl        False   False    3d18h   datadog             *                    ["REPLICATION CLIENT","PROCESS"]
somegrantfoo     True   True     3d19h   somegrantfoo     *                    ["SELECT","INSERT","UPDATE","DELETE","EXECUTE","CREATE TEMP
ORARY TABLES"]

mysql> show GRANTS for 'datadog';
+---------------------------------------------------------+
| Grants for datadog@%                                    |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `datadog`@`%`                     |
| GRANT SELECT ON `performance_schema`.* TO `datadog`@`%` |
+---------------------------------------------------------+
2 rows in set (0.06 sec)

alexbowers · 2022-04-08T16:08:05Z

I get the same problem also.

I can see for me listed in the status conditions the following:

      message: >-
        create failed: cannot create grant: Error 1221: Incorrect usage of DB
        GRANT and GLOBAL PRIVILEGES
      reason: ReconcileError
      status: 'False'
      type: Synced

alexbowers · 2022-04-08T16:12:43Z

I believe the problem is because * (for database) is being quoted, so it isn't being treated as a valid wildcard.

alexbowers · 2022-04-08T16:14:52Z

I have confirmed my suspicion with the following test:

GRANT PROCESS ON `*`.* TO 'datadog'@'%';

results in the error:

Error in query (1221): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

However,

GRANT PROCESS ON *.* TO 'datadog'@'%';

Does not.

displague · 2022-04-13T10:52:44Z

@alexbowers I'm curious how this would be handled:

GRANT PROCESS ON `*`.`*` TO 'datadog'@'%';

(I believe single quotes (') would work, rather than backticks, for MySQL, but it has been a while.)
golang/go#18478 is relevant.

Duologic · 2022-05-20T21:35:33Z

Can someone verify whether this is fixed in v0.5.0 with #83 ?

MattMencel · 2022-05-23T13:44:46Z

@Duologic I just upgraded and get this.

2022-05-23T13:43:10.216Z	DEBUG	provider-sql	Reconciling	{"controller": "managed/grant.mysql.sql.crossplane.io", "request": "/datadog-repl"}
2022-05-23T13:43:10.293Z	DEBUG	provider-sql	Cannot create external resource	{"controller": "managed/grant.mysql.sql.crossplane.io", "request": "/datadog-repl", "uid": "154dd68d-0b64-4505-bf3e-6b2383a60de6", "version": "254214149", "external-name": "datadog-repl", "error": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES", "errorVerbose": "Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES\ncannot create grant\ngithub.com/crossplane-contrib/provider-sql/pkg/controller/mysql/grant.(*external).Create\n\t/home/runner/work/provider-sql/provider-sql/pkg/controller/mysql/grant/reconciler.go:254\ngithub.com/crossplane/crossplane-runtime/pkg/reconciler/managed.(*Reconciler).Reconcile\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/github.com/crossplane/crossplane-runtime@v0.13.0/pkg/reconciler/managed/reconciler.go:670\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:293\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:248\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func1.1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/sigs.k8s.io/controller-runtime@v0.8.0/pkg/internal/controller/controller.go:211\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil.func1\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:155\nk8s.io/apimachinery/pkg/util/wait.BackoffUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:156\nk8s.io/apimachinery/pkg/util/wait.JitterUntil\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:133\nk8s.io/apimachinery/pkg/util/wait.JitterUntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:185\nk8s.io/apimachinery/pkg/util/wait.UntilWithContext\n\t/home/runner/work/provider-sql/provider-sql/.work/pkg/pkg/mod/k8s.io/apimachinery@v0.20.1/pkg/util/wait/wait.go:99\nruntime.goexit\n\t/opt/hostedtoolcache/go/1.17.10/x64/src/runtime/asm_amd64.s:1581"}
2022-05-23T13:43:10.294Z	DEBUG	controller-runtime.manager.events	Warning	{"object": {"kind":"Grant","name":"datadog-repl","uid":"154dd68d-0b64-4505-bf3e-6b2383a60de6","apiVersion":"mysql.sql.crossplane.io/v1alpha1","resourceVersion":"254214149"}, "reason": "CannotCreateExternalResource", "message": "cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES"}

cskinfill · 2022-06-21T17:41:59Z

I'm hitting this same problem trying to setup a mysql user for the prometheus-mysql exporter. We're evaluating using the provider and hoping to find some way to address this problem.

JaroVojtek · 2022-07-11T12:20:52Z

Hello, I am getting the same error while trying to grant PROCESS privilege to my sql user

apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: grant-process
spec:
  providerConfigRef:
    name: mysql-provider
  forProvider:
    privileges:
      - PROCESS
    userRef:
      name: docker
    database: '*'

Events:
  Type     Reason                        Age                      From                                   Message
  ----     ------                        ----                     ----                                   -------
  Warning  CannotCreateExternalResource  <invalid> (x12 over 2s)  managed/grant.mysql.sql.crossplane.io  cannot create grant: Error 1221: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Is PR#97 going to fix that ? @augustomelo Was it tested ?

augustomelo · 2022-07-11T12:57:17Z

Hello @JaroVojtek yes it will fix the issue (since the main problem was that passing "" the script would escape it, and since the documentations states that de database is not mandatory, the change also makes if you don't pass the database it will use "") unfortunately I was not able to set up the environment to test it, but running the make reviewable was ok, also since I am a first-time contributor I need someone to running workflows

JaroVojtek · 2022-10-19T15:04:10Z

Hello @augustomelo Was this already fixed or what is the status here please ? Thank you

augustomelo · 2022-10-19T15:12:39Z

Hey @JaroVojtek you can check it here #104, I am waiting for the #105 to be merged so the CI/CD can run the integration test (I ran it locally, and no error appeared)

guilledipa · 2022-11-02T06:42:42Z

Hey folks, I'm still having issues:

guillermodp@cloudshell:~ (guillermodp-anthos)$ kubectl get grant.mysql.sql.crossplane.io 
NAME                   READY   SYNCED   AGE    ROLE             DATABASE   PRIVILEGES
mysql-exporter-grant   False   False    3m4s   mysql-exporter   *          ["SELECT","PROCESS","REPLICATION CLIENT"]

The error that I'm getting is:

    - lastTransitionTime: "2022-11-02T22:35:54Z"
      message: 'create failed: cannot create grant: Error 1221: Incorrect usage of
        DB GRANT and GLOBAL PRIVILEGES'

For context,

This is the Grants object:

---
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: Grant
metadata:
  name: mysql-exporter-grant
spec:
  providerConfigRef:
    name: cluster1-percona
  forProvider:
    privileges:
      - SELECT
      - PROCESS
      - REPLICATION CLIENT
    database: '*'
    userRef:
      name: mysql-exporter

The mysql-exporter User was created successfully:

---
apiVersion: mysql.sql.crossplane.io/v1alpha1
kind: User
metadata:
  name: mysql-exporter
spec:
  providerConfigRef:
    name: cluster1-percona
  forProvider:
    passwordSecretRef:
      name: mysql-exporter-secret
      namespace: percona
      key: password
    resourceOptions:
      maxUserConnections: 3
  writeConnectionSecretToRef:
    name: connection-secret
    namespace: percona

$ kubectl get user.mysql.sql.crossplane.io
NAME             READY   SYNCED   AGE
mysql-exporter   True    True     30s

mysql> SHOW GRANTS FOR 'mysql-exporter'@'%';
+--------------------------------------------+
| Grants for mysql-exporter@%                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `mysql-exporter`@`%` |
+--------------------------------------------+

@augustomelo FYI

JaroVojtek · 2023-05-22T15:39:56Z

@guilledipa you have to youse database: '*' in your manifest instead

MattMencel added the bug Something isn't working label Apr 4, 2022

alexbowers mentioned this issue Apr 8, 2022

Handle * dbname without throwing error #84

Closed

2 tasks

augustomelo mentioned this issue Jun 9, 2022

Fixed database by passing a default value #97

Merged

2 tasks

Duologic closed this as completed in #97 Nov 21, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

GRANTs Failing for Several Grant Types #79

GRANTs Failing for Several Grant Types #79

MattMencel commented Apr 4, 2022

Duologic commented Apr 5, 2022

MattMencel commented Apr 5, 2022 •

edited

Loading

alexbowers commented Apr 8, 2022

alexbowers commented Apr 8, 2022

alexbowers commented Apr 8, 2022

displague commented Apr 13, 2022 •

edited

Loading

Duologic commented May 20, 2022 •

edited

Loading

MattMencel commented May 23, 2022

cskinfill commented Jun 21, 2022

JaroVojtek commented Jul 11, 2022

augustomelo commented Jul 11, 2022

JaroVojtek commented Oct 19, 2022

augustomelo commented Oct 19, 2022

guilledipa commented Nov 2, 2022 •

edited

Loading

JaroVojtek commented May 22, 2023

GRANTs Failing for Several Grant Types #79

GRANTs Failing for Several Grant Types #79

Comments

MattMencel commented Apr 4, 2022

What happened?

How can we reproduce it?

What environment did it happen in?

Debug Log

Duologic commented Apr 5, 2022

MattMencel commented Apr 5, 2022 • edited Loading

alexbowers commented Apr 8, 2022

alexbowers commented Apr 8, 2022

alexbowers commented Apr 8, 2022

displague commented Apr 13, 2022 • edited Loading

Duologic commented May 20, 2022 • edited Loading

MattMencel commented May 23, 2022

cskinfill commented Jun 21, 2022

JaroVojtek commented Jul 11, 2022

augustomelo commented Jul 11, 2022

JaroVojtek commented Oct 19, 2022

augustomelo commented Oct 19, 2022

guilledipa commented Nov 2, 2022 • edited Loading

JaroVojtek commented May 22, 2023

MattMencel commented Apr 5, 2022 •

edited

Loading

displague commented Apr 13, 2022 •

edited

Loading

Duologic commented May 20, 2022 •

edited

Loading

guilledipa commented Nov 2, 2022 •

edited

Loading