Dynamic PostgreSQL credentials using HashiCorp Vault (with PHP Symfony & Go examples)

I was playing around with HashiCorp Vault, trying to integrate dynamic secrets (one of many features Vault offers) with a web application.

Basically, have a web application connect to a database (PostgreSQL) using dynamically generated credentials (username & password), that you can rotate whenever you want and it'll all be transparent to your app.

Vault handles the credentials generation (and thus creating a corresponding username & password in PostgreSQL) and expiration (and thus, removing the username from the DB).

I'm by no means a Vault expert, this was actually my first hands-on with it. I thought I'd share this article with an example to:

• Be better at writing technical articles
• Learn to better articulate & communicate my thoughts
• Showcase real example of using Vault with your application as there aren't many.

Anyway, let's get into it.

Vault Initial Setup

First we need to setup some stuff in Vault. Installing Vault is really easy so go ahead and do that first.

Once you get Vault installed on your system, you can run the dev server with

vault server -dev

In the output, you'll get the three important information:

• The API endpoint (which is the same as the UI URL). If you ran the dev server without any arguments this is probably http://127.0.0.1:8200.
• The unseal token. This is used to unseal and Vault from its sealed state. Whenever Vault is rebooted and/or initialized, it starts in a sealed state so you'll need to unseal it first. We don't have to worry about this because when using dev server, Vault is already initilized and unsealed.
• The root token. The token we'll use to authenticate our requests to the API. This is only a good idea when running a dev server and trying out some stuff, but in the real world the root token is only there for emergencies and for initial setup of users/policies... etc.

Now that we have a Vault server running, leave that Terminal open and open a new one (or a new Tmux pane or whatever).

Let's configure our access to Vault.

# COnfigure our access
export VAULT_ADDR="http://127.0.0.1:8200"
export VAULT_TOKEN="THE ROOT TOKEN YOU GOT FROM RUNNING SERVER"

# verify the connection 
vault status

You should see that it says "Initialized: true" & "Sealed: false".

PostgreSQL Initial Setup

Let's do some initial setup on our PostgreSQL database. I'm using a VM (created using Vagrant) of Ubuntu 22.04 and I installed PostgreSQL 14 inside of it. Checkout out this article on DigitalOcean on how to install it.

Once it's installed let's do some setup:

# Inside the VM
sudo -i -u postgres

# Open PostgreSQL cli
psql

# and create a new "vault" user. 
CREATE ROLE "vault" WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'vault-password';

After that, let's make sure PostgreSQL accepts remote connections from outside the VM.

1. Open /etc/postgresql/14/main/postgresql.conf file and update listen_addresses to 0.0.0.0
2. Open /etc/postgresql/14/main/pg_hba.conf and add this line host all all 0.0.0.0/0 md5.

That basically tells the PostgreSQL server to listen to remote connections and not just locally (1), and the HBA config allows all users to connect from anywhere using their passwords (2).

Again, this is not a good idea for a production system as this exposes your PostgreSQL instance too much. We're only playing around here so it's okay.

Configuring Vault to use our PostgreSQL database

Now that we have both Vault & Postgres initialized, let's configure Vault to connect and manage Postgres credentials.

Connect Vault to Postgres

Vault can manage secrets using its Secrets Engines which range from AWS, GCP, Key Value, LDAP, SSH, databases... and so on. See the complete list on their docs.

Secrets Engines are Vault components that store, generate & encrypt secrets. The one that we are interested in is the database engine.

The database engine supports a wide varity of database flavors including but not limited to PostgreSQL, MySQL, Redshift and Elasticsearch.

Let's enable the engine and configure it to use our Postgrs database.

# Enable the database secrets engine
vault secrets enable database # 1

# Configure the engine to connect to our Postgres database using the user we created earlier.
vault write database/config/application_db \ # 2
    plugin_name=postgresql-database-plugin \ # 3
    allowed_roles="dbuser" \                 # 4
    connection_url="postgresql://{{username}}:{{password}}@192.168.56.101:5432/postgres" \ # 5
    username="vault" \ # 6
    password="vault-password"

