{"id":1044,"date":"2026-08-09T02:49:44","date_gmt":"2026-08-09T02:49:44","guid":{"rendered":"https:\/\/www.devopsschool.com\/tutorials\/?p=1044"},"modified":"2026-08-09T02:49:45","modified_gmt":"2026-08-09T02:49:45","slug":"hashicorp-vault-master-lab-postgresql-database-secrets-engine-userpass-authentication-vault-policy-dynamic-postgresql-credentials","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/tutorials\/hashicorp-vault-master-lab-postgresql-database-secrets-engine-userpass-authentication-vault-policy-dynamic-postgresql-credentials\/","title":{"rendered":"HashiCorp Vault Master Lab – PostgreSQL Database Secrets Engine + Userpass Authentication + Vault Policy + Dynamic PostgreSQL Credentials"},"content":{"rendered":"\n

Complete Beginner-Friendly Hands-On Tutorial for Ubuntu Linux<\/h2>\n\n\n\n
\n\n\n\n

1. Lab Objective<\/h1>\n\n\n\n

In this lab we will build the following environment on a single Ubuntu Linux server:<\/p>\n\n\n\n

                         Ubuntu Server\n                              |\n              +---------------+---------------+\n              |                               |\n              v                               v\n      HashiCorp Vault                    PostgreSQL\n       127.0.0.1:8200                  127.0.0.1:5432\n              |                               |\n              |                               |\n         userpass auth                        |\n              |                               |\n          student1                            |\n              |                               |\n     postgresql-engine-admin                  |\n         Vault Policy                         |\n              |                               |\n              v                               |\n       database\/ Secrets Engine --------------+\n              |\n              v\n        PostgreSQL Plugin\n   postgresql-database-plugin\n              |\n              v\n          app-crud\n        Vault DB Role\n              |\n              v\n    Dynamic PostgreSQL Login\n              |\n              v\n    PostgreSQL Role: app_crud\n              |\n              v\n       public.app_data\n              |\n      SELECT \/ INSERT \/\n       UPDATE \/ DELETE\n<\/code><\/pre>\n\n\n\n
By the end of this lab, students will understand:<\/p>\n\n\n\n
    \n
  • how Vault’s database secrets engine works;<\/li>\n\n\n\n
  • how PostgreSQL integration works;<\/li>\n\n\n\n
  • how to install PostgreSQL on Ubuntu;<\/li>\n\n\n\n
  • how to prepare PostgreSQL for Vault;<\/li>\n\n\n\n
  • how to create a dedicated database account for Vault;<\/li>\n\n\n\n
  • how to create a PostgreSQL group role;<\/li>\n\n\n\n
  • how to enable the Vault database secrets engine;<\/li>\n\n\n\n
  • how to configure the PostgreSQL database plugin;<\/li>\n\n\n\n
  • how to enable userpass<\/code>;<\/li>\n\n\n\n
  • how to create one Vault user;<\/li>\n\n\n\n
  • how to create and attach a Vault policy;<\/li>\n\n\n\n
  • how Vault ACL capabilities work;<\/li>\n\n\n\n
  • how to Create, Read, Update, List, and Delete Vault database roles;<\/li>\n\n\n\n
  • how Vault dynamically creates PostgreSQL usernames and passwords;<\/li>\n\n\n\n
  • how generated credentials receive limited PostgreSQL privileges;<\/li>\n\n\n\n
  • how to perform PostgreSQL SELECT, INSERT, UPDATE, and DELETE operations;<\/li>\n\n\n\n
  • how Vault leases work;<\/li>\n\n\n\n
  • how to renew database credentials;<\/li>\n\n\n\n
  • how to revoke database credentials;<\/li>\n\n\n\n
  • how revocation removes the PostgreSQL login;<\/li>\n\n\n\n
  • how to test both successful and denied operations.<\/li>\n<\/ul>\n\n\n\n
    \n\n\n\n
    2. Version Validation<\/h1>\n\n\n\n
    This tutorial targets the current Vault 2.x<\/strong> command and API model.<\/p>\n\n\n\n
    As of August 9, 2026, HashiCorp’s official installation documentation identifies:<\/p>\n\n\n\n
    Vault 2.0.4\n<\/code><\/pre>\n\n\n\n
    as the current downloadable Vault release, released on August 4, 2026.<\/p>\n\n\n\n
    Vault’s current PostgreSQL database plugin remains:<\/p>\n\n\n\n
    postgresql-database-plugin\n<\/code><\/pre>\n\n\n\n
    and the documented workflow remains:<\/p>\n\n\n\n
    database\/config\/<connection>\ndatabase\/roles\/<role>\ndatabase\/creds\/<role>\n<\/code><\/pre>\n\n\n\n
    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.<\/p>\n\n\n\n
    Always verify your installed versions before proceeding.<\/p>\n\n\n\n
    \n\n\n\n
    3. Important Terminology<\/h1>\n\n\n\n
    A very important point for beginners:<\/p>\n\n\n\n
    Vault does not<\/strong> have a separate secrets-engine type named:<\/p>\n\n\n\n
    postgresql\n<\/code><\/pre>\n\n\n\n
    Instead, Vault has:<\/p>\n\n\n\n
    database\n<\/code><\/pre>\n\n\n\n
    as the secrets engine.<\/p>\n\n\n\n
    Inside that engine we configure the:<\/p>\n\n\n\n
    postgresql-database-plugin\n<\/code><\/pre>\n\n\n\n
    Therefore:<\/p>\n\n\n\n
    Vault Database Secrets Engine\n            +\nPostgreSQL Database Plugin\n            =\nDynamic PostgreSQL Credentials\n<\/code><\/pre>\n\n\n\n
    PostgreSQL is officially supported by Vault’s database secrets engine for dynamic and static database credentials.<\/p>\n\n\n\n
    \n\n\n\n
    4. Lab Components<\/h1>\n\n\n\n
    We will use these names throughout the tutorial.<\/p>\n\n\n\n
    Ubuntu server\n    |\n    +-- Vault\n    |      Address: http:\/\/127.0.0.1:8200\n    |      Dev root token: root\n    |\n    +-- Vault secrets engine\n    |      database\/\n    |\n    +-- Vault database connection\n    |      vaultlab-postgres\n    |\n    +-- Vault dynamic DB role\n    |      app-crud\n    |\n    +-- Vault auth method\n    |      userpass\/\n    |\n    +-- Vault user\n    |      student1\n    |\n    +-- Vault policy\n    |      postgresql-engine-admin\n    |\n    +-- PostgreSQL\n           Address: 127.0.0.1:5432\n           Database: vaultlab\n\n           Vault management user:\n               vaultadmin\n\n           PostgreSQL permission role:\n               app_crud\n\n           Test table:\n               public.app_data\n<\/code><\/pre>\n\n\n\n
    Passwords used for this training lab:<\/p>\n\n\n\n
    PostgreSQL Vault account:\nUsername: vaultadmin\nPassword: VaultAdmin2026\n\nVault userpass account:\nUsername: student1\nPassword: StudentLab2026\n<\/code><\/pre>\n\n\n\n
    These are intentionally simple, fixed lab-only credentials<\/strong>.<\/p>\n\n\n\n
    Do not use these passwords in production.<\/p>\n\n\n\n
    \n\n\n\n
    5. Important Security Warning<\/h1>\n\n\n\n
    This lab uses:<\/p>\n\n\n\n
    Vault development mode\nVault root token = root\nHTTP instead of HTTPS\nPostgreSQL on localhost\nFixed demonstration passwords\nBroad Vault database-engine permissions\n<\/code><\/pre>\n\n\n\n
    This is appropriate for:<\/p>\n\n\n\n
    training\nclassrooms\nstudent labs\nexperimentation\nlearning\n<\/code><\/pre>\n\n\n\n
    It is not a production Vault architecture<\/strong>.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n\n\n\n
    6. Understanding the Three Permission Layers<\/h1>\n\n\n\n
    Students should understand that there are three different security layers.<\/p>\n\n\n\n
    Layer 1 \u2014 Vault Authentication<\/h2>\n\n\n\n
    Question:<\/p>\n\n\n\n
    Who are you?\n<\/code><\/pre>\n\n\n\n
    Answer:<\/p>\n\n\n\n
    userpass\nstudent1\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Layer 2 \u2014 Vault Authorization<\/h2>\n\n\n\n
    Question:<\/p>\n\n\n\n
    What may student1 do inside Vault?\n<\/code><\/pre>\n\n\n\n
    Answer:<\/p>\n\n\n\n
    Controlled by:<\/p>\n\n\n\n
    postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    Vault policy.<\/p>\n\n\n\n
    \n\n\n\n
    Layer 3 \u2014 PostgreSQL Authorization<\/h2>\n\n\n\n
    Question:<\/p>\n\n\n\n
    What may the dynamically generated PostgreSQL user do?\n<\/code><\/pre>\n\n\n\n
    Answer:<\/p>\n\n\n\n
    Controlled by PostgreSQL role:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    The dynamic login will receive membership in:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    7. Why We Use a PostgreSQL Group Role<\/h1>\n\n\n\n
    Instead of giving database permissions individually to every generated username, we create:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    as a PostgreSQL NOLOGIN<\/code> role.<\/p>\n\n\n\n
    It receives permissions such as:<\/p>\n\n\n\n
    SELECT\nINSERT\nUPDATE\nDELETE\n<\/code><\/pre>\n\n\n\n
    Vault-created PostgreSQL accounts simply become members of:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    Conceptually:<\/p>\n\n\n\n
                             app_crud\n                       PostgreSQL Role\n                             |\n                +------------+------------+\n                |            |            |\n                v            v            v\n          Dynamic User 1 User 2      User 3\n<\/code><\/pre>\n\n\n\n
    PostgreSQL role membership allows privileges assigned to one role to be inherited by member roles.<\/p>\n\n\n\n
    This makes database permission management much cleaner.<\/p>\n\n\n\n
    \n\n\n\n
    8. Step 1 \u2014 Update Ubuntu<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo apt update\n<\/code><\/pre>\n\n\n\n
    Upgrade packages:<\/p>\n\n\n\n
    sudo apt upgrade -y\n<\/code><\/pre>\n\n\n\n
    Install basic utilities:<\/p>\n\n\n\n
    sudo apt install -y \\\n  curl \\\n  wget \\\n  gpg \\\n  lsb-release \\\n  jq\n<\/code><\/pre>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    jq --version\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    9. Step 2 \u2014 Install PostgreSQL<\/h1>\n\n\n\n
    Install PostgreSQL server and client packages:<\/p>\n\n\n\n
    sudo apt install -y \\\n  postgresql \\\n  postgresql-client \\\n  postgresql-contrib\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    10. Step 3 \u2014 Verify PostgreSQL Version<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    psql --version\n<\/code><\/pre>\n\n\n\n
    Example:<\/p>\n\n\n\n
    psql (PostgreSQL) 16.x\n<\/code><\/pre>\n\n\n\n
    Your PostgreSQL major\/minor version may differ depending on your Ubuntu release.<\/p>\n\n\n\n
    That is acceptable for this lab.<\/p>\n\n\n\n
    \n\n\n\n
    11. Step 4 \u2014 Start PostgreSQL<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo systemctl enable --now postgresql\n<\/code><\/pre>\n\n\n\n
    Check:<\/p>\n\n\n\n
    sudo systemctl status postgresql --no-pager\n<\/code><\/pre>\n\n\n\n
    You should see:<\/p>\n\n\n\n
    active\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    12. Step 5 \u2014 Verify PostgreSQL Is Listening<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    pg_isready\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    \/var\/run\/postgresql:5432 - accepting connections\n<\/code><\/pre>\n\n\n\n
    Also check port 5432:<\/p>\n\n\n\n
    ss -lnt | grep 5432\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    13. Step 6 \u2014 Connect as PostgreSQL Administrator<\/h1>\n\n\n\n
    Ubuntu normally creates a Linux account named:<\/p>\n\n\n\n
    postgres\n<\/code><\/pre>\n\n\n\n
    and a PostgreSQL superuser named:<\/p>\n\n\n\n
    postgres\n<\/code><\/pre>\n\n\n\n
    Test it:<\/p>\n\n\n\n
    sudo -u postgres psql\n<\/code><\/pre>\n\n\n\n
    You should enter:<\/p>\n\n\n\n
    postgres=#\n<\/code><\/pre>\n\n\n\n
    Exit:<\/p>\n\n\n\n
    \\q\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    14. Step 7 \u2014 Create the Lab Working Directory<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    mkdir -p \"$HOME\/vault-postgresql-lab\"\n<\/code><\/pre>\n\n\n\n
    Set:<\/p>\n\n\n\n
    export LAB_DIR=\"$HOME\/vault-postgresql-lab\"\n<\/code><\/pre>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    echo \"$LAB_DIR\"\n<\/code><\/pre>\n\n\n\n
    Move into it:<\/p>\n\n\n\n
    cd \"$LAB_DIR\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    15. Step 8 \u2014 Create the PostgreSQL Vault Administrator<\/h1>\n\n\n\n
    Vault should not connect to PostgreSQL using the actual:<\/p>\n\n\n\n
    postgres\n<\/code><\/pre>\n\n\n\n
    superuser.<\/p>\n\n\n\n
    HashiCorp recommends using a database-specific account for Vault with only the permissions necessary to create, update, and delete managed database users.<\/p>\n\n\n\n
    Create:<\/p>\n\n\n\n
    vaultadmin\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql <<'SQL'\nSET password_encryption = 'scram-sha-256';\n\nCREATE ROLE vaultadmin\nWITH\n    LOGIN\n    CREATEROLE\n    PASSWORD 'VaultAdmin2026';\nSQL\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    SET\nCREATE ROLE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    16. Why Does vaultadmin<\/code> Need CREATEROLE?<\/h1>\n\n\n\n
    Vault must dynamically execute operations such as:<\/p>\n\n\n\n
    CREATE ROLE ...\nALTER ROLE ...\nDROP ROLE ...\n<\/code><\/pre>\n\n\n\n
    The account therefore needs permission to manage the PostgreSQL roles created by Vault.<\/p>\n\n\n\n
    PostgreSQL documents that a CREATEROLE<\/code> 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.<\/p>\n\n\n\n
    \n\n\n\n
    17. Step 9 \u2014 Create the Application Permission Role<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql <<'SQL'\nCREATE ROLE app_crud NOLOGIN;\nSQL\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    CREATE ROLE\n<\/code><\/pre>\n\n\n\n
    This role cannot directly log in.<\/p>\n\n\n\n
    Its purpose is only to hold database privileges.<\/p>\n\n\n\n
    \n\n\n\n
    18. Step 10 \u2014 Allow Vault to Grant app_crud<\/code><\/h1>\n\n\n\n
    Vault’s:<\/p>\n\n\n\n
    vaultadmin\n<\/code><\/pre>\n\n\n\n
    account must be allowed to grant membership in:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    to dynamically created accounts.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql <<'SQL'\nGRANT app_crud TO vaultadmin WITH ADMIN OPTION;\nSQL\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    GRANT ROLE\n<\/code><\/pre>\n\n\n\n
    WITH ADMIN OPTION<\/code> allows vaultadmin<\/code> to grant and revoke membership in app_crud<\/code>.<\/p>\n\n\n\n
    \n\n\n\n
    19. Step 11 \u2014 Create the Training Database<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    vaultlab\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres createdb vaultlab\n<\/code><\/pre>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    sudo -u postgres psql -lqt | cut -d '|' -f 1\n<\/code><\/pre>\n\n\n\n
    You should find:<\/p>\n\n\n\n
    vaultlab\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    20. Step 12 \u2014 Create a Test Table<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    public.app_data\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql -d vaultlab <<'SQL'\nCREATE TABLE public.app_data (\n    id BIGSERIAL PRIMARY KEY,\n    item_name VARCHAR(100) NOT NULL,\n    quantity INTEGER NOT NULL DEFAULT 0\n);\n\nINSERT INTO public.app_data\n    (item_name, quantity)\nVALUES\n    ('linux-server', 10),\n    ('vault-server', 5),\n    ('postgresql-server', 3);\nSQL\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    CREATE TABLE\nINSERT 0 3\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    21. Step 13 \u2014 Examine the Table<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d vaultlab \\\n  -c \"SELECT * FROM public.app_data;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
     id |      item_name       | quantity\n----+----------------------+----------\n  1 | linux-server         |       10\n  2 | vault-server         |        5\n  3 | postgresql-server    |        3\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    22. Step 14 \u2014 Restrict Public Schema Creation<\/h1>\n\n\n\n
    To make our later negative permission tests deterministic, remove general CREATE permission from the public<\/code> schema.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d vaultlab \\\n  -c \"REVOKE CREATE ON SCHEMA public FROM PUBLIC;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    REVOKE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    23. Step 15 \u2014 Give app_crud<\/code> Database Permissions<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql -d vaultlab <<'SQL'\nGRANT CONNECT\nON DATABASE vaultlab\nTO app_crud;\n\nGRANT USAGE\nON SCHEMA public\nTO app_crud;\n\nGRANT SELECT, INSERT, UPDATE, DELETE\nON TABLE public.app_data\nTO app_crud;\n\nGRANT USAGE, SELECT\nON SEQUENCE public.app_data_id_seq\nTO app_crud;\nSQL\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    GRANT\nGRANT\nGRANT\nGRANT\n<\/code><\/pre>\n\n\n\n
    The sequence permission is necessary because our BIGSERIAL<\/code> ID uses:<\/p>\n\n\n\n
    app_data_id_seq\n<\/code><\/pre>\n\n\n\n
    when rows are inserted.<\/p>\n\n\n\n
    \n\n\n\n
    24. Step 16 \u2014 Allow vaultadmin<\/code> to Connect<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d vaultlab \\\n  -c \"GRANT CONNECT ON DATABASE vaultlab TO vaultadmin;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    GRANT\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    25. PostgreSQL Permission Architecture<\/h1>\n\n\n\n
    We now have:<\/p>\n\n\n\n
    postgres\n  |\n  +-- vaultadmin\n  |      LOGIN\n  |      CREATEROLE\n  |      ADMIN OPTION on app_crud\n  |\n  +-- app_crud\n         NOLOGIN\n\n         CONNECT vaultlab\n         USAGE public schema\n         SELECT app_data\n         INSERT app_data\n         UPDATE app_data\n         DELETE app_data\n         sequence access\n<\/code><\/pre>\n\n\n\n
    Later:<\/p>\n\n\n\n
    Vault\n   |\n   +-- creates dynamic-user-123\n              |\n              +-- member of app_crud\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    26. Step 17 \u2014 Verify PostgreSQL Roles<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"\\du vaultadmin\"\n<\/code><\/pre>\n\n\n\n
    Then:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"\\du app_crud\"\n<\/code><\/pre>\n\n\n\n
    You should see:<\/p>\n\n\n\n
    vaultadmin\n<\/code><\/pre>\n\n\n\n
    with:<\/p>\n\n\n\n
    Create role\n<\/code><\/pre>\n\n\n\n
    and:<\/p>\n\n\n\n
    app_crud\n<\/code><\/pre>\n\n\n\n
    without login privileges.<\/p>\n\n\n\n
    \n\n\n\n
    27. Step 18 \u2014 Test Vault’s PostgreSQL Account<\/h1>\n\n\n\n
    This test is extremely important.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD='VaultAdmin2026' \\\npsql \\\n  -h 127.0.0.1 \\\n  -U vaultadmin \\\n  -d vaultlab \\\n  -c \"SELECT current_user, current_database();\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
     current_user | current_database\n--------------+-----------------\n vaultadmin   | vaultlab\n<\/code><\/pre>\n\n\n\n
    If this command fails, do not continue to Vault yet<\/strong>.<\/p>\n\n\n\n
    Vault will use essentially the same type of TCP connection.<\/p>\n\n\n\n
    \n\n\n\n
    28. Troubleshooting PostgreSQL Password Authentication<\/h1>\n\n\n\n
    If you receive:<\/p>\n\n\n\n
    password authentication failed\n<\/code><\/pre>\n\n\n\n
    find the PostgreSQL HBA configuration:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -tAc \"SHOW hba_file;\"\n<\/code><\/pre>\n\n\n\n
    Then inspect it:<\/p>\n\n\n\n
    HBA_FILE=\"$(\n  sudo -u postgres psql \\\n    -tAc \"SHOW hba_file;\" \\\n    | xargs\n)\"\n<\/code><\/pre>\n\n\n\n
    Display active rules:<\/p>\n\n\n\n
    sudo grep -vE '^[[:space:]]*(#|$)' \"$HBA_FILE\"\n<\/code><\/pre>\n\n\n\n
    You need an appropriate host rule for connections from:<\/p>\n\n\n\n
    127.0.0.1\n<\/code><\/pre>\n\n\n\n
    PostgreSQL client authentication is controlled by pg_hba.conf<\/code>; changes require the configuration to be reloaded.<\/p>\n\n\n\n
    Modern PostgreSQL supports SCRAM-SHA-256 password authentication.<\/p>\n\n\n\n
    \n\n\n\n
    29. PostgreSQL Checkpoint<\/h1>\n\n\n\n
    Do not continue unless all of these work:<\/p>\n\n\n\n
    [ ] PostgreSQL is running\n[ ] port 5432 is available\n[ ] vaultlab exists\n[ ] public.app_data exists\n[ ] vaultadmin exists\n[ ] app_crud exists\n[ ] vaultadmin has CREATEROLE\n[ ] vaultadmin has ADMIN OPTION on app_crud\n[ ] vaultadmin can connect using TCP\/password\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    30. Step 19 \u2014 Install Vault<\/h1>\n\n\n\n
    Add the official repository signing key:<\/p>\n\n\n\n
    wget -O- https:\/\/apt.releases.hashicorp.com\/gpg \\\n  | sudo gpg --dearmor \\\n  -o \/usr\/share\/keyrings\/hashicorp-archive-keyring.gpg\n<\/code><\/pre>\n\n\n\n
    Add the repository:<\/p>\n\n\n\n
    echo \\\n\"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\" \\\n| sudo tee \/etc\/apt\/sources.list.d\/hashicorp.list\n<\/code><\/pre>\n\n\n\n
    Update:<\/p>\n\n\n\n
    sudo apt update\n<\/code><\/pre>\n\n\n\n
    Install Vault:<\/p>\n\n\n\n
    sudo apt install -y vault\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    31. Step 20 \u2014 Verify Vault Version<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault version\n<\/code><\/pre>\n\n\n\n
    For the current release at the time this tutorial was prepared, you should see the Vault 2.0.4 release line.<\/p>\n\n\n\n
    The exact build suffix may differ.<\/p>\n\n\n\n
    \n\n\n\n
    32. Step 21 \u2014 Stop Any Existing Vault Service<\/h1>\n\n\n\n
    Because this tutorial uses Vault development mode:<\/p>\n\n\n\n
    sudo systemctl stop vault 2>\/dev\/null || true\n<\/code><\/pre>\n\n\n\n
    Check:<\/p>\n\n\n\n
    pgrep -a vault || true\n<\/code><\/pre>\n\n\n\n
    Ideally there should be no existing Vault process.<\/p>\n\n\n\n
    \n\n\n\n
    33. Step 22 \u2014 Start Vault Development Server<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    nohup vault server \\\n  -dev \\\n  -dev-root-token-id='root' \\\n  -dev-listen-address='127.0.0.1:8200' \\\n  > \"$LAB_DIR\/vault.log\" 2>&1 &\n<\/code><\/pre>\n\n\n\n
    Save its PID:<\/p>\n\n\n\n
    echo $! > \"$LAB_DIR\/vault.pid\"\n<\/code><\/pre>\n\n\n\n
    Wait:<\/p>\n\n\n\n
    sleep 2\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    34. Step 23 \u2014 Configure the Vault CLI<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    export VAULT_ADDR='http:\/\/127.0.0.1:8200'\n<\/code><\/pre>\n\n\n\n
    Set root token:<\/p>\n\n\n\n
    export VAULT_TOKEN='root'\n<\/code><\/pre>\n\n\n\n
    Check:<\/p>\n\n\n\n
    echo \"$VAULT_ADDR\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    http://127.0.0.1:8200\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    35. Step 24 \u2014 Verify Vault<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault status\n<\/code><\/pre>\n\n\n\n
    Important fields:<\/p>\n\n\n\n
    Initialized    true\nSealed         false\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    36. Step 25 \u2014 Verify Root Identity<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault token lookup\n<\/code><\/pre>\n\n\n\n
    You should see:<\/p>\n\n\n\n
    policies    [root]\n<\/code><\/pre>\n\n\n\n
    Everything through the initial Vault configuration will be performed using the root token.<\/p>\n\n\n\n
    Later we remove it.<\/p>\n\n\n\n
    \n\n\n\n
    37. Step 26 \u2014 Examine Existing Secrets Engines<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault secrets list\n<\/code><\/pre>\n\n\n\n
    Look for:<\/p>\n\n\n\n
    database\/\n<\/code><\/pre>\n\n\n\n
    On a fresh dev server, it should normally not exist.<\/p>\n\n\n\n
    \n\n\n\n
    38. Step 27 \u2014 Enable the Database Secrets Engine<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault secrets enable database\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Enabled the database secrets engine at: database\/\n<\/code><\/pre>\n\n\n\n
    This is the documented way to enable Vault’s database secrets engine.<\/p>\n\n\n\n
    \n\n\n\n
    39. Step 28 \u2014 Verify the Engine<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault secrets list\n<\/code><\/pre>\n\n\n\n
    You should now find:<\/p>\n\n\n\n
    database\/\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    40. Step 29 \u2014 Configure the PostgreSQL Connection<\/h1>\n\n\n\n
    Create the database connection:<\/p>\n\n\n\n
    vault write database\/config\/vaultlab-postgres \\\n  plugin_name='postgresql-database-plugin' \\\n  allowed_roles='app-crud' \\\n  connection_url='postgresql:\/\/{{username}}:{{password}}@127.0.0.1:5432\/vaultlab?sslmode=disable' \\\n  username='vaultadmin' \\\n  password='VaultAdmin2026' \\\n  password_authentication='scram-sha-256' \\\n  max_open_connections=5\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    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<\/code>, and scram-sha-256<\/code>.<\/p>\n\n\n\n
    \n\n\n\n
    41. What Did That Command Do?<\/h1>\n\n\n\n
    This part:<\/p>\n\n\n\n
    database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    creates a named Vault database configuration.<\/p>\n\n\n\n
    This:<\/p>\n\n\n\n
    plugin_name=postgresql-database-plugin\n<\/code><\/pre>\n\n\n\n
    tells Vault:<\/p>\n\n\n\n
    Use PostgreSQL.\n<\/code><\/pre>\n\n\n\n
    This:<\/p>\n\n\n\n
    allowed_roles=app-crud\n<\/code><\/pre>\n\n\n\n
    means:<\/p>\n\n\n\n
    Only the Vault database role named app-crud\nmay use this database connection.\n<\/code><\/pre>\n\n\n\n
    This:<\/p>\n\n\n\n
    {{username}}\n{{password}}\n<\/code><\/pre>\n\n\n\n
    allows Vault to insert:<\/p>\n\n\n\n
    vaultadmin\nVaultAdmin2026\n<\/code><\/pre>\n\n\n\n
    without hardcoding those values directly into the URL itself.<\/p>\n\n\n\n
    \n\n\n\n
    42. Why sslmode=disable<\/code>?<\/h1>\n\n\n\n
    Both Vault and PostgreSQL are running on:<\/p>\n\n\n\n
    127.0.0.1\n<\/code><\/pre>\n\n\n\n
    on the same training server.<\/p>\n\n\n\n
    Therefore this beginner lab disables PostgreSQL TLS to remove certificate configuration from the exercise.<\/p>\n\n\n\n
    For a production database:<\/p>\n\n\n\n
    use TLS\nvalidate certificates\nprotect database traffic\n<\/code><\/pre>\n\n\n\n
    The PostgreSQL Vault plugin supports PostgreSQL SSL connection parameters.<\/p>\n\n\n\n
    \n\n\n\n
    43. Why password_authentication=scram-sha-256<\/code>?<\/h1>\n\n\n\n
    Vault’s PostgreSQL plugin supports:<\/p>\n\n\n\n
    password\nscram-sha-256\n<\/code><\/pre>\n\n\n\n
    When configured for:<\/p>\n\n\n\n
    scram-sha-256\n<\/code><\/pre>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n\n\n\n
    44. Step 30 \u2014 Read the Database Configuration<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    You should see information such as:<\/p>\n\n\n\n
    allowed_roles\nconnection_details\nplugin_name\n<\/code><\/pre>\n\n\n\n
    Vault does not simply return the configured database password to you.<\/p>\n\n\n\n
    \n\n\n\n
    45. Step 31 \u2014 List Database Connections<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault list database\/config\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Keys\n----\nvaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    46. Vault-to-PostgreSQL Checkpoint<\/h1>\n\n\n\n
    At this stage:<\/p>\n\n\n\n
    Vault\n  |\n  |\ndatabase\/\n  |\n  |\nvaultlab-postgres\n  |\n  |\npostgresql-database-plugin\n  |\n  |\n127.0.0.1:5432\n  |\n  |\nvaultadmin\n  |\n  |\nPostgreSQL vaultlab\n<\/code><\/pre>\n\n\n\n
    The connection is working.<\/p>\n\n\n\n
    \n\n\n\n
    47. Step 32 \u2014 Create SQL for Dynamic Accounts<\/h1>\n\n\n\n
    Now define what Vault should do whenever credentials are requested.<\/p>\n\n\n\n
    Create:<\/p>\n\n\n\n
    cat > \"$LAB_DIR\/create-app-crud.sql\" <<'EOF'\nCREATE ROLE \"{{name}}\"\nWITH LOGIN\nPASSWORD '{{password}}'\nVALID UNTIL '{{expiration}}'\nINHERIT;\n\nGRANT app_crud TO \"{{name}}\";\nEOF\n<\/code><\/pre>\n\n\n\n
    Display:<\/p>\n\n\n\n
    cat \"$LAB_DIR\/create-app-crud.sql\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    48. Understanding the Template Variables<\/h1>\n\n\n\n
    Vault replaces:<\/p>\n\n\n\n
    {{name}}\n<\/code><\/pre>\n\n\n\n
    with a dynamically generated PostgreSQL username.<\/p>\n\n\n\n
    Vault replaces:<\/p>\n\n\n\n
    {{password}}\n<\/code><\/pre>\n\n\n\n
    with a dynamically generated password.<\/p>\n\n\n\n
    Vault replaces:<\/p>\n\n\n\n
    {{expiration}}\n<\/code><\/pre>\n\n\n\n
    with the credential expiration time.<\/p>\n\n\n\n
    HashiCorp’s current PostgreSQL plugin documents all three template fields for creation_statements<\/code>.<\/p>\n\n\n\n
    \n\n\n\n
    49. Step 33 \u2014 Create Renewal SQL<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    cat > \"$LAB_DIR\/renew-app-crud.sql\" <<'EOF'\nALTER ROLE \"{{name}}\"\nVALID UNTIL '{{expiration}}';\nEOF\n<\/code><\/pre>\n\n\n\n
    Display:<\/p>\n\n\n\n
    cat \"$LAB_DIR\/renew-app-crud.sql\"\n<\/code><\/pre>\n\n\n\n
    When Vault renews a credential lease, this changes PostgreSQL’s:<\/p>\n\n\n\n
    VALID UNTIL\n<\/code><\/pre>\n\n\n\n
    timestamp.<\/p>\n\n\n\n
    The PostgreSQL plugin supports renew_statements<\/code> with both {{name}}<\/code> and {{expiration}}<\/code>.<\/p>\n\n\n\n
    \n\n\n\n
    50. Step 34 \u2014 Create Revocation SQL<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    cat > \"$LAB_DIR\/revoke-app-crud.sql\" <<'EOF'\nREVOKE app_crud FROM \"{{name}}\";\nDROP ROLE IF EXISTS \"{{name}}\";\nEOF\n<\/code><\/pre>\n\n\n\n
    Display:<\/p>\n\n\n\n
    cat \"$LAB_DIR\/revoke-app-crud.sql\"\n<\/code><\/pre>\n\n\n\n
    When Vault revokes a credential:<\/p>\n\n\n\n
    1. membership in app_crud is removed\n2. the dynamic PostgreSQL login is deleted\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    51. Step 35 \u2014 Create Rollback SQL<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    cat > \"$LAB_DIR\/rollback-app-crud.sql\" <<'EOF'\nREVOKE app_crud FROM \"{{name}}\";\nDROP ROLE IF EXISTS \"{{name}}\";\nEOF\n<\/code><\/pre>\n\n\n\n
    Rollback is useful if Vault starts creating credentials but something in the creation workflow fails.<\/p>\n\n\n\n
    The PostgreSQL plugin supports creation, revocation, rollback, and renewal statement types.<\/p>\n\n\n\n
    \n\n\n\n
    52. Step 36 \u2014 Create the Vault Dynamic Database Role<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write database\/roles\/app-crud \\\n  db_name='vaultlab-postgres' \\\n  creation_statements=@\"$LAB_DIR\/create-app-crud.sql\" \\\n  renew_statements=@\"$LAB_DIR\/renew-app-crud.sql\" \\\n  revocation_statements=@\"$LAB_DIR\/revoke-app-crud.sql\" \\\n  rollback_statements=@\"$LAB_DIR\/rollback-app-crud.sql\" \\\n  default_ttl='10m' \\\n  max_ttl='1h'\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    HashiCorp’s current database tutorial explicitly supports loading creation statements from a file using syntax such as:<\/p>\n\n\n\n
    creation_statements=@file.sql\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    53. Step 37 \u2014 Read the Dynamic Role<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    You should see fields including:<\/p>\n\n\n\n
    creation_statements\ndb_name\ndefault_ttl\nmax_ttl\nrenew_statements\nrevocation_statements\nrollback_statements\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    54. Step 38 \u2014 List Database Roles<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault list database\/roles\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Keys\n----\napp-crud\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    55. Step 39 \u2014 Test Dynamic Credentials as Root<\/h1>\n\n\n\n
    Before introducing userpass<\/code>, verify that the database integration itself works.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected output resembles:<\/p>\n\n\n\n
    Key                Value\n---                -----\nlease_id           database\/creds\/app-crud\/...\nlease_duration     10m\nlease_renewable    true\npassword           <generated-password>\nusername           <generated-username>\n<\/code><\/pre>\n\n\n\n
    Vault’s current database workflow generates credentials by reading:<\/p>\n\n\n\n
    database\/creds\/<role>\n<\/code><\/pre>\n\n\n\n
    This proves:<\/p>\n\n\n\n
    Vault\n   \u2193\nPostgreSQL plugin\n   \u2193\nvaultadmin\n   \u2193\nCREATE ROLE\n   \u2193\nGRANT app_crud\n<\/code><\/pre>\n\n\n\n
    works.<\/p>\n\n\n\n
    We will generate a fresh credential later as student1<\/code>.<\/p>\n\n\n\n
    \n\n\n\n
    56. Step 40 \u2014 Create the Vault Policy<\/h1>\n\n\n\n
    Now create:<\/p>\n\n\n\n
    postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    This policy gives our student complete CRUD-style access underneath:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    and individual lease lookup, renewal, and revocation.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    cat > \"$LAB_DIR\/postgresql-engine-admin.hcl\" <<'EOF'\n# Complete CRUD-style access to the mounted database secrets engine.\n#\n# LAB ONLY:\n# This includes database connection configuration,\n# roles, credential-generation endpoints, reset endpoints,\n# rotate-root endpoints, and other database\/* operations.\n\npath \"database\/*\" {\n  capabilities = [\n    \"create\",\n    \"read\",\n    \"update\",\n    \"delete\",\n    \"list\"\n  ]\n}\n\n# Lookup a specific dynamic-secret lease.\n\npath \"sys\/leases\/lookup\" {\n  capabilities = [\"update\"]\n}\n\n# Renew a specific lease.\n\npath \"sys\/leases\/renew\" {\n  capabilities = [\"update\"]\n}\n\n# Revoke a specific lease.\n\npath \"sys\/leases\/revoke\" {\n  capabilities = [\"update\"]\n}\nEOF\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    57. Step 41 \u2014 Examine the Vault Policy<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    cat \"$LAB_DIR\/postgresql-engine-admin.hcl\"\n<\/code><\/pre>\n\n\n\n
    The important section is:<\/p>\n\n\n\n
    path \"database\/*\" {\n  capabilities = [\n    \"create\",\n    \"read\",\n    \"update\",\n    \"delete\",\n    \"list\"\n  ]\n}\n<\/code><\/pre>\n\n\n\n
    That gives:<\/p>\n\n\n\n
    CREATE\nREAD\nUPDATE\nDELETE\nLIST\n<\/code><\/pre>\n\n\n\n
    capabilities inside the database secrets engine.<\/p>\n\n\n\n
    HashiCorp’s current database tutorial uses the same full database-engine capability model for an administrative database policy.<\/p>\n\n\n\n
    \n\n\n\n
    58. Important Policy Explanation<\/h1>\n\n\n\n
    A Vault capability does not mean every API endpoint supports every operation.<\/p>\n\n\n\n
    For example:<\/p>\n\n\n\n
    database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    is fundamentally a credential generation endpoint.<\/p>\n\n\n\n
    Its supported operation is:<\/p>\n\n\n\n
    READ\n<\/code><\/pre>\n\n\n\n
    Even though our broad wildcard policy technically grants multiple capabilities to that path, the backend endpoint itself determines which operation is valid.<\/p>\n\n\n\n
    Think of it as:<\/p>\n\n\n\n
    Policy says:\n\"Student may perform these actions.\"\n\nBackend says:\n\"This particular endpoint only implements these actions.\"\n<\/code><\/pre>\n\n\n\n
    Both conditions must be satisfied.<\/p>\n\n\n\n
    \n\n\n\n
    59. Step 42 \u2014 Load the Vault Policy<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault policy write \\\n  postgresql-engine-admin \\\n  \"$LAB_DIR\/postgresql-engine-admin.hcl\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Uploaded policy: postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    60. Step 43 \u2014 Read the Vault Policy<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault policy read postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    Verify the contents.<\/p>\n\n\n\n
    \n\n\n\n
    61. Step 44 \u2014 List Vault Policies<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault policy list\n<\/code><\/pre>\n\n\n\n
    You should find:<\/p>\n\n\n\n
    default\npostgresql-engine-admin\nroot\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    62. Step 45 \u2014 Enable Userpass Authentication<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault auth enable userpass\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Enabled userpass auth method at: userpass\/\n<\/code><\/pre>\n\n\n\n
    Vault’s current userpass auth method authenticates users from username\/password entries configured underneath:<\/p>\n\n\n\n
    auth\/userpass\/users\/\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    63. Step 46 \u2014 Verify Userpass<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault auth list\n<\/code><\/pre>\n\n\n\n
    You should see:<\/p>\n\n\n\n
    userpass\/\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    64. Step 47 \u2014 Create One Vault User<\/h1>\n\n\n\n
    Create:<\/p>\n\n\n\n
    Username: student1\nPassword: StudentLab2026\nPolicy: postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write auth\/userpass\/users\/student1 \\\n  password='StudentLab2026' \\\n  policies='postgresql-engine-admin' \\\n  token_ttl='1h' \\\n  token_max_ttl='4h'\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: auth\/userpass\/users\/student1\n<\/code><\/pre>\n\n\n\n
    Userpass supports policy assignment and token TTL\/max-TTL configuration.<\/p>\n\n\n\n
    \n\n\n\n
    65. Step 48 \u2014 Read the User<\/h1>\n\n\n\n
    Still as root:<\/p>\n\n\n\n
    vault read auth\/userpass\/users\/student1\n<\/code><\/pre>\n\n\n\n
    You should see information such as:<\/p>\n\n\n\n
    token_policies\ntoken_ttl\ntoken_max_ttl\n<\/code><\/pre>\n\n\n\n
    You will not see the plaintext password returned.<\/p>\n\n\n\n
    \n\n\n\n
    66. Step 49 \u2014 List Userpass Users<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault list auth\/userpass\/users\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Keys\n----\nstudent1\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    67. Administrative Setup Is Complete<\/h1>\n\n\n\n
    Root has now completed:<\/p>\n\n\n\n
    PostgreSQL installation\n        \u2193\nDatabase creation\n        \u2193\nvaultadmin creation\n        \u2193\napp_crud PostgreSQL role\n        \u2193\nVault database engine\n        \u2193\nPostgreSQL plugin\n        \u2193\nVault DB connection\n        \u2193\nVault DB dynamic role\n        \u2193\nVault policy\n        \u2193\nuserpass\n        \u2193\nstudent1\n<\/code><\/pre>\n\n\n\n
    Now we switch to the student identity.<\/p>\n\n\n\n
    \n\n\n\n
    68. Step 50 \u2014 Remove the Root Token<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    unset VAULT_TOKEN\n<\/code><\/pre>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    echo \"${VAULT_TOKEN:-VAULT_TOKEN is not set}\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    VAULT_TOKEN is not set\n<\/code><\/pre>\n\n\n\n
    This step is critical.<\/p>\n\n\n\n
    Otherwise later authorization tests would accidentally execute as:<\/p>\n\n\n\n
    root\n<\/code><\/pre>\n\n\n\n
    and become meaningless.<\/p>\n\n\n\n
    \n\n\n\n
    69. Step 51 \u2014 Login as student1<\/code><\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault login \\\n  -method=userpass \\\n  username='student1' \\\n  password='StudentLab2026'\n<\/code><\/pre>\n\n\n\n
    Expected output contains information similar to:<\/p>\n\n\n\n
    token                 <generated-vault-token>\ntoken_duration        1h\ntoken_renewable       true\ntoken_policies        [\"default\" \"postgresql-engine-admin\"]\n<\/code><\/pre>\n\n\n\n
    The actual token is different each time.<\/p>\n\n\n\n
    \n\n\n\n
    70. Step 52 \u2014 Verify Student Identity<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault token lookup\n<\/code><\/pre>\n\n\n\n
    You should find:<\/p>\n\n\n\n
    postgresql-engine-admin\ndefault\n<\/code><\/pre>\n\n\n\n
    You should not<\/strong> find:<\/p>\n\n\n\n
    root\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    71. Step 53 \u2014 Verify Database Role Capabilities<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault token capabilities \\\n  database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected capabilities include:<\/p>\n\n\n\n
    create\ndelete\nlist\nread\nupdate\n<\/code><\/pre>\n\n\n\n
    The exact ordering may differ.<\/p>\n\n\n\n
    \n\n\n\n
    72. Step 54 \u2014 Verify Configuration Capabilities<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault token capabilities \\\n  database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    Again, you should see CRUD-style capabilities.<\/p>\n\n\n\n
    This confirms the student has broad administrative access inside:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    73. Step 55 \u2014 Negative Vault Permission Test<\/h1>\n\n\n\n
    Vault dev mode also normally provides a KV secrets engine mounted at:<\/p>\n\n\n\n
    secret\/\n<\/code><\/pre>\n\n\n\n
    Try:<\/p>\n\n\n\n
    vault kv put \\\n  secret\/student-should-not-access \\\n  test=value\n<\/code><\/pre>\n\n\n\n
    This should fail with:<\/p>\n\n\n\n
    permission denied\n<\/code><\/pre>\n\n\n\n
    That failure is correct<\/strong>.<\/p>\n\n\n\n
    Our policy grants access to:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    not:<\/p>\n\n\n\n
    secret\/*\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    74. Vault Authorization Model<\/h1>\n\n\n\n
    At this point:<\/p>\n\n\n\n
    student1\n   |\n   | userpass login\n   v\nVault Token\n   |\n   | postgresql-engine-admin\n   v\ndatabase\/*\n   |\n   +-- config\n   +-- roles\n   +-- creds\n   +-- reset\n   +-- rotate-root\n   +-- other database engine operations\n<\/code><\/pre>\n\n\n\n
    but:<\/p>\n\n\n\n
    secret\/*\n<\/code><\/pre>\n\n\n\n
    remains denied.<\/p>\n\n\n\n
    \n\n\n\n
    75. Step 56 \u2014 Demonstrate CREATE<\/h1>\n\n\n\n
    First delete the role that root created earlier so the student can demonstrate the complete lifecycle.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault delete database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data deleted (if it existed) at: database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Confirm:<\/p>\n\n\n\n
    vault list database\/roles\n<\/code><\/pre>\n\n\n\n
    The role should be absent.<\/p>\n\n\n\n
    Now CREATE it as student1<\/code>:<\/p>\n\n\n\n
    vault write database\/roles\/app-crud \\\n  db_name='vaultlab-postgres' \\\n  creation_statements=@\"$LAB_DIR\/create-app-crud.sql\" \\\n  renew_statements=@\"$LAB_DIR\/renew-app-crud.sql\" \\\n  revocation_statements=@\"$LAB_DIR\/revoke-app-crud.sql\" \\\n  rollback_statements=@\"$LAB_DIR\/rollback-app-crud.sql\" \\\n  default_ttl='10m' \\\n  max_ttl='1h'\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    That demonstrates:<\/p>\n\n\n\n
    CREATE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    76. Step 57 \u2014 Demonstrate READ<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    That demonstrates:<\/p>\n\n\n\n
    READ\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    77. Step 58 \u2014 Demonstrate LIST<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault list database\/roles\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Keys\n----\napp-crud\n<\/code><\/pre>\n\n\n\n
    That demonstrates:<\/p>\n\n\n\n
    LIST\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    78. Step 59 \u2014 Demonstrate UPDATE<\/h1>\n\n\n\n
    Change:<\/p>\n\n\n\n
    default TTL\n10 minutes\n<\/code><\/pre>\n\n\n\n
    to:<\/p>\n\n\n\n
    5 minutes\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write database\/roles\/app-crud \\\n  db_name='vaultlab-postgres' \\\n  creation_statements=@\"$LAB_DIR\/create-app-crud.sql\" \\\n  renew_statements=@\"$LAB_DIR\/renew-app-crud.sql\" \\\n  revocation_statements=@\"$LAB_DIR\/revoke-app-crud.sql\" \\\n  rollback_statements=@\"$LAB_DIR\/rollback-app-crud.sql\" \\\n  default_ttl='5m' \\\n  max_ttl='1h'\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Read it:<\/p>\n\n\n\n
    vault read database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Confirm:<\/p>\n\n\n\n
    default_ttl\n<\/code><\/pre>\n\n\n\n
    now corresponds to five minutes.<\/p>\n\n\n\n
    That demonstrates:<\/p>\n\n\n\n
    UPDATE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    79. Step 60 \u2014 Demonstrate DELETE<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault delete database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data deleted (if it existed) at: database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Confirm:<\/p>\n\n\n\n
    vault read database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    No value found\n<\/code><\/pre>\n\n\n\n
    That demonstrates:<\/p>\n\n\n\n
    DELETE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    80. CRUD Checkpoint<\/h1>\n\n\n\n
    The student has now proven:<\/p>\n\n\n\n
    CREATE\nvault write database\/roles\/app-crud\n\nREAD\nvault read database\/roles\/app-crud\n\nUPDATE\nvault write database\/roles\/app-crud\n\nLIST\nvault list database\/roles\n\nDELETE\nvault delete database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    This validates the Vault ACL policy.<\/p>\n\n\n\n
    \n\n\n\n
    81. Step 61 \u2014 Recreate the Dynamic Role<\/h1>\n\n\n\n
    We need it for the rest of the lab.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write database\/roles\/app-crud \\\n  db_name='vaultlab-postgres' \\\n  creation_statements=@\"$LAB_DIR\/create-app-crud.sql\" \\\n  renew_statements=@\"$LAB_DIR\/renew-app-crud.sql\" \\\n  revocation_statements=@\"$LAB_DIR\/revoke-app-crud.sql\" \\\n  rollback_statements=@\"$LAB_DIR\/rollback-app-crud.sql\" \\\n  default_ttl='10m' \\\n  max_ttl='1h'\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    82. Step 62 \u2014 Generate Dynamic PostgreSQL Credentials<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected output:<\/p>\n\n\n\n
    Key                Value\n---                -----\nlease_id           database\/creds\/app-crud\/...\nlease_duration     10m\nlease_renewable    true\npassword           ...\nusername           ...\n<\/code><\/pre>\n\n\n\n
    Every request creates a different PostgreSQL credential.<\/p>\n\n\n\n
    \n\n\n\n
    83. Step 63 \u2014 Capture Credentials Automatically<\/h1>\n\n\n\n
    Instead of copying them manually:<\/p>\n\n\n\n
    CREDS_JSON=\"$(\n  vault read \\\n    -format=json \\\n    database\/creds\/app-crud\n)\"\n<\/code><\/pre>\n\n\n\n
    Extract username:<\/p>\n\n\n\n
    export DB_USER=\"$(\n  printf '%s' \"$CREDS_JSON\" \\\n  | jq -r '.data.username'\n)\"\n<\/code><\/pre>\n\n\n\n
    Extract password:<\/p>\n\n\n\n
    export DB_PASS=\"$(\n  printf '%s' \"$CREDS_JSON\" \\\n  | jq -r '.data.password'\n)\"\n<\/code><\/pre>\n\n\n\n
    Extract Vault lease ID:<\/p>\n\n\n\n
    export LEASE_ID=\"$(\n  printf '%s' \"$CREDS_JSON\" \\\n  | jq -r '.lease_id'\n)\"\n<\/code><\/pre>\n\n\n\n
    Remove the JSON copy:<\/p>\n\n\n\n
    unset CREDS_JSON\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    84. Step 64 \u2014 Verify Variables<\/h1>\n\n\n\n
    Display the generated username:<\/p>\n\n\n\n
    echo \"$DB_USER\"\n<\/code><\/pre>\n\n\n\n
    It may look similar to:<\/p>\n\n\n\n
    v-userpass-app-crud-...\n<\/code><\/pre>\n\n\n\n
    The exact format and random portion will differ.<\/p>\n\n\n\n
    Verify password exists without displaying it:<\/p>\n\n\n\n
    test -n \"$DB_PASS\" \\\n  && test \"$DB_PASS\" != \"null\" \\\n  && echo \"Dynamic PostgreSQL password captured.\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Dynamic PostgreSQL password captured.\n<\/code><\/pre>\n\n\n\n
    Display lease:<\/p>\n\n\n\n
    echo \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    It should resemble:<\/p>\n\n\n\n
    database\/creds\/app-crud\/<unique-id>\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    85. Step 65 \u2014 Verify the Dynamic PostgreSQL Role Exists<\/h1>\n\n\n\n
    As PostgreSQL administrator:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d postgres \\\n  -c \"SELECT rolname, rolcanlogin, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';\"\n<\/code><\/pre>\n\n\n\n
    You should see the generated username.<\/p>\n\n\n\n
    Important fields:<\/p>\n\n\n\n
    rolcanlogin = true\nrolvaliduntil = future timestamp\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    86. Step 66 \u2014 Verify Group Membership<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d postgres \\\n  -c \"SELECT pg_has_role('$DB_USER', 'app_crud', 'MEMBER') AS member_of_app_crud;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
     member_of_app_crud\n--------------------\n t\n<\/code><\/pre>\n\n\n\n
    This proves Vault executed:<\/p>\n\n\n\n
    GRANT app_crud TO \"<dynamic-user>\";\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    87. Step 67 \u2014 Login to PostgreSQL Using Vault Credentials<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"SELECT current_user, current_database();\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    current_user        | <dynamic-user>\ncurrent_database    | vaultlab\n<\/code><\/pre>\n\n\n\n
    The Vault-generated credential is working.<\/p>\n\n\n\n
    \n\n\n\n
    88. PostgreSQL CRUD Exercise<\/h1>\n\n\n\n
    Now we will prove that the dynamic PostgreSQL account has:<\/p>\n\n\n\n
    CREATE DATA\nREAD DATA\nUPDATE DATA\nDELETE DATA\n<\/code><\/pre>\n\n\n\n
    permission on:<\/p>\n\n\n\n
    public.app_data\n<\/code><\/pre>\n\n\n\n
    These database-level operations are separate from the earlier Vault API CRUD operations.<\/p>\n\n\n\n
    \n\n\n\n
    89. Step 68 \u2014 READ Data with SELECT<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"SELECT * FROM public.app_data;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    linux-server\nvault-server\npostgresql-server\n<\/code><\/pre>\n\n\n\n
    The dynamic user can:<\/p>\n\n\n\n
    SELECT\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    90. Step 69 \u2014 CREATE Data with INSERT<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"INSERT INTO public.app_data (item_name, quantity) VALUES ('vault-dynamic-user', 25) RETURNING *;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    vault-dynamic-user | 25\n<\/code><\/pre>\n\n\n\n
    The dynamic user can:<\/p>\n\n\n\n
    INSERT\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    91. Step 70 \u2014 Verify INSERT<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"SELECT * FROM public.app_data WHERE item_name='vault-dynamic-user';\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    92. Step 71 \u2014 UPDATE Data<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"UPDATE public.app_data SET quantity=50 WHERE item_name='vault-dynamic-user' RETURNING *;\"\n<\/code><\/pre>\n\n\n\n
    Expected quantity:<\/p>\n\n\n\n
    50\n<\/code><\/pre>\n\n\n\n
    The dynamic user can:<\/p>\n\n\n\n
    UPDATE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    93. Step 72 \u2014 DELETE Data<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"DELETE FROM public.app_data WHERE item_name='vault-dynamic-user' RETURNING *;\"\n<\/code><\/pre>\n\n\n\n
    The row should be returned and removed.<\/p>\n\n\n\n
    The dynamic user can:<\/p>\n\n\n\n
    DELETE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    94. PostgreSQL CRUD Proven<\/h1>\n\n\n\n
    We have now demonstrated:<\/p>\n\n\n\n
    READ\nSELECT\n\nCREATE\nINSERT\n\nUPDATE\nUPDATE\n\nDELETE\nDELETE\n<\/code><\/pre>\n\n\n\n
    using a PostgreSQL username and password that:<\/p>\n\n\n\n
    did not exist before the request\n<\/code><\/pre>\n\n\n\n
    and were dynamically created by Vault.<\/p>\n\n\n\n
    \n\n\n\n
    95. Step 73 \u2014 Negative PostgreSQL Test<\/h1>\n\n\n\n
    The dynamic account has data CRUD permissions.<\/p>\n\n\n\n
    It should not<\/strong> be a PostgreSQL administrator.<\/p>\n\n\n\n
    Try creating another table:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"CREATE TABLE public.should_fail (id INTEGER);\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    permission denied for schema public\n<\/code><\/pre>\n\n\n\n
    That failure is correct.<\/p>\n\n\n\n
    \n\n\n\n
    96. Step 74 \u2014 Another Negative Test<\/h1>\n\n\n\n
    Try creating another PostgreSQL role:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"CREATE ROLE should_fail;\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    permission denied to create role\n<\/code><\/pre>\n\n\n\n
    Correct.<\/p>\n\n\n\n
    Therefore:<\/p>\n\n\n\n
    Dynamic user\n    |\n    +-- SELECT     YES\n    +-- INSERT     YES\n    +-- UPDATE     YES\n    +-- DELETE     YES\n    |\n    +-- CREATE TABLE   NO\n    +-- CREATE ROLE    NO\n    +-- SUPERUSER      NO\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    97. Step 75 \u2014 Examine the Vault Lease<\/h1>\n\n\n\n
    Every dynamic database credential receives a Vault lease.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault lease lookup \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    Look for:<\/p>\n\n\n\n
    expire_time\nissue_time\nrenewable\nttl\n<\/code><\/pre>\n\n\n\n
    Vault’s lease API supports lookup, renewal, and immediate revocation of dynamic secrets.<\/p>\n\n\n\n
    \n\n\n\n
    98. Step 76 \u2014 Check PostgreSQL Expiration Before Renewal<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d postgres \\\n  -c \"SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';\"\n<\/code><\/pre>\n\n\n\n
    Take note of:<\/p>\n\n\n\n
    rolvaliduntil\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    99. Step 77 \u2014 Renew the Credential<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault lease renew \\\n  -increment='20m' \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    lease_id          database\/creds\/app-crud\/...\nlease_duration    ...\nlease_renewable   true\n<\/code><\/pre>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n\n\n\n
    100. Step 78 \u2014 Verify PostgreSQL Expiration Changed<\/h1>\n\n\n\n
    Run again:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d postgres \\\n  -c \"SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = '$DB_USER';\"\n<\/code><\/pre>\n\n\n\n
    The:<\/p>\n\n\n\n
    rolvaliduntil\n<\/code><\/pre>\n\n\n\n
    timestamp should reflect the renewed lifetime.<\/p>\n\n\n\n
    This demonstrates:<\/p>\n\n\n\n
    Vault Lease Renewal\n        \u2193\nrenew_statements\n        \u2193\nALTER ROLE\n        \u2193\nPostgreSQL VALID UNTIL updated\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    101. Step 79 \u2014 Confirm the Credential Still Works<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"SELECT COUNT(*) FROM public.app_data;\"\n<\/code><\/pre>\n\n\n\n
    The query should succeed.<\/p>\n\n\n\n
    \n\n\n\n
    102. Step 80 \u2014 Revoke the Dynamic Credential<\/h1>\n\n\n\n
    Now manually revoke it:<\/p>\n\n\n\n
    vault lease revoke \\\n  -sync \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Revoked lease: database\/creds\/app-crud\/...\n<\/code><\/pre>\n\n\n\n
    The synchronous option makes Vault wait for backend revocation rather than merely queueing it. The current lease API supports synchronous immediate revocation.<\/p>\n\n\n\n
    \n\n\n\n
    103. What Happens During Revocation?<\/h1>\n\n\n\n
    Vault uses our configured:<\/p>\n\n\n\n
    revocation_statements\n<\/code><\/pre>\n\n\n\n
    which contain:<\/p>\n\n\n\n
    REVOKE app_crud FROM \"{{name}}\";\nDROP ROLE IF EXISTS \"{{name}}\";\n<\/code><\/pre>\n\n\n\n
    Therefore:<\/p>\n\n\n\n
    Vault\n  |\n  v\nRevoke lease\n  |\n  v\nPostgreSQL plugin\n  |\n  v\nREVOKE app_crud\n  |\n  v\nDROP ROLE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    104. Step 81 \u2014 Confirm the PostgreSQL Account Is Gone<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d postgres \\\n  -c \"SELECT rolname FROM pg_roles WHERE rolname = '$DB_USER';\"\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    (0 rows)\n<\/code><\/pre>\n\n\n\n
    This is one of the most important results in the lab.<\/p>\n\n\n\n
    The account no longer exists.<\/p>\n\n\n\n
    \n\n\n\n
    105. Step 82 \u2014 Prove the Old Credential No Longer Works<\/h1>\n\n\n\n
    Try:<\/p>\n\n\n\n
    PGPASSWORD=\"$DB_PASS\" \\\npsql \\\n  -h 127.0.0.1 \\\n  -U \"$DB_USER\" \\\n  -d vaultlab \\\n  -c \"SELECT * FROM public.app_data;\"\n<\/code><\/pre>\n\n\n\n
    Expected failure similar to:<\/p>\n\n\n\n
    FATAL: role \"<dynamic-user>\" does not exist\n<\/code><\/pre>\n\n\n\n
    That failure proves the credential was actually revoked in PostgreSQL.<\/p>\n\n\n\n
    \n\n\n\n
    106. Dynamic Credential Lifecycle<\/h1>\n\n\n\n
    Students have now demonstrated the complete lifecycle:<\/p>\n\n\n\n
    student1\n   |\n   | Vault authentication\n   v\nVault token\n   |\n   | policy permits database\/creds\/app-crud\n   v\nVault Database Engine\n   |\n   v\nPostgreSQL Plugin\n   |\n   v\nCREATE ROLE\n   |\n   v\nGRANT app_crud\n   |\n   v\nDynamic Username + Password\n   |\n   v\nPostgreSQL CRUD\n   |\n   v\nVault Lease\n   |\n   +---------------------+\n   |                     |\n   v                     v\nRenew                  Revoke\n   |                     |\n   v                     v\nALTER ROLE           REVOKE role\nVALID UNTIL          DROP ROLE\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    107. Step 83 \u2014 Generate Another Credential<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    You should receive:<\/p>\n\n\n\n
    different username\ndifferent password\ndifferent lease ID\n<\/code><\/pre>\n\n\n\n
    This is the core concept behind Vault dynamic database credentials.<\/p>\n\n\n\n
    Applications do not have to permanently share:<\/p>\n\n\n\n
    appuser \/ permanent-password\n<\/code><\/pre>\n\n\n\n
    Instead:<\/p>\n\n\n\n
    Application A \u2192 credential A\nApplication B \u2192 credential B\nApplication C \u2192 credential C\n<\/code><\/pre>\n\n\n\n
    with independent lease lifecycles.<\/p>\n\n\n\n
    \n\n\n\n
    108. Optional \u2014 Database Connection CRUD<\/h1>\n\n\n\n
    Because our student policy grants:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    the user also has administrative access to database connection configuration.<\/p>\n\n\n\n
    This portion is optional because deleting a connection intentionally breaks credential generation until it is recreated.<\/p>\n\n\n\n
    Make sure there are no active dynamic credential leases<\/strong> before performing it.<\/p>\n\n\n\n
    \n\n\n\n
    109. READ the Connection<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault read database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    110. LIST Connections<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault list database\/config\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    111. UPDATE the Connection<\/h1>\n\n\n\n
    Change maximum open connections from:<\/p>\n\n\n\n
    5\n<\/code><\/pre>\n\n\n\n
    to:<\/p>\n\n\n\n
    8\n<\/code><\/pre>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write database\/config\/vaultlab-postgres \\\n  plugin_name='postgresql-database-plugin' \\\n  allowed_roles='app-crud' \\\n  connection_url='postgresql:\/\/{{username}}:{{password}}@127.0.0.1:5432\/vaultlab?sslmode=disable' \\\n  username='vaultadmin' \\\n  password='VaultAdmin2026' \\\n  password_authentication='scram-sha-256' \\\n  max_open_connections=8\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    The database configuration API distinguishes create and update operations.<\/p>\n\n\n\n
    \n\n\n\n
    112. Reset the PostgreSQL Plugin Connection<\/h1>\n\n\n\n
    A useful administrative operation is:<\/p>\n\n\n\n
    vault write \\\n  -force \\\n  database\/reset\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    This closes\/restarts the underlying plugin connection while preserving its stored configuration. Vault documents the reset endpoint for database connections.<\/p>\n\n\n\n
    \n\n\n\n
    113. DELETE the Connection<\/h1>\n\n\n\n
    Do this only as an exercise.<\/p>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault delete database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data deleted...\n<\/code><\/pre>\n\n\n\n
    Confirm:<\/p>\n\n\n\n
    vault read database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    No value found\n<\/code><\/pre>\n\n\n\n
    The current Vault database API explicitly supports deleting database connections.<\/p>\n\n\n\n
    \n\n\n\n
    114. Prove Credential Generation Is Broken<\/h1>\n\n\n\n
    Try:<\/p>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    It should now fail because:<\/p>\n\n\n\n
    app-crud\n<\/code><\/pre>\n\n\n\n
    references:<\/p>\n\n\n\n
    vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    and that connection no longer exists.<\/p>\n\n\n\n
    This failure is expected.<\/p>\n\n\n\n
    \n\n\n\n
    115. Recreate the Connection<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault write database\/config\/vaultlab-postgres \\\n  plugin_name='postgresql-database-plugin' \\\n  allowed_roles='app-crud' \\\n  connection_url='postgresql:\/\/{{username}}:{{password}}@127.0.0.1:5432\/vaultlab?sslmode=disable' \\\n  username='vaultadmin' \\\n  password='VaultAdmin2026' \\\n  password_authentication='scram-sha-256' \\\n  max_open_connections=5\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    Success! Data written to: database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    Now:<\/p>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    should work again.<\/p>\n\n\n\n
    If you generate a credential during this test, revoke its lease before continuing.<\/p>\n\n\n\n
    \n\n\n\n
    116. Complete Vault CRUD Summary<\/h1>\n\n\n\n
    Database Connection<\/h2>\n\n\n\n
    CREATE<\/h3>\n\n\n\n
    vault write database\/config\/vaultlab-postgres ...\n<\/code><\/pre>\n\n\n\n
    READ<\/h3>\n\n\n\n
    vault read database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    LIST<\/h3>\n\n\n\n
    vault list database\/config\n<\/code><\/pre>\n\n\n\n
    UPDATE<\/h3>\n\n\n\n
    vault write database\/config\/vaultlab-postgres ...\n<\/code><\/pre>\n\n\n\n
    DELETE<\/h3>\n\n\n\n
    vault delete database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    117. Complete Vault Role CRUD Summary<\/h1>\n\n\n\n
    CREATE<\/h2>\n\n\n\n
    vault write database\/roles\/app-crud \\\n  db_name='vaultlab-postgres' \\\n  creation_statements=@\"$LAB_DIR\/create-app-crud.sql\" \\\n  renew_statements=@\"$LAB_DIR\/renew-app-crud.sql\" \\\n  revocation_statements=@\"$LAB_DIR\/revoke-app-crud.sql\" \\\n  rollback_statements=@\"$LAB_DIR\/rollback-app-crud.sql\" \\\n  default_ttl='10m' \\\n  max_ttl='1h'\n<\/code><\/pre>\n\n\n\n
    READ<\/h2>\n\n\n\n
    vault read database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    LIST<\/h2>\n\n\n\n
    vault list database\/roles\n<\/code><\/pre>\n\n\n\n
    UPDATE<\/h2>\n\n\n\n
    Use the same vault write<\/code> command with changed settings.<\/p>\n\n\n\n
    DELETE<\/h2>\n\n\n\n
    vault delete database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    118. Credential Generation<\/h1>\n\n\n\n
    vault read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    Response:<\/p>\n\n\n\n
    lease_id\nusername\npassword\nlease_duration\nlease_renewable\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    119. Lease Lookup<\/h1>\n\n\n\n
    vault lease lookup \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    120. Lease Renewal<\/h1>\n\n\n\n
    vault lease renew \\\n  -increment='20m' \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    121. Lease Revocation<\/h1>\n\n\n\n
    vault lease revoke \\\n  -sync \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    122. Userpass Command Summary<\/h1>\n\n\n\n
    Enable:<\/p>\n\n\n\n
    vault auth enable userpass\n<\/code><\/pre>\n\n\n\n
    Create:<\/p>\n\n\n\n
    vault write auth\/userpass\/users\/student1 \\\n  password='StudentLab2026' \\\n  policies='postgresql-engine-admin' \\\n  token_ttl='1h' \\\n  token_max_ttl='4h'\n<\/code><\/pre>\n\n\n\n
    Read:<\/p>\n\n\n\n
    vault read auth\/userpass\/users\/student1\n<\/code><\/pre>\n\n\n\n
    List:<\/p>\n\n\n\n
    vault list auth\/userpass\/users\n<\/code><\/pre>\n\n\n\n
    Login:<\/p>\n\n\n\n
    vault login \\\n  -method=userpass \\\n  username='student1' \\\n  password='StudentLab2026'\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    123. Vault Policy Used in This Lab<\/h1>\n\n\n\n
    path \"database\/*\" {\n  capabilities = [\n    \"create\",\n    \"read\",\n    \"update\",\n    \"delete\",\n    \"list\"\n  ]\n}\n\npath \"sys\/leases\/lookup\" {\n  capabilities = [\"update\"]\n}\n\npath \"sys\/leases\/renew\" {\n  capabilities = [\"update\"]\n}\n\npath \"sys\/leases\/revoke\" {\n  capabilities = [\"update\"]\n}\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    124. Why This Policy Is Very Powerful<\/h1>\n\n\n\n
    This policy gives the user:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    access.<\/p>\n\n\n\n
    That means more than simply requesting credentials.<\/p>\n\n\n\n
    The user can potentially:<\/p>\n\n\n\n
    create database connections\nmodify database connections\ndelete database connections\n\ncreate Vault DB roles\nmodify Vault DB roles\ndelete Vault DB roles\n\ngenerate database credentials\n\nreset database plugin connections\n\naccess rotate-root endpoints\n<\/code><\/pre>\n\n\n\n
    Therefore this is appropriate for the requested:<\/p>\n\n\n\n
    complete database secret-engine CRUD lab\n<\/code><\/pre>\n\n\n\n
    but it is too broad for a normal application<\/strong>.<\/p>\n\n\n\n
    \n\n\n\n
    125. Production-Style Consumer Policy<\/h1>\n\n\n\n
    For comparison, an application usually needs something closer to:<\/p>\n\n\n\n
    path \"database\/creds\/app-crud\" {\n  capabilities = [\"read\"]\n}\n<\/code><\/pre>\n\n\n\n
    Nothing more.<\/p>\n\n\n\n
    The application would not need access to:<\/p>\n\n\n\n
    database\/config\/*\ndatabase\/roles\/*\ndatabase\/reset\/*\ndatabase\/rotate-root\/*\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    126. Production Separation of Responsibilities<\/h1>\n\n\n\n
    A better production design would have:<\/p>\n\n\n\n
    Vault Platform Administrator\n           |\n           +-- configure engine\n           +-- configure database connections\n           +-- configure roles\n           +-- manage policies\n\n\nApplication\n           |\n           +-- read database\/creds\/app-crud\n<\/code><\/pre>\n\n\n\n
    Do not normally give applications:<\/p>\n\n\n\n
    database\/*\n<\/code><\/pre>\n\n\n\n
    administrative access.<\/p>\n\n\n\n
    \n\n\n\n
    127. Complete Architecture<\/h1>\n\n\n\n
                              +----------------+\n                          |    student1    |\n                          +-------+--------+\n                                  |\n                             username\/password\n                                  |\n                                  v\n                          +----------------+\n                          |    userpass    |\n                          +-------+--------+\n                                  |\n                              authenticates\n                                  |\n                                  v\n                          +----------------+\n                          |  Vault Token   |\n                          +-------+--------+\n                                  |\n                     postgresql-engine-admin\n                                  |\n                                  v\n                    +-------------------------+\n                    | database\/ Secrets Engine|\n                    +------------+------------+\n                                 |\n                                 |\n                      vaultlab-postgres\n                                 |\n                                 v\n                    +-------------------------+\n                    | PostgreSQL DB Plugin    |\n                    | postgresql-database-    |\n                    | plugin                  |\n                    +------------+------------+\n                                 |\n                                 |\n                            vaultadmin\n                                 |\n                                 v\n                      PostgreSQL 127.0.0.1\n                                 |\n                                 |\n                        CREATE dynamic role\n                                 |\n                                 v\n                +--------------------------------+\n                | v-userpass-app-crud-XXXXXXXX   |\n                +---------------+----------------+\n                                |\n                         GRANT app_crud\n                                |\n                                v\n                       +----------------+\n                       |    app_crud    |\n                       |    NOLOGIN     |\n                       +-------+--------+\n                               |\n             +-----------------+----------------+\n             |                 |                |\n           SELECT            INSERT           UPDATE\n             |                 |                |\n             +-----------------+----------------+\n                               |\n                             DELETE\n                               |\n                               v\n                      public.app_data\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    128. Authentication vs Authorization<\/h1>\n\n\n\n
    Students should be able to answer:<\/p>\n\n\n\n
    Who authenticates student1<\/code>?<\/h2>\n\n\n\n
    Vault userpass\n<\/code><\/pre>\n\n\n\n
    What does student1 receive?<\/h2>\n\n\n\n
    A Vault token.\n<\/code><\/pre>\n\n\n\n
    What controls the Vault token?<\/h2>\n\n\n\n
    postgresql-engine-admin policy\n<\/code><\/pre>\n\n\n\n
    What does database\/creds\/app-crud<\/code> do?<\/h2>\n\n\n\n
    Generates a PostgreSQL username\/password.\n<\/code><\/pre>\n\n\n\n
    What controls that database username’s permissions?<\/h2>\n\n\n\n
    PostgreSQL app_crud role.\n<\/code><\/pre>\n\n\n\n
    What controls how long it exists?<\/h2>\n\n\n\n
    Vault lease and PostgreSQL VALID UNTIL.\n<\/code><\/pre>\n\n\n\n
    What happens when Vault revokes it?<\/h2>\n\n\n\n
    Vault executes PostgreSQL revocation statements\nand drops the dynamic PostgreSQL role.\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    129. Troubleshooting \u2014 Vault Cannot Connect to PostgreSQL<\/h1>\n\n\n\n
    Typical error:<\/p>\n\n\n\n
    error creating database object\nconnection refused\npassword authentication failed\n<\/code><\/pre>\n\n\n\n
    First test PostgreSQL manually:<\/p>\n\n\n\n
    PGPASSWORD='VaultAdmin2026' \\\npsql \\\n  -h 127.0.0.1 \\\n  -U vaultadmin \\\n  -d vaultlab \\\n  -c \"SELECT current_user;\"\n<\/code><\/pre>\n\n\n\n
    If that fails, fix PostgreSQL first.<\/p>\n\n\n\n
    Check:<\/p>\n\n\n\n
    pg_isready\n<\/code><\/pre>\n\n\n\n
    Check:<\/p>\n\n\n\n
    ss -lnt | grep 5432\n<\/code><\/pre>\n\n\n\n
    Check service:<\/p>\n\n\n\n
    sudo systemctl status postgresql --no-pager\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    130. Troubleshooting \u2014 Vault Database Config Fails<\/h1>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    database = vaultlab\nusername = vaultadmin\npassword = VaultAdmin2026\nhost     = 127.0.0.1\nport     = 5432\n<\/code><\/pre>\n\n\n\n
    Then retry:<\/p>\n\n\n\n
    vault write database\/config\/vaultlab-postgres \\\n  plugin_name='postgresql-database-plugin' \\\n  allowed_roles='app-crud' \\\n  connection_url='postgresql:\/\/{{username}}:{{password}}@127.0.0.1:5432\/vaultlab?sslmode=disable' \\\n  username='vaultadmin' \\\n  password='VaultAdmin2026' \\\n  password_authentication='scram-sha-256'\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    131. Troubleshooting \u2014 permission denied to create role<\/code><\/h1>\n\n\n\n
    Check:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"\\du vaultadmin\"\n<\/code><\/pre>\n\n\n\n
    The account must have:<\/p>\n\n\n\n
    Create role\n<\/code><\/pre>\n\n\n\n
    If necessary:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"ALTER ROLE vaultadmin CREATEROLE;\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    132. Troubleshooting \u2014 must have admin option<\/code><\/h1>\n\n\n\n
    Check membership:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"\\du vaultadmin\"\n<\/code><\/pre>\n\n\n\n
    Reapply:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"GRANT app_crud TO vaultadmin WITH ADMIN OPTION;\"\n<\/code><\/pre>\n\n\n\n
    PostgreSQL’s ADMIN OPTION<\/code> is what allows a role to grant\/revoke membership in another role.<\/p>\n\n\n\n
    \n\n\n\n
    133. Troubleshooting \u2014 Dynamic User Cannot SELECT<\/h1>\n\n\n\n
    Check PostgreSQL privileges:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d vaultlab \\\n  -c \"\\dp public.app_data\"\n<\/code><\/pre>\n\n\n\n
    Reapply:<\/p>\n\n\n\n
    sudo -u postgres psql -d vaultlab <<'SQL'\nGRANT CONNECT ON DATABASE vaultlab TO app_crud;\nGRANT USAGE ON SCHEMA public TO app_crud;\nGRANT SELECT, INSERT, UPDATE, DELETE\nON TABLE public.app_data\nTO app_crud;\nGRANT USAGE, SELECT\nON SEQUENCE public.app_data_id_seq\nTO app_crud;\nSQL\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    134. Troubleshooting \u2014 INSERT Fails on Sequence<\/h1>\n\n\n\n
    Typical error:<\/p>\n\n\n\n
    permission denied for sequence app_data_id_seq\n<\/code><\/pre>\n\n\n\n
    Fix:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -d vaultlab \\\n  -c \"GRANT USAGE, SELECT ON SEQUENCE public.app_data_id_seq TO app_crud;\"\n<\/code><\/pre>\n\n\n\n
    Then retry the INSERT.<\/p>\n\n\n\n
    \n\n\n\n
    135. Troubleshooting \u2014 Vault Permission Denied<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault token lookup\n<\/code><\/pre>\n\n\n\n
    Verify:<\/p>\n\n\n\n
    postgresql-engine-admin\n<\/code><\/pre>\n\n\n\n
    is present.<\/p>\n\n\n\n
    Check capability:<\/p>\n\n\n\n
    vault token capabilities \\\n  database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    Expected:<\/p>\n\n\n\n
    create, delete, list, read, update\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    136. Troubleshooting \u2014 Still Logged In as Root<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    echo \"${VAULT_TOKEN:-not-set}\"\n<\/code><\/pre>\n\n\n\n
    If it prints:<\/p>\n\n\n\n
    root\n<\/code><\/pre>\n\n\n\n
    then:<\/p>\n\n\n\n
    unset VAULT_TOKEN\n<\/code><\/pre>\n\n\n\n
    Login again:<\/p>\n\n\n\n
    vault login \\\n  -method=userpass \\\n  username='student1' \\\n  password='StudentLab2026'\n<\/code><\/pre>\n\n\n\n
    Then:<\/p>\n\n\n\n
    vault token lookup\n<\/code><\/pre>\n\n\n\n
    Ensure:<\/p>\n\n\n\n
    root\n<\/code><\/pre>\n\n\n\n
    is not listed as a policy.<\/p>\n\n\n\n
    \n\n\n\n
    137. Troubleshooting \u2014 Credential Revocation Fails<\/h1>\n\n\n\n
    Make sure you do not have a persistent interactive PostgreSQL session open as the generated account.<\/p>\n\n\n\n
    Exit it with:<\/p>\n\n\n\n
    \\q\n<\/code><\/pre>\n\n\n\n
    Then retry:<\/p>\n\n\n\n
    vault lease revoke \\\n  -sync \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    Check Vault logs:<\/p>\n\n\n\n
    tail -100 \"$LAB_DIR\/vault.log\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    138. Troubleshooting \u2014 Userpass Login Fails<\/h1>\n\n\n\n
    Confirm user exists using root\/admin access:<\/p>\n\n\n\n
    vault read auth\/userpass\/users\/student1\n<\/code><\/pre>\n\n\n\n
    Check password carefully:<\/p>\n\n\n\n
    StudentLab2026\n<\/code><\/pre>\n\n\n\n
    Repeated incorrect login attempts can interact with Vault’s user-lockout protections supported by userpass.<\/p>\n\n\n\n
    \n\n\n\n
    139. Student Success Checklist<\/h1>\n\n\n\n
    A successful lab should satisfy all of these:<\/p>\n\n\n\n
    [ ] PostgreSQL installed\n\n[ ] PostgreSQL service running\n\n[ ] PostgreSQL accepts connections on 5432\n\n[ ] vaultlab database exists\n\n[ ] app_data table exists\n\n[ ] vaultadmin exists\n\n[ ] vaultadmin has CREATEROLE\n\n[ ] app_crud exists\n\n[ ] app_crud is NOLOGIN\n\n[ ] vaultadmin has ADMIN OPTION on app_crud\n\n[ ] vaultadmin TCP\/password login works\n\n[ ] Vault installed\n\n[ ] Vault version verified\n\n[ ] Vault dev server running\n\n[ ] Vault initialized\n\n[ ] Vault unsealed\n\n[ ] database\/ secrets engine enabled\n\n[ ] vaultlab-postgres connection configured\n\n[ ] PostgreSQL plugin connection works\n\n[ ] app-crud Vault DB role exists\n\n[ ] postgresql-engine-admin policy exists\n\n[ ] userpass enabled\n\n[ ] student1 exists\n\n[ ] student1 can login\n\n[ ] student1 has postgresql-engine-admin\n\n[ ] student1 does not have root\n\n[ ] student1 cannot access unrelated secret\/\n\n[ ] student1 can CREATE database role\n\n[ ] student1 can READ database role\n\n[ ] student1 can LIST database roles\n\n[ ] student1 can UPDATE database role\n\n[ ] student1 can DELETE database role\n\n[ ] student1 can generate PostgreSQL credentials\n\n[ ] dynamic PostgreSQL role exists\n\n[ ] dynamic account belongs to app_crud\n\n[ ] dynamic account can SELECT\n\n[ ] dynamic account can INSERT\n\n[ ] dynamic account can UPDATE\n\n[ ] dynamic account can DELETE\n\n[ ] dynamic account cannot CREATE TABLE\n\n[ ] dynamic account cannot CREATE ROLE\n\n[ ] credential has a Vault lease\n\n[ ] lease can be looked up\n\n[ ] lease can be renewed\n\n[ ] PostgreSQL VALID UNTIL changes\n\n[ ] lease can be revoked\n\n[ ] PostgreSQL dynamic role disappears\n\n[ ] revoked credentials no longer work\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    140. Cleanup \u2014 Revoke Any Remaining Credentials First<\/h1>\n\n\n\n
    Before stopping Vault, revoke every credential you created during the lab.<\/p>\n\n\n\n
    For a known lease:<\/p>\n\n\n\n
    vault lease revoke \\\n  -sync \\\n  \"$LEASE_ID\"\n<\/code><\/pre>\n\n\n\n
    If that lease was already revoked, simply continue.<\/p>\n\n\n\n
    \n\n\n\n
    141. Delete Vault Dynamic Role<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault delete database\/roles\/app-crud\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    142. Delete Vault Database Connection<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    vault delete database\/config\/vaultlab-postgres\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    143. Remove Sensitive Shell Variables<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    unset DB_USER\nunset DB_PASS\nunset LEASE_ID\nunset VAULT_TOKEN\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    144. Stop Vault<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    kill \"$(cat \"$LAB_DIR\/vault.pid\")\" 2>\/dev\/null || true\n<\/code><\/pre>\n\n\n\n
    Check:<\/p>\n\n\n\n
    pgrep -a vault || true\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    145. Remove PostgreSQL Lab Database<\/h1>\n\n\n\n
    First terminate any remaining connection to the lab database:<\/p>\n\n\n\n
    sudo -u postgres psql <<'SQL'\nSELECT pg_terminate_backend(pid)\nFROM pg_stat_activity\nWHERE datname = 'vaultlab'\n  AND pid <> pg_backend_pid();\nSQL\n<\/code><\/pre>\n\n\n\n
    Drop database:<\/p>\n\n\n\n
    sudo -u postgres dropdb \\\n  --if-exists \\\n  vaultlab\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    146. Remove PostgreSQL Lab Roles<\/h1>\n\n\n\n
    Remove the membership:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"REVOKE app_crud FROM vaultadmin;\"\n<\/code><\/pre>\n\n\n\n
    Drop permission role:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"DROP ROLE IF EXISTS app_crud;\"\n<\/code><\/pre>\n\n\n\n
    Drop Vault database user:<\/p>\n\n\n\n
    sudo -u postgres psql \\\n  -c \"DROP ROLE IF EXISTS vaultadmin;\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    147. Remove Vault CLI Token File<\/h1>\n\n\n\n
    Vault login may have created:<\/p>\n\n\n\n
    ~\/.vault-token\n<\/code><\/pre>\n\n\n\n
    Remove it:<\/p>\n\n\n\n
    rm -f \"$HOME\/.vault-token\"\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    148. Remove Lab Files<\/h1>\n\n\n\n
    Run:<\/p>\n\n\n\n
    rm -rf \"$LAB_DIR\"\n<\/code><\/pre>\n\n\n\n
    Then:<\/p>\n\n\n\n
    unset LAB_DIR\nunset VAULT_ADDR\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    149. Final Learning Flow<\/h1>\n\n\n\n
    Students should remember this sequence:<\/p>\n\n\n\n
    PostgreSQL\n    \u2193\nCreate vaultadmin\n    \u2193\nCreate app_crud permission role\n    \u2193\nCreate vaultlab database\n    \u2193\nCreate app_data table\n    \u2193\nGrant CRUD to app_crud\n    \u2193\nVault\n    \u2193\nEnable database secrets engine\n    \u2193\nConfigure PostgreSQL plugin\n    \u2193\nCreate dynamic database role\n    \u2193\nEnable userpass\n    \u2193\nCreate student1\n    \u2193\nAttach postgresql-engine-admin\n    \u2193\nLogin as student1\n    \u2193\nCreate \/ Read \/ Update \/ List \/ Delete Vault DB role\n    \u2193\nGenerate dynamic PostgreSQL credential\n    \u2193\nVault creates temporary PostgreSQL login\n    \u2193\nVault grants app_crud\n    \u2193\nTemporary user performs SELECT \/ INSERT \/ UPDATE \/ DELETE\n    \u2193\nVault tracks credential with lease\n    \u2193\nRenew lease\n    \u2193\nPostgreSQL VALID UNTIL changes\n    \u2193\nRevoke lease\n    \u2193\nPostgreSQL dynamic user is deleted\n    \u2193\nOld username\/password stops working\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    150. The Most Important Concept<\/h1>\n\n\n\n
    Before Vault:<\/p>\n\n\n\n
    Application\n     |\n     |\nPermanent Username\nPermanent Password\n     |\n     v\nPostgreSQL\n<\/code><\/pre>\n\n\n\n
    The credential may exist for:<\/p>\n\n\n\n
    months\nyears\n<\/code><\/pre>\n\n\n\n
    and may be shared by many applications.<\/p>\n\n\n\n
    With Vault:<\/p>\n\n\n\n
    Application\n     |\n     | authenticate\n     v\nVault\n     |\n     | request credentials\n     v\nDynamic Username\nDynamic Password\n     |\n     | short TTL\n     v\nPostgreSQL\n     |\n     | lease expires\/revoked\n     v\nCredential removed\n<\/code><\/pre>\n\n\n\n
    This is the fundamental benefit of Vault’s PostgreSQL database secrets engine.<\/p>\n\n\n\n
    \n\n\n\n
    151. Final Architecture Summary<\/h1>\n\n\n\n
                          USER \/ STUDENT\n                           |\n                           |\n                     student1\/password\n                           |\n                           v\n                    +--------------+\n                    |   USERPASS   |\n                    +------+-------+\n                           |\n                           v\n                    +--------------+\n                    | VAULT TOKEN  |\n                    +------+-------+\n                           |\n                  postgresql-engine-admin\n                           |\n                           v\n             +----------------------------+\n             | Database Secrets Engine    |\n             | database\/                  |\n             +-------------+--------------+\n                           |\n                    vaultlab-postgres\n                           |\n                           v\n             +----------------------------+\n             | PostgreSQL Plugin          |\n             | postgresql-database-plugin |\n             +-------------+--------------+\n                           |\n                       vaultadmin\n                           |\n                           v\n                +----------------------+\n                | PostgreSQL           |\n                | vaultlab             |\n                +----------+-----------+\n                           |\n                  create dynamic user\n                           |\n                           v\n                +----------------------+\n                | Temporary DB Login   |\n                +----------+-----------+\n                           |\n                     member of\n                           |\n                           v\n                +----------------------+\n                | app_crud             |\n                | PostgreSQL Role      |\n                +----------+-----------+\n                           |\n               SELECT \/ INSERT \/ UPDATE\n                        \/ DELETE\n                           |\n                           v\n                +----------------------+\n                | public.app_data      |\n                +----------------------+\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    152. Validation Notes<\/h1>\n\n\n\n
    The Vault-specific commands and endpoint structure in this tutorial were checked against the current Vault 2.x<\/strong> documentation, including the Vault 2.0.4 release information, database secrets-engine API, PostgreSQL database plugin API, current userpass<\/code> API, and current lease management API.<\/p>\n\n\n\n
    The PostgreSQL permission design uses documented PostgreSQL roles, CREATEROLE<\/code>, role membership, and WITH ADMIN OPTION<\/code>.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    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.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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: By the… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1044","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1044","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/comments?post=1044"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1044\/revisions"}],"predecessor-version":[{"id":1045,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1044\/revisions\/1045"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/media?parent=1044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/categories?post=1044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/tags?post=1044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}