I have been working with YugabyteDB for a while now. I am always experiment with yugbayte + (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.
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 loo
ps=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 t
ime=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=978
0)
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-17
16386830'::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;
SET
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 ((has
hed 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 loop
s=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.relow
ner
-> 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) (ac
tual 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 l
oops=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 loop
s=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--Bp1TDJdp645o0K8gH
mqp-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 flagalter 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 onlyCREATEROLEpermission - 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 ROLEstatement creates a role-
NOLOGINwill 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:
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_nameis set to postgresql-database-plugin, based on docuemntation -
allowed_rolesit can be a list of roles. We just need one. -
usernameandpasswordwere created above -
root_rotation_statementis for making the vault change its own password. I think this is a good practice. We will rotate the this password immediatel, 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:
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 yugbaytedb 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_statementsis a minimal requirement. Others are optional. This statment 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:
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-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
PGPASSWORD=$(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:
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 volidity 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 revokign /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
docker compose down -v --remove-orphans
That’s all. Feel free to reach out to me on the community slack/Twitter for more infor or feedback. Ta
References:
- Postgres
- Vault
- YugabyteDB
Source link
lol
