Creating users/databases with dash

[maartenschalekamp]

Report

Creating users with a dash in the name will completely fail. Trying to wrap the username with backticks `` then fails to update the secrets annotations.

Creating databases with a dash fails, when wrapping with backticks, it works but causes continues out of sync between requested and desired.

More about the problem

All logs and details under steps to reproduce

Steps to reproduce

When creating the following user my-test-username

  users:
  - dbs:
    - my-test-database
    grants:
    - ALL PRIVILEGES
    hosts:
    - '%'
    name: my-test-username
    passwordSecretRef:
      key: my-test-password
      name: database-users
    withGrantOption: false

The operator will fail to create the user

2025-02-13T08:09:49.824Z    INFO    User not created    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "f2e56c27-be58-49b0-90be-f336120a344d", "user": "my-test-username"}
2025-02-13T08:09:49.824Z    INFO    Creating/updating user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "f2e56c27-be58-49b0-90be-f336120a344d", "user": "my-test-username"}
2025-02-13T08:09:49.825Z    ERROR    failed to update user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "f2e56c27-be58-49b0-90be-f336120a344d", "user": {"name":"my-test-username","passwordSecretRef":{"name":"database-users","key":"my-test-password"},"dbs":["my-test-database"],"hosts":["%"],"grants":["ALL PRIVILEGES"]}, "error": "exec: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test-database' at line 1", "errorVerbose": "Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test-database' at line 1\nexec\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/pxc/users.(*Manager).UpsertUser\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/pxc/users/users.go:318\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).reconcileCustomUsers\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users_custom.go:126\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:316\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1700"}
github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).reconcileCustomUsers
    /go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users_custom.go:128
github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile
    /go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:316
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224

If you try to wrap the username with backticks, the error moves to the database name that contains the dash in the name

    users:
        - dbs:
            - my-test-database
          grants:
            - ALL PRIVILEGES
          hosts:
            - "%"
          name: \`my-test-username\`
          passwordSecretRef:
            key: my-test-password
            name: database-users
          withGrantOption: false

Error on database name

2025-02-13T08:17:44.206Z    INFO    User not created    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "afb2db54-cbe8-4ba6-95ec-c72a28784d2c", "user": "\\`my-test-username\\`"}
2025-02-13T08:17:44.206Z    INFO    Creating/updating user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "afb2db54-cbe8-4ba6-95ec-c72a28784d2c", "user": "\\`my-test-username\\`"}
2025-02-13T08:17:44.207Z    ERROR    failed to update user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "afb2db54-cbe8-4ba6-95ec-c72a28784d2c", "user": {"name":"\\`my-test-username\\`","passwordSecretRef":{"name":"database-users","key":"my-test-password"},"dbs":["my-test-database"],"hosts":["%"],"grants":["ALL PRIVILEGES"]}, "error": "exec: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test-database' at line 1", "errorVerbose": "Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test-database' at line 1\nexec\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/pxc/users.(*Manager).UpsertUser\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/pxc/users/users.go:318\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).reconcileCustomUsers\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users_custom.go:126\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:316\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1700"}
github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).reconcileCustomUsers
    /go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users_custom.go:128
github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile
    /go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:316
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224

When wrapping the database name with backtics

users:
  - dbs:
      - "`my-test-database`"
    grants:
      - ALL PRIVILEGES
    hosts:
      - "%"
    name: \`my-test-username\`
    passwordSecretRef:
      key: my-test-password
      name: database-users
    withGrantOption: false

We get an error when it tries to update the secret annotations because of the backticks on the username.

2025-02-13T08:21:59.774Z    INFO    User not created    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "8abde52e-20cb-46cb-b6ee-fde421146f1b", "user": "\\`my-test-username\\`"}
2025-02-13T08:21:59.774Z    INFO    Creating/updating user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "8abde52e-20cb-46cb-b6ee-fde421146f1b", "user": "\\`my-test-username\\`"}
2025-02-13T08:21:59.855Z    ERROR    Reconciler error    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "8abde52e-20cb-46cb-b6ee-fde421146f1b", "error": "reconcile custom users: update user secret: Secret \"database-users\" is invalid: metadata.annotations: Invalid value: \"percona.com/pxc-\\\\`my-test-username\\\\`-hash\": name part must consist of alphanumeric characters, '-', '_' or '.', and must start and end with an alphanumeric character (e.g. 'MyName',  or 'my.name',  or '123-abc', regex used for validation is '([A-Za-z0-9][-A-Za-z0-9_.]*)?[A-Za-z0-9]')", "errorVerbose": "Secret \"database-users\" is invalid: metadata.annotations: Invalid value: \"percona.com/pxc-\\\\`my-test-username\\\\`-hash\": name part must consist of alphanumeric characters, '-', '_' or '.', and must start and end with an alphanumeric character (e.g. 'MyName',  or 'my.name',  or '123-abc', regex used for validation is '([A-Za-z0-9][-A-Za-z0-9_.]*)?[A-Za-z0-9]')\nupdate user secret\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).reconcileCustomUsers\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users_custom.go:135\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:316\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1700\nreconcile custom users\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).Reconcile\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:318\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Reconcile\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:116\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:303\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263\nsigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2\n\t/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1700"}
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).reconcileHandler
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:316
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).processNextWorkItem
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:263
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller[...]).Start.func2.2
    /go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:224

If we then try to remove the dashes from the username

  users:
  - dbs:
    - '`my-test-database`'
    grants:
    - ALL PRIVILEGES
    hosts:
    - '%'
    name: mytestusername
    passwordSecretRef:
      key: my-test-password
      name: database-users
    withGrantOption: false

It was then successful, but it detects a drift between the requested databases and the current databases

2025-02-13T08:27:04.235Z    INFO    DBs changed    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "4095f18b-9afe-4c5e-905d-048f795c8544", "current": {"my-test-database":{}}, "desired": ["`my-test-database`"], "user": "mytestusername"}
2025-02-13T08:27:04.235Z    INFO    Creating/updating user    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "4095f18b-9afe-4c5e-905d-048f795c8544", "user": "mytestusername"}
2025-02-13T08:27:04.301Z    INFO    User created/updated    {"controller": "pxc-controller", "namespace": "databases", "name": "pxc", "reconcileID": "4095f18b-9afe-4c5e-905d-048f795c8544", "user": "mytestusername"}

Versions

1. Kubernetes: v1.31.2+rke2r1
2. Operator: 1.16.1
3. Database: 1.16.1

Anything else?

No response