Let's explain what we did here:

1. We enabled the secrets engine in Vault under the path database.
2. We are writing new config data to our database engine, we called this config application_db but you can call it whatever you want.
3. The plugin name to use. We're using PostgreSQL here.
4. The allowed roles that are to be associated with this config. Role here refers to Vault role not Postgres role. We'll create the Vault role after this.
5. Setup the connection URL to Postgres. 192.168.56.101 is my VM's IP address so change that to yours (or 127.0.0.1 if you're running it locally). We use the default postgres database to connect since our user vault does not have a databse nor that it needs one.
6. Supply the username & password for the vault user we created earlier.

Now, let's create a Vault role that will manage the credential creation in both Vault & Postgres.

When creating a role, we supply:

• Creation statements: Vault will use this to know how to create the user in Postgres whenever we ask for new credentials.
• Revocation statements: Vault will execute these commands against Postgres whenever the credentials have expired (TTL reached).
• TTL (Time-To-Live) of the credentials. Once the credentials expire Vault will execute the Revokation statements and will remove the credentials from its storage.

Here's how to do that with Postgres:

vault write database/roles/dbuser 
    db_name="app" \
    max_ttl="10m" \
    creation_statements="CREATE USER \"{{name}}\" WITH SUPERUSER ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_statements="REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"{{name}}\"; DROP OWNED BY \"{{name}}\"; DROP ROLE \"{{name}}\";"

For clarity here's what the creation & revocation statements look like:

-- Creation
CREATE USER "{{name}}" WITH SUPERUSER ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "{{name}}";

-- Revokation
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "{{name}}";
DROP OWNED BY "{{name}}";
DROP ROLE "{{name}}";

Basically, we created a Vault role that:

• Expires in 10 minutes post creation.
• Creates a Postgres user with superuser privileges, that has access to all tables in the public schema.
• Properly drops the Postgres user upon expiration.

Of course, SUPERUSER privileges are not needed and even dangerous to give to a user that only needs SELECT, UPDATE, INSERT & DELETE privileges. In production, you'll need to tune the creation statements to a more secure one: Remove SUPERUSER & only give the least needed permissions to only neccessary tables.

I added the SUPERUSER privilege and set a short TTL just for the sake of example.

Now that we have a Vault role setup, let's actually try creating some credentials, by simply reading the from database/creds/dbuser, like so:

vault read database/creds/dbuser

That will output

• The Lease ID, which you can use to re-new the lease on the credentials and not have them expire.
• The Lease duration, in our case it's 10 minutes.
• The generated username & password!

Let's test it out using psql

psql -h192.168.56.101 -u v-root-truly-Jixx1aASFjSmjjYF2Vin-1660473935 -d postgres -p

Supply the generated password and you should be in!

Example applications

Now, the strategy we're using here is our example applications need to connect to Vault and read/generate new credentials and use them before the current ones expire (i.e. before every 10 minutes).

Let's do two example application to showcase how that's done.
One using Go and one using PHP (w/ Symfony framework).

But first, let's create a table and populate it with some dummy data. With psql:

CREATE DATABASE app;
\c app
CREATE TABLE users (id serial, name VARCHAR);
INSERT INTO users (name) VALUES ('Jack Reacher');

Go example!

The Go application will expose an HTTP endpoint that will just simply
get all users from our Postgres database using Vault generated credentials.

Create a new directory and execute the following.

go mod init go-vault-example
go get -u "github.com/mittwald/vaultgo"
go get -u "github.com/gorilla/mux"
go get -u "github.com/lib/pq"

That will install our app dependencies which are

• Mux for our HTTP endpoint
• pq to enable Postgres support while using database/sql package to query our Postgres database.
• Vault Go which is a library we'll use to read credentials from our Vault server.

Here's our complete Go app:

package main

