Vault generates Postgresql credentials dynamically based on configured roles. This can increase our security, reduce secret sprawl and help auditing by having every service access the database with unique credentials.

For more information on the Postgresql Secret Backend visit here and API docs here.

Requirements To Get Started

Install Postgresql

brew install postgres

For additional installation instructions: Postgresql

Install Vault

brew install vault

For additional installation instructions: Vault

The Museum of Neo-Pre-Post-Modern Art

So why am I interested in using Vault and Posgresql?

Well.....I have just started a new Museum, The Museum of Neo-Pre-Post-Modern Art (or as the kids call it: MoNPPMA), and it is going to revolutionize the art world! We are going to display the finest of fine art, store all of our art in the most modern of modern databases AND allow any members of the museum to have access to all our data (using the most fine and modern of techniques).

So now our job is to setup the Database and Roles for each of different access levels using Vault and Postgresql.

We need 3 Roles for users of our Database:

Admin - Can do anything
Curator - Can add new art to our database
Member - Can only read from our database

Thanks do some generous donations from some generous patrons (who wish to stay anonymous), we have 3 pieces of Art so far:

Hamptons Road - Frank Stella - 1972

rsz_1hamptons_road

L.A. Cone - Richard Serra - 1986

L.A.Cone

Dhotel Nuance d'abricot - Jean Dubuffet - 1947

rsz_dhotel_nuance_dabricot

With our 3 pieces of art all ready, it's time to get our infrastructure setup and open ourselves up and start taking this Art World by storm!

Setting up our Database

Create our Museum Database:

createdb museum

Connect to our Museum Database:

psql museum

Create a role for Vault to be able to generate Users:

CREATE ROLE vault_user WITH SUPERUSER LOGIN CREATEROLE;

Create our Artworks table:

CREATE TABLE artworks (id SERIAL PRIMARY KEY, title TEXT, year INTEGER);

Insert some Art:

INSERT INTO artworks (title, year) VALUES ('Hamptons Road', 1972);

Setting up our Vault Server & Auditing

Start Vault Server

vault server -dev

Set Vault Address to HTTP

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

Export our Vault Token

export VAULT_TOKEN=<INSERT YOUR VAULT TOKEN HERE>

Enable Auditing

vault audit-enable file file_path=vault_audit.log log_raw=true hmac_accessor=false

Tail the Audit Logs

tail -F vault_audit.log | while read line; do echo "$line" | jq; done

For installation instructions for jq

Configuring our Database

Mount our Database Backend:

vault mount database

Configure our settings for our Database Backend:

vault write database/config/postgresql_museum \
    plugin_name=postgresql-database-plugin \
    allowed_roles="admin,curator,member" \
    connection_url="postgresql://vault_user@localhost:5432/museum?sslmode=disable"

You should see a message like the following:

The following warnings were returned from the Vault server:
* Read access to this endpoint should be controlled via ACLs as it will return the connection details as is, including passwords, if any.

There are a lot of things happening here, so lets break down the Database settings piece by piece:

Name our Database Settings:

vault write database/config/postgresql_museum

Note: We could call this config anything we want!

Use the Postgresql Database Plugin:

plugin_name=postgresql-database-plugin

Configure our 3 roles:

allowed_roles="admin,curator,member"

Set our connection URI:

connection_url="postgresql://vault_user@localhost:5432/museum?sslmode=disable"

