YugabyteDB ♥️ Hashicorp Vault - Fun Times

Yogendra Rampuria - Yogi — Wed, 22 May 2024 16:16:03 +0000

Updated version at https://yogendra.me/2024/05/23/yugabytedb-and-vault-fun-times/

I have been working with YugabyteDB for a while now. I am always experiment with YugabyteDB + (something). Today, its Vault.

I have also worked on Vault for a bit and did a lightening talk earlier this year. That talks was primarily around the data masking. But today, I was exploring the database secret engine.

For the uninitiated, Vault provides you with ability to dynamically generate database credentials for your application. It does this by leveraging the simple RBAC SQLs provided by the database engine. It supports variety of databases including Postgres, and YugabyteDB by compatibility.

What triggered this post?

One of the things that I encountered working with Vault was failing to cleanup the old credentials due to timeouts. On looking into vault code, I saw that the automatically generated queries tries to find all the privileges on user through introspection. One of these queries can be really slow if you have large number of database users created.

SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='....';"

Lets look at the query plan (visualize):

yugabyte=# EXPLAIN ANALYZE SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830';
                                                                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=1025.94..1027.20 rows=251 width=32) (actual time=10538.331..10538.331 rows=0 loops=1)
   ->  Sort  (cost=1025.94..1026.57 rows=251 width=32) (actual time=10538.329..10538.329 rows=0 loops=1)
         Sort Key: ((nc.nspname)::information_schema.sql_identifier)
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=944.18..1015.94 rows=251 width=32) (actual time=10538.307..10538.307 rows=0 loops=1)
               Join Filter: ((pg_has_role(u_grantor.oid, 'USAGE'::text) OR pg_has_role(pg_authid.oid, 'USAGE'::text) OR (pg_authid.rolname = 'PUBLIC'::name)) AND ((has
hed SubPlan 1) OR (hashed SubPlan 2)))
               ->  Nested Loop  (cost=724.18..763.38 rows=120 width=136) (actual time=761.744..7273.274 rows=9780 loops=1)
                     ->  Nested Loop  (cost=724.18..744.98 rows=120 width=72) (actual time=761.405..4018.709 rows=9780 loops=1)
                           ->  HashAggregate  (cost=724.18..725.38 rows=120 width=177) (actual time=761.064..766.771 rows=9780 loops=1)
                                 Group Key: pr_c.grantor, pr_c.grantee, a.attname, pr_c.relname, pr_c.relnamespace, pr_c.prtype, pr_c.grantable, pr_c.relowner
                                 ->  Append  (cost=16.50..721.78 rows=120 width=177) (actual time=25.204..755.990 rows=9780 loops=1)
                                       ->  Hash Join  (cost=16.50..635.50 rows=100 width=177) (actual time=25.202..28.599 rows=9774 loops=1)
                                             Hash Cond: (pr_c.oid = a.attrelid)
                                             ->  Subquery Scan on pr_c  (cost=0.00..615.00 rows=200 width=117) (actual time=7.796..9.656 rows=1086 loops=1)
                                                   Filter: (pr_c.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
                                                   Rows Removed by Filter: 678
                                                   ->  Result  (cost=0.00..465.00 rows=10000 width=117) (actual time=7.791..9.245 rows=1764 loops=1)
                                                         ->  ProjectSet  (cost=0.00..165.00 rows=10000 width=108) (actual time=7.784..8.774 rows=1764 loops=1)
                                                               ->  Seq Scan on pg_class  (cost=0.00..105.00 rows=1000 width=108) (actual time=7.755..8.038 rows=212 loops=1)
                                                                     Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                                     Rows Removed by Filter: 135
                                             ->  Hash  (cost=15.25..15.25 rows=100 width=68) (actual time=17.359..17.359 rows=2137 loops=1)
                                                   Buckets: 4096 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 241kB
                                                   ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.00..15.25 rows=100 width=68) (actual time=9.298..16.561 rows=2137 loops=1)
                                                         Index Cond: (attnum > 0)
                                                         Remote Filter: (NOT attisdropped)
                                       ->  Nested Loop  (cost=0.00..84.48 rows=20 width=177) (actual time=245.590..725.759 rows=6 loops=1)
                                             ->  Subquery Scan on pr_a  (cost=0.00..79.08 rows=20 width=109) (actual time=245.222..723.723 rows=6 loops=1)
                                                   Filter: (pr_a.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
                                                   ->  Result  (cost=0.00..64.08 rows=1000 width=109) (actual time=245.220..723.717 rows=6 loops=1)
                                                         ->  ProjectSet  (cost=0.00..34.08 rows=1000 width=100) (actual time=245.216..723.710 rows=6 loops=1)
                                                               ->  Nested Loop  (cost=0.00..28.08 rows=100 width=104) (actual time=7.824..720.093 rows=2137 loops=1)
                                                                     ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1  (cost=0.00..15.25 rows=1 00 width=100) (actual time=7.012..8.463 rows=2137 loops=1)
                                                                           Index Cond: (attnum > 0)
                                                                           Remote Filter: (NOT attisdropped)
                                                                     ->  Index Scan using pg_class_oid_index on pg_class cc  (cost=0.00..0.15 rows=1 width=8) (actual time=0.327..0.327 rows=1 loops=2137)
                                                                           Index Cond: (oid = a_1.attrelid)
                                             ->  Index Scan using pg_class_oid_index on pg_class c  (cost=0.00..0.32 rows=1 width=76) (actual time=0.334..0.334 rows=1 loops=6)
                                                   Index Cond: (oid = pr_a.attrelid)
                                                   Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                           ->  Index Scan using pg_namespace_oid_index on pg_namespace nc  (cost=0.00..0.14 rows=1 width=68) (actual time=0.327..0.327 rows=1 loops=9780)
                                 Index Cond: (oid = pr_c.relnamespace)
                     ->  Index Scan using pg_authid_oid_index on pg_authid u_grantor  (cost=0.00..0.14 rows=1 width=68) (actual time=0.327..0.327 rows=1 loops=9780)
                           Index Cond: (oid = pr_c.grantor)
               ->  Append  (cost=0.00..0.20 rows=2 width=68) (actual time=0.333..0.333 rows=0 loops=9780)
                     ->  Index Scan using pg_authid_oid_index on pg_authid  (cost=0.00..0.16 rows=1 width=68) (actual time=0.327..0.327 rows=0 loops=9780)
                           Index Cond: (oid = pr_c.grantee)
                           Filter: (((rolname)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
                           Rows Removed by Filter: 1
                     ->  Subquery Scan on "*SELECT* 2"  (cost=0.01..0.03 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=9780)
                           Filter: (pr_c.grantee = "*SELECT* 2".oid)
                           ->  Result  (cost=0.01..0.01 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=9780)
                                 One-Time Filter: ((('PUBLIC'::character varying)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
               SubPlan 1
                 ->  Seq Scan on pg_authid a_2  (cost=0.00..107.50 rows=1000 width=32) (never executed)
                       Filter: pg_has_role(oid, 'USAGE'::text)
               SubPlan 2
                 ->  Seq Scan on pg_authid a_3  (cost=0.00..107.50 rows=1000 width=32) (never executed)
                       Filter: pg_has_role(oid, 'USAGE'::text)
 Planning Time: 3.016 ms
 Execution Time: 10538.738 ms
 Peak Memory Usage: 5583 kB

We can change this to use cost based optimizer. Lets look at that query plan (visualize)

yugabyte=# set yb_enable_base_scans_cost_model=on;
yugabyte=# EXPLAIN ANALYZE SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830';
                                                                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=5322.60..5324.90 rows=461 width=32) (actual time=423.430..423.430 rows=0 loops=1)
   ->  Sort  (cost=5322.60..5323.75 rows=461 width=32) (actual time=423.429..423.429 rows=0 loops=1)
         Sort Key: ((nc.nspname)::information_schema.sql_identifier)
         Sort Method: quicksort  Memory: 25kB
         ->  Hash Join  (cost=4195.81..5302.20 rows=461 width=32) (actual time=423.423..423.423 rows=0 loops=1)
               Hash Cond: (x.grantor = u_grantor.oid)
               Join Filter: ((pg_has_role(u_grantor.oid, 'USAGE'::text) OR pg_has_role(pg_authid.oid, 'USAGE'::text) OR (pg_authid.rolname = 'PUBLIC'::name)) AND ((hashed SubPlan 1) OR (hashed SubPlan 2)))
               ->  Nested Loop  (cost=2586.82..3680.29 rows=1101 width=136) (actual time=422.699..422.699 rows=0 loops=1)
                     Join Filter: (x.grantee = pg_authid.oid)
                     Rows Removed by Join Filter: 9780
                     ->  Merge Join  (cost=2582.12..3124.06 rows=220 width=72) (actual time=418.664..420.096 rows=9780 loops=1)
                           Merge Cond: (nc.oid = x.relnamespace)
                           ->  Index Scan using pg_namespace_oid_index on pg_namespace nc  (cost=4.71..540.86 rows=1000 width=68) (actual time=0.634..0.637 rows=4 loops=1)
                           ->  Sort  (cost=2577.41..2577.96 rows=220 width=12) (actual time=418.025..418.340 rows=9780 loops=1)
                                 Sort Key: x.relnamespace
                                 Sort Method: quicksort  Memory: 843kB
                                 ->  Subquery Scan on x  (cost=2564.45..2568.85 rows=220 width=12) (actual time=414.694..416.964 rows=9780 loops=1)
                                       ->  HashAggregate  (cost=2564.45..2566.65 rows=220 width=177) (actual time=414.694..416.041 rows=9780 loops=1)
                                             Group Key: pr_c.grantor, pr_c.grantee, a.attname, pr_c.relname, pr_c.relnamespace, pr_c.prtype, pr_c.grantable, pr_c.relowner
                                             ->  Append  (cost=9.41..2560.05 rows=220 width=177) (actual time=2.695..410.859 rows=9780 loops=1)
                                                   ->  Nested Loop  (cost=9.41..666.35 rows=20 width=177) (actual time=2.694..401.406 rows=9774 loops=1)
                                                         ->  Subquery Scan on pr_c  (cost=4.71..548.44 rows=4 width=117) (actual time=1.960..4.298 rows=1086 loops=1)
                                                               Filter: (pr_c.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
                                                               Rows Removed by Filter: 678
                                                               ->  Result  (cost=4.71..545.44 rows=200 width=117) (actual time=1.959..3.808 rows=1764 loops=1)
                                                                     ->  ProjectSet  (cost=4.71..539.44 rows=200 width=108) (actual time=1.956..3.291 rows=1764 loops=1)
                                                                           ->  Seq Scan on pg_class  (cost=4.71..538.24 rows=20 width=108) (actual time=1.947..2.198 ro ws=212 loops=1)
                                                                                 Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                                                 Rows Removed by Filter: 135
                                                         ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=4.71..29.47 rows=1 width=68) (actual time=0.353..0.356 rows=9 loops=1086)
                                                               Index Cond: ((attrelid = pr_c.oid) AND (attnum > 0))
                                                               Remote Filter: (NOT attisdropped)
                                                   ->  Hash Join  (cost=1087.67..1890.40 rows=200 width=177) (actual time=6.023..8.451 rows=6 loops=1)
                                                         Hash Cond: (pr_a.attrelid = c.oid)
                                                         ->  Subquery Scan on pr_a  (cost=549.49..1350.59 rows=200 width=109) (actual time=4.802..7.230 rows=6 loops=1)
                                                               Filter: (pr_a.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
                                                               ->  Result  (cost=549.49..1200.59 rows=10000 width=109) (actual time=4.801..7.228 rows=6 loops=1)
                                                                     ->  ProjectSet  (cost=549.49..900.59 rows=10000 width=100) (actual time=4.800..7.225 rows=6 loops=1)
                                                                           ->  Hash Join  (cost=549.49..840.59 rows=1000 width=104) (actual time=4.255..6.523 rows=2137 loops=1)
                                                                                 Hash Cond: (a_1.attrelid = cc.oid)
                                                                                 ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1  (cost=4.71..287.62 rows=1000 width=100) (actual time=3.184..5.148 rows=2137 loops=1)
                                                                                       Index Cond: (attnum > 0)
                                                                                       Remote Filter: (NOT attisdropped)
                                                                                 ->  Hash  (cost=532.28..532.28 rows=1000 width=8) (actual time=1.060..1.060 rows=347 loops=1)
                                                                                       Buckets: 1024  Batches: 1  Memory Usage: 22kB
                                                                                       ->  Seq Scan on pg_class cc  (cost=4.71..532.28 rows=1000 width=8) (actual time=0.951..1.022 rows=347 loops=1)
                                                         ->  Hash  (cost=537.93..537.93 rows=20 width=76) (actual time=1.206..1.206 rows=212 loops=1)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 31kB
                                                               ->  Seq Scan on pg_class c  (cost=4.71..537.93 rows=20 width=76) (actual time=1.085..1.177 rows=212 loops=1)
                                                                     Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                                     Rows Removed by Filter: 135
                     ->  Materialize  (cost=4.71..536.44 rows=6 width=68) (actual time=0.000..0.000 rows=1 loops=9780)
                           ->  Append  (cost=4.71..536.41 rows=6 width=68) (actual time=0.391..0.392 rows=1 loops=1)
                                 ->  Seq Scan on pg_authid  (cost=4.71..536.36 rows=5 width=68) (actual time=0.390..0.390 rows=1 loops=1)
                                       Filter: (((rolname)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
                                       Rows Removed by Filter: 15
                                 ->  Result  (cost=0.01..0.01 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1)
                                       One-Time Filter: ((('PUBLIC'::character varying)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
               ->  Hash  (cost=528.86..528.86 rows=1000 width=68) (actual time=0.718..0.718 rows=16 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 10kB
                     ->  Seq Scan on pg_authid u_grantor  (cost=4.71..528.86 rows=1000 width=68) (actual time=0.693..0.699 rows=16 loops=1)
               SubPlan 1
                 ->  Seq Scan on pg_authid a_2  (cost=4.71..532.98 rows=333 width=32) (never executed)
                       Filter: pg_has_role(oid, 'USAGE'::text)
               SubPlan 2
                 ->  Seq Scan on pg_authid a_3  (cost=4.71..532.98 rows=333 width=32) (never executed)
                       Filter: pg_has_role(oid, 'USAGE'::text)
 Planning Time: 44.508 ms
 Execution Time: 423.630 ms
 Peak Memory Usage: 7852 kB
(70 rows)

This is a huge improvement. We went from 10s+ to 400ms. Since this query is deep insides vault code, we can't change the query. But we can solve it. We have 2 options

Quick way to solve this is to just alter the vault db user (vault_admin) and set this flag
```
alter role vault_admin set yb_enable_base_scans_cost_model=on;
```
Use custom revoke statement. This way you avoid the code path running this query, entirely. See the example of this in the instructions below

If you want to try this yourself, please follow the rest of the blog to understand how to setup and YugabyteDB and Vault to work together.

Running YugabyteDB and Vault Together

NOTE: There is a YugabyteDB plugin for Vault, but its in dev preview at the moment, so lets just stick with PG Plugin

Lets jump into demo mode straight. I will be using a docker based setup. If you want to follow along and go through the whole process, just pull this docker compose file and launch it.

wget https://gist.githubusercontent.com/yogendra/cc10b0f3b3918f3490b7fd672dc57c36/raw/docker-compose.yaml
docker compose up -d
docker compose exec shell bash -l

Setup

Lets first check versions and connectivity. Starting with yugabytedb.

ysqlsh -c 'select version();'

Expected Output

                                                                                                                          version

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2-YB-2.20.3.1-b0 on aarch64-unknown-linux-gnu, compiled by clang version 16.0.6 (/opt/yb-build/llvm/yb-llvm-v16.0.6-yb-3-1695119965-1e6329f4-cent
os7-aarch64-build/src/llvm-project/clang 1e6329f40e5c531c09ade7015278078682293ebd), 64-bit
(1 row)

This looks good. Now we will check vault

vault status

Expected output:

Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    1
Threshold       1
Version         1.16.2
Build Date      2024-04-22T16:25:54Z
Storage Type    inmem
Cluster Name    vault-cluster-e8cd73ab
Cluster ID      068df8dd-a466-307f-e559-517129785fc3
HA Enabled      false

Next, we create a database role for vault to connect. This user/role should have permission to create application roles.

ysqlsh -c "CREATE ROLE vault_admin WITH ENCRYPTED PASSWORD  'P@ssw0rd' LOGIN NOSUPERUSER CREATEROLE;"

Vault database user (vault_admin) is given only CREATEROLE permission
This prevents any accidental misconfiguration on vault side from altering any data per say

Expected output:

CREATE ROLE

Noe, we need to create a database role for our application. Roles created by vault will "inherit" the permissions from this role

ysqlsh -c "
CREATE ROLE "approle" WITH NOLOGIN; \
GRANT USAGE ON SCHEMA public to GROUP "approle"; \
GRANT ALL ON ALL TABLES IN SCHEMA public to GROUP "approle" ; \
GRANT ALL ON ALL SEQUENCES IN SCHEMA public to  GROUP "approle" ; \
"

CREATE ROLE statement creates a role
- NOLOGIN will ensure that this role is not used for any login
GRANT statements grants (one or more) privileges on a set of objects
- Just enough permissions that should be needed for a application to work
- Grant privilege as a group, so individual users created by vault, will become member of this and inherits the permissions.
- USAGE permission for schema is needed for an application user to be abel to access tables and sequences under the schema.

Expected output:

GRANT

Lets check the new role in database

ysqlsh -c '\du'

This is a shorthand command for a very long query. It shows the defines users and roles.

Expected output:

                                    List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
approle      | Cannot login                                               | {}
postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
vault_admin  | Create role                                                | {}
yb_db_admin  | No inheritance, Cannot login                               | {}
yb_extension | Cannot login                                               | {}
yb_fdw       | Cannot login                                               | {}
yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you can see approle is defined here and it cannot login

Now, on this vault setup. First we enable database secrets engine

vault secrets enable database

Expected output:

Success! Enabled the database secrets engine at: database/

New, create a vault database secrets connection

vault write database/config/yugabytedb \
  name="yugabyte" \
  plugin_name=postgresql-database-plugin \
  allowed_roles='approle' \
  verify_connection="true" \
  username="vault_admin" \
  password="P@ssw0rd" \
  root_rotation_statements="ALTER ROLE {{username}} WITH ENCRYPTED PASSWORD '{{password}}';" \
  password_authentication="scram-sha-256" \
  connection_url="postgres://{{username}}:{{password}}@$PGHOST:5433/$PGDATABASE"

plugin_name is set to postgresql-database-plugin, based on documentation
allowed_roles it can be a list of roles. We just need one.
username and password were created above
root_rotation_statement is for making the vault change its own password. I think this is a good practice. We will rotate the this password immediately, below.
password_authentication is set to scram-sha-256 based on Postgres' authentication methods.
connection_url is templated to use username and password. $YB_HOST will be substituted to a proper host name by the shell. You can replace this with any other hostname where yugabytedb is running.

Expected output:

Success! Data written to: database/config/yugabytedb

After creating the connect, first thing we need to do is to secure the database password of the admin user. So we rotate root credentials

vault write -force database/rotate-root/yugabytedb

Expected output:

Success! Data written to: database/rotate-root/yugabytedb

Now the password for vault_admin is also rotated. Lets check if we can login with the original password (Hint: we can't)

PGPASSWORD=P@ssw0rd ysqlsh -U vault_admin -c 'select 1 as works;'

Expected output:

ysqlsh: FATAL:  password authentication failed for user "vault_admin"

Great! Now even I don't know whats the password to impersonate vault. Next, we create a dynamic db credentials generation config for yugabytedb to use the automatic statement generation capability

  vault write database/roles/approle \
    db_name=yugabytedb \
    creation_statements="CREATE ROLE \"{{name}}\" WITH ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' LOGIN IN ROLE \"approle\";" \
    revocation_statements="DROP ROLE \"{{name}}\";" \
    rollback_statements="DROP ROLE \"{{name}}\";" \
    renew_statements="ALTER ROLE \"{{name}}\" WITH VALID UNTIL '{{expiration}}';" \
    default_ttl="1h" \
    max_ttl="24h"

creation_statements is a minimal requirement. Others are optional. This statement is creating a user in database:
- With dynamic username
- With dynamic password
- With expiration based policy (1hr in this case)
- With membership of approle group
revocation_statement and rollback_statements are simple DROP ROLE statements. Since we do not make any additional grants.
renew_statement alters the role and extends the validity of the role

Expected output:

Success! Data written to: database/roles/approle

Test

Vault is now ready to create dynamic credentials for out application. Lets create a create a credential with this config and check

CREDS="$(vault read database/creds/approle)"
echo "$CREDS"

Storing this in a variable so that we can get lease_id, username, and password in following steps

Expected output:

Key                Value
---                -----
lease_id           database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT
lease_duration     1h
lease_renewable    true
password           aVOzfQ3ICNl3-ztUA21y
username           v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741

Important: Record this as we will need lease_id, username and password for following setups

Also, check on database side what we see in the user list.

ysqlsh -c '\du'

Expected output:

                    Role name                    |                         Attributes                         | Member of
-------------------------------------------------+------------------------------------------------------------+-----------
 approle                                         | Cannot login                                               | {}
 postgres                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 | Password valid until 2024-05-22 16:12:26+00                | {approle}
 vault_admin                                     | Create role                                                | {}
 yb_db_admin                                     | No inheritance, Cannot login                               | {}
 yb_extension                                    | Cannot login                                               | {}
 yb_fdw                                          | Cannot login                                               | {}
 yugabyte                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 is a dynamically created user with 1 hour validity
Its part of approle group

Let's use the credentials for this newly created user to connect to database and execute a query

PGPASSWORD=$(echo "$CREDS"| grep password | cut -c20-) PGUSER=$(echo "$CREDS"| grep username | cut -c20-) ysqlsh -c 'select count(customer_id) from customers;'

Using CREDS variable to extract username and password for the connection
Reading data table customers

Expected output:

 count
-------
    91
(1 row)

So, far all okay. Lets test a renewal operation to validate our renew query

vault lease renew $(echo "$CREDS"| grep lease_id| cut -c20-)

Expected output:

Key                Value
---                -----
lease_id           database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT
lease_duration     1h
lease_renewable    true

On database, we should see updated validity time

ysqlsh -c '\du'

Expected output:

                                                      List of roles
                    Role name                    |                         Attributes                         | Member of
-------------------------------------------------+------------------------------------------------------------+-----------
 approle                                         | Cannot login                                               | {}
 postgres                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 | Password valid until 2024-05-22 16:20:58+00                | {approle}
 vault_admin                                     | Create role                                                | {}
 yb_db_admin                                     | No inheritance, Cannot login                               | {}
 yb_extension                                    | Cannot login                                               | {}
 yb_fdw                                          | Cannot login                                               | {}
 yugabyte                                        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you can see, the expiry time has gone up from 16:12:26+00 to 16:20:58+00. Next (and laster) operation is revoking /deleting dynamic user

vault lease revoke $(echo "$CREDS"| grep lease_id| cut -c20-)

Expected output

All revocation operations queued successfully!

Let's check lease information (Hint: there isn't any)

vault lease lookup $(echo "$CREDS"| grep lease_id| cut -c20-)

Expected output:

error looking up lease id database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT: Error making API request.

URL: PUT http://vault:8200/v1/sys/leases/lookup
Code: 400. Errors:

* invalid lease

And on the db side, v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 should be gone.

ysqlsh -c '\du'

Expected output:

                                      List of roles
  Role name   |                         Attributes                         | Member of
--------------+------------------------------------------------------------+-----------
 approle      | Cannot login                                               | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vault_admin  | Create role                                                | {}
 yb_db_admin  | No inheritance, Cannot login                               | {}
 yb_extension | Cannot login                                               | {}
 yb_fdw       | Cannot login                                               | {}
 yugabyte     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Dynamically created role (v-token-approle-L65VQiweoYeDxqnMaBF5-171639074) is gone.

Cleanup

To clean up this lab:

Exit shell

  exit

Delete containers

  docker compose down -v --remove-orphans

That's all. Feel free to reach out to me on the community slack/Twitter for more info or feedback. Ta

DEV Community: Yogendra Rampuria - Yogi

YugabyteDB ♥️ Hashicorp Vault - Fun Times

What triggered this post?

Running YugabyteDB and Vault Together

Setup

Test

Cleanup

References

Testing Post Via Dev.to

yogendra / yogendra.me

My Website

Yogendra.Me - Web Site

How to Contribute

How to build or check changes

FAQ

Why GIT and HEXO

Why not use Blogger

Why not use WP/Drupal