admin
1bc8aadb85
- Streamlined README focused on quick start - Complete examples for all major use cases - Decision tree for choosing right pattern - Comprehensive troubleshooting guide
566 lines
17 KiB
YAML
566 lines
17 KiB
YAML
# Example 5: MySQL User Password Rotation with Previous Version
|
|
#
|
|
# Use case: MySQL application user password that rotates every 90 days
|
|
#
|
|
# Characteristics:
|
|
# - Password rotates every 90 days
|
|
# - MySQL requires old password to authenticate before changing (when using SET PASSWORD)
|
|
# - keepPreviousVersion creates mysql-app-user-previous
|
|
# - CronJob uses old password to authenticate, sets new password
|
|
# - Application pods restart automatically via Reloader
|
|
|
|
---
|
|
apiVersion: v1
|
|
kind: Namespace
|
|
metadata:
|
|
name: mysql
|
|
|
|
---
|
|
# ManagedSecret for MySQL application user
|
|
apiVersion: secrets.c5ai.ch/v1alpha1
|
|
kind: ManagedSecret
|
|
metadata:
|
|
name: mysql-app-user
|
|
namespace: mysql
|
|
spec:
|
|
vault:
|
|
address: "http://openbao.openbao.svc.cluster.local:8200"
|
|
authMethod: kubernetes
|
|
role: managedsecret-operator
|
|
kvVersion: v2
|
|
mount: secret
|
|
path: mysql/app-user
|
|
|
|
fields:
|
|
- name: username
|
|
type: static
|
|
value: "app_user"
|
|
|
|
- name: password
|
|
type: generated
|
|
generator:
|
|
type: password
|
|
length: 32
|
|
minDigits: 6
|
|
minSymbols: 6
|
|
minLowercase: 8
|
|
minUppercase: 8
|
|
# MySQL doesn't allow some special chars in passwords
|
|
symbolCharacters: "!@#$%^&*"
|
|
allowRepeat: false
|
|
|
|
- name: host
|
|
type: static
|
|
value: "mysql.mysql.svc.cluster.local"
|
|
|
|
- name: port
|
|
type: static
|
|
value: "3306"
|
|
|
|
- name: database
|
|
type: static
|
|
value: "app_db"
|
|
|
|
- name: connection-string
|
|
type: static
|
|
value: "mysql://app_user@mysql.mysql.svc.cluster.local:3306/app_db"
|
|
|
|
destination:
|
|
name: mysql-app-secret
|
|
type: Opaque
|
|
# Keep previous version for authentication during rotation
|
|
keepPreviousVersion: true
|
|
previousVersionTTL: 1h
|
|
|
|
rotation:
|
|
enabled: true
|
|
schedule: 2160h # 90 days
|
|
rotateGeneratedOnly: true
|
|
|
|
---
|
|
# Separate ManagedSecret for MySQL root credentials (no rotation)
|
|
apiVersion: secrets.c5ai.ch/v1alpha1
|
|
kind: ManagedSecret
|
|
metadata:
|
|
name: mysql-root
|
|
namespace: mysql
|
|
spec:
|
|
vault:
|
|
address: "http://openbao.openbao.svc.cluster.local:8200"
|
|
authMethod: kubernetes
|
|
role: managedsecret-operator
|
|
kvVersion: v2
|
|
mount: secret
|
|
path: mysql/root
|
|
|
|
fields:
|
|
- name: username
|
|
type: static
|
|
value: "root"
|
|
|
|
- name: password
|
|
type: generated
|
|
generator:
|
|
type: password
|
|
length: 40
|
|
minDigits: 8
|
|
minSymbols: 8
|
|
|
|
destination:
|
|
name: mysql-root-secret
|
|
type: Opaque
|
|
|
|
# Root password doesn't rotate automatically
|
|
rotation:
|
|
enabled: false
|
|
|
|
---
|
|
# ServiceAccount for password rotation job
|
|
apiVersion: v1
|
|
kind: ServiceAccount
|
|
metadata:
|
|
name: mysql-rotator
|
|
namespace: mysql
|
|
|
|
---
|
|
# Role to allow reading secrets
|
|
apiVersion: rbac.authorization.k8s.io/v1
|
|
kind: Role
|
|
metadata:
|
|
name: mysql-rotator
|
|
namespace: mysql
|
|
rules:
|
|
- apiGroups: [""]
|
|
resources: ["secrets"]
|
|
verbs: ["get", "list"]
|
|
resourceNames:
|
|
- "mysql-app-secret"
|
|
- "mysql-app-secret-previous"
|
|
- "mysql-root-secret"
|
|
|
|
---
|
|
# RoleBinding
|
|
apiVersion: rbac.authorization.k8s.io/v1
|
|
kind: RoleBinding
|
|
metadata:
|
|
name: mysql-rotator
|
|
namespace: mysql
|
|
roleRef:
|
|
apiGroup: rbac.authorization.k8s.io
|
|
kind: Role
|
|
name: mysql-rotator
|
|
subjects:
|
|
- kind: ServiceAccount
|
|
name: mysql-rotator
|
|
namespace: mysql
|
|
|
|
---
|
|
# CronJob to detect rotation and update MySQL password
|
|
apiVersion: batch/v1
|
|
kind: CronJob
|
|
metadata:
|
|
name: mysql-password-rotator
|
|
namespace: mysql
|
|
spec:
|
|
# Run every 5 minutes to check for rotation
|
|
schedule: "*/5 * * * *"
|
|
successfulJobsHistoryLimit: 3
|
|
failedJobsHistoryLimit: 3
|
|
jobTemplate:
|
|
spec:
|
|
backoffLimit: 3
|
|
template:
|
|
metadata:
|
|
annotations:
|
|
reloader.stakater.com/auto: "true"
|
|
spec:
|
|
serviceAccountName: mysql-rotator
|
|
restartPolicy: OnFailure
|
|
containers:
|
|
- name: rotate-password
|
|
image: mysql:8.0
|
|
env:
|
|
# Current credentials
|
|
- name: MYSQL_USER
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: username
|
|
- name: NEW_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: password
|
|
- name: MYSQL_HOST
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: host
|
|
- name: MYSQL_PORT
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: port
|
|
- name: MYSQL_DATABASE
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: database
|
|
|
|
# Previous credentials (for authentication)
|
|
- name: OLD_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret-previous
|
|
key: password
|
|
optional: true
|
|
|
|
# Root credentials for fallback
|
|
- name: MYSQL_ROOT_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-root-secret
|
|
key: password
|
|
|
|
command:
|
|
- bash
|
|
- -c
|
|
- |
|
|
set -e
|
|
|
|
echo "=========================================="
|
|
echo "MySQL Password Rotation Check"
|
|
echo "=========================================="
|
|
echo "User: $MYSQL_USER"
|
|
echo "Host: $MYSQL_HOST:$MYSQL_PORT"
|
|
echo "Database: $MYSQL_DATABASE"
|
|
echo ""
|
|
|
|
# Check if previous password exists (rotation happened)
|
|
if [ -z "$OLD_PASSWORD" ]; then
|
|
echo "No previous password found - no rotation needed"
|
|
exit 0
|
|
fi
|
|
|
|
# Check if passwords are different
|
|
if [ "$OLD_PASSWORD" = "$NEW_PASSWORD" ]; then
|
|
echo "Passwords are identical - no rotation needed"
|
|
exit 0
|
|
fi
|
|
|
|
echo "Rotation detected! Old and new passwords differ."
|
|
echo ""
|
|
|
|
# Test if current password in MySQL is the old one
|
|
echo "Testing authentication with OLD password..."
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$OLD_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
|
|
echo "✓ Old password still active in MySQL"
|
|
echo ""
|
|
echo "Updating to NEW password..."
|
|
|
|
# Method 1: Use ALTER USER (MySQL 5.7.6+, doesn't require old password)
|
|
# Connect as root to change password
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -uroot -p"$MYSQL_ROOT_PASSWORD" \
|
|
-e "ALTER USER '$MYSQL_USER'@'%' IDENTIFIED BY '$NEW_PASSWORD';" 2>/dev/null; then
|
|
echo "✓ Password updated using ALTER USER (as root)"
|
|
elif mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -uroot -p"$MYSQL_ROOT_PASSWORD" \
|
|
-e "SET PASSWORD FOR '$MYSQL_USER'@'%' = PASSWORD('$NEW_PASSWORD');" 2>/dev/null; then
|
|
echo "✓ Password updated using SET PASSWORD (as root)"
|
|
else
|
|
# Method 2: Use SET PASSWORD as the user (requires old password connection)
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$OLD_PASSWORD" \
|
|
-e "SET PASSWORD = PASSWORD('$NEW_PASSWORD');" 2>/dev/null; then
|
|
echo "✓ Password updated using SET PASSWORD (as user)"
|
|
else
|
|
echo "✗ ERROR: Failed to update password with any method"
|
|
exit 1
|
|
fi
|
|
fi
|
|
|
|
echo ""
|
|
|
|
# Verify new password works
|
|
echo "Verifying NEW password..."
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$NEW_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
|
|
echo "✓ New password verified and working!"
|
|
echo ""
|
|
echo "Flushing privileges..."
|
|
mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -uroot -p"$MYSQL_ROOT_PASSWORD" -e "FLUSH PRIVILEGES;" || echo "Warning: Could not flush privileges"
|
|
echo ""
|
|
echo "=========================================="
|
|
echo "Rotation completed successfully"
|
|
echo "=========================================="
|
|
exit 0
|
|
else
|
|
echo "✗ ERROR: New password doesn't work!"
|
|
exit 1
|
|
fi
|
|
else
|
|
echo "Old password doesn't work - checking if new password already active..."
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$NEW_PASSWORD" -e "SELECT 1;" > /dev/null 2>&1; then
|
|
echo "✓ New password is already active in MySQL"
|
|
echo "Rotation was already completed"
|
|
exit 0
|
|
else
|
|
echo "✗ ERROR: Neither old nor new password works!"
|
|
echo "Manual intervention required"
|
|
echo ""
|
|
echo "Attempting to reset using root credentials..."
|
|
if mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -uroot -p"$MYSQL_ROOT_PASSWORD" \
|
|
-e "ALTER USER '$MYSQL_USER'@'%' IDENTIFIED BY '$NEW_PASSWORD';"; then
|
|
echo "✓ Password reset successfully using root"
|
|
exit 0
|
|
else
|
|
echo "✗ Failed to reset password"
|
|
exit 1
|
|
fi
|
|
fi
|
|
fi
|
|
|
|
---
|
|
# Example MySQL StatefulSet (simplified)
|
|
apiVersion: apps/v1
|
|
kind: StatefulSet
|
|
metadata:
|
|
name: mysql
|
|
namespace: mysql
|
|
spec:
|
|
serviceName: mysql
|
|
replicas: 1
|
|
selector:
|
|
matchLabels:
|
|
app: mysql
|
|
template:
|
|
metadata:
|
|
labels:
|
|
app: mysql
|
|
spec:
|
|
containers:
|
|
- name: mysql
|
|
image: mysql:8.0
|
|
env:
|
|
- name: MYSQL_ROOT_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-root-secret
|
|
key: password
|
|
- name: MYSQL_DATABASE
|
|
value: "app_db"
|
|
ports:
|
|
- containerPort: 3306
|
|
name: mysql
|
|
volumeMounts:
|
|
- name: data
|
|
mountPath: /var/lib/mysql
|
|
livenessProbe:
|
|
exec:
|
|
command:
|
|
- mysqladmin
|
|
- ping
|
|
- -h
|
|
- localhost
|
|
initialDelaySeconds: 30
|
|
periodSeconds: 10
|
|
readinessProbe:
|
|
exec:
|
|
command:
|
|
- mysql
|
|
- -h
|
|
- localhost
|
|
- -e
|
|
- SELECT 1
|
|
initialDelaySeconds: 5
|
|
periodSeconds: 2
|
|
# InitContainer to create app user on first run
|
|
initContainers:
|
|
- name: init-db-user
|
|
image: mysql:8.0
|
|
env:
|
|
- name: MYSQL_ROOT_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-root-secret
|
|
key: password
|
|
- name: APP_USER
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: username
|
|
- name: APP_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: password
|
|
command:
|
|
- sh
|
|
- -c
|
|
- |
|
|
# Wait for MySQL to be ready
|
|
until mysql -h mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1" > /dev/null 2>&1; do
|
|
echo "Waiting for MySQL..."
|
|
sleep 2
|
|
done
|
|
|
|
# Create user if not exists
|
|
mysql -h mysql -uroot -p"$MYSQL_ROOT_PASSWORD" <<EOF
|
|
CREATE USER IF NOT EXISTS '$APP_USER'@'%' IDENTIFIED BY '$APP_PASSWORD';
|
|
GRANT ALL PRIVILEGES ON app_db.* TO '$APP_USER'@'%';
|
|
FLUSH PRIVILEGES;
|
|
EOF
|
|
|
|
echo "User $APP_USER created/updated"
|
|
volumeClaimTemplates:
|
|
- metadata:
|
|
name: data
|
|
spec:
|
|
accessModes: ["ReadWriteOnce"]
|
|
resources:
|
|
requests:
|
|
storage: 10Gi
|
|
|
|
---
|
|
# Service for MySQL
|
|
apiVersion: v1
|
|
kind: Service
|
|
metadata:
|
|
name: mysql
|
|
namespace: mysql
|
|
spec:
|
|
selector:
|
|
app: mysql
|
|
ports:
|
|
- port: 3306
|
|
targetPort: 3306
|
|
name: mysql
|
|
clusterIP: None # Headless service
|
|
|
|
---
|
|
# Example application using MySQL
|
|
apiVersion: apps/v1
|
|
kind: Deployment
|
|
metadata:
|
|
name: app
|
|
namespace: mysql
|
|
spec:
|
|
replicas: 2
|
|
selector:
|
|
matchLabels:
|
|
app: mysql-client
|
|
template:
|
|
metadata:
|
|
labels:
|
|
app: mysql-client
|
|
annotations:
|
|
# Reloader restarts app when credentials change
|
|
reloader.stakater.com/auto: "true"
|
|
spec:
|
|
containers:
|
|
- name: app
|
|
image: mysql:8.0
|
|
env:
|
|
- name: MYSQL_HOST
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: host
|
|
- name: MYSQL_PORT
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: port
|
|
- name: MYSQL_USER
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: username
|
|
- name: MYSQL_PASSWORD
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: password
|
|
- name: MYSQL_DATABASE
|
|
valueFrom:
|
|
secretKeyRef:
|
|
name: mysql-app-secret
|
|
key: database
|
|
command:
|
|
- sh
|
|
- -c
|
|
- |
|
|
# Your application code here
|
|
while true; do
|
|
mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" \
|
|
-D"$MYSQL_DATABASE" -e "SELECT NOW();" || echo "Connection failed"
|
|
sleep 30
|
|
done
|
|
|
|
---
|
|
# How the rotation flow works:
|
|
#
|
|
# Initial Setup:
|
|
# - mysql-app-secret created with initial password
|
|
# - InitContainer creates MySQL user with this password
|
|
# - Application connects successfully
|
|
#
|
|
# Day 90: Rotation
|
|
# 1. ManagedSecret operator:
|
|
# - Generates NEW password
|
|
# - Creates mysql-app-secret-previous (OLD password)
|
|
# - Updates mysql-app-secret (NEW password)
|
|
#
|
|
# 2. CronJob (within 5 minutes):
|
|
# - Detects previous secret exists
|
|
# - Authenticates to MySQL with OLD password (or as root)
|
|
# - Runs ALTER USER to set NEW password
|
|
# - Verifies NEW password works
|
|
#
|
|
# 3. Reloader:
|
|
# - Detects mysql-app-secret change
|
|
# - Restarts application pods
|
|
# - Pods reconnect with NEW password
|
|
#
|
|
# 4. Cleanup (after 1 hour):
|
|
# - mysql-app-secret-previous deleted automatically
|
|
|
|
---
|
|
# Testing commands:
|
|
#
|
|
# # Check current credentials
|
|
# kubectl get secret mysql-app-secret -n mysql -o yaml
|
|
#
|
|
# # Test connection with current credentials
|
|
# MYSQL_PASS=$(kubectl get secret mysql-app-secret -n mysql -o jsonpath='{.data.password}' | base64 -d)
|
|
# kubectl run -it --rm mysql-test --image=mysql:8.0 --restart=Never -- \
|
|
# mysql -h mysql.mysql.svc.cluster.local -u app_user -p"$MYSQL_PASS" -e "SELECT NOW();"
|
|
#
|
|
# # Force rotation
|
|
# kubectl annotate managedsecret mysql-app-user -n mysql reconcile="$(date +%s)" --overwrite
|
|
#
|
|
# # Watch rotation
|
|
# kubectl get secrets -n mysql -w
|
|
# kubectl get jobs -n mysql -w
|
|
#
|
|
# # View rotation logs
|
|
# kubectl logs -n mysql -l job-name=mysql-password-rotator-XXXXX
|
|
|
|
---
|
|
# Important MySQL-specific notes:
|
|
#
|
|
# 1. User Host Specification:
|
|
# - '%' allows connection from any host
|
|
# - Consider restricting to specific pods/namespaces
|
|
# - Example: 'app_user'@'%.mysql.svc.cluster.local'
|
|
#
|
|
# 2. Authentication Methods:
|
|
# - MySQL 8.0+ uses caching_sha2_password by default
|
|
# - Older apps may need mysql_native_password
|
|
# - Specify during user creation if needed
|
|
#
|
|
# 3. Privilege Management:
|
|
# - Rotation only changes password, not privileges
|
|
# - Manage privileges separately via root access
|
|
#
|
|
# 4. Connection Pooling:
|
|
# - Application connection pools need to be refreshed
|
|
# - Reloader restart handles this automatically
|
|
# - Consider graceful shutdown for long-running transactions |