Skip to main content

Just-in-time MySQL access

Overview

This tutorial will deploy an example cluster to highlight Otterize's MySQL capabilities. Within that cluster is a client service that hits an endpoint on a server, which then connects to a database. The server runs two different database operations:

  1. An INSERT operation to append a table within the database
  2. A SELECT operation to validate the updates.

The server needs appropriate permissions to access the database. You could use one admin user for all services, which is insecure and is the cause for many security breaches. With Otterize, you can specify required access, and have Otterize create users and perform correctly scoped SQL GRANTs just in time, as the service spins up and down.

In this tutorial, we will:

  • Optionally, spin up a MySQL database instance on AWS, based on Amazon RDS for MySQL, or in your Kubernetes cluster, based on the official MySQL Docker image. Alternatively, you could use any MySQL server of your choice.
  • Deploy an example cluster
  • Deploy Otterize in our cluster and give it access to our database instance
  • Declare a ClientIntents resource for the server, specifying required access
  • See that the required access has been granted

Prerequisites

1. Minikube Cluster

Prepare a Kubernetes cluster with Minikube

For this tutorial you'll need a local Kubernetes cluster. Having a cluster with a CNI that supports NetworkPolicies isn't required for this tutorial, but is recommended so that your cluster works with other tutorials.

If you don't have the Minikube CLI, first install it.

Then start your Minikube cluster with Calico, in order to enforce network policies.

minikube start --cpus=4 --memory 4096 --disk-size 32g --cni=calico

2. Deploy Otterize

To deploy Otterize, head over to Otterize Cloud and associate a Kubernetes cluster on the Integrations page, and follow the instructions. If you already have a Kubernetes cluster connected, skip this step.

3. Deploy a MySQL database instance

Already have a MySQL database instance? Skip to the tutorial.

Deploy a MySQL database instance, based on Amazon RDS for MySQL

