Blog

Deploying MySQL users and databases with AWS CloudFormation

17 May, 2018
Xebia Background Header Wave

Although CloudFormation is very good in creating MySQL database servers with Amazon RDS, it only contains a single user which
has full control over the entire server. The mundane task of creating normal users and database schemas is not supported. With this Custom Resource Provider
you can deploy a MySQL user with its own database schema as part of the CloudFormation template.
This plugin creates a user and a database schema and grants the user full access to the newly created schema, with grant option. This allows the application to create all the resources it requires and grant privileges to other users. The password for the user can be specified directly or taken from the AWS Parameter Store.

How does it work?

It is quite easy: you specify a CloudFormation resource of the Custom::MySQLUser, as follows:

  KongUser:
    Type: Custom::MySQLUser
    DependsOn: KongPassword
    Properties:
      Name: kong
      PasswordParameterName: /mysql/kong/password
      WithDatabase: true
      DeletionPolicy: Retain
      Database:                   # the server to create the new user or database in
        Host: mysql
        Port: 3306
        Database: root
        User: root
        PasswordParameterName: /mysql/root/password
      ServiceToken: !Sub 'arn:aws:lambda:${AWS::Region}:${AWS::AccountId}:function:binxioio-cfn-mysql-user-provider-vpc-${AppVPC}'

   KongPassword:
    Type: Custom::Secret
    Properties:
      Name: /mysql/kong/password
      ServiceToken: !Sub 'arn:aws:lambda:${AWS::Region}:${AWS::AccountId}:function:binxio-cfn-secret-provider'

After the deployment, the MySQL user ‘kong’ has been created together with a matching database ‘kong’. The password for the user ‘kong’ was generated by the secret provider and stored as the parameter ‘/mysql/kong/password’. The password for the root database user has been obtained by querying the Parameter /mysql/root/password. If you just want to create a user with which you can login to the MySQL database server, without a database, specify WithDatabase as false.
The password for the user and the database connection can be specified directly (Password) or taken from the AWS Parameter Store (PasswordParameterName). We recommend
to always use the Parameter Store.
By default WithDatabase is set to true, which means that a database schema is created with the same name as the user. If you only wish to create a user, specify false.
When the resource is deleted, by default the user account is locked (RetainPolicy set to Retain). If you wish to delete the user (and the data), set RetainPolicy to drop.
If a user with the same name already exists, the user is “adopted” and it’s password is changed. If WithDatabase is specified and a database/schema with the same name
already exists, the user is granted all permissions on the database.

Installation

To install this Custom Resource, type:

export VPC_ID=$(aws ec2  --output text --query 'Vpcs[?IsDefault].VpcId' describe-vpcs)
export SUBNET_ID=$(aws ec2 --output text --query Subnets[0].SubnetId \
         describe-subnets --filters Name=vpc-id,Values=$VPC_ID)
export SG_ID=$(aws ec2 --output text --query "SecurityGroups[*].GroupId" \
         describe-security-groups --group-names default  --filters Name=vpc-id,Values=$VPC_ID)

aws cloudformation create-stack \
 --capabilities CAPABILITY_IAM \
 --stack-name cfn-mysql-user-provider \
 --template-body file://cloudformation/cfn-custom-resource-provider.json  \
 --parameters \
             ParameterKey=VPC,ParameterValue=$VPC_ID \
             ParameterKey=Subnet,ParameterValue=$SUBNET_ID \
                ParameterKey=SecurityGroup,ParameterValue=$SG_ID

aws cloudformation wait stack-create-complete  --stack-name cfn-mysql-user-provider 

Note that this uses the default VPC, subnet and security group. As the Lambda function needs to connect to the database, you will need to
install this custom resource provider for each vpc that you want to be able to create database users.
This CloudFormation template will use our pre-packaged provider from s3://binxio-public/lambdas/cfn-mysql-user-provider-latest.zip.
If you have not done so, please install the secret provider too.

cd ..
git clone https https://github.com/binxio/cfn-secret-provider.git 
cd cfn-secret-provider
aws cloudformation create-stack \
    --capabilities CAPABILITY_IAM \
    --stack-name cfn-secret-provider \
    --template-body file://cloudformation/cfn-custom-resource-provider.json 
aws cloudformation wait stack-create-complete  --stack-name cfn-secret-provider 

Demo

To install the simple sample of the Custom Resource, type:

aws cloudformation create-stack --stack-name cfn-mysql-user-provider-demo \
 --template-body file://cloudformation/demo-stack.json
aws cloudformation wait stack-create-complete  --stack-name cfn-mysql-user-provider-demo

It will create a MySQL database for demoing purposes, so it is quite time consuming…

Conclusion

With this solution MySQL users and database schemas can be provisioned just like the RDS instance, while keeping the
passwords safely stored in the AWS Parameter Store.
If you have any questions, do not hesitate to contact me.

Mark van Holsteijn
Mark van Holsteijn is a senior software systems architect at Xebia Cloud-native solutions. He is passionate about removing waste in the software delivery process and keeping things clear and simple.
Questions?

Get in touch with us to learn more about the subject and related solutions

Explore related posts