Downloads

Blog

Manage MySQL Users with Kubernetes

May 20, 2021

Author

Share this Post:

Manage MySQL Users with Kubernetes Quite a common request that we receive from the community and customers is to provide a way to manage database users with Operators – both MongoDB and MySQL. Even though we see it as an interesting task, our Operators are mainly a tool to simplify the deployment and management of our software on Kubernetes. Our goal is to provide the database cluster which is ready to host mission-critical applications and deployed with the best practices.

Why Manage Users with Operators?

There are few use cases:

Simplify the CICD pipeline. It is simpler to apply a single manifest than running multiple commands to create the user after the DB is ready.

Give control over DB users to developers or applications, but without providing direct access to the database.

There is an opinion that Kubernetes will transition from container orchestrator to a control plane to manage everything. For some companies, it is a strategy.

We want to have the functionality to provision users with Operators, but it does not seem to be the right solution to do it separately for each Operator. It looks like it can be unified.

What if we take it to another level and create a way to provision users on any database through the Kubernetes control plane? The user has created the MySQL instance on a public cloud through the control plane, so why not create the DB user the same way?

Crossplane.io – A Kubernetes addon that enables users to declaratively describe and provision the infrastructure through the k8s control plane. By design, it is extendable through “providers”. One of them – provider-sql – enables the functionality to manage MySQL and PostgreSQL users (and even databases) through CRDs. Let’s see how to make it work with Percona XtraDB Cluster Operator.

Action

Prerequisites:

Kubernetes cluster

Percona XtraDB Cluster deployed with Operator (see the docs here)

The goal is to create a Custom Resource (CR) object through Kubernetes API to trigger crossplane.io to create the user on the PXC cluster. As a summary it will look like this:

Manage MySQL Users with Kubernetes

A user creates the CR with the desired user and grants

Crossplane detects it

provider-sql (Crossplane provider) is configured through a Secret object which has the PXC endpoint and root credentials

provider-sql connects to PXC and creates the user

I have placed all the files for this blog post into the public github repository along with the condensed runbook which just lists all the steps. You can find it all here.

Install crossplane

The simplest way is to do it through helm:

kubectl create namespace crossplane<br>helm repo add crossplane-alpha https://charts.crossplane.io/alpha<br>helm install crossplane --namespace crossplane crossplane-alpha/crossplane

1	kubectl create namespace crossplane<br>helm repo add crossplane-alpha https://charts.crossplane.io/alpha<br>helm install crossplane --namespace crossplane crossplane-alpha/crossplane

Other installation methods can be found in crossplane.io documentation.

Install provider-sql

Provider in Crossplane is a concept similar to Terraform’s provider. Everything in crossplane is done through Kubernetes API and that includes the installation of Providers:

$ cat crossplane-provider-sql.yaml<br>apiVersion: pkg.crossplane.io/v1beta1<br>kind: Provider<br>metadata:<br>  name: provider-sql<br>spec:<br>  package: "crossplane/provider-sql:master"<br><br>$ kubectl apply -f crossplane-provider-sql.yaml

1	$ cat crossplane-provider-sql.yaml<br>apiVersion: pkg.crossplane.io/v1beta1<br>kind: Provider<br>metadata:<br> name: provider-sql<br>spec:<br> package: "crossplane/provider-sql:master"<br><br>$ kubectl apply -f crossplane-provider-sql.yaml

This is going to install Custom Resource Definitions for provider-sql which is going to be used to manage MySQL users on our PXC cluster. Full docs for provider-sql can be found here, but they are not very detailed.

Almost There

Everything is installed and needs a last configuration touch.

Create a secret which provider-sql is going to use to connect to the MySQL database. I have cluster1 Percona XtraDB cluster deployed in a pxc namespace and the corresponding secret will look like this:

$ cat crossplane-secret.yaml<br>apiVersion: v1<br>kind: Secret<br>metadata:<br>  name: crossplane-secret<br>  namespace: pxc<br>stringData:<br>  username: root<br>  password: <root password><br>  endpoint: cluster1-haproxy.pxc.svc.cluster.local<br>  port: "3306"<br><br>type: Opaque

1	$ cat crossplane-secret.yaml<br>apiVersion: v1<br>kind: Secret<br>metadata:<br> name: crossplane-secret<br> namespace: pxc<br>stringData:<br> username: root<br> password: <root password><br> endpoint: cluster1-haproxy.pxc.svc.cluster.local<br> port: "3306"<br><br>type: Opaque