vault_user is our user.
localhost:5432 is the Network location and Port.
museum is our database.
We disable sslmode since we are working locally (and I'm too lazy to configure it).

More information about Postgresql connection URI here.

Configuring our Database Roles

Creating the Admin Role


We next need to tell Vault what permissions each Role will have, how long Users created with Role will be valid for, and what to do when revoking that User's access.

Here is an example of creating the Admin:

vault write database/roles/admin \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\" 
    WITH SUPERUSER LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

There are a lot of things happening here, so lets it break down piece by piece:

Name our Role:

vault write database/roles/curator

Configure which database settings the Role is for:

db_name=postgresql_museum

What SQL to run when creating the Role:

creation_statements="CREATE ROLE \"{{name}}\"
    WITH SUPERUSER LOGIN PASSWORD '{{password}}'
    VALID UNTIL '{{expiration}}';"

For more information on Postgresql Roles: Docs.


What SQL to run when revoking the Role:

revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"

How long the User is valid for:

default_ttl="1h"

The maximum time the User is valid for if extended:

max_ttl="24h"

Creating the Curator Role


vault write database/roles/curator  \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\" 
    WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
    GRANT UPDATE ON artworks TO \"{{name}}\"; \
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

Users created with the Curator role we allow to update the artworks table, and to be able to read from any of the tables in the public schema:

"CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';

GRANT UPDATE ON artworks TO \"{{name}}\";
    
GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";"

Creating the Member Role


vault write database/roles/member \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\"
    WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

Users created with the Member role we ONLY allow to read from any of the tables in the public schema:

"CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';

GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";"
Generating a Password, Logging in, using our Database

Admin


Generate a username and password for a Admin:

vault read database/creds/admin

You should see something like:

Key             Value
---             -----
lease_id        database/creds/admin/37e7cfb1-8cbc-4251-76c7-ba9ef7ad8979
lease_duration  1h0m0s
lease_renewable true
password        A1a-70yxsqxw52z18s25
username        v-root-admin-u038p31q1008q26zw4u8-1511634378

Login:

psql users --username v-root-admin-u038p31q1008q26zw4u8-1511634378 --password

# And type in the password:  A1a-70yxsqxw52z18s25

Create the Artists Table:

CREATE TABLE artists (id serial PRIMARY KEY, first_name TEXT, last_name TEXT);


INSERT INTO artists (first_name, last_name) VALUES ('Frank', 'Stella');
INSERT INTO artists (first_name, last_name) VALUES ('Richard', 'Serra');
INSERT INTO artists (first_name, last_name) VALUES ('Jean', 'Dubuffet');


INSERT INTO artworks (title, year) VALUES ('Dhotel Nuance d''abricot', 1947);


Curator


Generate a username and password for a Curator:

vault read database/creds/curator

You should see something like:

Key             Value
---             -----
lease_id        database/creds/curator/3fded819-956b-542f-6fea-7dec76653f94
lease_duration  1h0m0s
lease_renewable true
password        A1a-uwq9v2wuvpuwqu0r
username        v-root-curator-10u4q2z899127x27pp29-1511634462

Login:

psql users --username v-root-curator-10u4q2z899127x27pp29-1511634462 --password

# And type in the password:  A1a-uwq9v2wuvpuwqu0r

Insert into the Artworks Table:

INSERT INTO artworks (title, year) VALUES ('L.A. Cone', 1986);

In addition to inserting into the artworks table, we also have permission to alter the artworks table.

Add a Foreign Key:

ALTER TABLE artworks ADD FOREIGN KEY(artist_id) REFERENCES artists(id);

Add a Foreign Key Constraints:

ALTER TABLE artworks ADD FOREIGN KEY (artist_id) REFERENCES artists;

Associate Artists with their Art:

UPDATE artworks 
    SET artist_id = 
    (SELECT id FROM artists WHERE last_name = 'Dubuffet')
    WHERE title = 'Dhotel Nuance d''abricot';

UPDATE artworks
    SET artist_id = 
    (SELECT id FROM artists WHERE last_name = 'Serra') 
    WHERE title = 'L.A. Cone';

UPDATE artworks
    SET artist_id = 
    (SELECT id FROM artists WHERE last_name = 'Stella')
    WHERE title = 'Hamptons Road';

Curator's however do not have permissions to edit tables other than the 'artworks' table.

Try to perform an authorized action:

INSERT INTO (first_name, last_name) artists VALUES ('Richard', 'Serra');

ERROR:  permission denied for relation artists

Member


Generate a username and password for a Member:

vault read database/creds/member


Key             Value
---             -----
lease_id        database/creds/member/d2199f8a-82fb-0692-968f-9b65269099de
lease_duration  1h0m0s
lease_renewable true
password        A1a-z81t351s9v7363y8
username        v-root-member-6s9sxp662urqxu1q1357-1511634555

Login:

psql users --username v-root-readonly-5qytqu25t9t1qu6t0vyv-1511140881 --password

# And type in the password:  A1a-9x046wvswszt96ts

Read from the Artworks Table:

SELECT * FROM artworks

 id |          title          | year 
----+-------------------------+------
  1 | Hamptons Road           | 1972
  2 | Dhotel Nuance d'abricot | 1947
  3 | L.A. Cone               | 1986

Find all Artist with artwork created in the 70s:

SELECT (first_name, last_name) 
    FROM artists INNER JOIN artworks 
    ON artists.id = artworks.artist_id 
    WHERE artworks.year 
    BETWEEN 1970 AND 1979;

      row       
----------------
 (Frank,Stella)

Members are not allowed to insert into any tables.

Try to perform an unauthorized action:

INSERT INTO artworks (title, year) VALUES ('Dhotel Nuance d''abricot', 1947);

ERROR:  permission denied for relation artworks
And we did it, MoNPPMA is ready to open!

Boy sitting alone inside large gallery room with white walls viewing exhibition in Barcelona
Photo by Aaina Sharma / Unsplash

TLDR

Setting up the Database

createdb museum

psql museum

CREATE TABLE artworks (id serial, title text, year integer);

INSERT INTO artworks (title, year) VALUES ('Hamptons Road', 1972);

CREATE ROLE vault_user WITH SUPERUSER LOGIN CREATEROLE;

Setting up Vault

vault server -dev

export VAULT_ADDR=http://127.0.0.1:8200

vault mount database

Setting up the Database Settings and Roles

vault write database/config/postgresql_museum \
    plugin_name=postgresql-database-plugin \
    allowed_roles="admin,curator,member" \
    connection_url="postgresql://vault_user@localhost:5432/museum?sslmode=disable"

vault write database/roles/admin \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\" 
    WITH SUPERUSER LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

vault write database/roles/curator  \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\" 
    WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
    GRANT UPDATE ON artworks TO \"{{name}}\"; \
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

vault write database/roles/member \
    db_name=postgresql_museum \
    creation_statements="CREATE ROLE \"{{name}}\" 
    WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
    revocation_sql="SELECT revoke_access('{{name}}'); DROP user \"{{name}}\";"  \
    default_ttl="1h" \
    max_ttl="24h"

Generating Users and Logging in

vault read database/creds/admin

vault read database/creds/curator

vault read database/creds/member

psql users --username <Insert Generated Username> --password

# And type in the password
HTTP Versions of the Vault Commands

Setting up Vault

Enable Auditing
{
"type": "file",
"options": {
  "path": "vault_audit.log",
  "log_raw": "true",
   "hmac_accessor": "false"
  }
}

 curl \
    --header "X-Vault-Token: $VAULT_TOKEN" \
    --request PUT \
    --data @audit.json \
    http://127.0.0.1:8200/v1/sys/audit/bookstore-audit
Mount the Database Backend
{
  "type": "aws",
  "config": {
    "force_no_cache": true
  }
}

 curl \
    --header "X-Vault-Token: ..." \
    --request POST \
    --data @payload.json \
    http://127.0.0.1:8200/v1/sys/mounts/database

Setting up the Database Settings and Roles


Configure the Postgresql Database Plugin
{
  "plugin_name": "postgresql-database-plugin",
  "allowed_roles": "readonly",
  "connection_url": "postgresql://vault_user@localhost:5432/bookstore?sslmode=disable"
}

 curl \
    --header "X-Vault-Token: $VAULT_TOKEN" \
    --request POST \
    --data @payload.json \
    http://127.0.0.1:8200/v1/database/config/postgresql_bookstore

Configure the Roles
{
  "db_name": "postgresql_bookstore",
  "creation_statements": "CREATE ROLE \"{{name}}\" 
  WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";",
  "default_ttl":"1h",
  "max_ttl":"24h"
}

 curl \
    --header "X-Vault-Token: $VAULT_TOKEN" \
    --request POST \
    --data @payload.json \
    http://127.0.0.1:8200/v1/database/roles/readonly

Generating Users and Logging in

Generate a Username and Password for the Readonly Role
 curl \
    --header "X-Vault-Token: $VAULT_TOKEN" \
    http://127.0.0.1:8200/v1/database/creds/readonly