Introduction
MarketLedger needs a settlement report for its small market vendors. The report should read yesterday's totals, not customer email addresses or bank references, and it should not write orders. Its current account-creation policy is broader than that job needs.
You will narrow that policy, obtain a real temporary PostgreSQL login through OpenBao, and save a complete tab-separated report using the supplied query. You need basic terminal and path skills. SQL permissions, credential files and the relevant command options are explained before use; no Python programming or cloud account is required.
See the Shop and the Report It Needs
In this step, you will start with the working business and a normal report before changing permissions. Use Terminal throughout this lab. cd changes directory; an absolute path works from anywhere:
cd /home/labex/project/market-report
PostgreSQL is the prepared database. OpenBao is a prepared secrets service that can ask that database to create a new login for a limited time. These are real processes in this lab's container. They are not a hosted database or a production secrets deployment.
The prepared shop.py is a small client for the working shop service. Running it does not require writing Python. list shows today's orders:
python3 shop.py list
Initially expect an empty list, []. Place one normal order. --order identifies the order, --vendor identifies its vendor, and --cents gives the amount as a whole number of cents:
python3 shop.py place --order O201 --vendor V101 --cents 600
Expect a stored, paid order for the synthetic trading date 2026-10-29. If you repeat the command, the service refuses the duplicate ID; use list to see the existing order rather than deleting or overwriting it.
The settlement covers the previous date, 2026-10-28. Today's new order must not alter yesterday's totals. cat prints a text file; inspect the prepared working-example report:
cat demo.tsv
Expect three vendor rows:
vendor_id vendor_name paid_orders total_cents
V101 Birch Pantry 2 4700
V102 Elm Bakery 1 2000
V103 Maple Kitchen 1 2700
The separators are tab characters, which is why the filename ends in .tsv. Four paid orders total 9400 cents. The cancelled Elm order is excluded. The demo was read with a separate prepared reader; copying it does not demonstrate that your new database identity can run the report.
A view is a named database query that exposes selected results. reporting.daily_settlement joins vendor names with paid order totals and leaves out customer email addresses and bank references. The original records stay in the private public.orders and public.vendors tables. Here reporting and public are schema names, which group database objects; the word public does not mean every role should have access.
You may edit role.sql, register the settlement-report OpenBao role, obtain that role's credentials, and create your report and credential files. Keep the shop, original database records, other report role, supplied query, environment file, operator token and prepared runtime unchanged. You do not need a database administrator password or OpenBao root token.
For an ordinary No such file or directory error, return with the absolute cd above, use ls to check the actual filenames, and retry. Do not reset the environment to repair a spelling mistake.
Limit the Account-Creation Policy to the Settlement View
In this step, you will remove permissions the report does not need. Start in Terminal, in /home/labex/project/market-report.
Read the current account-creation statements:
cat role.sql
OpenBao substitutes three placeholders when it issues credentials: {{name}} becomes a fresh database username, {{password}} becomes a generated password, and {{expiration}} becomes the account's expiry time. Keep these placeholders; do not replace them with your own fixed username or password.
CREATE ROLE ... WITH LOGIN creates a PostgreSQL login. VALID UNTIL limits when its password can authenticate a new connection. It does not terminate every connection that was already open. You will study explicit revocation and natural lease expiry in the next guided lab.
GRANT gives a permission. USAGE ON SCHEMA reporting permits looking up objects in that schema. SELECT ON reporting.daily_settlement permits reading that one view. The current extra grants on public allow reading private tables and changing orders; this report needs neither.
The final ALTER ROLE ... SET log_statement = 'all' line enables native SQL auditing for each newly created report login. Keep it: queries made with that identity should remain attributable. Management account-creation statements, which contain passwords, are not logged through this per-login setting. The prepared OpenBao audit log also hashes secret values.
Replace the file with the narrower statements below. cat > role.sql redirects the supplied text into that file. <<'ROLESQL' begins a here-document; the quoted marker preserves the enclosed text exactly until the line containing only ROLESQL:
cat > role.sql <<'ROLESQL'
CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';
GRANT USAGE ON SCHEMA reporting TO "{{name}}";
GRANT SELECT ON reporting.daily_settlement TO "{{name}}";
ALTER ROLE "{{name}}" SET log_statement = 'all';
ROLESQL
Read it back:
cat role.sql
The role has a generated login, a bounded password lifetime, schema lookup permission and read access to one view. It has no raw-table access, write grants or administrator attributes. A view permission is useful here because the supplied view intentionally omits private data; a view exposing those private fields would not provide the same boundary.
If the terminal waits for more input because you omitted the ending marker, type ROLESQL alone on a line. If the saved file is incomplete, repeat the entire block. This changes only your policy file, not the running database yet.
Register the Policy with the Restricted OpenBao Operator
In this step, you will register your account policy without using a secrets-service administrator identity. Stay in Terminal.
Read the prepared environment file before loading it:
cat env.sh
source env.sh runs those assignments in this terminal's current shell. export makes a value available to child commands. The file adds the prepared bao tool to PATH, the shell's command-search list, and sets the local OpenBao and PostgreSQL addresses. $(cat ...) reads the prepared operator token from its private file; the token itself is not printed. These settings belong to this terminal session. A new terminal must load them again:
source env.sh
An OpenBao operator token authorizes requests to the secrets service. It is not a PostgreSQL username or password. This token may manage and obtain credentials for settlement-report and revoke that role's exact leases. It cannot read the database management connection or manage the separate inventory report.
bao write writes configuration to the named OpenBao path. db_name=market selects the prepared database connection. creation_statements=@role.sql reads your SQL file as that field's value; the @ is OpenBao's file-input syntax. default_ttl=10m gives new leases ten minutes by default, and max_ttl=15m caps their lifetime configuration. A TTL is a time to live. Use these short limits for the report rather than hours or an unlimited account:
bao write database/roles/settlement-report db_name=market creation_statements=@role.sql default_ttl=10m max_ttl=15m
Expect confirmation that data was written to database/roles/settlement-report. This changes the creation policy; it has not issued a database login yet. A policy change affects future accounts. It does not remove permissions from a login that was already issued. After any registered policy change, obtain a new credential and regenerate your report with it. The completion check requires the report identity to have been issued after your final policy registration. Do not leave an earlier overbroad report login active; the conditional recovery in the next step shows how to end its exact lease before replacing its credential file.
The policy self-check reads the current registered policy and exercises its SQL in a disposable PostgreSQL fixture. It does not issue a lease, modify the live database or run your live report. sudo lets this prepared checker inspect protected configuration and run its isolated ordinary account; your normal OpenBao and database commands use the restricted identities:
sudo python3 /opt/labex-report/verify.py policy
If it reports an extra permission or missing expiry, correct role.sql and register it again. Keep the placeholders and auditing clause. Equivalent permission statements that implement the stated boundary are accepted; the check does not require a particular editor or SQL spelling.
Obtain a Temporary Login and Run the Real Report
In this step, you will use a newly issued database identity to produce your settlement file. Start in the same Terminal session, with env.sh loaded.
A credential response contains three different things: a lease ID identifying this issuance in OpenBao, a generated PostgreSQL username, and its password. The operator token from the previous step authorized issuance; it is not one of these database credentials.
Keep credential files private. umask 077 makes newly created files readable and writable only by your account; it does not change existing files:
umask 077
bao read requests credentials for the role. -format=json chooses structured JSON output. > saves that output into lease.json, replacing an existing file with that name. This command really creates a new PostgreSQL login and starts its lease clock; do not run it repeatedly just to inspect the same lease:
bao read -format=json database/creds/settlement-report > lease.json
jq selects fields from JSON. -r prints string values without JSON quote marks. Read the non-secret identity, lease ID and lease duration; do not print the password:
jq '.lease_id, .lease_duration, .data.username' lease.json
Expect a database/creds/settlement-report/... lease ID, a duration of 600 seconds, and a generated username. Their random suffixes will differ each time.
The PostgreSQL client psql reads PGUSER and PGPASSWORD from the environment. Quoted $(...) substitutions set them from this issuance. These values stay in this terminal session and its child commands:
export PGUSER="$(jq -r .data.username lease.json)"
export PGPASSWORD="$(jq -r .data.password lease.json)"
psql -X ignores personal psql startup customizations, -v ON_ERROR_STOP=1 stops on SQL errors, and -c supplies one SQL command. SELECT current_user asks PostgreSQL which identity this connection actually uses:
psql -X -v ON_ERROR_STOP=1 -c 'SELECT current_user;'
Expect the same generated username as in lease.json, not the shop writer, demo reader or database administrator.
The prepared report.sql selects the four public report columns, orders rows by vendor ID, and copies them to standard output as tab-separated data with a header. Keep the query unchanged. -f reads SQL from that file, while > saves only its report output:
psql -X -v ON_ERROR_STOP=1 -f report.sql > settlement.tsv
Read your actual result:
cat settlement.tsv
Its three vendor rows must match the earlier business example: Birch 2 / 4700, Elm 1 / 2000, Maple 1 / 2700. No customer email or bank reference should appear. The total remains 9400 cents because today's O201 order is outside yesterday's settlement.
If a later connection fails with password authentication failed, the ten-minute lease may have expired while you were reading. That is a credential failure, not a denied table query. Repeat the credential-issuance command once, reload both PGUSER and PGPASSWORD from the new file, and rerun the identity and report commands. A new username is expected. Do not edit the recorded username or extend expiry manually.
There is a different recovery if you changed the policy after already issuing credentials. That earlier account keeps its original grants. Before replacing its lease.json, run bao lease revoke "$(jq -r .lease_id lease.json)". The quoted substitution supplies that exact saved lease ID; it is not a database username or a lease prefix. Never revoke the inventory report or a whole lease prefix. After ending the earlier issuance, request a new credential, reload both database variables, and regenerate the report using the commands above. This avoids leaving an older overbroad login usable beside the new limited login.
Confirm Private Data and Writes Stay Unavailable
In this step, you will test the account boundary while preserving the shop and its original data. Use the same Terminal with your issued PostgreSQL identity.
The following read should fail. SELECT * requests all columns; the policy must stop this account before it can read raw orders:
psql -X -v ON_ERROR_STOP=1 -c 'SELECT * FROM public.orders;'
Expect a PostgreSQL permission denied error for the private schema or table. $? is the exit status of the immediately preceding command; echo prints it. Read it immediately:
echo $?
Expect 1, a refused SQL query. A connection failure with status 2 does not demonstrate this permission boundary; use the reissue-and-reload recovery from the previous step if the lease expired.
Now attempt a harmless-to-the-data write: UPDATE ... WHERE false matches no rows, so it could not change orders even if the account were incorrectly overprivileged. It must still be refused for insufficient permission:
psql -X -v ON_ERROR_STOP=1 -c 'UPDATE public.orders SET amount_cents = 0 WHERE false;'
echo $?
Again expect a permission error and status 1. The account can read the intended view, yet it cannot read raw private data or write orders.
The prepared shop client uses the shop service's separate writer identity, rather than your report credentials. Confirm the order you placed is still available:
python3 shop.py list
Run the read-only completion check. It first reads existing native OpenBao issuance and PostgreSQL query evidence, then checks your actual report and preserved business data. It does not create a missing login or generate the report for you:
sudo python3 /opt/labex-report/verify.py result
You now have a complete settlement produced with a real dynamically issued, limited database identity. The next independent lab starts with the correct permission policy and focuses on ending access and observing new-connection failures.
Summary
You registered a limited account-creation policy, obtained a fresh PostgreSQL login through a restricted OpenBao operator, and used that identity to save a real settlement. Its view exposes the necessary business totals while private tables, writes and administrator privileges remain outside the report's scope.
The operator token, lease ID and database credentials have distinct jobs. This exercise still relies on trusted prepared management components; temporary accounts do not remove that bootstrap trust. The next lab examines exact lease revocation, natural expiry and recovery through a new issuance.