import (
    "database/sql"
    "net/http"
    "strings"
    "fmt"

    vault "github.com/mittwald/vaultgo"
    "github.com/gorilla/mux"
    _ "github.com/lib/pq"
)

type VaultCreds struct {
    Data struct {
        User        string `json:"username"`
        Password    string  `json:"password"`
    } `json:"data"`
}

type DbConnection struct {
    Dbname string
    Host string
    Port int
    User string 
    Password string
}

// Read (generate) credentials from our Vault server.
// Don't forget to update your Vault address and token.
func getDBConnectionConfig() DbConnection {
    c, err := vault.NewClient("VAULT SERVER ADDRESS", vault.WithCaPath(""), vault.WithAuthToken("VAULT AUTH TOKEN"))
    if err != nil {
        panic(err)
    }

    key := []string{"v1", "database", "creds", "dbuser"}
    options := &vault.RequestOptions{}
    response := &VaultCreds{}

    err = c.Read(key, response, options)
    if err != nil {
        panic(err)
    }

    return DbConnection{
        Dbname: "app",
        Host: "192.168.56.101",
        Port: 5432,
        User: response.Data.User,
        Password: response.Data.Password,
    }
}

// This function opens up a new Postgres connection to our server and returns it.
func openConnection() *sql.DB {
    config := getDBConnectionConfig()
    connStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", config.Host, config.Port, config.User, config.Password, config.Dbname)

    db, err := sql.Open("postgres", connStr)
    if err != nil {
        panic(err)
    }

    err = db.Ping()
    if err != nil {
        panic(err)
    }

    fmt.Printf("Connected to PostgreSQL db using user <%s> and password <%s>\n", config.User, config.Password);
    return db
}

// Factory to create the function that handles the index request "/".
// It queries the database and return a join of all names in the users table. Pretty simple.
func newIndexHandler(db *sql.DB) func(http.ResponseWriter, *http.Request) {
    return func(w http.ResponseWriter, r *http.Request) {
        rows, err := db.Query(`SELECT name FROM users`)
        if err != nil {
            panic(err)
        }
        defer rows.Close()


        names := []string{}
        for rows.Next() {
            var name string
            err = rows.Scan(&name)
            if err != nil {
                panic(err)
            }

            names = append(names, name)
        }

        w.WriteHeader(http.StatusOK)
        fmt.Fprint(w, strings.Join(names, ", "))
    }
}

func main() {
    // Setup DB connection
    db := openConnection()
    defer db.Close();

    // Setup router 
    r := mux.NewRouter()
    r.HandleFunc("/", newIndexHandler(db))
    http.Handle("/", r)

    // Start listening
    fmt.Println("Listening on 127.0.0.1:8002")
    http.ListenAndServe("127.0.0.1:8002", r)
}

That's a pretty basic Go program that fetches data from our users table in Postgres.
The only interesting part is, it read the credentials from our Vault server and uses them to connect.
Our app is completely oblivious to how those credentials are generated and when. It just reads them and uses them.

go build
./go-vault-example

#
# Output is something like
# Connected to PostgreSQL db using user <v-root-truly-OQZIsxFo71ytoXb3aIKo-1660475644> and password <4u-A74pocYWqzUaSqW5L>                                                                     │
# Listening on 127.0.0.1:8002
#

Visit http://127.0.0.1:8002 in your browser and you'll see some data output.

Now, the only time the credentials are read (and thus generated) is when we start our program. If we wanted to re-generate a new pair of credentials, we'll need to stop it and run it again which isn't really a good idea.
So let's use system signals for that by adding this snippet right before starting the HTTP server.

// Setup reload signal using SIGHUP
// Sending SIGHUP signal to our process will make it close the current DB connection
// and open a new one with newly generated credentials.
signals := make(chan os.Signal, 1)
signal.Notify(signals, syscall.SIGHUP)
go func() {
    for {
        <-signals

        fmt.Println("Reloading: Terminating current connection and creating a new one.")
        db.Close()
        db = openConnection()
    }
}()