Follow the installation instructions on the AWS RDS documentation.

  • You may use the Free tier template for this tutorial.
  • Under "Settings", choose "Auto generate password". Make sure you save the generated password after the instance is created.
  • Under "Connectivity", enable public access to allow access from your Kubernetes cluster. Otterize will require that access to manage credentials for you. Additionally, make sure you choose a security group that allows inbound access from the internet.
  • Deploy a MySQL database instance, based on the official MySQL Docker image

    To deploy a local MySQL database instance, you can use the official MySQL Docker image. Run the following command to deploy a MySQL instance with the root password set to password:

    kubectl create namespace otterize-tutorial-mysql
    kubectl apply -n otterize-tutorial-mysql -f https://docs.otterize.com/code-examples/mysql/database.yaml

    Use the following values as your MySQL host and password:

    export MYSQLHOST=mysql.otterize-tutorial-mysql.svc.cluster.local
    export MYSQLUSER=root
    export MYSQLPASSWORD=password

    Tutorial

    Setup MySQL database and table for the tutorial

    Throughout this tutorial, we will refer to your MySQL host & credentials via environment variables, so make sure to set them up:

    export MYSQLHOST=<YOURMYSQLHOST> # For RDS, this is the endpoint; for the official MySQL docker image, this is `mysql.otterize-tutorial-mysql.svc.cluster.local`
    export MYSQLUSER=<YOURUSER> # For RDS, this is the username set during the RDS instance deployment, typically 'admin'; for the official MySQL docker image, this is `root`
    export MYSQLPASSWORD=<YOURPASSWORD> # For RDS, this is the password set during the RDS instance deployment; for the official MySQL docker image, this is `password`

    Next, start a MySQL client to connect to your MySQL instance, and create a database named otterize_tutorial and a table named example in your MySQL instance. Our tutorial server will use this database and table to perform INSERT and SELECT operations.

    kubectl create namespace otterize-tutorial-mysql
    kubectl run -n otterize-tutorial-mysql -it --rm --image=mysql:latest --restart=Never mysql-client -- mysql -h $MYSQLHOST -u $MYSQLUSER -p$MYSQLPASSWORD \
    -e 'CREATE DATABASE IF NOT EXISTS otterize_example;

    USE otterize_example;

    CREATE TABLE IF NOT EXISTS example
    (
    file_name VARCHAR(255),
    upload_time BIGINT
    );

    exit;
    '

    Deploy tutorial services and request database credentials

    Next, set up the namespace used for our tutorial and deploy the client & server services in it:

    kubectl create namespace otterize-tutorial-mysql
    kubectl apply -n otterize-tutorial-mysql -f https://docs.otterize.com/code-examples/mysql/client-server.yaml
    kubectl patch deployment -n otterize-tutorial-mysql server --type='json' -p="[{\"op\": \"replace\", \"path\": \"/spec/template/spec/containers/0/env/0/value\", \"value\": \"$MYSQLHOST\"}]"
    Expand to see the deployment YAML
    apiVersion: apps/v1
    kind: Deployment
    metadata:
    name: server
    spec:
    replicas: 1
    selector:
    matchLabels:
    app: server
    template:
    metadata:
    annotations:
    credentials-operator.otterize.com/user-password-secret-name: server-creds
    labels:
    app: server
    spec:
    serviceAccountName: server
    containers:
    - name: server
    imagePullPolicy: Always
    image: 'otterize/mysql-tutorial-server'
    ports:
    - containerPort: 80
    env:
    - name: DB_HOST
    value: database
    - name: DB_NAME
    value: otterize_example
    - name: DB_PORT
    value: "3306"
    - name: DB_SERVER_USER
    valueFrom:
    secretKeyRef:
    name: server-creds
    key: username
    - name: DB_SERVER_PASSWORD
    valueFrom:
    secretKeyRef:
    name: server-creds
    key: password
    ---
    apiVersion: v1
    kind: Service
    metadata:
    name: server
    spec:
    type: ClusterIP
    selector:
    app: server
    ports:
    - name: http
    port: 80
    targetPort: 80
    ---
    apiVersion: v1
    kind: ServiceAccount
    metadata:
    name: server
    ---
    apiVersion: apps/v1
    kind: Deployment
    metadata:
    name: client
    spec:
    replicas: 1
    selector:
    matchLabels:
    app: client
    template:
    metadata:
    labels:
    app: client
    spec:
    containers:
    - name: client
    imagePullPolicy: Always
    image: 'otterize/mysql-tutorial-client'
    ports:
    - containerPort: 80

    Our server's Deployment spec specifies an annotation on its Pod, which requests that the Otterize operator provision a username and password for it:

      template:
    metadata:
    annotations:
    credentials-operator.otterize.com/user-password-secret-name: server-creds

    This specifies that the secret server-creds will be populated with keys containing the username and password used by this pod to connect to the database. The secret will only be created by the Otterize operator after it is integrated with your database by applying a MySQLServerConfig resources.

    View logs for the server

    After the client, server, and database are up and running, we can see that the server does not have the appropriate access to the database by inspecting the logs with the following command.

    kubectl logs -f -n otterize-tutorial-mysql deploy/server

    Example log:

    Unable to perform INSERT operation
    Unable to perform SELECT operation

    Deploy a MySQLServerConfig to allow Otterize DB access

    Let's apply a MySQLServerConfig so Otterize will know how to access our database instance.

    First, create a Kuberentes secret containing the database credentials:

    kubectl create secret generic mysql-tutorial-db-credentials -n otterize-tutorial-mysql --from-literal=username=$MYSQLUSER --from-literal=password=$MYSQLPASSWORD

    Next, apply the MySQLServerConfig to the cluster, and patch it with your DB instance address:

    kubectl apply -n otterize-tutorial-mysql -f https://docs.otterize.com/code-examples/mysql/mysqlserverconfig.yaml
    kubectl patch mysqlserverconfig -n otterize-tutorial-mysql mysql-tutorial-db --type='json' -p="[{\"op\": \"replace\", \"path\": \"/spec/address\", \"value\": \"$MYSQLHOST\"}]"

    This MySQLServerConfig tells Otterize how to access a database instance named mysql-tutorial-db, meaning that when intents are applied requesting access permissions to mysql-tutorial-db, the Otterize operator will be able to configure them:

    apiVersion: k8s.otterize.com/v1alpha3
    kind: MySQLServerConfig
    metadata:
    name: mysql-tutorial-db
    spec:
    address: mysql.otterize-tutorial-mysql.svc.cluster.local:3306 # Your MySQL server address
    credentials:
    secretRef:
    name: mysql-tutorial-db-credentials

    In this tutorial, we use the admin user to grant Otterize permissions to create users and grant them access to the database. In a production environment, it is recommended to create a dedicated user for Otterize, and grant it the necessary permissions to create and manage other users.

    Define your ClientIntents

    ClientIntents are Otterize’s way of defining access through unique relationships, which lead to perfectly scoped access. In this example, we provide our server workload the ability to insert and select records to allow it to access the database.

    Below is our intents.yaml file. As you can see, it is scoped to our database named otterize_tutorial and our example table. We also have limited the access to just SELECT and INSERT operations. We could add more databases, tables, or operations if our service required more access.

    Specifying the table and operations is optional. If you don't specify the table, access will be granted to all tables in the specified database. If you don't specify the operations, all operations will be allowed.

    apiVersion: k8s.otterize.com/v1alpha3
    kind: ClientIntents
    metadata:
    name: client-intents-for-server
    spec:
    service:
    name: server
    calls:
    - name: mysql-tutorial-db
    type: database
    databaseResources:
    - databaseName: otterize_example
    table: example
    operations:
    - SELECT
    - INSERT

    We can now apply our intents. Behind the scenes, the Otterize operator created the user for our server workload and executed GRANT queries on the database, making our SELECT and INSERT errors disappear.

    kubectl apply -n otterize-tutorial-mysql -f https://docs.otterize.com/code-examples/mysql/clientintents.yaml

    View logs for the server

    We can now view the server logs once again. This time, we should see that the server has the appropriate access to the database:

    kubectl logs -f -n otterize-tutorial-mysql deploy/server

    Example log:

    Successfully INSERTED into our table

    Successfully SELECTED, most recent value: 2024-04-30T13:20:46Z

    That’s it! If your service’s functionality changes, adding or removing access is as simple as updating your ClientIntents definitions. For fun, try altering the operations to just SELECT or INSERT.

    Teardown

    To remove the deployed examples, run:

    kubectl delete clientintents.k8s.otterize.com -n otterize-tutorial-mysql client-intents-for-server
    kubectl delete namespace otterize-tutorial-mysql