Complete Beginner-Friendly Hands-On Tutorial for Ubuntu Linux

1. Lab Objective

In this lab we will build the following environment on a single Ubuntu Linux server:

                         Ubuntu Server
                              |
              +---------------+---------------+
              |                               |
              v                               v
      HashiCorp Vault                    PostgreSQL
       127.0.0.1:8200                  127.0.0.1:5432
              |                               |
              |                               |
         userpass auth                        |
              |                               |
          student1                            |
              |                               |
     postgresql-engine-admin                  |
         Vault Policy                         |
              |                               |
              v                               |
       database/ Secrets Engine --------------+
              |
              v
        PostgreSQL Plugin
   postgresql-database-plugin
              |
              v
          app-crud
        Vault DB Role
              |
              v
    Dynamic PostgreSQL Login
              |
              v
    PostgreSQL Role: app_crud
              |
              v
       public.app_data
              |
      SELECT / INSERT /
       UPDATE / DELETE

By the end of this lab, students will understand:

how Vault’s database secrets engine works;
how PostgreSQL integration works;
how to install PostgreSQL on Ubuntu;
how to prepare PostgreSQL for Vault;
how to create a dedicated database account for Vault;
how to create a PostgreSQL group role;
how to enable the Vault database secrets engine;
how to configure the PostgreSQL database plugin;
how to enable userpass;
how to create one Vault user;
how to create and attach a Vault policy;
how Vault ACL capabilities work;
how to Create, Read, Update, List, and Delete Vault database roles;
how Vault dynamically creates PostgreSQL usernames and passwords;
how generated credentials receive limited PostgreSQL privileges;
how to perform PostgreSQL SELECT, INSERT, UPDATE, and DELETE operations;
how Vault leases work;
how to renew database credentials;
how to revoke database credentials;
how revocation removes the PostgreSQL login;
how to test both successful and denied operations.

2. Version Validation

This tutorial targets the current Vault 2.x command and API model.

As of August 9, 2026, HashiCorp’s official installation documentation identifies:

Vault 2.0.4

as the current downloadable Vault release, released on August 4, 2026.

Vault’s current PostgreSQL database plugin remains:

postgresql-database-plugin

and the documented workflow remains:

database/config/<connection>
database/roles/<role>
database/creds/<role>

This lab deliberately installs PostgreSQL from the Ubuntu repository instead of forcing one PostgreSQL major release. This makes the tutorial usable on multiple supported Ubuntu releases.

Always verify your installed versions before proceeding.

3. Important Terminology

A very important point for beginners:

Vault does not have a separate secrets-engine type named:

postgresql

Instead, Vault has:

database

as the secrets engine.

Inside that engine we configure the:

postgresql-database-plugin

Therefore:

Vault Database Secrets Engine
            +
PostgreSQL Database Plugin
            =
Dynamic PostgreSQL Credentials

PostgreSQL is officially supported by Vault’s database secrets engine for dynamic and static database credentials.

4. Lab Components

We will use these names throughout the tutorial.

Ubuntu server
    |
    +-- Vault
    |      Address: http://127.0.0.1:8200
    |      Dev root token: root
    |
    +-- Vault secrets engine
    |      database/
    |
    +-- Vault database connection
    |      vaultlab-postgres
    |
    +-- Vault dynamic DB role
    |      app-crud
    |
    +-- Vault auth method
    |      userpass/
    |
    +-- Vault user
    |      student1
    |
    +-- Vault policy
    |      postgresql-engine-admin
    |
    +-- PostgreSQL
           Address: 127.0.0.1:5432
           Database: vaultlab

           Vault management user:
               vaultadmin

           PostgreSQL permission role:
               app_crud

           Test table:
               public.app_data

Passwords used for this training lab:

PostgreSQL Vault account:
Username: vaultadmin
Password: VaultAdmin2026

Vault userpass account:
Username: student1
Password: StudentLab2026

These are intentionally simple, fixed lab-only credentials.

Do not use these passwords in production.

5. Important Security Warning

This lab uses:

Vault development mode
Vault root token = root
HTTP instead of HTTPS
PostgreSQL on localhost
Fixed demonstration passwords
Broad Vault database-engine permissions

This is appropriate for:

training
classrooms
student labs
experimentation
learning

It is not a production Vault architecture.

A production Vault deployment should use persistent storage, TLS, proper initialization/unseal strategy, restricted administrative policies, audit devices, secure database networking, and separate operator/application identities.

6. Understanding the Three Permission Layers

Students should understand that there are three different security layers.

Layer 1 — Vault Authentication

Question:

Who are you?

Answer:

userpass
student1

Layer 2 — Vault Authorization

Question:

What may student1 do inside Vault?

Answer:

Controlled by:

postgresql-engine-admin

Vault policy.

Layer 3 — PostgreSQL Authorization

Question:

What may the dynamically generated PostgreSQL user do?

Answer:

Controlled by PostgreSQL role:

app_crud

The dynamic login will receive membership in:

app_crud

7. Why We Use a PostgreSQL Group Role

Instead of giving database permissions individually to every generated username, we create:

app_crud

as a PostgreSQL NOLOGIN role.

It receives permissions such as:

SELECT
INSERT
UPDATE
DELETE

Vault-created PostgreSQL accounts simply become members of:

app_crud

Conceptually:

                         app_crud
                       PostgreSQL Role
                             |
                +------------+------------+
                |            |            |
                v            v            v
          Dynamic User 1 User 2      User 3

PostgreSQL role membership allows privileges assigned to one role to be inherited by member roles.

This makes database permission management much cleaner.

8. Step 1 — Update Ubuntu

Run:

sudo apt update

Upgrade packages:

sudo apt upgrade -y

Install basic utilities:

sudo apt install -y \
  curl \
  wget \
  gpg \
  lsb-release \
  jq

Verify:

jq --version

9. Step 2 — Install PostgreSQL

Install PostgreSQL server and client packages:

sudo apt install -y \
  postgresql \
  postgresql-client \
  postgresql-contrib

10. Step 3 — Verify PostgreSQL Version

Run:

psql --version

Example:

psql (PostgreSQL) 16.x

Your PostgreSQL major/minor version may differ depending on your Ubuntu release.

That is acceptable for this lab.

11. Step 4 — Start PostgreSQL

Run:

sudo systemctl enable --now postgresql

Check:

sudo systemctl status postgresql --no-pager

You should see:

active

12. Step 5 — Verify PostgreSQL Is Listening

Run:

pg_isready

Expected:

/var/run/postgresql:5432 - accepting connections

Also check port 5432:

ss -lnt | grep 5432

13. Step 6 — Connect as PostgreSQL Administrator

Ubuntu normally creates a Linux account named:

postgres

and a PostgreSQL superuser named:

postgres

Test it:

sudo -u postgres psql

You should enter:

postgres=#

Exit:

\q

14. Step 7 — Create the Lab Working Directory

Run:

mkdir -p "$HOME/vault-postgresql-lab"

Set:

export LAB_DIR="$HOME/vault-postgresql-lab"

Verify:

echo "$LAB_DIR"

Move into it:

cd "$LAB_DIR"

15. Step 8 — Create the PostgreSQL Vault Administrator

Vault should not connect to PostgreSQL using the actual:

postgres

superuser.

HashiCorp recommends using a database-specific account for Vault with only the permissions necessary to create, update, and delete managed database users.

Create:

vaultadmin

Run:

sudo -u postgres psql <<'SQL'
SET password_encryption = 'scram-sha-256';

CREATE ROLE vaultadmin
WITH
    LOGIN
    CREATEROLE
    PASSWORD 'VaultAdmin2026';
SQL

Expected:

SET
CREATE ROLE

