Qlik Talend Data Integration: Manage database access with HashiCorp Vault in a Job

[TalendSolutionExpert]

Vault is a product developed by HashiCorp, the same company known for tools like Vagrant, Packer, Terraform, or Consul. Vault main use cases include:

• General Secret Storage
• Employee Credential Storage
• API Key Generation for Scripts
• Data Encryption

In the data world and especially with the cloud, security is a critical concern. Even if cloud providers offer a broad range of security features, you have still a responsibility in your data security, at rest or in transit.

This article introduces HashiCorp Vault and demonstrates the benefits of using such a tool. The article implements one feature of HashiCorp Vault:

• Rolling users for database access

In this use case, each time a Job needs access to a database, it requests a user then at the end of the Job, the user is discarded. It is like having an access token with a time to live.

Attachments

Script files are in the attached archive named: demo-vault-scripts.zip
Jobs are in the attached archive named: demo-vault-jobs.zip

Database

Configuration

This article uses a MySQL database named demo, with one table named customer. The demonstration uses Docker to make it easier to run, but if you have a MySQL server, it is the same.

1. Using the example below:

   1. Change the root password.
   2. Create the user talend (superuser).
   3. Change the user’s password.
   4. Create a database demo.

      $ docker run --name demo-mysql \
        -e MYSQL_ROOT_PASSWORD=password123 \
        -e MYSQL_USER=talend \
        -e MYSQL_PASSWORD=talend123 \
        -e MYSQL_DATABASE=demo \
        -p 3306:3306 \
        -v /Users/mgainhao/Desktop/Vault:/tmp/Vault \
        -d mysql:5.7
      

2. Add the grant privileges to Talend, as it is needed later, giving Talend the right to create new users, as shown below:

   $ docker exec -ti demo-mysql /bin/bash
   root@297387f8b2dc:/# mysql -uroot -ppassword123 < /tmp/Vault/grant.sql
   

   This script executes the following requests:

   GRANT ALL PRIVILEGES ON *.* TO 'talend'@'%' WITH GRANT OPTION;
   FLUSH PRIVILEGES;

3. Create the table and add some data.

   1. Connect to the database and run the customers.sql script:

      $ docker exec -ti demo-mysql /bin/bash
      root@297387f8b2dc:/# mysql -utalend -ptalend123 demo < /tmp/Vault/customers.sql

   2. Validate that the table is correct:

      $ docker exec -ti demo-mysql /bin/bash
      root@297387f8b2dc:/# mysql -u talend -ptalend123
      mysql> use demo
      Database changed
      mysql> show tables;
      +----------------+
      | Tables_in_demo |
      +----------------+
      | customers      |
      +----------------+
      1 row in set (0.00 sec) 
      mysql> desc customers;
      +------------------+-------------+------+-----+---------+-------+
      | Field            | Type        | Null | Key | Default | Extra |
      +------------------+-------------+------+-----+---------+-------+
      | cust_id          | int(11)     | YES  |     | NULL    |       |
      | first_name       | varchar(50) | YES  |     | NULL    |       |
      | last_name        | varchar(50) | YES  |     | NULL    |       |
      | email            | varchar(50) | YES  |     | NULL    |       |
      | gender           | varchar(50) | YES  |     | NULL    |       |
      | credit_card      | varchar(50) | YES  |     | NULL    |       |
      | credit_card_type | varchar(50) | YES  |     | NULL    |       |
      +------------------+-------------+------+-----+---------+-------+
      7 rows in set (0.00 sec)
      
      mysql> select * FROM customers LIMIT 5;
      +---------+------------+-----------+---------------------------+--------+--------------------+---------------------------+
      | cust_id | first_name | last_name | email                     | gender | credit_card        | credit_card_type          |
      +---------+------------+-----------+---------------------------+--------+--------------------+---------------------------+
      |       1 | Nannette   | Petticrew | npetticrew0@moonfruit.com | Female | 30176537857049     | diners-club-carte-blanche |
      |       2 | Allix      | Skull     | askull1@yellowpages.com   | Female | 5100137169726930   | mastercard                |
      |       3 | Bealle     | Nansom    | bnansom2@samsung.com      | Male   | 201452480095660    | diners-club-enroute       |
      |       4 | Fraze      | Tinman    | ftinman3@google.cn        | Male   | 493631225284294425 | switch                    |
      |       5 | Sanson     | Monkton   | smonkton4@earthlink.net   | Male   | 6374832314805558   | instapayment              |
      +---------+------------+-----------+---------------------------+--------+--------------------+---------------------------+
      5 rows in set (0.00 sec)
      

Talend Job

To test your connection, build a simple Job.

1. Create a connection to retrieve the schema.

   

