# terraform-mysql Terraform module for managing MySQL users and roles. ## Overview This Terraform module helps in managing MySQL users and roles. It supports creating users with different roles and assigning specific privileges to each role. ## Features - Create MySQL roles with specific privileges. - Create MySQL users and assign them to roles. - Create custom roles and custom privileges. - Support for caching_sha2_password and AWSAuthenticationPlugin authentication plugins. - Configurable MySQL provider for AWS RDS with AWS IAM authentication. ## Usage ```hcl # Configure the MySQL provider for AWS RDS with AWS IAM authentication enabled provider "mysql" { endpoint = "aws://dev-mysql.cluster-co2yckwm6hcp.us-east-1.rds.amazonaws.com" username = "admin" tls = "true" } module "test" { source = "/path/to/module" # Roles are automatically created once module is initialized. # Example of creating read, and write users that are associated with the read, and write roles. read_only_users = [ { user = "test_user_1" auth_plugin = "caching_sha2_password" host_identifier = "%" }, ] read_write_users = [ { user = "test_user_2" auth_plugin = "AWSAuthenticationPlugin" host_identifier = "192.168.40.0/22" }, ] custom_roles = { custom_role_1 = { name = "custom_role_1" privileges = ["SELECT", "SHOW VIEW", "USAGE"] }, custom_role_2 = { name = "custom_role_2" privileges = ["SELECT", "SHOW VIEW", "USAGE"] } } custom_role_users = [ { user = "custom_user_1" auth_plugin = "caching_sha2_password" host_identifier = "%" roles = ["custom_role_1"] }, { user = "custom_user_2" auth_plugin = "AWSAuthenticationPlugin" host_identifier = "192.168.40.0/22" roles = ["custom_role_2"] } ] } ``` ## Inputs | Name | Description | Type | Default | Required | |-------------------------------------------|--------------------------------------------------------------|-----------------|-----------------------------------------------|---| | `read_only_role_name` | Name of the read-only role | `string` | `readonly_role` | no | | `read_write_role_name` | Name of the read-write role | `string` | `readwrite_role` | no | | `read_only_role_privileges` | Privileges for the read-only role | `list(string) ` | `SELECT, SHOW VIEW, USAGE` | no | | `read_write_role_privileges` | read_write_role_privileges | `list(string) ` | `SELECT,SHOW VIEW,INSERT,UPDATE,USAGE,DELETE` | no| | `read_only_users` | List of read-only users | `list(map)` | n/a | yes | | `read_write_users` | List of read-write users | `list(map)` | n/a | yes | | `database_permissions` | Name of the database | `string` | `"*"` | no | | `user_source_host` | Default user source host | `string` | `"%"` | yes | | `auth_plugin` | Auth plugins | `string` | n/a | yes | | `custom_role_users` | A list of read-write-delete MySQL users and their passwords | `list(object)` | `[]` | no | | `custom_role_name` | Custom role name | `string` | yes | no | ## Resources | Name | Type | |---------------|----------| | `mysql_grant` | Resource | | `mysql_role` | Resource | | `mysql_user` | Resource | - End user mysql client needs to use rds-combined-ca-bundle.pem file to connect to the RDS instance. The file can be downloaded from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem The file can be downloaded from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem - The mysql client command when caching_sha2_password authentication plugin is used: ```bash mysql -h -u -p --ssl-ca=rds-combined-ca-bundle.pem ``` - The mysql client command when AWSAuthenticationPlugin authentication plugin is used: - How to connect using AWS IAM authentication: - Create an IAM user with the required permissions. - Use the AWS CLI to generate an authentication token. - Use the token to connect to the RDS instance. - Generate an authentication token using the AWS CLI: - ```bash # Step 1: Assume the desired AWS role using awsume awsume # Step 2: Generate an authentication token using the AWS CLI aws rds generate-db-auth-token --hostname --port 3306 --region --username OR TOKEN="$(aws rds generate-db-auth-token --hostname dev-mysql.cluster-co2yckwm6hcp.us-east-1.rds.amazonaws.com --port 3306 --region us-east-1 --username your_iam_user)" )" echo $TOKEN The whole output will be your password to connect to the RDS instance. Example: dev-mysql.cluster-co2yckwm6hcp.us-east-1.rds.amazonaws.com:3306/?Action=connect&DBUser=tmoin&X-Amz-Algorithm=AWS4-HMAC- SHA256&X-Amz-Credential=ASIARQCJYUUUHYYCLOH%2F20240920%2Fus-east-1%2Frds-db%2Faws4_request&X-Amz-Date=20240920T042337Z &X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Security-Token=FwoGZXIvYXdzEELLKUYD2Cls7GpET0wLiwfiKtAeZ0Nghy%2FHbZt 134BWn7LRp9tplvwwX%2FPJioKdIAAvbIMfYGNDh9uj2qKzRMsIOqYBY3NaRpDGx9AFzh9PmO1Z5t%2B18QWQMKWU9cBBNVnfRcQwrYH9vrBQ4UV5Db7CM OiCNueJh2o7kd%2F7rZ3ix%2F9tUaKrE4g4Ai9heN5zfgrMgBNjRQE%2F3mAwhjvJ4Nqlgs454DziSAC8QIDDsdulD0bI9gRF5HvAcAmnFcUi7OmKILps7 cGMi2fjlf%2B1S1s%2BjeVhOkuQqJa3qifUslGT4S3IfUy%2BCRUjmeg3vLWOIQ5UQcRDbs%3D&X-Amz-Signature=b84d1bc9e699872bc97df494b86 a10fanm3ks7bc8009d1561fd2e0053564302535ea ``` - The mysql client command when AWS IAM authentication is used: - ```bash mysql -h -u -p --ssl-ca=rds-combined-ca-bundle.pem --enable-cleartext-plugin --password= ``` - How to connect using Datagrip: - Create a new connection to the RDS instance in Datagrip. - In the Advanced tab, set the SSL mode to `REQUIRED`. - In the SSL tab, set the SSL key store to the path of the rds-combined-ca-bundle.pem file. - In the General tab, set the user and password to the desired values. - Click on the Test Connection button to verify the connection. - Click on the OK button to save the connection. - Default roles. - Default mysql role will be assigned to the users so users don't have to switch to the role after login to mysql. - That way users don't have to run set role when they connect (which could be problematic if using user credentials to run a service locally). The default roles are: - read_only_role - read_write_role