16. Why Does `vaultadmin` Need CREATEROLE?

Vault must dynamically execute operations such as:

CREATE ROLE ...
ALTER ROLE ...
DROP ROLE ...

The account therefore needs permission to manage the PostgreSQL roles created by Vault.

PostgreSQL documents that a CREATEROLE account can manage roles for which it has the appropriate administrative relationship; roles created by such an account automatically receive that relationship for their creator.

17. Step 9 — Create the Application Permission Role

Create:

app_crud

Run:

sudo -u postgres psql <<'SQL'
CREATE ROLE app_crud NOLOGIN;
SQL

Expected:

CREATE ROLE

This role cannot directly log in.

Its purpose is only to hold database privileges.

18. Step 10 — Allow Vault to Grant `app_crud`

Vault’s:

vaultadmin

account must be allowed to grant membership in:

app_crud

to dynamically created accounts.

Run:

sudo -u postgres psql <<'SQL'
GRANT app_crud TO vaultadmin WITH ADMIN OPTION;
SQL

Expected:

GRANT ROLE

WITH ADMIN OPTION allows vaultadmin to grant and revoke membership in app_crud.

19. Step 11 — Create the Training Database

Create:

vaultlab

Run:

sudo -u postgres createdb vaultlab

Verify:

sudo -u postgres psql -lqt | cut -d '|' -f 1

You should find:

vaultlab

20. Step 12 — Create a Test Table

Create:

public.app_data

Run:

sudo -u postgres psql -d vaultlab <<'SQL'
CREATE TABLE public.app_data (
    id BIGSERIAL PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0
);

INSERT INTO public.app_data
    (item_name, quantity)
VALUES
    ('linux-server', 10),
    ('vault-server', 5),
    ('postgresql-server', 3);
SQL

Expected:

CREATE TABLE
INSERT 0 3

21. Step 13 — Examine the Table

Run:

sudo -u postgres psql \
  -d vaultlab \
  -c "SELECT * FROM public.app_data;"

Expected:

 id |      item_name       | quantity
----+----------------------+----------
  1 | linux-server         |       10
  2 | vault-server         |        5
  3 | postgresql-server    |        3

22. Step 14 — Restrict Public Schema Creation

To make our later negative permission tests deterministic, remove general CREATE permission from the public schema.

Run:

sudo -u postgres psql \
  -d vaultlab \
  -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;"

Expected:

REVOKE

23. Step 15 — Give `app_crud` Database Permissions

Run:

sudo -u postgres psql -d vaultlab <<'SQL'
GRANT CONNECT
ON DATABASE vaultlab
TO app_crud;

GRANT USAGE
ON SCHEMA public
TO app_crud;

GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE public.app_data
TO app_crud;

GRANT USAGE, SELECT
ON SEQUENCE public.app_data_id_seq
TO app_crud;
SQL

Expected:

GRANT
GRANT
GRANT
GRANT

The sequence permission is necessary because our BIGSERIAL ID uses:

app_data_id_seq

when rows are inserted.

24. Step 16 — Allow `vaultadmin` to Connect

Run:

sudo -u postgres psql \
  -d vaultlab \
  -c "GRANT CONNECT ON DATABASE vaultlab TO vaultadmin;"

Expected:

GRANT

25. PostgreSQL Permission Architecture

We now have:

postgres
  |
  +-- vaultadmin
  |      LOGIN
  |      CREATEROLE
  |      ADMIN OPTION on app_crud
  |
  +-- app_crud
         NOLOGIN

         CONNECT vaultlab
         USAGE public schema
         SELECT app_data
         INSERT app_data
         UPDATE app_data
         DELETE app_data
         sequence access

Later:

Vault
   |
   +-- creates dynamic-user-123
              |
              +-- member of app_crud

26. Step 17 — Verify PostgreSQL Roles

Run:

sudo -u postgres psql \
  -c "\du vaultadmin"

Then:

sudo -u postgres psql \
  -c "\du app_crud"

You should see:

vaultadmin

with:

Create role

and:

app_crud

without login privileges.

27. Step 18 — Test Vault’s PostgreSQL Account

This test is extremely important.

Run:

PGPASSWORD='VaultAdmin2026' \
psql \
  -h 127.0.0.1 \
  -U vaultadmin \
  -d vaultlab \
  -c "SELECT current_user, current_database();"

Expected:

 current_user | current_database
--------------+-----------------
 vaultadmin   | vaultlab

If this command fails, do not continue to Vault yet.

Vault will use essentially the same type of TCP connection.

28. Troubleshooting PostgreSQL Password Authentication

If you receive:

password authentication failed

find the PostgreSQL HBA configuration:

sudo -u postgres psql \
  -tAc "SHOW hba_file;"

Then inspect it:

HBA_FILE="$(
  sudo -u postgres psql \
    -tAc "SHOW hba_file;" \
    | xargs
)"

Display active rules:

sudo grep -vE '^[[:space:]]*(#|$)' "$HBA_FILE"

You need an appropriate host rule for connections from:

127.0.0.1

PostgreSQL client authentication is controlled by pg_hba.conf; changes require the configuration to be reloaded.

Modern PostgreSQL supports SCRAM-SHA-256 password authentication.

29. PostgreSQL Checkpoint

Do not continue unless all of these work:

[ ] PostgreSQL is running
[ ] port 5432 is available
[ ] vaultlab exists
[ ] public.app_data exists
[ ] vaultadmin exists
[ ] app_crud exists
[ ] vaultadmin has CREATEROLE
[ ] vaultadmin has ADMIN OPTION on app_crud
[ ] vaultadmin can connect using TCP/password

30. Step 19 — Install Vault

Add the official repository signing key:

wget -O- https://apt.releases.hashicorp.com/gpg \
  | sudo gpg --dearmor \
  -o /usr/share/keyrings/hashicorp-archive-keyring.gpg

Add the repository:

echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(grep -oP '(?<=UBUNTU_CODENAME=).*' /etc/os-release || lsb_release -cs) main" \
| sudo tee /etc/apt/sources.list.d/hashicorp.list

Update:

sudo apt update

Install Vault:

sudo apt install -y vault

31. Step 20 — Verify Vault Version

Run:

vault version

For the current release at the time this tutorial was prepared, you should see the Vault 2.0.4 release line.

The exact build suffix may differ.

32. Step 21 — Stop Any Existing Vault Service

Because this tutorial uses Vault development mode:

sudo systemctl stop vault 2>/dev/null || true

Check:

pgrep -a vault || true

Ideally there should be no existing Vault process.

33. Step 22 — Start Vault Development Server

Run:

nohup vault server \
  -dev \
  -dev-root-token-id='root' \
  -dev-listen-address='127.0.0.1:8200' \
  > "$LAB_DIR/vault.log" 2>&1 &

Save its PID:

echo $! > "$LAB_DIR/vault.pid"

Wait:

sleep 2

34. Step 23 — Configure the Vault CLI

Run:

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

Set root token:

export VAULT_TOKEN='root'

Check:

echo "$VAULT_ADDR"

Expected:

http://127.0.0.1:8200

35. Step 24 — Verify Vault

Run:

vault status

Important fields:

Initialized    true
Sealed         false

36. Step 25 — Verify Root Identity

Run:

vault token lookup

You should see:

policies    [root]

Everything through the initial Vault configuration will be performed using the root token.

Later we remove it.

37. Step 26 — Examine Existing Secrets Engines

Run:

vault secrets list

Look for:

database/

On a fresh dev server, it should normally not exist.

38. Step 27 — Enable the Database Secrets Engine

Run:

vault secrets enable database

Expected:

Success! Enabled the database secrets engine at: database/

This is the documented way to enable Vault’s database secrets engine.