You can get the root password from the secret which is created when PXC is deployed through the Operator. Quick way to get it is like this:

$ kubectl get secret -n pxc my-cluster-secrets -o yaml | awk '/root:/ {print $2}' | base64 --decode && echo<br><root password>

1	$ kubectl get secret -n pxc my-cluster-secrets -o yaml \| awk '/root:/ {print $2}' \| base64 --decode && echo<br><root password>

Crossplane will use the endpoint and port as a MySQL connection string, and username and password to connect to it. Configure provider-sql to get the information from the secret:

$ cat crossplane-mysql-config.yaml<br>apiVersion: mysql.sql.crossplane.io/v1alpha1<br>kind: ProviderConfig<br>metadata:<br>  name: cluster1-pxc<br>spec:<br>  credentials:<br>    source: MySQLConnectionSecret<br>    connectionSecretRef:<br>      namespace: pxc<br>      name: crossplane-secret<br><br>$ kubectl apply -f crossplane-mysql-config.yaml

$ cat crossplane-mysql-config.yaml apiVersion: mysql.sql.crossplane.io/v1alpha1 kind: ProviderConfig metadata: name: cluster1-pxc spec: credentials: source: MySQLConnectionSecret connectionSecretRef: namespace: pxc name: crossplane-secret $ kubectl apply -f crossplane-mysql-config.yaml

Let’s verify that configuration is in place:

$ kubectl get providerconfig.mysql.sql.crossplane.io<br>NAME           AGE<br>cluster1-pxc   14s

1	$ kubectl get providerconfig.mysql.sql.crossplane.io<br>NAME AGE<br>cluster1-pxc 14s

Do It

All set. Crossplane can now connect to the database and create the users. From the Kubernetes and user perspective, it is just creating the custom resources through the control plane API.

Database Creation

$ cat crossplane-db.yaml<br>apiVersion: mysql.sql.crossplane.io/v1alpha1<br>kind: Database<br>metadata:<br>  name: my-db<br>spec:<br>  providerConfigRef:<br>    name: cluster1-pxc<br><br>$ kubectl apply -f crossplane-db.yaml<br>database.mysql.sql.crossplane.io/my-db created<br><br>$ kubectl get database.mysql.sql.crossplane.io<br>NAME    READY   SYNCED   AGE<br>my-db   True    True     14s

$ cat crossplane-db.yaml apiVersion: mysql.sql.crossplane.io/v1alpha1 kind: Database metadata: name: my-db spec: providerConfigRef: name: cluster1-pxc $ kubectl apply -f crossplane-db.yaml database.mysql.sql.crossplane.io/my-db created $ kubectl get database.mysql.sql.crossplane.io NAME READY SYNCED AGE my-db True True 14s

This created the database on my Percona XtraDB Cluster:

$ mysql -u root -p -h cluster1-haproxy<br>Server version: 8.0.22-13.1 Percona XtraDB Cluster (GPL), Release rel13, Revision a48e6d5, WSREP version 26.4.3<br>...<br>mysql> show databases like 'my-db';<br>+------------------+<br>| Database (my-db) |<br>+------------------+<br>| my-db            |<br>+------------------+<br>1 row in set (0.01 sec)

$ mysql -u root -p -h cluster1-haproxy Server version: 8.0.22-13.1 Percona XtraDB Cluster (GPL), Release rel13, Revision a48e6d5, WSREP version 26.4.3 ... mysql> show databases like 'my-db'; +------------------+ | Database (my-db) | +------------------+ | my-db | +------------------+ 1 row in set (0.01 sec)

The DB can be deleted through Kubernetes API as well – just delete the corresponding database.mysql.sql.crossplane.io object.

User Creation

The user needs a password. Password should never be stored as plain text, so let’s put it into a Secret:

$ cat user-secret.yaml<br>apiVersion: v1<br>kind: Secret<br>metadata:<br>  name: my-user-secret<br>stringData:<br>  password: mysuperpass<br>type: Opaque

1	$ cat user-secret.yaml<br>apiVersion: v1<br>kind: Secret<br>metadata:<br> name: my-user-secret<br>stringData:<br> password: mysuperpass<br>type: Opaque

We can create the user now:

$ cat crossplane-user.yaml<br>apiVersion: mysql.sql.crossplane.io/v1alpha1<br>kind: User<br>metadata:<br>  name: my-user<br>spec:<br>  providerConfigRef:<br>    name: cluster1-pxc<br>  forProvider:<br>    passwordSecretRef:<br>      name: my-user-secret<br>      namespace: default<br>      key: password<br>  writeConnectionSecretToRef:<br>    name: connection-secret<br>    namespace: default<br><br>$ kubectl apply -f crossplane-user.yaml<br>user.mysql.sql.crossplane.io/my-user created<br><br>$ kubectl get user.mysql.sql.crossplane.io<br>NAME      READY   SYNCED   AGE<br>my-user   True    True     11s

$ cat crossplane-user.yaml apiVersion: mysql.sql.crossplane.io/v1alpha1 kind: User metadata: name: my-user spec: providerConfigRef: name: cluster1-pxc forProvider: passwordSecretRef: name: my-user-secret namespace: default key: password writeConnectionSecretToRef: name: connection-secret namespace: default $ kubectl apply -f crossplane-user.yaml user.mysql.sql.crossplane.io/my-user created $ kubectl get user.mysql.sql.crossplane.io NAME READY SYNCED AGE my-user True True 11s

And add some grants:

$ cat crossplane-grants.yaml<br>apiVersion: mysql.sql.crossplane.io/v1alpha1<br>kind: Grant<br>metadata:<br>  name: my-grant<br>spec:<br>  providerConfigRef:<br>    name: cluster1-pxc<br>  forProvider:<br>    privileges:<br>      - DROP<br>      - CREATE ROUTINE<br>      - EVENT<br>    userRef:<br>      name: my-user<br>    databaseRef:<br>      name: my-db<br><br>$ kubectl apply -f crossplane-grants.yaml<br>grant.mysql.sql.crossplane.io/my-grant created<br><br>$ kubectl get grant.mysql.sql.crossplane.io<br>NAME       READY   SYNCED   AGE   ROLE      DATABASE   PRIVILEGES<br>my-grant   True    True     7s    my-user   my-db      [DROP CREATE ROUTINE EVENT]

$ cat crossplane-grants.yaml apiVersion: mysql.sql.crossplane.io/v1alpha1 kind: Grant metadata: name: my-grant spec: providerConfigRef: name: cluster1-pxc forProvider: privileges: - DROP - CREATE ROUTINE - EVENT userRef: name: my-user databaseRef: name: my-db $ kubectl apply -f crossplane-grants.yaml grant.mysql.sql.crossplane.io/my-grant created $ kubectl get grant.mysql.sql.crossplane.io NAME READY SYNCED AGE ROLE DATABASE PRIVILEGES my-grant True True 7s my-user my-db [DROP CREATE ROUTINE EVENT]

Verify that the user is there:

mysql> show grants for 'my-user';<br>+-----------------------------------------------------------------+<br>| Grants for my-user@%                                            |<br>+-----------------------------------------------------------------+<br>| GRANT USAGE ON *.* TO `my-user`@`%`                             |<br>| GRANT DROP, CREATE ROUTINE, EVENT ON `my-db`.* TO `my-user`@`%` |<br>+-----------------------------------------------------------------+<br>2 rows in set (0.00 sec)

mysql> show grants for 'my-user'; +-----------------------------------------------------------------+ | Grants for my-user@% | +-----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `my-user`@`%` | | GRANT DROP, CREATE ROUTINE, EVENT ON `my-db`.* TO `my-user`@`%` | +-----------------------------------------------------------------+ 2 rows in set (0.00 sec)

Keeping the State

Kubernetes is declarative and its controllers always do their best to keep the declared configuration and real state in sync. It means that if you are going to delete the user manually from the database (not through Kubernetes API), on the next pass of a reconcile loop Crossplane will sync the state and recreate the user and grants again.

Conclusion

Some functionality in one database engine differs a lot from the other, but sometimes there is a pattern. User creation is one of these patterns that can be unified across multiple database engines. Luckily Cloud Native Foundation landscape is huge and consists of a lot of building blocks which when used together can deliver wonderful infrastructures or applications.

This blog post shows that the community might have already found a better solution to the problem and re-inventing it might be a waste of time.

Extending crossplane.io providers to support other database engines (like MongoDB) is a challenge but can be solved. We are drafting a proposal and will work with our teams and community to deliver this.