Skip to content

Latest commit

 

History

History
283 lines (223 loc) · 7.71 KB

gcp-secretmanager.md

File metadata and controls

283 lines (223 loc) · 7.71 KB
Raw

Run on GKE and manage Cloud SQL (MySQL) with GCP SecretManager

mysql-operator can get the credentials of the MySQL user (which is used to access to the target MySQL cluster) from GCP SecretManager

In this example, we'll use Cloud SQL for MySQL, and run mysql-operator on GKE.

1. Prepare GCP resources

1.1. Prepare env var and gcloud

  1. Set environment variables

    INSTANCE_NAME=mysql-test
ZONE=asia-northeast1-b
REGION=asia-northeast1
SECRET_NAME=mysql-password
SA_NAME=mysql-operator
GKE_CLUSTER_NAME=hello-cluster
NAMESPACE=mysql-operator
KSA_NAME=mysql-operator-controller-manager

  2. Configure gcloud project

    PROJECT=<your_project_id>
gcloud config set project $PROJECT

1.2. Create GKE cluster

  1. Create GKE cluster

    gcloud container clusters create-auto $GKE_CLUSTER_NAME --location=$REGION

  2. Set up kubeconfig

    gcloud container clusters get-credentials $GKE_CLUSTER_NAME --location=$REGION

1.3. Create Cloud SQL instance.

  1. Generate random password for root user.

    ROOT_PASSWORD=$(openssl rand -base64 32)

  2. Create Cloud SQL instance.

    gcloud sql instances create $INSTANCE_NAME \
--cpu=1 \
--memory=3840MiB \
--zone=${ZONE} \
--root-password=$ROOT_PASSWORD \
--project ${PROJECT}

    For existing instance, you can reset the root password with the following command:

    gcloud sql users set-password root \
    --host=% \
    --instance=$INSTANCE_NAME \
    --password=$ROOT_PASSWORD

1.4. Create SecretManager secret for root password

  1. Create Secret mysql-password with value password, which will be used for the credentials of custom resource MySQL.

    gcloud secrets create $SECRET_NAME --replication-policy="automatic" --project ${PROJECT}

    echo -n "${ROOT_PASSWORD}" | gcloud secrets versions add $SECRET_NAME --data-file=- --project ${PROJECT}

1.5. Create Service Account for mysql-operator

  1. Create service account mysql-operator

    gcloud iam service-accounts create $SA_NAME --display-name=$SA_NAME

  2. Grant necessary permission for the created Secret to the service account

    1. roles/secretmanager.secretAccessor: To allow mysql-operator to get root password from SecretManager

      gcloud secrets add-iam-policy-binding $SECRET_NAME \
    --member="serviceAccount:${SA_NAME}@${PROJECT}.iam.gserviceaccount.com" \
    --role="roles/secretmanager.secretAccessor" --project ${PROJECT}

    2. roles/cloudsql.client: To allow mysql-operator can connect to Cloud SQL

      gcloud projects add-iam-policy-binding $PROJECT \
    --member="serviceAccount:${SA_NAME}@${PROJECT}.iam.gserviceaccount.com" \
    --role="roles/cloudsql.client"

    3. roles/iam.workloadIdentityUser: To allow to Kubernete Pod to impersonate the Service Account

      gcloud iam service-accounts add-iam-policy-binding ${SA_NAME}@${PROJECT}.iam.gserviceaccount.com \
    --role roles/iam.workloadIdentityUser \
    --member "serviceAccount:${PROJECT}.svc.id.goog[${NAMESPACE}/${KSA_NAME}]"

      For more details, read Workload Identity

2. Install mysql-operator with Helm

  1. Create a Namespace.

    kubectl create ns $NAMESPACE

  2. Deploy with Helm.

    helm repo add nakamasato https://nakamasato.github.io/helm-charts