// Start Listening
// ... etc.

Don't forget to add os, syscall & os/signals imports.

That snippet runs a goroutine that waits for SIGHUP signal.
Upon receiving it, it closes the current database connection and creates a new one, effectively re-generating new credentials.

Build and run the app again, and let's try it out.

PID=$(pgrep go-vault-example) # Get the PID of our process
kill -HUP $PID # Send a SIGHUP signal

Our program will print something like:

Reloading: Terminating current connection and creating a new one.
Connected to PostgreSQL db using user <v-root-truly-63msxAJoWUb1QJ50hxfL-1660476797> and password <oaY-a1bl4IShVzcKEoNp>

Since we have our credentials expire after 10 minutes, we just need to send a SIGHUP signal to our program before that happens.
We can set up a cron job to do so that runs every 8 minutes or something like that.

To conclude:

1. We start our program, and it generates a new pair of credentials.
2. We reload our program every 8 minutes and by that we generate new credentials.
3. The old credentials will always expire and be removed from our database (that leaves 2 minutes of the old user not being used by anyone).

That's it, that's a basic example of how you can use Vault dynamic credentials in your Go app.

PHP Example (w/ Symfony Framework)

Let's create a new Symfony 6.1 minimal application, with Doctrine ORM, Vault PHP Client & a couple of its dependencies.

composer create-project symfony/skeleton:"6.1.*" php-vault-example
cd php-vault-example
composer require orm
composer require csharpru/vault-php
composer require alextartan/guzzle-psr18-adapter
composer require laminas/laminas-diactoros

Symfony by default uses YAML files for configuration but that doesn't really fit us for this use case. We need to use PHP config files because that gives us the ability to run some custom code to get the database credentials from Vault.
First, let's remove the connection config from config/packages/doctrine.yml

doctrine:
    # Remove the "dbal" line and its children.
    dbal:

Then create a new file config/packages/db_connection.php like so

<?php

use App\Init\VaultConfig;
use Symfony\Config\DoctrineConfig;

$config = new VaultConfig();
$credentials = $config->getDbConfig();

return static function (DoctrineConfig $doctrine) use ($credentials) {
    $dbal = $doctrine->dbal()
        ->connection('default')
        ->driver('pdo_pgsql')
        ->dbname('app')
        ->serverVersion('14')
        ->user($credentials['username'])
        ->password($credentials['password'])
        ->host('192.168.56.101')
        ->port('5432');
};

We still don't have the VaultConfig class yet so let's create that in src/Init/VaultConfig.php

<?php

namespace App\Init;

use GuzzleHttp\Psr7\Uri;
use Laminas\Diactoros\RequestFactory;
use Laminas\Diactoros\StreamFactory;
use Vault\Client;
use Vault\AuthenticationStrategies\TokenAuthenticationStrategy;

class VaultConfig {

    public const VAULT_ADDR = 'http://127.0.0.1:8200';

    public const VAULT_TOKEN = 'hvs.mjlW8faLW7GHQdbxAc3t8aEz';

    public const CREDENTIALS_PATH = '/database/creds/dbuser';

    private Client $client;

    public function __construct() {
        $client = new Client(
            new Uri(self::VAULT_ADDR),
            new \AlexTartan\GuzzlePsr18Adapter\Client(),
            new RequestFactory(),
            new StreamFactory()
        );

        try {
            $authenticated = $client->setAuthenticationStrategy(new TokenAuthenticationStrategy(self::VAULT_TOKEN))
                ->authenticate();
        } catch (\Exception $e) {
            die($e->getMessage());
        }

        if (!$authenticated) {
            die("Could not authenticate to Vault server at: " . self::VAULT_ADDR);
        }

        $this->client = $client;
    }


    public function getDbConfig(): array {
        try {
            $response = $this->client->read(self::CREDENTIALS_PATH);
        } catch (\Exception $e) {
            die($e->getMessage());
        }

        return $response->getData();
    }
}

