YugabyteDB ♥️ Vault: Fun Times
I have been working with YugabyteDB for a while now. I am always experimenting with YugabyteDB + (something). Today, its Vault.
I have also worked on Vault for a bit and did a 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.
1SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='....';"
Lets look at the query plan (visualize):
1yugabyte=# EXPLAIN ANALYZE SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830';
2 QUERY PLAN
3
4----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 Unique (cost=1025.94..1027.20 rows=251 width=32) (actual time=10538.331..10538.331 rows=0 loops=1)
6 -> Sort (cost=1025.94..1026.57 rows=251 width=32) (actual time=10538.329..10538.329 rows=0 loops=1)
7 Sort Key: ((nc.nspname)::information_schema.sql_identifier)
8 Sort Method: quicksort Memory: 25kB
9 -> Nested Loop (cost=944.18..1015.94 rows=251 width=32) (actual time=10538.307..10538.307 rows=0 loops=1)
10 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
11hed SubPlan 1) OR (hashed SubPlan 2)))
12 -> Nested Loop (cost=724.18..763.38 rows=120 width=136) (actual time=761.744..7273.274 rows=9780 loops=1)
13 -> Nested Loop (cost=724.18..744.98 rows=120 width=72) (actual time=761.405..4018.709 rows=9780 loops=1)
14 -> HashAggregate (cost=724.18..725.38 rows=120 width=177) (actual time=761.064..766.771 rows=9780 loops=1)
15 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
16 -> Append (cost=16.50..721.78 rows=120 width=177) (actual time=25.204..755.990 rows=9780 loops=1)
17 -> Hash Join (cost=16.50..635.50 rows=100 width=177) (actual time=25.202..28.599 rows=9774 loops=1)
18 Hash Cond: (pr_c.oid = a.attrelid)
19 -> Subquery Scan on pr_c (cost=0.00..615.00 rows=200 width=117) (actual time=7.796..9.656 rows=1086 loops=1)
20 Filter: (pr_c.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
21 Rows Removed by Filter: 678
22 -> Result (cost=0.00..465.00 rows=10000 width=117) (actual time=7.791..9.245 rows=1764 loops=1)
23 -> ProjectSet (cost=0.00..165.00 rows=10000 width=108) (actual time=7.784..8.774 rows=1764 loops=1)
24 -> Seq Scan on pg_class (cost=0.00..105.00 rows=1000 width=108) (actual time=7.755..8.038 rows=212 loops=1)
25 Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
26 Rows Removed by Filter: 135
27 -> Hash (cost=15.25..15.25 rows=100 width=68) (actual time=17.359..17.359 rows=2137 loops=1)
28 Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 241kB
29 -> 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)
30 Index Cond: (attnum > 0)
31 Remote Filter: (NOT attisdropped)
32 -> Nested Loop (cost=0.00..84.48 rows=20 width=177) (actual time=245.590..725.759 rows=6 loops=1)
33 -> Subquery Scan on pr_a (cost=0.00..79.08 rows=20 width=109) (actual time=245.222..723.723 rows=6 loops=1)
34 Filter: (pr_a.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
35 -> Result (cost=0.00..64.08 rows=1000 width=109) (actual time=245.220..723.717 rows=6 loops=1)
36 -> ProjectSet (cost=0.00..34.08 rows=1000 width=100) (actual time=245.216..723.710 rows=6 loops=1)
37 -> Nested Loop (cost=0.00..28.08 rows=100 width=104) (actual time=7.824..720.093 rows=2137 loops=1)
38 -> 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)
39 Index Cond: (attnum > 0)
40 Remote Filter: (NOT attisdropped)
41 -> 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)
42 Index Cond: (oid = a_1.attrelid)
43 -> 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)
44 Index Cond: (oid = pr_a.attrelid)
45 Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
46 -> 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)
47 Index Cond: (oid = pr_c.relnamespace)
48 -> 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)
49 Index Cond: (oid = pr_c.grantor)
50 -> Append (cost=0.00..0.20 rows=2 width=68) (actual time=0.333..0.333 rows=0 loops=9780)
51 -> 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)
52 Index Cond: (oid = pr_c.grantee)
53 Filter: (((rolname)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
54 Rows Removed by Filter: 1
55 -> Subquery Scan on "*SELECT* 2" (cost=0.01..0.03 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=9780)
56 Filter: (pr_c.grantee = "*SELECT* 2".oid)
57 -> Result (cost=0.01..0.01 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=9780)
58 One-Time Filter: ((('PUBLIC'::character varying)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
59 SubPlan 1
60 -> Seq Scan on pg_authid a_2 (cost=0.00..107.50 rows=1000 width=32) (never executed)
61 Filter: pg_has_role(oid, 'USAGE'::text)
62 SubPlan 2
63 -> Seq Scan on pg_authid a_3 (cost=0.00..107.50 rows=1000 width=32) (never executed)
64 Filter: pg_has_role(oid, 'USAGE'::text)
65 Planning Time: 3.016 ms
66 Execution Time: 10538.738 ms
67 Peak Memory Usage: 5583 kB
We can change this to use cost based optimizer. Lets look at that query plan (visualize)
1yugabyte=# set yb_enable_base_scans_cost_model=on;
2yugabyte=# EXPLAIN ANALYZE SELECT DISTINCT table_schema FROM information_schema.role_column_grants WHERE grantee='v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830';
3 QUERY PLAN
4
5------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 Unique (cost=5322.60..5324.90 rows=461 width=32) (actual time=423.430..423.430 rows=0 loops=1)
7 -> Sort (cost=5322.60..5323.75 rows=461 width=32) (actual time=423.429..423.429 rows=0 loops=1)
8 Sort Key: ((nc.nspname)::information_schema.sql_identifier)
9 Sort Method: quicksort Memory: 25kB
10 -> Hash Join (cost=4195.81..5302.20 rows=461 width=32) (actual time=423.423..423.423 rows=0 loops=1)
11 Hash Cond: (x.grantor = u_grantor.oid)
12 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)))
13 -> Nested Loop (cost=2586.82..3680.29 rows=1101 width=136) (actual time=422.699..422.699 rows=0 loops=1)
14 Join Filter: (x.grantee = pg_authid.oid)
15 Rows Removed by Join Filter: 9780
16 -> Merge Join (cost=2582.12..3124.06 rows=220 width=72) (actual time=418.664..420.096 rows=9780 loops=1)
17 Merge Cond: (nc.oid = x.relnamespace)
18 -> 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)
19 -> Sort (cost=2577.41..2577.96 rows=220 width=12) (actual time=418.025..418.340 rows=9780 loops=1)
20 Sort Key: x.relnamespace
21 Sort Method: quicksort Memory: 843kB
22 -> Subquery Scan on x (cost=2564.45..2568.85 rows=220 width=12) (actual time=414.694..416.964 rows=9780 loops=1)
23 -> HashAggregate (cost=2564.45..2566.65 rows=220 width=177) (actual time=414.694..416.041 rows=9780 loops=1)
24 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
25 -> Append (cost=9.41..2560.05 rows=220 width=177) (actual time=2.695..410.859 rows=9780 loops=1)
26 -> Nested Loop (cost=9.41..666.35 rows=20 width=177) (actual time=2.694..401.406 rows=9774 loops=1)
27 -> Subquery Scan on pr_c (cost=4.71..548.44 rows=4 width=117) (actual time=1.960..4.298 rows=1086 loops=1)
28 Filter: (pr_c.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
29 Rows Removed by Filter: 678
30 -> Result (cost=4.71..545.44 rows=200 width=117) (actual time=1.959..3.808 rows=1764 loops=1)
31 -> ProjectSet (cost=4.71..539.44 rows=200 width=108) (actual time=1.956..3.291 rows=1764 loops=1)
32 -> 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)
33 Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
34 Rows Removed by Filter: 135
35 -> 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)
36 Index Cond: ((attrelid = pr_c.oid) AND (attnum > 0))
37 Remote Filter: (NOT attisdropped)
38 -> Hash Join (cost=1087.67..1890.40 rows=200 width=177) (actual time=6.023..8.451 rows=6 loops=1)
39 Hash Cond: (pr_a.attrelid = c.oid)
40 -> Subquery Scan on pr_a (cost=549.49..1350.59 rows=200 width=109) (actual time=4.802..7.230 rows=6 loops=1)
41 Filter: (pr_a.prtype = ANY ('{INSERT,SELECT,UPDATE,REFERENCES}'::text[]))
42 -> Result (cost=549.49..1200.59 rows=10000 width=109) (actual time=4.801..7.228 rows=6 loops=1)
43 -> ProjectSet (cost=549.49..900.59 rows=10000 width=100) (actual time=4.800..7.225 rows=6 loops=1)
44 -> Hash Join (cost=549.49..840.59 rows=1000 width=104) (actual time=4.255..6.523 rows=2137 loops=1)
45 Hash Cond: (a_1.attrelid = cc.oid)
46 -> 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)
47 Index Cond: (attnum > 0)
48 Remote Filter: (NOT attisdropped)
49 -> Hash (cost=532.28..532.28 rows=1000 width=8) (actual time=1.060..1.060 rows=347 loops=1)
50 Buckets: 1024 Batches: 1 Memory Usage: 22kB
51 -> 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)
52 -> Hash (cost=537.93..537.93 rows=20 width=76) (actual time=1.206..1.206 rows=212 loops=1)
53 Buckets: 1024 Batches: 1 Memory Usage: 31kB
54 -> 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)
55 Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
56 Rows Removed by Filter: 135
57 -> Materialize (cost=4.71..536.44 rows=6 width=68) (actual time=0.000..0.000 rows=1 loops=9780)
58 -> Append (cost=4.71..536.41 rows=6 width=68) (actual time=0.391..0.392 rows=1 loops=1)
59 -> Seq Scan on pg_authid (cost=4.71..536.36 rows=5 width=68) (actual time=0.390..0.390 rows=1 loops=1)
60 Filter: (((rolname)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
61 Rows Removed by Filter: 15
62 -> Result (cost=0.01..0.01 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1)
63 One-Time Filter: ((('PUBLIC'::character varying)::information_schema.sql_identifier)::text = 'v-token-approle--Bp1TDJdp645o0K8gHmqp-1716386830'::text)
64 -> Hash (cost=528.86..528.86 rows=1000 width=68) (actual time=0.718..0.718 rows=16 loops=1)
65 Buckets: 1024 Batches: 1 Memory Usage: 10kB
66 -> 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)
67 SubPlan 1
68 -> Seq Scan on pg_authid a_2 (cost=4.71..532.98 rows=333 width=32) (never executed)
69 Filter: pg_has_role(oid, 'USAGE'::text)
70 SubPlan 2
71 -> Seq Scan on pg_authid a_3 (cost=4.71..532.98 rows=333 width=32) (never executed)
72 Filter: pg_has_role(oid, 'USAGE'::text)
73 Planning Time: 44.508 ms
74 Execution Time: 423.630 ms
75 Peak Memory Usage: 7852 kB
76(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 flag1alter 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.
1wget https://gist.githubusercontent.com/yogendra/cc10b0f3b3918f3490b7fd672dc57c36/raw/docker-compose.yaml 2docker compose up -d 3docker compose exec shell bash -l
Setup
Lets first check versions and connectivity. Starting with yugabytedb.
1ysqlsh -c 'select version();'
Expected Output
1 version
2
3-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4PostgreSQL 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
5os7-aarch64-build/src/llvm-project/clang 1e6329f40e5c531c09ade7015278078682293ebd), 64-bit
6(1 row)
NOTE: In the docker compose environment, vault binary might take a few minutes to download. You can wait for it to finish and monitor the download with following command
1until command -v vault &> /dev/null ; do echo "Wait for vault to be installed" ; sleep 3; done; command -v vaultExpected output:
1Waiting for vault to be installed 2Waiting for vault to be installed 3Waiting for vault to be installed 4. 5. 6/usr/bin/vaultThis will ensure that you continue only after vault is installed
This looks good. Now we will check vault
1vault status
Expected output:
1Key Value
2--- -----
3Seal Type shamir
4Initialized true
5Sealed false
6Total Shares 1
7Threshold 1
8Version 1.16.2
9Build Date 2024-04-22T16:25:54Z
10Storage Type inmem
11Cluster Name vault-cluster-e8cd73ab
12Cluster ID 068df8dd-a466-307f-e559-517129785fc3
13HA Enabled false
Next, we create a database role for vault to connect. This user/role should have permission to create application roles.
1ysqlsh -c "CREATE ROLE vault_admin WITH ENCRYPTED PASSWORD 'P@ssw0rd' LOGIN CREATEROLE;
2GRANT yb_db_admin TO vault_admin;"
- Vault database user (
vault_admin) is given onlyCREATEROLEpermission - This prevents any accidental misconfiguration on vault side from altering any data per say
- Granting
yb_db_adminto allowSET yb_make_next_ddl_statement_nonbreaking=TRUE;to be executed.
Expected output:
1GRANT ROLE
Noe, we need to create a database role for our application. Roles created by vault will "inherit" the permissions from this role
1ysqlsh -c "
2CREATE ROLE "approle" WITH NOLOGIN; \
3GRANT USAGE ON SCHEMA public to GROUP "approle"; \
4GRANT ALL ON ALL TABLES IN SCHEMA public to GROUP "approle" ; \
5GRANT ALL ON ALL SEQUENCES IN SCHEMA public to GROUP "approle" ; \
6"
CREATE ROLEstatement creates a roleNOLOGINwill ensure that this role is not used for any login
GRANTstatements 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.
USAGEpermission for schema is needed for an application user to be abel to access tables and sequences under the schema.
Expected output:
1GRANT
Lets check the new role in database
1ysqlsh -c '\du'
This is a shorthand command for a very long query. It shows the defines users and roles.
Expected output:
1 List of roles
2 Role name | Attributes | Member of
3--------------+------------------------------------------------------------+-----------
4approle | Cannot login | {}
5postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
6vault_admin | Create role | {}
7yb_db_admin | No inheritance, Cannot login | {}
8yb_extension | Cannot login | {}
9yb_fdw | Cannot login | {}
10yugabyte | 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
1vault secrets enable database
Expected output:
1Success! Enabled the database secrets engine at: database/
New, create a vault database secrets connection
1vault write database/config/yugabytedb \
2 name="yugabyte" \
3 plugin_name=postgresql-database-plugin \
4 allowed_roles='approle' \
5 verify_connection="true" \
6 username="vault_admin" \
7 password="P@ssw0rd" \
8 root_rotation_statements="ALTER ROLE {{username}} WITH ENCRYPTED PASSWORD '{{password}}';" \
9 password_authentication="scram-sha-256" \
10 connection_url="postgres://{{username}}:{{password}}@$PGHOST:5433/$PGDATABASE"
plugin_nameis set to postgresql-database-plugin, based on documentationallowed_rolesit can be a list of roles. We just need one.usernameandpasswordwere created aboveroot_rotation_statementis for making the vault change its own password. I think this is a good practice. We will rotate the this password immediately, below.password_authenticationis set toscram-sha-256based on Postgres' authentication methods.connection_urlis templated to useusernameandpassword.$YB_HOSTwill be substituted to a proper host name by the shell. You can replace this with any other hostname where yugabytedb is running.
Expected output:
1Success! 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
1vault write -force database/rotate-root/yugabytedb
Expected output:
1Success! 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)
1PGPASSWORD=P@ssw0rd ysqlsh -U vault_admin -c 'select 1 as works;'
Expected output:
1ysqlsh: 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
1 vault write database/roles/approle \
2 db_name=yugabytedb \
3 creation_statements="SET yb_make_next_ddl_statement_nonbreaking=TRUE; CREATE ROLE \"{{name}}\" WITH ENCRYPTED PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' LOGIN IN ROLE \"approle\";" \
4 revocation_statements="SET yb_make_next_ddl_statement_nonbreaking=TRUE; DROP ROLE \"{{name}}\";" \
5 rollback_statements="SET yb_make_next_ddl_statement_nonbreaking=TRUE; DROP ROLE \"{{name}}\";" \
6 renew_statements="SET yb_make_next_ddl_statement_nonbreaking=TRUE; ALTER ROLE \"{{name}}\" WITH VALID UNTIL '{{expiration}}';" \
7 default_ttl="1h" \
8 max_ttl="24h"
Update: Added SET yb_make_next_ddl_statement_nonbreaking=TRUE; to statements, as version < v2.21, user creation , alter and drop, causes SCHEMA_MISMATCH error for other connections.
creation_statementsis 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
approlegroup
revocation_statementandrollback_statementsare simpleDROP ROLEstatements. Since we do not make any additional grants.renew_statementalters the role and extends the validity of the role
Expected output:
1Success! 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
1CREDS="$(vault read database/creds/approle)"
2echo "$CREDS"
- Storing this in a variable so that we can get lease_id, username, and password in following steps
Expected output:
1Key Value
2--- -----
3lease_id database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT
4lease_duration 1h
5lease_renewable true
6password aVOzfQ3ICNl3-ztUA21y
7username 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.
1ysqlsh -c '\du'
Expected output:
1 Role name | Attributes | Member of
2-------------------------------------------------+------------------------------------------------------------+-----------
3 approle | Cannot login | {}
4 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
5 v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 | Password valid until 2024-05-22 16:12:26+00 | {approle}
6 vault_admin | Create role | {}
7 yb_db_admin | No inheritance, Cannot login | {}
8 yb_extension | Cannot login | {}
9 yb_fdw | Cannot login | {}
10 yugabyte | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741is 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
1PGPASSWORD=$(echo "$CREDS"| grep password | cut -c20-) PGUSER=$(echo "$CREDS"| grep username | cut -c20-) ysqlsh -c 'select count(customer_id) from customers;'
- Using
CREDSvariable to extract username and password for the connection - Reading data table
customers
Expected output:
1 count
2-------
3 91
4(1 row)
So, far all okay. Lets test a renewal operation to validate our renew query
1vault lease renew $(echo "$CREDS"| grep lease_id| cut -c20-)
Expected output:
1Key Value
2--- -----
3lease_id database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT
4lease_duration 1h
5lease_renewable true
On database, we should see updated validity time
1ysqlsh -c '\du'
Expected output:
1 List of roles
2 Role name | Attributes | Member of
3-------------------------------------------------+------------------------------------------------------------+-----------
4 approle | Cannot login | {}
5 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
6 v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 | Password valid until 2024-05-22 16:20:58+00 | {approle}
7 vault_admin | Create role | {}
8 yb_db_admin | No inheritance, Cannot login | {}
9 yb_extension | Cannot login | {}
10 yb_fdw | Cannot login | {}
11 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
1vault lease revoke $(echo "$CREDS"| grep lease_id| cut -c20-)
Expected output
1All revocation operations queued successfully!
Let's check lease information (Hint: there isn't any)
1vault lease lookup $(echo "$CREDS"| grep lease_id| cut -c20-)
Expected output:
1error looking up lease id database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT: Error making API request.
2
3URL: PUT http://vault:8200/v1/sys/leases/lookup
4Code: 400. Errors:
5
6* invalid lease
And on the db side, v-token-approle-L65VQiweoYeDxqnMaBF5-1716390741 should be gone.
1ysqlsh -c '\du'
Expected output:
1 List of roles
2 Role name | Attributes | Member of
3--------------+------------------------------------------------------------+-----------
4 approle | Cannot login | {}
5 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
6 vault_admin | Create role | {}
7 yb_db_admin | No inheritance, Cannot login | {}
8 yb_extension | Cannot login | {}
9 yb_fdw | Cannot login | {}
10 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
1exitDelete containers
1docker 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
References
comments powered by Disqus