2. Retrieve the schema.

   

   

3. Create a Job.

   

As you can see, you have access to your data. Now, ask yourself: Do you want the username and password stored in the Job, as context, or do you want to add more security? In a traditional scenario, the username and password do not change very often, so it is a security concern.

Vault Installation

Vault is a simple Go application that can be run as a server.

1. Download Vault from the Vault web site.

2. For this demo, run it as dev.

   For more information on dev mode, see the Vault documentation page "Dev" Server Model.

   $ vault server -dev
   
   WARNING! dev mode is enabled! In this mode, Vault runs entirely in-memory
   and starts unsealed with a single unseal key. The root token is already
   authenticated to the CLI, so you can immediately begin using Vault.
   You may need to set the following environment variable:
   export VAULT_ADDR='http://127.0.0.1:8200'
   
   The unseal key and root token are displayed below in case you want to
   seal/unseal the Vault or re-authenticate.
   
   Unseal Key: wVog0ksf6V+LIAzvaStFA9qLiWyLFBJTHrIPHthTlds=
   Root Token: ec418e4f-ffe7-26af-83a2-a204f5810bba
   
   Development mode should NOT be used in production installations!
     ==> Vault server started! Log data will stream in below:

   Vault generates two things:

   • Unseal key: is used to seal/unseal the entire vault, that nobody can access the vault in case of emergency.

     For more information, see the Vault documentation page Seal/Unseal.

   • Root token: are tokens that have the root policy attached to them. Root tokens can do anything in Vault.

     For more information, see the Vault documentation page Tokens.

3. Create the environment variable VAULT_ADDR:

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

4. Enable the audit file:

   $ vault audit enable file file_path=/Users/mgainhao/vault_audit.log

Database access management

In this case, configure Vault to generate a MySQL user with the rights to access the demo database for each run of your Job.

Vault configuration

1. Enable the database secrets feature and create a configuration for your database.

   1. Enable database secrets:

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

   2. Configure the database:

      $ vault write database/config/demo \
        plugin_name=mysql-database-plugin \
       connection_url="{{username}}:{{password}}@tcp(127.0.0.1:3306)/" \
       allowed_roles="read-customers" \
       username="talend" \
       password="talend123"

   3. For more security, you can rotate the talend password:

      $ curl \
       --header "X-Vault-Token: ec418e4f-ffe7-26af-83a2-a204f5810bba" \
       --request POST \
       http://127.0.0.1:8200/v1/database/rotate-root/demo

   4. You should not be able to use talend123 as the password to connect:

      $ $ docker exec -ti demo-mysql /bin/bash
      root@297387f8b2dc:/# mysql -u talend -ptalend123
      mysql: [Warning] Using a password on the command line interface can be insecure.
      ERROR 1045 (28000): Access denied for user 'talend'@'localhost' (using password: YES)

2. Create the corresponding role: read-customers.

1. Create roles:

   $ vault write database/roles/read-customers \
    db_name=demo \
    creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON demo.* TO '{{name}}'@'%';" \
    default_ttl="10m" \
    max_ttl="1h"
   Success! Data written to: database/roles/read-customers

2. Get credentials:

   $ vault read database/creds/read-customers
   Key Value
   --- -----
   lease_id database/creds/read-customers/e5fafe75-2089-5601-7f4f-a99931079016
   lease_duration 10m
   lease_renewable true
   password A1a-5j0jMQJVUIAqPOyE
   username v-root-read-custo-sQAdqVgGzela70

   $ curl -H "X-Vault-Token: ec418e4f-ffe7-26af-83a2-a204f5810bba" -X GET http://127.0.0.1:8200/v1/database/creds/read-customers 
   {"request_id":"2121d2b0-43ac-6ec8-ca32-e53dcaed1dc7","lease_id":"database/creds/read-customers/d0ca33ea-6a26-884e-9f43-aaa66fa47438","renewable":true,"lease_duration":600,"data":{"password":"A1a-71TvYjf3vUuvr8K9","username":"v-root-read-custo-v3i79UmU2zn0av"},"wrap_info":null,"warnings":null,"auth":null}

   Vault uses a lease for each request. If you want to revoke a lease at the end of the Job, you need the lease_id.

Talend Job with Vault

1. Update the Job to add the request to a lease before running the query.

   

2. There are three parts to the Job:

   1. Request a lease and get a username and a password for the database:

      

      

      

      As you can see, you pushed the Vault token as a header. There are several ways to authenticate to Vault. This example uses the root token, in a real world, you would not.

   2. Query the table:

      

      The component is now using global variables to dynamically get the username and password.

   3. Revoke the lease:

      

      

   4. The Job can now request access to the database, query the table with the new access, and revoke the access once it is done.