Make sure to update the VAULT_ADDR & VAULT_TOKEN to your values.
Now let's create a simple controller that will read data from our users table.

Create a new file src/Controller/IndexController.php

<?php

namespace App\Controller;

use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

class IndexController extends AbstractController {
    #[Route('/', name: 'app_index')]
    public function index(Connection $connection): JsonResponse  {
        $users = $connection->executeQuery("SELECT * FROM users")
            ->fetchAllAssociative();

        return $this->json([
            'data' => $users,
            'connection' => [
                'user' => $connection->getParams()['user'],
                'password' => $connection->getParams()['password'],
            ]
        ]);
    }
}

Let's start a local PHP server and see our response

php -S localhost:8000 -t public

Visit http://localhost:8000 you'll see a JSON response containing our users, and also the currently used PostgreSQL username/password.

Now, Symfony actually caches the config data after executing it the first time, so our credentials are only generated once and then cached which is good thing because we definetly don't want our database credentials be generated on every request.
So if we wanted to generate a new pair, all we need to do is clear the symfony cache!

./bin/console cache:clear

If you visit your web page again, you'll see a new pair of credentials being used. Same as we did for our Go app, we just need to clear the cache before our credentials expire, and we'll be good.
To conclude:

1. We start our PHP application, and it reads the config for the first time and generate new credentials.
2. We clear Symfony cache every 8 minutes or so, so that the config is re-read and new credentials get generated & used.
3. The old credentials will always expire and be removed from our database (that leaves 2 minutes of the old user not being used by anyone).

Final notes & recap

• I haven't tested this approach in a production environment and I'll sure that will bring up some nice challenges, such as what happens when your application is getting a lot of concurrent requests and you rotate your keys? You could get some downtime everytime you rotate keys so that's something to keep an eye for.
• The TTLs we used are obviously not realistic and are only meant for testing. In real life you'd want a longer TTL depending on your use cases.
• As I said in the beginning of the article, I'm by no means a Vault or Go expert (I mainly work with PHP). I only wanted to write this article to share examples of using Vault inside an application.
• If you notice anything wrong or something could be done better, I'd love your feedback.

Let's recap what we did

Let's do a recap of what we did today:

1. We set up a local dev Vault server to test things out with.
2. We set up Vault to be able to talk to PostgreSQL and dynamically generate credentials using the database secrets engine.
3. We tested out the integration by generating new credentials and seeing them also created in PostgreSQL. Whenever the credentials expired we did notice that the user disappeared from our Postgres database.
4. We created a basic Go application that reads the dynamic credentials from Vault and uses them to query the database.
5. We then added a mechanism to our Go app to be able to rotate keys by using system signals (SIGHUP)
6. We then created a simple PHP application using Symfony framework, and we did same thing as in Go.

Thanks for reading!

Comments

[b-tin]

Thanks, @breda for your post, it's very useful for me to understand vault database password rotation.
But I have questions, if we don't implement the kill-signal, the app will fail health check and recreate again -> maybe that would cause downtime. Do we have any way to be proactive to trigger rotation without modifying the application code?

[Bouchaala Reda]

Yes you're correct. Having an external trigger to rotate credentials is not the best way to do it really, I only used it for the sake of bravity and to showcase how we dynamically update our credentials.

In a production setup, you'd want an internal trigger instead, something like a time.Tick() where the duration of each tick is less than the credentials lease duration. Choosing the tick duration depends on the type of workload you have, if you have a lot of long-running connections (say max is 2 hours), then choosing a long lease duration (eg, 3 hours) and short tick duration (eg, 1 hour) will be good so that old connections can still run and new connections use the new credentials, but then again... it all depends. Hope this helps!

[Jeremy Moore]

Great write-up. I've been thinking about using Vault with our applications with Symfony (and docker and other things). This is some valuable information.

[Bouchaala Reda]

Glad I could be of help. Thanks for your feedback.