DEV Community

flpslv
flpslv

Posted on

Provisioning MySQL/MariaDB Vault Database Secrets Engine with Terraform

#terraform #vault #mariadb

The goal of this post is to provide dynamic/temporary database credentials without having to manually create and manage them all.

I'm just going to start this by saying that this is just a proof of concept and best practices were not followed at all. (mainly security ones). All the procedures from this point are just simple tests with focus on easing all the side tasks just to see the whole process working.

Testing Containers

We'll be using MariaDB and Vault containers, (quick) launched as specified on both projects official DockerHub pages.

  • Vault 
docker run --rm \
    --name vault \
    --cap-add=IPC_LOCK \
    -e 'VAULT_LOCAL_CONFIG={"backend": {"file": {"path": "/vault/file"}}, "default_lease_ttl": "168h", "max_lease_ttl": "720h"}' \
    -p 8200:8200 \
    -d vault
Enter fullscreen mode Exit fullscreen mode

When vault finishes starting up, we can see from the container logs the following:

You may need to set the following environment variable:

$ export VAULT_ADDR='http://0.0.0.0:8200

The unseal key and root token are displayed below in case >you want to
seal/unseal the Vault or re-authenticate.

Unseal Key: kSUgoPDPyBrCGWc4s93CIlMUnDLZGcxdu4doYCkWSPs=
Root Token: s.I6TnqhrgYh8uET91FUsNvIwV

Development mode should NOT be used in production >installations!

So, we have our vault address, and the root token.

  • MariaDB 
docker run --rm \
    --name mariadb \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=mysecretpw \
    -d mariadb:latest
Enter fullscreen mode Exit fullscreen mode

And here we have our root user and password for MariaDB.

Because the root user shouldn't be used for anything, we're going to create a dedicated user for vault interactions.

Login to the database using mysql -h 127.0.0.1 -u root -p

and create the vault user

grant CREATE USER, SELECT, INSERT, UPDATE ON *.* TO 'vault'@'%' identified by 'myvaultsecretpw' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode

Terraform

With all the services running and configured, it's time to take care of the terraform part.

Once again, this is only a POC. All the hardcoded and weak passwords are meant for testing purposes.

provider "vault" {
    address = "http://localhost:8200"
        #Token provided from vault container log
    token = "s.I6TnqhrgYh8uET91FUsNvIwV" 
}

resource "vault_auth_backend" "userpass" {
  type = "userpass"
}

# USERS
resource "vault_generic_endpoint" "user_userro" {
  depends_on           = [vault_auth_backend.userpass]
  path                 = "auth/userpass/users/userro"
  ignore_absent_fields = true

  data_json = <<EOT
{
  "policies": ["db-ro"],
  "password": "userro"
}
EOT
}

resource "vault_generic_endpoint" "user_userrw" {
  depends_on           = [vault_auth_backend.userpass]
  path                 = "auth/userpass/users/userrw"
  ignore_absent_fields = true

  data_json = <<EOT
{
  "policies": ["db-all", "db-ro"],
  "password": "userrw"
}
EOT
}

# POLICIES
# Read-Only access policy
resource "vault_policy" "dbro" {
  name   = "db-ro"
  policy = file("policies/dbro.hcl")
}

# All permissions access policy
resource "vault_policy" "dball" {
  name   = "db-all"
  policy = file("policies/dball.hcl")
}


# DB
resource "vault_mount" "mariadb" {
  path = "mariadb"
  type = "database"
}

resource "vault_database_secret_backend_connection" "mariadb_connection" {
  backend       = vault_mount.mariadb.path
  name          = "mariadb"
  allowed_roles = ["db-ro", "db-all"]
  verify_connection = true

  mysql{
    connection_url = "{{username}}:{{password}}@tcp(192.168.11.71:3306)/"
  }

# note that I have my database address hardcoded and I'm using my lan IP, since I'm running the mysql client directly from my host and vault/mariadb are running inside containers with their ports exposed.

  data = {
    username = "vault"
    password = "myvaultsecretpw"
  } 

}

resource "vault_database_secret_backend_role" "role" {
  backend             = vault_mount.mariadb.path
  name                = "db-ro"
  db_name             = vault_database_secret_backend_connection.mariadb_connection.name
  creation_statements = ["GRANT SELECT ON *.* TO '{{name}}'@'%' IDENTIFIED BY '{{password}}';"]
}

