Spring boot MariaDB vault username credentials not available during startup

Question

Problem statement: java.sql.SQLSyntaxErrorException: Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : (conn=1058) Access denied for user ''@'localhost' to database 'my-db' User is not available during startup.

Vault configuration:

vault write database/config/my-db \     
plugin_name=mysql-database-plugin \
connection_url="{{username}}:{{password}}@tcp(127.0.0.1:3306)/" \                                                     
allowed_roles="healthy-role-r-wr" \
username="root" \
password="myrootpassword"

vault write database/roles/healthy-role-r-wr \
db_name=my-db \
creation_statements="CREATE USER '{{name}}'@'localhost' IDENTIFIED BY '{{password}}'; GRANT ALL PRIVILEGES ON my-db.* TO '{{name}}'@'localhost'; FLUSH PRIVILEGES;" \default_ttl="1h" \
max_ttl="24h"

I had to mention host @localhost without that I cannot connect to mysql while testing mysql command.

vault read database/creds/healthy-role-r-wr                                
Key                Value
---                -----
lease_id           database/creds/healthy-role-r-wr/DugI5aIeYPUg0lmjtcNSM87L
lease_duration     1h
lease_renewable    true
password           C1yN7Tl-00XlwkOwbFCh
username           v_root_healthy-ro_7nl7RPTqb6QkAJ

mysql -uv_root_healthy-ro_7nl7RPTqb6QkAJ -pC1yN7Tl-00XlwkOwbFCh my-db

MariaDB [my-db]> select count(*) from my_table;
1 row in set (0.025 sec)

So seems Vault is rightly configured.

application.yml

spring:
  application:
    name: my-springboot-app
  cloud:
    vault:
      host: 127.0.0.1
      port: 8200
      scheme: http
      authentication: token
      token: TOKEN
      generic:
        enabled: true
      database:
        backend: mysql
      kv:
        enabled: true
        backend: tcds/kv/my-springboot-app
        application-name: my-springboot-app
  config:
    import: vault://secret/my-springboot-app
  datasource:
    url: jdbc:mariadb://127.0.0.1:3306/my-db
    driver-class-name: org.mariadb.jdbc.Driver
    platform: mariadb
    role: healthy-role-r-wr
    #username: ${dbusername} 
    #password: ${dbpassword}
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update

POM:

<parent>
<groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.4</version>
    <relativePath /> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>11</java.version>
    <spring-cloud.version>2020.0.1</spring-cloud.version>
</properties>

Dependancies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-starter-vault-config</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-vault-config-databases</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
</dependency>
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-dependencies</artifactId>
            <version>${spring-cloud.version}</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>

In vault I have created a secret, essentially creating a KV with username and password as showed in application.yml above. If I pass this like:

#username: ${dbusername}
#password: ${dbpassword}

Then application start up cleanly and I can make API calls to get the user list. But this is not the way it suppose to be. As the username and password is being generated dynamically and must be used by application.

It seems I am missing some configuration since spring boot app can recognize the vault and it can communicate with it, just that it is not able to find the way to read the dynamic credentials.

Answer 1

Your issue sounds similar in ways to the one I ran into and have been working through for a few days so I'm posting here in case it helps you or anyone else.

If you are storing the username and/or password in your kv with property names that match what are going to be loaded dynamically by the database plugin, there will be duplicate property loaders with the same key, and the kv values will take precedence.

For example in my case I had done

vault kv put secret/foo spring.datasource.password=wrongpassword

(the default key for the password used by the DB plugin to load the password from is spring.datasource.password)

While I could call

vault read database/creds/some-role

and get valid credentials, when I ran my SpringBoot app I found the password being used was the kv value. When debugging, I saw this was due to property loader precedence.

If you clear your kv of any key/value pair that would be used by the DB plugin, it should use the dynamic credentials generated by the role you defined.

Also, my understanding is that to configure the datasource you would need a bootstrap.yml file that would contain your vault information to modify startup behavior of the SpringBoot app.

spring:
   application:
      name: <app name>
   cloud:
      vault:
         database:
            enabled: true
            role: <role>
            backend: database
         host: <host>
         port: <port>
         scheme: https
         authentication: token
         token: <your vault token>
         uri: <vault URL>

This other answer was also helpful to me. It allowed me to see my app's property values spring cloud - bootstrap.properties outside my jar

Answer 2

The role you created should be listed under spring.cloud.vault.database as below

database:
  enabled: true
  role: healthy-role-r-wr
  backend: database # not mysql as you have it in your config

I have created a video on this in detail if you want to go through it in depth.