39. Step 28 — Verify the Engine

Run:

vault secrets list

You should now find:

database/

40. Step 29 — Configure the PostgreSQL Connection

Create the database connection:

vault write database/config/vaultlab-postgres \
  plugin_name='postgresql-database-plugin' \
  allowed_roles='app-crud' \
  connection_url='postgresql://{{username}}:{{password}}@127.0.0.1:5432/vaultlab?sslmode=disable' \
  username='vaultadmin' \
  password='VaultAdmin2026' \
  password_authentication='scram-sha-256' \
  max_open_connections=5

Expected:

Success! Data written to: database/config/vaultlab-postgres

Vault verifies the connection by default when a database configuration is created, so success here is an important integration checkpoint. The current PostgreSQL plugin supports the templated connection URL, postgresql-database-plugin, and scram-sha-256.

41. What Did That Command Do?

This part:

database/config/vaultlab-postgres

creates a named Vault database configuration.

This:

plugin_name=postgresql-database-plugin

tells Vault:

Use PostgreSQL.

This:

allowed_roles=app-crud

means:

Only the Vault database role named app-crud
may use this database connection.

This:

{{username}}
{{password}}

allows Vault to insert:

vaultadmin
VaultAdmin2026

without hardcoding those values directly into the URL itself.

42. Why `sslmode=disable`?

Both Vault and PostgreSQL are running on:

127.0.0.1

on the same training server.

Therefore this beginner lab disables PostgreSQL TLS to remove certificate configuration from the exercise.

For a production database:

use TLS
validate certificates
protect database traffic

The PostgreSQL Vault plugin supports PostgreSQL SSL connection parameters.

43. Why `password_authentication=scram-sha-256`?

Vault’s PostgreSQL plugin supports:

password
scram-sha-256

When configured for:

scram-sha-256

Vault can prepare PostgreSQL-compatible SCRAM password material rather than exposing generated passwords as plaintext password values in PostgreSQL SQL logging. PostgreSQL 10 or newer is required for this mode.

44. Step 30 — Read the Database Configuration

Run:

vault read database/config/vaultlab-postgres

You should see information such as:

allowed_roles
connection_details
plugin_name

Vault does not simply return the configured database password to you.

45. Step 31 — List Database Connections

Run:

vault list database/config

Expected:

Keys
----
vaultlab-postgres

46. Vault-to-PostgreSQL Checkpoint

At this stage:

Vault
  |
  |
database/
  |
  |
vaultlab-postgres
  |
  |
postgresql-database-plugin
  |
  |
127.0.0.1:5432
  |
  |
vaultadmin
  |
  |
PostgreSQL vaultlab

The connection is working.

47. Step 32 — Create SQL for Dynamic Accounts

Now define what Vault should do whenever credentials are requested.

Create:

cat > "$LAB_DIR/create-app-crud.sql" <<'EOF'
CREATE ROLE "{{name}}"
WITH LOGIN
PASSWORD '{{password}}'
VALID UNTIL '{{expiration}}'
INHERIT;

GRANT app_crud TO "{{name}}";
EOF

Display:

cat "$LAB_DIR/create-app-crud.sql"

48. Understanding the Template Variables

Vault replaces:

{{name}}

with a dynamically generated PostgreSQL username.

Vault replaces:

{{password}}

with a dynamically generated password.

Vault replaces:

{{expiration}}

with the credential expiration time.

HashiCorp’s current PostgreSQL plugin documents all three template fields for creation_statements.

49. Step 33 — Create Renewal SQL

Create:

cat > "$LAB_DIR/renew-app-crud.sql" <<'EOF'
ALTER ROLE "{{name}}"
VALID UNTIL '{{expiration}}';
EOF

Display:

cat "$LAB_DIR/renew-app-crud.sql"

When Vault renews a credential lease, this changes PostgreSQL’s:

VALID UNTIL

timestamp.

The PostgreSQL plugin supports renew_statements with both {{name}} and {{expiration}}.

50. Step 34 — Create Revocation SQL

Create:

cat > "$LAB_DIR/revoke-app-crud.sql" <<'EOF'
REVOKE app_crud FROM "{{name}}";
DROP ROLE IF EXISTS "{{name}}";
EOF

Display:

cat "$LAB_DIR/revoke-app-crud.sql"

When Vault revokes a credential:

1. membership in app_crud is removed
2. the dynamic PostgreSQL login is deleted

51. Step 35 — Create Rollback SQL

Create:

cat > "$LAB_DIR/rollback-app-crud.sql" <<'EOF'
REVOKE app_crud FROM "{{name}}";
DROP ROLE IF EXISTS "{{name}}";
EOF

Rollback is useful if Vault starts creating credentials but something in the creation workflow fails.

The PostgreSQL plugin supports creation, revocation, rollback, and renewal statement types.

52. Step 36 — Create the Vault Dynamic Database Role

Run:

vault write database/roles/app-crud \
  db_name='vaultlab-postgres' \
  creation_statements=@"$LAB_DIR/create-app-crud.sql" \
  renew_statements=@"$LAB_DIR/renew-app-crud.sql" \
  revocation_statements=@"$LAB_DIR/revoke-app-crud.sql" \
  rollback_statements=@"$LAB_DIR/rollback-app-crud.sql" \
  default_ttl='10m' \
  max_ttl='1h'

Expected:

Success! Data written to: database/roles/app-crud

HashiCorp’s current database tutorial explicitly supports loading creation statements from a file using syntax such as:

creation_statements=@file.sql

53. Step 37 — Read the Dynamic Role

Run:

vault read database/roles/app-crud

You should see fields including:

creation_statements
db_name
default_ttl
max_ttl
renew_statements
revocation_statements
rollback_statements

54. Step 38 — List Database Roles

Run:

vault list database/roles

Expected:

Keys
----
app-crud

55. Step 39 — Test Dynamic Credentials as Root

Before introducing userpass, verify that the database integration itself works.

Run:

vault read database/creds/app-crud

Expected output resembles:

Key                Value
---                -----
lease_id           database/creds/app-crud/...
lease_duration     10m
lease_renewable    true
password           <generated-password>
username           <generated-username>

Vault’s current database workflow generates credentials by reading:

database/creds/<role>

This proves:

Vault
   ↓
PostgreSQL plugin
   ↓
vaultadmin
   ↓
CREATE ROLE
   ↓
GRANT app_crud

works.

We will generate a fresh credential later as student1.

56. Step 40 — Create the Vault Policy

Now create:

postgresql-engine-admin

This policy gives our student complete CRUD-style access underneath:

database/*

and individual lease lookup, renewal, and revocation.

Run:

cat > "$LAB_DIR/postgresql-engine-admin.hcl" <<'EOF'
# Complete CRUD-style access to the mounted database secrets engine.
#
# LAB ONLY:
# This includes database connection configuration,
# roles, credential-generation endpoints, reset endpoints,
# rotate-root endpoints, and other database/* operations.

path "database/*" {
  capabilities = [
    "create",
    "read",
    "update",
    "delete",
    "list"
  ]
}

# Lookup a specific dynamic-secret lease.

path "sys/leases/lookup" {
  capabilities = ["update"]
}

# Renew a specific lease.

path "sys/leases/renew" {
  capabilities = ["update"]
}

# Revoke a specific lease.

path "sys/leases/revoke" {
  capabilities = ["update"]
}
EOF

57. Step 41 — Examine the Vault Policy

Run:

cat "$LAB_DIR/postgresql-engine-admin.hcl"

The important section is:

path "database/*" {
  capabilities = [
    "create",
    "read",
    "update",
    "delete",
    "list"
  ]
}

That gives:

CREATE
READ
UPDATE
DELETE
LIST

capabilities inside the database secrets engine.

HashiCorp’s current database tutorial uses the same full database-engine capability model for an administrative database policy.

58. Important Policy Explanation

A Vault capability does not mean every API endpoint supports every operation.

For example:

database/creds/app-crud

is fundamentally a credential generation endpoint.

Its supported operation is:

READ

Even though our broad wildcard policy technically grants multiple capabilities to that path, the backend endpoint itself determines which operation is valid.

Think of it as:

Policy says:
"Student may perform these actions."

Backend says:
"This particular endpoint only implements these actions."

Both conditions must be satisfied.

59. Step 42 — Load the Vault Policy

Run:

vault policy write \
  postgresql-engine-admin \
  "$LAB_DIR/postgresql-engine-admin.hcl"

Expected:

Success! Uploaded policy: postgresql-engine-admin

60. Step 43 — Read the Vault Policy

Run:

vault policy read postgresql-engine-admin

Verify the contents.

61. Step 44 — List Vault Policies

Run:

vault policy list

You should find:

default
postgresql-engine-admin
root

62. Step 45 — Enable Userpass Authentication

Run:

vault auth enable userpass

Expected:

Success! Enabled userpass auth method at: userpass/

Vault’s current userpass auth method authenticates users from username/password entries configured underneath:

auth/userpass/users/

63. Step 46 — Verify Userpass

Run:

vault auth list

You should see:

userpass/

64. Step 47 — Create One Vault User

Create:

Username: student1
Password: StudentLab2026
Policy: postgresql-engine-admin

Run:

vault write auth/userpass/users/student1 \
  password='StudentLab2026' \
  policies='postgresql-engine-admin' \
  token_ttl='1h' \
  token_max_ttl='4h'

Expected:

Success! Data written to: auth/userpass/users/student1

Userpass supports policy assignment and token TTL/max-TTL configuration.

65. Step 48 — Read the User

Still as root:

vault read auth/userpass/users/student1

You should see information such as:

token_policies
token_ttl
token_max_ttl

You will not see the plaintext password returned.

66. Step 49 — List Userpass Users

Run:

vault list auth/userpass/users

Expected:

Keys
----
student1

67. Administrative Setup Is Complete

Root has now completed:

PostgreSQL installation
        ↓
Database creation
        ↓
vaultadmin creation
        ↓
app_crud PostgreSQL role
        ↓
Vault database engine
        ↓
PostgreSQL plugin
        ↓
Vault DB connection
        ↓
Vault DB dynamic role
        ↓
Vault policy
        ↓
userpass
        ↓
student1

Now we switch to the student identity.

68. Step 50 — Remove the Root Token

Run:

unset VAULT_TOKEN

Verify:

echo "${VAULT_TOKEN:-VAULT_TOKEN is not set}"

Expected:

VAULT_TOKEN is not set

This step is critical.

Otherwise later authorization tests would accidentally execute as:

root

and become meaningless.

69. Step 51 — Login as `student1`

Run:

vault login \
  -method=userpass \
  username='student1' \
  password='StudentLab2026'

Expected output contains information similar to:

token                 <generated-vault-token>
token_duration        1h
token_renewable       true
token_policies        ["default" "postgresql-engine-admin"]

The actual token is different each time.

70. Step 52 — Verify Student Identity

Run:

vault token lookup

You should find:

postgresql-engine-admin
default

You should not find:

root

71. Step 53 — Verify Database Role Capabilities

Run:

vault token capabilities \
  database/roles/app-crud

Expected capabilities include:

create
delete
list
read
update

The exact ordering may differ.

72. Step 54 — Verify Configuration Capabilities

Run:

vault token capabilities \
  database/config/vaultlab-postgres

Again, you should see CRUD-style capabilities.

This confirms the student has broad administrative access inside:

database/*

73. Step 55 — Negative Vault Permission Test

Vault dev mode also normally provides a KV secrets engine mounted at:

secret/

Try:

vault kv put \
  secret/student-should-not-access \
  test=value

This should fail with:

permission denied

That failure is correct.

Our policy grants access to:

database/*

not:

secret/*

74. Vault Authorization Model

At this point:

student1
   |
   | userpass login
   v
Vault Token
   |
   | postgresql-engine-admin
   v
database/*
   |
   +-- config
   +-- roles
   +-- creds
   +-- reset
   +-- rotate-root
   +-- other database engine operations

but:

secret/*

remains denied.

75. Step 56 — Demonstrate CREATE

First delete the role that root created earlier so the student can demonstrate the complete lifecycle.

Run:

vault delete database/roles/app-crud

Expected:

Success! Data deleted (if it existed) at: database/roles/app-crud

Confirm:

vault list database/roles

The role should be absent.

Now CREATE it as student1:

vault write database/roles/app-crud \
  db_name='vaultlab-postgres' \
  creation_statements=@"$LAB_DIR/create-app-crud.sql" \
  renew_statements=@"$LAB_DIR/renew-app-crud.sql" \
  revocation_statements=@"$LAB_DIR/revoke-app-crud.sql" \
  rollback_statements=@"$LAB_DIR/rollback-app-crud.sql" \
  default_ttl='10m' \
  max_ttl='1h'

Expected:

Success! Data written to: database/roles/app-crud

That demonstrates:

CREATE

76. Step 57 — Demonstrate READ

Run:

vault read database/roles/app-crud

That demonstrates:

READ

77. Step 58 — Demonstrate LIST

Run:

vault list database/roles

Expected:

Keys
----
app-crud

That demonstrates:

LIST

78. Step 59 — Demonstrate UPDATE

Change:

default TTL
10 minutes

to:

5 minutes

Run:

vault write database/roles/app-crud \
  db_name='vaultlab-postgres' \
  creation_statements=@"$LAB_DIR/create-app-crud.sql" \
  renew_statements=@"$LAB_DIR/renew-app-crud.sql" \
  revocation_statements=@"$LAB_DIR/revoke-app-crud.sql" \
  rollback_statements=@"$LAB_DIR/rollback-app-crud.sql" \
  default_ttl='5m' \
  max_ttl='1h'

Expected:

Success! Data written to: database/roles/app-crud

Read it:

vault read database/roles/app-crud

Confirm:

default_ttl

now corresponds to five minutes.

That demonstrates:

UPDATE

79. Step 60 — Demonstrate DELETE

Run:

vault delete database/roles/app-crud

Expected:

Success! Data deleted (if it existed) at: database/roles/app-crud

Confirm:

vault read database/roles/app-crud

Expected:

No value found

That demonstrates:

DELETE

80. CRUD Checkpoint

The student has now proven:

CREATE
vault write database/roles/app-crud

READ
vault read database/roles/app-crud

UPDATE
vault write database/roles/app-crud

LIST
vault list database/roles

DELETE
vault delete database/roles/app-crud

This validates the Vault ACL policy.

81. Step 61 — Recreate the Dynamic Role

We need it for the rest of the lab.

Run:

vault write database/roles/app-crud \
  db_name='vaultlab-postgres' \
  creation_statements=@"$LAB_DIR/create-app-crud.sql" \
  renew_statements=@"$LAB_DIR/renew-app-crud.sql" \
  revocation_statements=@"$LAB_DIR/revoke-app-crud.sql" \
  rollback_statements=@"$LAB_DIR/rollback-app-crud.sql" \
  default_ttl='10m' \
  max_ttl='1h'

82. Step 62 — Generate Dynamic PostgreSQL Credentials

Run:

vault read database/creds/app-crud

Expected output:

Key                Value
---                -----
lease_id           database/creds/app-crud/...
lease_duration     10m
lease_renewable    true
password           ...
username           ...

Every request creates a different PostgreSQL credential.

83. Step 63 — Capture Credentials Automatically

Instead of copying them manually:

CREDS_JSON="$(
  vault read \
    -format=json \
    database/creds/app-crud
)"

Extract username:

export DB_USER="$(
  printf '%s' "$CREDS_JSON" \
  | jq -r '.data.username'
)"

Extract password:

export DB_PASS="$(
  printf '%s' "$CREDS_JSON" \
  | jq -r '.data.password'
)"

Extract Vault lease ID:

export LEASE_ID="$(
  printf '%s' "$CREDS_JSON" \
  | jq -r '.lease_id'
)"

Remove the JSON copy:

unset CREDS_JSON

84. Step 64 — Verify Variables

Display the generated username:

echo "$DB_USER"

It may look similar to:

v-userpass-app-crud-...

The exact format and random portion will differ.

Verify password exists without displaying it:

test -n "$DB_PASS" \
  && test "$DB_PASS" != "null" \
  && echo "Dynamic PostgreSQL password captured."

Expected:

Dynamic PostgreSQL password captured.

Display lease:

echo "$LEASE_ID"

It should resemble:

database/creds/app-crud/<unique-id>

85. Step 65 — Verify the Dynamic PostgreSQL Role Exists

As PostgreSQL administrator:

sudo -u postgres psql \
  -d postgres \
  -c "SELECT rolname, rolcanlogin, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';"

You should see the generated username.

Important fields:

rolcanlogin = true
rolvaliduntil = future timestamp

86. Step 66 — Verify Group Membership

Run:

sudo -u postgres psql \
  -d postgres \
  -c "SELECT pg_has_role('$DB_USER', 'app_crud', 'MEMBER') AS member_of_app_crud;"

Expected:

 member_of_app_crud
--------------------
 t

This proves Vault executed:

GRANT app_crud TO "<dynamic-user>";

87. Step 67 — Login to PostgreSQL Using Vault Credentials

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "SELECT current_user, current_database();"

Expected:

current_user        | <dynamic-user>
current_database    | vaultlab

The Vault-generated credential is working.

88. PostgreSQL CRUD Exercise

Now we will prove that the dynamic PostgreSQL account has:

CREATE DATA
READ DATA
UPDATE DATA
DELETE DATA

permission on:

public.app_data

These database-level operations are separate from the earlier Vault API CRUD operations.

89. Step 68 — READ Data with SELECT

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "SELECT * FROM public.app_data;"

Expected:

linux-server
vault-server
postgresql-server

The dynamic user can:

SELECT

90. Step 69 — CREATE Data with INSERT

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "INSERT INTO public.app_data (item_name, quantity) VALUES ('vault-dynamic-user', 25) RETURNING *;"

Expected:

vault-dynamic-user | 25

The dynamic user can:

INSERT

91. Step 70 — Verify INSERT

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "SELECT * FROM public.app_data WHERE item_name='vault-dynamic-user';"

92. Step 71 — UPDATE Data

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "UPDATE public.app_data SET quantity=50 WHERE item_name='vault-dynamic-user' RETURNING *;"

Expected quantity:

The dynamic user can:

UPDATE

93. Step 72 — DELETE Data

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "DELETE FROM public.app_data WHERE item_name='vault-dynamic-user' RETURNING *;"

The row should be returned and removed.

The dynamic user can:

DELETE

94. PostgreSQL CRUD Proven

We have now demonstrated:

READ
SELECT

CREATE
INSERT

UPDATE
UPDATE

DELETE
DELETE

using a PostgreSQL username and password that:

did not exist before the request

and were dynamically created by Vault.

95. Step 73 — Negative PostgreSQL Test

The dynamic account has data CRUD permissions.

It should not be a PostgreSQL administrator.

Try creating another table:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "CREATE TABLE public.should_fail (id INTEGER);"

Expected:

permission denied for schema public

That failure is correct.

96. Step 74 — Another Negative Test

Try creating another PostgreSQL role:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "CREATE ROLE should_fail;"

Expected:

permission denied to create role

Correct.

Therefore:

Dynamic user
    |
    +-- SELECT     YES
    +-- INSERT     YES
    +-- UPDATE     YES
    +-- DELETE     YES
    |
    +-- CREATE TABLE   NO
    +-- CREATE ROLE    NO
    +-- SUPERUSER      NO

97. Step 75 — Examine the Vault Lease

Every dynamic database credential receives a Vault lease.

Run:

vault lease lookup "$LEASE_ID"

Look for:

expire_time
issue_time
renewable
ttl

Vault’s lease API supports lookup, renewal, and immediate revocation of dynamic secrets.

98. Step 76 — Check PostgreSQL Expiration Before Renewal

Run:

sudo -u postgres psql \
  -d postgres \
  -c "SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';"

Take note of:

rolvaliduntil

99. Step 77 — Renew the Credential

Run:

vault lease renew \
  -increment='20m' \
  "$LEASE_ID"

Expected:

lease_id          database/creds/app-crud/...
lease_duration    ...
lease_renewable   true

Vault is not required to honor the requested increment exactly; the role and mount TTL limits still apply. Vault’s documented lease-renew command supports this model for database credentials.

100. Step 78 — Verify PostgreSQL Expiration Changed

Run again:

sudo -u postgres psql \
  -d postgres \
  -c "SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';"

The:

rolvaliduntil

timestamp should reflect the renewed lifetime.

This demonstrates:

Vault Lease Renewal
        ↓
renew_statements
        ↓
ALTER ROLE
        ↓
PostgreSQL VALID UNTIL updated

101. Step 79 — Confirm the Credential Still Works

Run:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "SELECT COUNT(*) FROM public.app_data;"

The query should succeed.

102. Step 80 — Revoke the Dynamic Credential

Now manually revoke it:

vault lease revoke \
  -sync \
  "$LEASE_ID"

Expected:

Success! Revoked lease: database/creds/app-crud/...

The synchronous option makes Vault wait for backend revocation rather than merely queueing it. The current lease API supports synchronous immediate revocation.

103. What Happens During Revocation?

Vault uses our configured:

revocation_statements

which contain:

REVOKE app_crud FROM "{{name}}";
DROP ROLE IF EXISTS "{{name}}";

Therefore:

Vault
  |
  v
Revoke lease
  |
  v
PostgreSQL plugin
  |
  v
REVOKE app_crud
  |
  v
DROP ROLE

104. Step 81 — Confirm the PostgreSQL Account Is Gone

Run:

sudo -u postgres psql \
  -d postgres \
  -c "SELECT rolname FROM pg_roles WHERE rolname = '$DB_USER';"

Expected:

(0 rows)

This is one of the most important results in the lab.

The account no longer exists.

105. Step 82 — Prove the Old Credential No Longer Works

Try:

PGPASSWORD="$DB_PASS" \
psql \
  -h 127.0.0.1 \
  -U "$DB_USER" \
  -d vaultlab \
  -c "SELECT * FROM public.app_data;"

Expected failure similar to:

FATAL: role "<dynamic-user>" does not exist

That failure proves the credential was actually revoked in PostgreSQL.

106. Dynamic Credential Lifecycle

Students have now demonstrated the complete lifecycle:

student1
   |
   | Vault authentication
   v
Vault token
   |
   | policy permits database/creds/app-crud
   v
Vault Database Engine
   |
   v
PostgreSQL Plugin
   |
   v
CREATE ROLE
   |
   v
GRANT app_crud
   |
   v
Dynamic Username + Password
   |
   v
PostgreSQL CRUD
   |
   v
Vault Lease
   |
   +---------------------+
   |                     |
   v                     v
Renew                  Revoke
   |                     |
   v                     v
ALTER ROLE           REVOKE role
VALID UNTIL          DROP ROLE

107. Step 83 — Generate Another Credential

Run:

vault read database/creds/app-crud

You should receive:

different username
different password
different lease ID

This is the core concept behind Vault dynamic database credentials.

Applications do not have to permanently share:

appuser / permanent-password

Instead:

Application A → credential A
Application B → credential B
Application C → credential C

with independent lease lifecycles.

108. Optional — Database Connection CRUD

Because our student policy grants:

database/*

the user also has administrative access to database connection configuration.

This portion is optional because deleting a connection intentionally breaks credential generation until it is recreated.

Make sure there are no active dynamic credential leases before performing it.

109. READ the Connection

Run:

vault read database/config/vaultlab-postgres

110. LIST Connections

Run:

vault list database/config

Expected:

vaultlab-postgres

111. UPDATE the Connection

Change maximum open connections from:

to:

Run:

vault write database/config/vaultlab-postgres \
  plugin_name='postgresql-database-plugin' \
  allowed_roles='app-crud' \
  connection_url='postgresql://{{username}}:{{password}}@127.0.0.1:5432/vaultlab?sslmode=disable' \
  username='vaultadmin' \
  password='VaultAdmin2026' \
  password_authentication='scram-sha-256' \
  max_open_connections=8

Expected:

Success! Data written to: database/config/vaultlab-postgres

The database configuration API distinguishes create and update operations.

112. Reset the PostgreSQL Plugin Connection

A useful administrative operation is:

vault write \
  -force \
  database/reset/vaultlab-postgres

This closes/restarts the underlying plugin connection while preserving its stored configuration. Vault documents the reset endpoint for database connections.

113. DELETE the Connection

Do this only as an exercise.

Run:

vault delete database/config/vaultlab-postgres

Expected:

Success! Data deleted...

Confirm:

vault read database/config/vaultlab-postgres

Expected:

No value found

The current Vault database API explicitly supports deleting database connections.

114. Prove Credential Generation Is Broken

Try:

vault read database/creds/app-crud

It should now fail because:

app-crud

references:

vaultlab-postgres

and that connection no longer exists.

This failure is expected.

115. Recreate the Connection

Run:

vault write database/config/vaultlab-postgres \
  plugin_name='postgresql-database-plugin' \
  allowed_roles='app-crud' \
  connection_url='postgresql://{{username}}:{{password}}@127.0.0.1:5432/vaultlab?sslmode=disable' \
  username='vaultadmin' \
  password='VaultAdmin2026' \
  password_authentication='scram-sha-256' \
  max_open_connections=5

Expected:

Success! Data written to: database/config/vaultlab-postgres

Now:

vault read database/creds/app-crud

should work again.

If you generate a credential during this test, revoke its lease before continuing.

116. Complete Vault CRUD Summary

Database Connection

CREATE

vault write database/config/vaultlab-postgres ...

READ

vault read database/config/vaultlab-postgres

LIST

vault list database/config

UPDATE

vault write database/config/vaultlab-postgres ...

DELETE

vault delete database/config/vaultlab-postgres

117. Complete Vault Role CRUD Summary

CREATE

vault write database/roles/app-crud \
  db_name='vaultlab-postgres' \
  creation_statements=@"$LAB_DIR/create-app-crud.sql" \
  renew_statements=@"$LAB_DIR/renew-app-crud.sql" \
  revocation_statements=@"$LAB_DIR/revoke-app-crud.sql" \
  rollback_statements=@"$LAB_DIR/rollback-app-crud.sql" \
  default_ttl='10m' \
  max_ttl='1h'

READ

vault read database/roles/app-crud

LIST

vault list database/roles

UPDATE

Use the same vault write command with changed settings.

DELETE

vault delete database/roles/app-crud

118. Credential Generation

vault read database/creds/app-crud

Response:

lease_id
username
password
lease_duration
lease_renewable

119. Lease Lookup

vault lease lookup "$LEASE_ID"

120. Lease Renewal

vault lease renew \
  -increment='20m' \
  "$LEASE_ID"

121. Lease Revocation

vault lease revoke \
  -sync \
  "$LEASE_ID"

122. Userpass Command Summary

Enable:

vault auth enable userpass

Create:

vault write auth/userpass/users/student1 \
  password='StudentLab2026' \
  policies='postgresql-engine-admin' \
  token_ttl='1h' \
  token_max_ttl='4h'

Read:

vault read auth/userpass/users/student1

List:

vault list auth/userpass/users

vault login \
  -method=userpass \
  username='student1' \
  password='StudentLab2026'

123. Vault Policy Used in This Lab

path "database/*" {
  capabilities = [
    "create",
    "read",
    "update",
    "delete",
    "list"
  ]
}

path "sys/leases/lookup" {
  capabilities = ["update"]
}

path "sys/leases/renew" {
  capabilities = ["update"]
}

path "sys/leases/revoke" {
  capabilities = ["update"]
}

124. Why This Policy Is Very Powerful

This policy gives the user:

database/*

access.

That means more than simply requesting credentials.

The user can potentially:

create database connections
modify database connections
delete database connections

create Vault DB roles
modify Vault DB roles
delete Vault DB roles

generate database credentials

reset database plugin connections

access rotate-root endpoints

Therefore this is appropriate for the requested:

complete database secret-engine CRUD lab

but it is too broad for a normal application.

125. Production-Style Consumer Policy

For comparison, an application usually needs something closer to:

path "database/creds/app-crud" {
  capabilities = ["read"]
}

Nothing more.

The application would not need access to:

database/config/*
database/roles/*
database/reset/*
database/rotate-root/*

126. Production Separation of Responsibilities

A better production design would have:

Vault Platform Administrator
           |
           +-- configure engine
           +-- configure database connections
           +-- configure roles
           +-- manage policies


Application
           |
           +-- read database/creds/app-crud

Do not normally give applications:

database/*

administrative access.

127. Complete Architecture

                          +----------------+
                          |    student1    |
                          +-------+--------+
                                  |
                             username/password
                                  |
                                  v
                          +----------------+
                          |    userpass    |
                          +-------+--------+
                                  |
                              authenticates
                                  |
                                  v
                          +----------------+
                          |  Vault Token   |
                          +-------+--------+
                                  |
                     postgresql-engine-admin
                                  |
                                  v
                    +-------------------------+
                    | database/ Secrets Engine|
                    +------------+------------+
                                 |
                                 |
                      vaultlab-postgres
                                 |
                                 v
                    +-------------------------+
                    | PostgreSQL DB Plugin    |
                    | postgresql-database-    |
                    | plugin                  |
                    +------------+------------+
                                 |
                                 |
                            vaultadmin
                                 |
                                 v
                      PostgreSQL 127.0.0.1
                                 |
                                 |
                        CREATE dynamic role
                                 |
                                 v
                +--------------------------------+
                | v-userpass-app-crud-XXXXXXXX   |
                +---------------+----------------+
                                |
                         GRANT app_crud
                                |
                                v
                       +----------------+
                       |    app_crud    |
                       |    NOLOGIN     |
                       +-------+--------+
                               |
             +-----------------+----------------+
             |                 |                |
           SELECT            INSERT           UPDATE
             |                 |                |
             +-----------------+----------------+
                               |
                             DELETE
                               |
                               v
                      public.app_data

128. Authentication vs Authorization

Students should be able to answer:

Who authenticates `student1`?

Vault userpass

What does student1 receive?

A Vault token.

What controls the Vault token?

postgresql-engine-admin policy

What does `database/creds/app-crud` do?

Generates a PostgreSQL username/password.

What controls that database username’s permissions?

PostgreSQL app_crud role.

What controls how long it exists?

Vault lease and PostgreSQL VALID UNTIL.

What happens when Vault revokes it?

Vault executes PostgreSQL revocation statements
and drops the dynamic PostgreSQL role.

129. Troubleshooting — Vault Cannot Connect to PostgreSQL

Typical error:

error creating database object
connection refused
password authentication failed

First test PostgreSQL manually:

PGPASSWORD='VaultAdmin2026' \
psql \
  -h 127.0.0.1 \
  -U vaultadmin \
  -d vaultlab \
  -c "SELECT current_user;"

If that fails, fix PostgreSQL first.

Check:

pg_isready

Check:

ss -lnt | grep 5432

Check service:

sudo systemctl status postgresql --no-pager

130. Troubleshooting — Vault Database Config Fails

Verify:

database = vaultlab
username = vaultadmin
password = VaultAdmin2026
host     = 127.0.0.1
port     = 5432

Then retry:

vault write database/config/vaultlab-postgres \
  plugin_name='postgresql-database-plugin' \
  allowed_roles='app-crud' \
  connection_url='postgresql://{{username}}:{{password}}@127.0.0.1:5432/vaultlab?sslmode=disable' \
  username='vaultadmin' \
  password='VaultAdmin2026' \
  password_authentication='scram-sha-256'

131. Troubleshooting — `permission denied to create role`

Check:

sudo -u postgres psql \
  -c "\du vaultadmin"

The account must have:

Create role

If necessary:

sudo -u postgres psql \
  -c "ALTER ROLE vaultadmin CREATEROLE;"

132. Troubleshooting — `must have admin option`

Check membership:

sudo -u postgres psql \
  -c "\du vaultadmin"

Reapply:

sudo -u postgres psql \
  -c "GRANT app_crud TO vaultadmin WITH ADMIN OPTION;"

PostgreSQL’s ADMIN OPTION is what allows a role to grant/revoke membership in another role.

133. Troubleshooting — Dynamic User Cannot SELECT

Check PostgreSQL privileges:

sudo -u postgres psql \
  -d vaultlab \
  -c "\dp public.app_data"

Reapply:

sudo -u postgres psql -d vaultlab <<'SQL'
GRANT CONNECT ON DATABASE vaultlab TO app_crud;
GRANT USAGE ON SCHEMA public TO app_crud;
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLE public.app_data
TO app_crud;
GRANT USAGE, SELECT
ON SEQUENCE public.app_data_id_seq
TO app_crud;
SQL

134. Troubleshooting — INSERT Fails on Sequence

Typical error:

permission denied for sequence app_data_id_seq

Fix:

sudo -u postgres psql \
  -d vaultlab \
  -c "GRANT USAGE, SELECT ON SEQUENCE public.app_data_id_seq TO app_crud;"

Then retry the INSERT.

135. Troubleshooting — Vault Permission Denied

Run:

vault token lookup

Verify:

postgresql-engine-admin

is present.

Check capability:

vault token capabilities \
  database/roles/app-crud

Expected:

create, delete, list, read, update

136. Troubleshooting — Still Logged In as Root

Run:

echo "${VAULT_TOKEN:-not-set}"

If it prints:

root

then:

unset VAULT_TOKEN

vault login \
  -method=userpass \
  username='student1' \
  password='StudentLab2026'

Then:

vault token lookup

Ensure:

root

is not listed as a policy.

137. Troubleshooting — Credential Revocation Fails

Make sure you do not have a persistent interactive PostgreSQL session open as the generated account.

Exit it with:

\q

Then retry:

vault lease revoke \
  -sync \
  "$LEASE_ID"

Check Vault logs:

tail -100 "$LAB_DIR/vault.log"

138. Troubleshooting — Userpass Login Fails

Confirm user exists using root/admin access:

vault read auth/userpass/users/student1

Check password carefully:

StudentLab2026

Repeated incorrect login attempts can interact with Vault’s user-lockout protections supported by userpass.

139. Student Success Checklist

A successful lab should satisfy all of these:

[ ] PostgreSQL installed

[ ] PostgreSQL service running

[ ] PostgreSQL accepts connections on 5432

[ ] vaultlab database exists

[ ] app_data table exists

[ ] vaultadmin exists

[ ] vaultadmin has CREATEROLE

[ ] app_crud exists

[ ] app_crud is NOLOGIN

[ ] vaultadmin has ADMIN OPTION on app_crud

[ ] vaultadmin TCP/password login works

[ ] Vault installed

[ ] Vault version verified

[ ] Vault dev server running

[ ] Vault initialized

[ ] Vault unsealed

[ ] database/ secrets engine enabled

[ ] vaultlab-postgres connection configured

[ ] PostgreSQL plugin connection works

[ ] app-crud Vault DB role exists

[ ] postgresql-engine-admin policy exists

[ ] userpass enabled

[ ] student1 exists

[ ] student1 can login

[ ] student1 has postgresql-engine-admin

[ ] student1 does not have root

[ ] student1 cannot access unrelated secret/

[ ] student1 can CREATE database role

[ ] student1 can READ database role

[ ] student1 can LIST database roles

[ ] student1 can UPDATE database role

[ ] student1 can DELETE database role

[ ] student1 can generate PostgreSQL credentials

[ ] dynamic PostgreSQL role exists

[ ] dynamic account belongs to app_crud

[ ] dynamic account can SELECT

[ ] dynamic account can INSERT

[ ] dynamic account can UPDATE

[ ] dynamic account can DELETE

[ ] dynamic account cannot CREATE TABLE

[ ] dynamic account cannot CREATE ROLE

[ ] credential has a Vault lease

[ ] lease can be looked up

[ ] lease can be renewed

[ ] PostgreSQL VALID UNTIL changes

[ ] lease can be revoked

[ ] PostgreSQL dynamic role disappears

[ ] revoked credentials no longer work

140. Cleanup — Revoke Any Remaining Credentials First

Before stopping Vault, revoke every credential you created during the lab.

For a known lease:

vault lease revoke \
  -sync \
  "$LEASE_ID"

If that lease was already revoked, simply continue.

141. Delete Vault Dynamic Role

Run:

vault delete database/roles/app-crud

142. Delete Vault Database Connection

Run:

vault delete database/config/vaultlab-postgres

143. Remove Sensitive Shell Variables

Run:

unset DB_USER
unset DB_PASS
unset LEASE_ID
unset VAULT_TOKEN

144. Stop Vault

Run:

kill "$(cat "$LAB_DIR/vault.pid")" 2>/dev/null || true

Check:

pgrep -a vault || true

145. Remove PostgreSQL Lab Database

First terminate any remaining connection to the lab database:

sudo -u postgres psql <<'SQL'
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'vaultlab'
  AND pid <> pg_backend_pid();
SQL

Drop database:

sudo -u postgres dropdb \
  --if-exists \
  vaultlab

146. Remove PostgreSQL Lab Roles

Remove the membership:

sudo -u postgres psql \
  -c "REVOKE app_crud FROM vaultadmin;"

Drop permission role:

sudo -u postgres psql \
  -c "DROP ROLE IF EXISTS app_crud;"

Drop Vault database user:

sudo -u postgres psql \
  -c "DROP ROLE IF EXISTS vaultadmin;"

147. Remove Vault CLI Token File

Vault login may have created:

~/.vault-token

Remove it:

rm -f "$HOME/.vault-token"

148. Remove Lab Files

Run:

rm -rf "$LAB_DIR"

Then:

unset LAB_DIR
unset VAULT_ADDR

149. Final Learning Flow

Students should remember this sequence:

PostgreSQL
    ↓
Create vaultadmin
    ↓
Create app_crud permission role
    ↓
Create vaultlab database
    ↓
Create app_data table
    ↓
Grant CRUD to app_crud
    ↓
Vault
    ↓
Enable database secrets engine
    ↓
Configure PostgreSQL plugin
    ↓
Create dynamic database role
    ↓
Enable userpass
    ↓
Create student1
    ↓
Attach postgresql-engine-admin
    ↓
Login as student1
    ↓
Create / Read / Update / List / Delete Vault DB role
    ↓
Generate dynamic PostgreSQL credential
    ↓
Vault creates temporary PostgreSQL login
    ↓
Vault grants app_crud
    ↓
Temporary user performs SELECT / INSERT / UPDATE / DELETE
    ↓
Vault tracks credential with lease
    ↓
Renew lease
    ↓
PostgreSQL VALID UNTIL changes
    ↓
Revoke lease
    ↓
PostgreSQL dynamic user is deleted
    ↓
Old username/password stops working

150. The Most Important Concept

Before Vault:

Application
     |
     |
Permanent Username
Permanent Password
     |
     v
PostgreSQL

The credential may exist for:

months
years

and may be shared by many applications.

With Vault:

Application
     |
     | authenticate
     v
Vault
     |
     | request credentials
     v
Dynamic Username
Dynamic Password
     |
     | short TTL
     v
PostgreSQL
     |
     | lease expires/revoked
     v
Credential removed

This is the fundamental benefit of Vault’s PostgreSQL database secrets engine.

151. Final Architecture Summary

                      USER / STUDENT
                           |
                           |
                     student1/password
                           |
                           v
                    +--------------+
                    |   USERPASS   |
                    +------+-------+
                           |
                           v
                    +--------------+
                    | VAULT TOKEN  |
                    +------+-------+
                           |
                  postgresql-engine-admin
                           |
                           v
             +----------------------------+
             | Database Secrets Engine    |
             | database/                  |
             +-------------+--------------+
                           |
                    vaultlab-postgres
                           |
                           v
             +----------------------------+
             | PostgreSQL Plugin          |
             | postgresql-database-plugin |
             +-------------+--------------+
                           |
                       vaultadmin
                           |
                           v
                +----------------------+
                | PostgreSQL           |
                | vaultlab             |
                +----------+-----------+
                           |
                  create dynamic user
                           |
                           v
                +----------------------+
                | Temporary DB Login   |
                +----------+-----------+
                           |
                     member of
                           |
                           v
                +----------------------+
                | app_crud             |
                | PostgreSQL Role      |
                +----------+-----------+
                           |
               SELECT / INSERT / UPDATE
                        / DELETE
                           |
                           v
                +----------------------+
                | public.app_data      |
                +----------------------+

152. Validation Notes

The Vault-specific commands and endpoint structure in this tutorial were checked against the current Vault 2.x documentation, including the Vault 2.0.4 release information, database secrets-engine API, PostgreSQL database plugin API, current userpass API, and current lease management API.

The PostgreSQL permission design uses documented PostgreSQL roles, CREATEROLE, role membership, and WITH ADMIN OPTION.

The dynamic-role pattern follows HashiCorp’s current recommended PostgreSQL approach of creating a PostgreSQL group/permission role and granting that role to Vault-generated login accounts.

The important difference from many older Vault tutorials is that this guide explicitly configures PostgreSQL creation, renewal, rollback, and revocation behavior instead of blindly depending on generic backend defaults. HashiCorp specifically warns that generic database statements may be unsuitable for production database deployments.

The exact commands are designed for a single Ubuntu server with PostgreSQL and Vault running locally. Package versions, Ubuntu PostgreSQL paths, and exact command output formatting can vary between Ubuntu releases, so the included verification checkpoints should be treated as mandatory parts of the student exercise rather than skipped.

rajeshkumar

Complete Beginner-Friendly Hands-On Tutorial for Ubuntu Linux

1. Lab Objective

2. Version Validation

3. Important Terminology

4. Lab Components

5. Important Security Warning

6. Understanding the Three Permission Layers

Layer 1 — Vault Authentication

Layer 2 — Vault Authorization

Layer 3 — PostgreSQL Authorization

7. Why We Use a PostgreSQL Group Role

8. Step 1 — Update Ubuntu

9. Step 2 — Install PostgreSQL

10. Step 3 — Verify PostgreSQL Version

11. Step 4 — Start PostgreSQL

12. Step 5 — Verify PostgreSQL Is Listening

13. Step 6 — Connect as PostgreSQL Administrator

14. Step 7 — Create the Lab Working Directory

15. Step 8 — Create the PostgreSQL Vault Administrator

16. Why Does vaultadmin Need CREATEROLE?

17. Step 9 — Create the Application Permission Role

18. Step 10 — Allow Vault to Grant app_crud

19. Step 11 — Create the Training Database

20. Step 12 — Create a Test Table

21. Step 13 — Examine the Table

22. Step 14 — Restrict Public Schema Creation

23. Step 15 — Give app_crud Database Permissions

24. Step 16 — Allow vaultadmin to Connect

25. PostgreSQL Permission Architecture

26. Step 17 — Verify PostgreSQL Roles

27. Step 18 — Test Vault’s PostgreSQL Account

28. Troubleshooting PostgreSQL Password Authentication

29. PostgreSQL Checkpoint

30. Step 19 — Install Vault

31. Step 20 — Verify Vault Version

32. Step 21 — Stop Any Existing Vault Service

33. Step 22 — Start Vault Development Server

34. Step 23 — Configure the Vault CLI

35. Step 24 — Verify Vault

36. Step 25 — Verify Root Identity

37. Step 26 — Examine Existing Secrets Engines

38. Step 27 — Enable the Database Secrets Engine

39. Step 28 — Verify the Engine

40. Step 29 — Configure the PostgreSQL Connection

41. What Did That Command Do?

42. Why sslmode=disable?

43. Why password_authentication=scram-sha-256?

44. Step 30 — Read the Database Configuration

45. Step 31 — List Database Connections

46. Vault-to-PostgreSQL Checkpoint

47. Step 32 — Create SQL for Dynamic Accounts

48. Understanding the Template Variables

49. Step 33 — Create Renewal SQL

50. Step 34 — Create Revocation SQL

51. Step 35 — Create Rollback SQL

52. Step 36 — Create the Vault Dynamic Database Role

53. Step 37 — Read the Dynamic Role

54. Step 38 — List Database Roles

55. Step 39 — Test Dynamic Credentials as Root

56. Step 40 — Create the Vault Policy

57. Step 41 — Examine the Vault Policy

58. Important Policy Explanation

59. Step 42 — Load the Vault Policy

60. Step 43 — Read the Vault Policy

61. Step 44 — List Vault Policies

62. Step 45 — Enable Userpass Authentication

63. Step 46 — Verify Userpass

64. Step 47 — Create One Vault User

65. Step 48 — Read the User

66. Step 49 — List Userpass Users

67. Administrative Setup Is Complete

68. Step 50 — Remove the Root Token

69. Step 51 — Login as student1

70. Step 52 — Verify Student Identity

71. Step 53 — Verify Database Role Capabilities

72. Step 54 — Verify Configuration Capabilities

73. Step 55 — Negative Vault Permission Test

74. Vault Authorization Model

75. Step 56 — Demonstrate CREATE

76. Step 57 — Demonstrate READ

16. Why Does `vaultadmin` Need CREATEROLE?

18. Step 10 — Allow Vault to Grant `app_crud`

23. Step 15 — Give `app_crud` Database Permissions

24. Step 16 — Allow `vaultadmin` to Connect

42. Why `sslmode=disable`?

43. Why `password_authentication=scram-sha-256`?

69. Step 51 — Login as `student1`

Who authenticates `student1`?

What does `database/creds/app-crud` do?