resource "vault_database_secret_backend_role" "role-all" {
  backend             = vault_mount.mariadb.path
  name                = "db-all"
  db_name             = vault_database_secret_backend_connection.mariadb_connection.name
  creation_statements = ["GRANT ALL ON *.* TO '{{name}}'@'%' IDENTIFIED BY '{{password}}';"]
}
Enter fullscreen mode Exit fullscreen mode

Policy files used in previous code listing:

  • policies/dball.hcl 
path "mariadb/creds/db-all" {
  policy = "read"
  capabilities = ["list"]
}
Enter fullscreen mode Exit fullscreen mode
  • policies/dbro.hcl 
path "mariadb/creds/db-ro" {
  policy = "read"
  capabilities = ["list"]
}
Enter fullscreen mode Exit fullscreen mode

After this, the usual process:
terraform init
terraform apply

And we have everything ready for some testing.

Testing

Testing the RO user

Login to vault

$ vault login -method userpass username=userro
Password (will be hidden): 
Success! You are now authenticated. The token information displayed below
is already stored in the token helper. You do NOT need to run "vault login"
again. Future Vault requests will automatically use this token.
(...)
Enter fullscreen mode Exit fullscreen mode

request DB credentials

$ vault read mariadb/creds/db-ro
Key                Value
---                -----
lease_id           mariadb/creds/db-ro/uGldyp0BAa2GhUlFyrEwuIbs
lease_duration     168h
lease_renewable    true
password           8vykdcZNHp-I0pajVtoN
username           v_userpass-d_db-ro_75wxnJaL69FW4
Enter fullscreen mode Exit fullscreen mode

Login to DB
(REMEMBER: The next cmd is a really BAD PRACTICE !!! )

$ mysql -h 127.0.0.1 -u v_userpass-d_db-ro_75wxnJaL69FW4 -p'8vykdcZNHp-I0pajVtoN'

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 101
Server version: 10.3.13-MariaDB-1:10.3.13+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
Enter fullscreen mode Exit fullscreen mode

... try to do stuff

MariaDB [(none)]> create database my_database;
ERROR 1044 (42000): Access denied for user 'v_userpass-d_db-ro_75wxnJaL69FW4'@'%' to database 'my_database'

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_db              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use my_db;

Database changed
MariaDB [my_db]> show tables;
+-----------------+
| Tables_in_my_db |
+-----------------+
| my_table        |
+-----------------+
1 row in set (0.00 sec)

MariaDB [my_db]> select * from my_table;
Empty set (0.00 sec)

MariaDB [my_db]>
Enter fullscreen mode Exit fullscreen mode

Testing the RW user

Login to vault

$ vault login -method userpass username=userrw
Password (will be hidden): 
Success! You are now authenticated. The token information displayed below
is already stored in the token helper. You do NOT need to run "vault login"
again. Future Vault requests will automatically use this token.
(...)
Enter fullscreen mode Exit fullscreen mode

request DB credentials

$ vault read mariadb/creds/db-all
Key                Value
---                -----
lease_id           mariadb/creds/db-all/GHRHvpuqa2ITP9tX54YHEePl
lease_duration     168h
lease_renewable    true
password           L--8mPBoprFZcaItINKI
username           v_userpass-j_db-all_DMwlhs9nGxA8
Enter fullscreen mode Exit fullscreen mode

Login to DB
(REMEMBER AGAIN: The next cmd is a really BAD PRACTICE !!! )

$ mysql -h 127.0.0.1 -u v_userpass-j_db-all_DMwlhs9nGxA8 -p'L--8mPBoprFZcaItINKI'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 109
Server version: 10.3.13-MariaDB-1:10.3.13+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
Enter fullscreen mode Exit fullscreen mode

... try to do stuff

MariaDB [(none)]> create database my_database;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use my_db;
Database changed

MariaDB [my_db]> create table the_table (i integer);
Query OK, 0 rows affected (0.03 sec)

MariaDB [my_db]> show tables;
+-----------------+
| Tables_in_my_db |
+-----------------+
| my_table        |
| the_table       |
+-----------------+
2 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

And that's it. We now have the base for our MariaDB dynamic/temporary users.

Top comments (0)

Read next

bccaxias profile image

Levantando um SQL Server RDS em AWS

Bruno Caxias -

lakhera2015 profile image

AWS Under the Hood - Day 4

Prashant Lakhera -

devleader profile image

API Gateways in Zuplo – Creating an ASCII Art API

Dev Leader -

paulike profile image

Numeric Type Conversions

Paul Ngugi -