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...

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 probablyhttp://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.conffile and updatelisten_addressesto0.0.0.0
2. Open/etc/postgresql/14/main/pg_hba.confand add this linehost 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 thedatabaseengine.

Thedatabaseengine 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://{}:{}@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 pathdatabase.
2. We are writing new config data to our database engine, we called this configapplication_dbbut 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.101is my VM's IP address so change that to yours (or 127.0.0.1 if you're running it locally). We use the defaultpostgresdatabase to connect since our uservaultdoes not have a databse nor that it needs one.
6. Supply the username & password for thevaultuser 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 \"{}\" WITH SUPERUSER ENCRYPTED PASSWORD '{}' VALID UNTIL '{}'; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"{}\";" \
 revocation_statements="REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM \"{}\"; DROP OWNED BY \"{}\"; DROP ROLE \"{}\";"

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

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

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

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 thepublicschema.
• 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 fromdatabase/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 usingdatabase/sqlpackage 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  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 SIGHUB 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() {
 <-signals

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

// Start Listening
// ... etc.

Don't forget to addos,syscall&os/signalsimports.

That snippet runs a goroutine that waits for SIGHUB 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  and password 

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 fromconfig/packages/doctrine.yml

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

Then create a new fileconfig/packages/db_connection.phplike so

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 theVaultConfigclass yet so let's create that insrc/Init/VaultConfig.php

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 filesrc/Controller/IndexController.php

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 thedatabasesecrets 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.