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
L.A. Cone - Richard Serra - 1986
Dhotel Nuance d'abricot - Jean Dubuffet - 1947
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.
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"
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!
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
{
"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
{
"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
{
"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
{
"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
curl \
--header "X-Vault-Token: $VAULT_TOKEN" \
http://127.0.0.1:8200/v1/database/creds/readonly