Singlestore DB with MySQL plugin in Vault database secret engine

Avatar
Himanshu Sharma
  • Updated

Introduction:

Vault uses several database secret engines to generate dynamic credentials based on configured roles. It works with several different databases through a plugin interface. There are many built-in database types and an exposed framework for running custom database types for extendability.

In this guide, we are going to cover one such scenario, where it is required to set up the Vault secret engine for a Singlestore DB using the Vault MySQL plugin.

To give a short idea about SingleStore DB, it primarily stores relational data, though it can also store JSON data, graph data, and time series data. It supports blended workloads, commonly referred to as HTAP workloads, as well as more traditional OLTP and OLAP use cases. For queries, it compiles Structured Query Language (SQL) into machine code.

Reference -  https://www.singlestore.com/blog/a-brief-introduction-to-memsql/


Setup:

Our lab setup has the following prerequisites:-

  • Docker Desktop.

  • The latest available Vault binary.

On local machine (Apple Silicon M1 chip, in my case):-

  • The SingleStore Dev Image is the fastest way to develop with SingleStore on your computer or in a CI/CD environment. We need to run the following docker container locally.

Reference - https://github.com/singlestore-labs/singlestoredb-dev-image

~ % docker run  -d --name singlestoredb-dev -e ROOT_PASSWORD="rootpassword" --platform linux/amd64 -p 3306:3306 -p 8080:8080 -p 9000:9000 ghcr.io/singlestore-labs/singlestoredb-dev:latest

Unable to find image 'ghcr.io/singlestore-labs/singlestoredb-dev:latest' locally
latest: Pulling from singlestore-labs/singlestoredb-dev

...

Digest: sha256:4c4bfb5f63858ae55e8dcc4b01b10a81ac17bb88d39378d4948c957f449007ef

Status: Downloaded newer image for ghcr.io/singlestore-labs/singlestoredb-dev:latest
60950724bfc03d8afe018c216779c6122e7fecfd4b3d710e77ff1e3cc09e225d

  • Now exec to the container and log in to the Singlestore DB using ROOT_PASSWORD:

~ % docker exec -it 60950724bfc0 sh

sh-4.4$ singlestore -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.32 SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

singlestore>

  • Create a sample DB named "test", followed by TABLE named "courses" & PROCEDURE named "courses_sp":

singlestore> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (2.88 sec)

singlestore> USE test;
Database changed

singlestore> CREATE TABLE courses(course_code TEXT, section_number INT, number_students INT);
Query OK, 0 rows affected (0.25 sec)

singlestore> DELIMITER //
singlestore> CREATE OR REPLACE PROCEDURE courses_sp (course_code TEXT, section_number INT, number_students INT) AS
    ->   DECLARE
    ->     code TEXT = UCASE(course_code);
    ->     num_students INT = number_students + 1;
    ->   BEGIN
    ->     INSERT INTO courses VALUES (code, section_number, num_students);
    -> END //
Query OK, 1 row affected (0.03 sec)
singlestore> DELIMITER ;

singlestore> show procedures;
+--------------------+------------------+---------+
| Procedures_in_test | Routine Lifetime | Definer |
+--------------------+------------------+---------+
| courses_sp         | Permanent        | root@%  |
+--------------------+------------------+---------+
1 row in set (0.00 sec)

  • We will create a few records in DB now:

singlestore> CALL courses_sp("cs-101",1,13);
Query OK, 0 rows affected (0.82 sec)

singlestore> CALL courses_sp("cs-201",1,11);
Query OK, 0 rows affected (0.00 sec)

singlestore> CALL courses_sp("cs-201",2,8);
Query OK, 0 rows affected (0.00 sec)

singlestore> CALL courses_sp("cs-301",1,14);
Query OK, 0 rows affected (0.01 sec)

singlestore> SELECT * FROM courses ORDER BY course_code, section_number;
+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              14 |
| CS-201      |              1 |              12 |
| CS-201      |              2 |               9 |
| CS-301      |              1 |              15 |
+-------------+----------------+-----------------+
4 rows in set (0.13 sec)

  • We will create a ROLE named "to", followed by its CREATE USER and GRANT permissions to the user.

singlestore> CREATE ROLE 'ro';
Query OK, 0 rows affected (0.01 sec)

singlestore>
singlestore>
singlestore> SHOW ROLES;
+-------+
| Roles |
+-------+
| ro    |
+-------+
1 row in set (0.00 sec)

singlestore> CREATE USER ro;
Query OK, 0 rows affected (0.02 sec)