helm repo update

    helm install mysql-operator nakamasato/mysql-operator \
    --set cloudSecretManagerType=gcp \
    --set gcpServiceAccount=${SA_NAME}@${PROJECT}.iam.gserviceaccount.com \
    --set gcpProjectId=$PROJECT \
    --set cloudSQL.instanceConnectionName=$PROJECT:$REGION:$INSTANCE_NAME \
    -n $NAMESPACE

  3. Check Helm release.

    helm list -n $NAMESPACE
NAME            NAMESPACE       REVISION        UPDATED                                 STATUS          CHART                   APP VERSION
mysql-operator  mysql-operator  1               2023-09-09 12:03:54.220046 +0900 JST    deployed        mysql-operator-v0.3.0   v0.3.0

  4. Check mysql-operator Pod.

    kubectl get pod -n $NAMESPACE
NAME                                                 READY   STATUS    RESTARTS   AGE
mysql-operator-controller-manager-77649f6bb9-xbt9l   2/2     Running   0          2m59s

3. Create custom resources (Manage MySQL users, databases, schemas, etc.)

  1. Create sample MySQL, MySQLUser, MySQLDB.

    If you want to create sample MySQL, MySQLUser, and MySQLDB at once, you can use the following command:

    kubectl apply -k https://github.com/nakamasato/mysql-operator/config/samples-on-k8s-with-gcp-secretmanager

  2. Create MySQL

    kubectl apply -f - <<EOF
apiVersion: mysql.nakamasato.com/v1alpha1
kind: MySQL
metadata:
  name: $INSTANCE_NAME
spec:
  host: "127.0.0.1" # auth SQL proxy
  adminUser:
    name: root
    type: raw
  adminPassword:
    name: $SECRET_NAME
    type: gcp
EOF

    Check the status

    NAME         HOST        ADMINUSER   CONNECTED   USERCOUNT   DBCOUNT   REASON
mysql-test   127.0.0.1   root        true        0           0         Ping succeded and updated MySQLClients

  3. Create MySQL user sample-user.

    kubectl apply -f - <<EOF
apiVersion: mysql.nakamasato.com/v1alpha1
kind: MySQLUser
metadata:
  name: sample-user
spec:
  mysqlName: $INSTANCE_NAME
EOF

    kubectl get secret
NAME                           TYPE     DATA   AGE
mysql-mysql-test-sample-user   Opaque   1      99s

  4. Create MySQL DB sample_db

    kubectl apply -f - <<EOF
apiVersion: mysql.nakamasato.com/v1alpha1
kind: MySQLDB
metadata:
  name: sample-db # this is a Kubernetes object name, not the name for MySQL database
spec:
  dbName: sample_db # this is MySQL database name
  mysqlName: $INSTANCE_NAME
EOF

    kubectl get mysqldb
NAME        PHASE   REASON                          SCHEMAMIGRATION
sample-db   Ready   Database successfully created   {"dirty":false,"version":0}

  5. Check

    cloud-sql-proxy ${PROJECT}:${REGION}:${INSTANCE_NAME}

    mysql -uroot -p${ROOT_PASSWORD} --host 127.0.0.1

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_db          |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
    mysql> select User, Host from mysql.user where User = 'sample-user';
+-------------+------+
| User        | Host |
+-------------+------+
| sample-user | %    |
+-------------+------+
1 row in set (0.01 sec)

4. Clean up

4.1. Kubernetes resources

Custom resources

kubectl delete mysqldb sample-db
kubectl delete mysqluser sample-user
kubectl delete mysql $INSTANCE_NAME

Uninstall mysql-operator

helm uninstall mysql-operator -n $NAMESPACE

4.2. GCP resources

gcloud container clusters delete $GKE_CLUSTER_NAME --location $REGION
gcloud sql instances delete ${INSTANCE_NAME} --project ${PROJECT}
gcloud iam service-accounts delete ${SA_NAME}@${PROJECT}.iam.gserviceaccount.com --project ${PROJECT}
gcloud secrets delete $SECRET_NAME --project ${PROJECT}