singlestore> GRANT SELECT ON *.* TO ro;
Query OK, 0 rows affected (0.00 sec)

  • Open a second local session, where we will run the Vault in the dev mode, it will show activities in the logs:

~ % vault server -dev -dev-root-token-id root
==> Vault server configuration:

Administrative Namespace:
             Api Address: http://127.0.0.1:8200
...

2024-07-25T12:36:40.024+0530 [INFO]  secrets.database.database_2e3b5d89: initializing database rotation queue
2024-07-25T12:36:40.024+0530 [INFO]  core: successful mount: namespace="" path=database/ type=database version="v1.17.1+builtin.vault"
2024-07-25T12:36:40.035+0530 [INFO]  secrets.database.database_2e3b5d89: populating role rotation queue
2024-07-25T12:36:40.035+0530 [INFO]  secrets.database.database_2e3b5d89: starting periodic ticker

  • Open a third local session where we will enable and configure the Vault database secret engine for MySQL with its respective plugin "mysql-database-plugin" and specify "allowed_roles" as "readonly":

~ % export VAULT_ADDR='http://127.0.0.1:8200'
~ % export VAULT_TOKEN=root

~ % vault status
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    1
Threshold       1
Version         1.17.1
Build Date      2024-06-25T16:33:25Z
Storage Type    inmem
Cluster Name    vault-cluster-c0ad328f
Cluster ID      e7ed5fbe-d1a5-fcf8-1c68-1645c52518fa
HA Enabled      false

~ % vault secrets enable database
Success! Enabled the database secrets engine at: database/

~ % vault write database/config/mysql-database \
    plugin_name=mysql-database-plugin \
    connection_url="{{username}}:{{password}}@tcp(127.0.0.1:3306)/" \
    allowed_roles="readonly" \
    username="root" \
    password="rootpassword"
Success! Data written to: database/config/mysql-database

  • Now, we will configure the Vault role named "readonly" to pass "creation_statements" (passed through readonly.sql file) to execute over Singlestore DB. These creation statements will generate a templated ROLE and USER also GRANT access to the same.

~ % cat readonly.sql
CREATE ROLE "{{name}}";
CREATE USER "{{name}}" IDENTIFIED BY "{{password}}";
GRANT ALL ON *.* TO "{{name}}";

~ % vault write database/roles/readonly \
      db_name=mysql-database \
      creation_statements=@readonly.sql \
      default_ttl=1h \
      max_ttl=24h
Success! Data written to: database/roles/readonly

  • Once configured, we can now generate dynamic credentials by hitting the "database/creds" endpoint for the role "readonly", also, we could manage leases like renew for the respective dynamic credentials:

~ % vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/Hes0rElVuzIoHrzZJMmyTe5o
lease_duration     1h
lease_renewable    true
password           a6Oqmb4WOIwv-cNvB0AS
username           v-token-readonly-BzARAEps5v5lDuB


~ % vault list sys/leases/lookup/database/creds/readonly
Keys
----
Hes0rElVuzIoHrzZJMmyTe5o

~ % LEASE_ID=$(vault list -format=json sys/leases/lookup/database/creds/readonly | jq -r ".[0]")

~ % vault lease renew database/creds/readonly/$LEASE_ID
Key                Value
---                -----
lease_id           database/creds/readonly/Hes0rElVuzIoHrzZJMmyTe5o
lease_duration     1h
lease_renewable    true

~ % vault list sys/leases/lookup/database/creds/readonly
Keys
----
Hes0rElVuzIoHrzZJMmyTe5o

~ % vault read database/creds/readonly
Key                Value
---                -----
lease_id           database/creds/readonly/k5FkVL45jW8Vz2cRtlExsMXb
lease_duration     1h
lease_renewable    true
password           qQ8fM8xgt6VBDeSyPA0-
username           v-token-readonly-A0eTOjp4zucvUWn

  • In the end, using the above newly generated credential we could try to log in to the database to check the authenticity of the credentials:

~ % docker exec -it 60950724bfc0 sh
sh-4.4$ singlestore -u v-token-readonly-A0eTOjp4zucvUWn -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7076
Server version: 5.7.32 SingleStoreDB source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

singlestore>

 

Next, if needed we can also set password_policy and username_template to customize dynamic credentials as per our requirement.

 

Conclusion:

Following this guide, you may configure Singlestore DB with the Vault database secret engine using the MySQL plugin to generate dynamic credentials for the user or application, and we could also manage leases to renew or revoke this credential (if compromised or expired per se TTL).

Articles in this section

See more

Related articles