DEV Community

Yogendra Rampuria - Yogi
Yogendra Rampuria - Yogi

Posted on

YugabyteDB ♥️ Hashicorp Vault - Fun Times

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='....';"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

  1. 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;
    
  2. 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();'
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

This looks good. Now we will check vault

vault status
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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" ; \
"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

Lets check the new role in database

ysqlsh -c '\du'
Enter fullscreen mode Exit fullscreen mode

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 | {}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Expected output:

Success! Enabled the database secrets engine at: database/
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Expected output:

Success! Data written to: database/rotate-root/yugabytedb
Enter fullscreen mode Exit fullscreen mode

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;'
Enter fullscreen mode Exit fullscreen mode

Expected output:

ysqlsh: FATAL:  password authentication failed for user "vault_admin"
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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 | {}
Enter fullscreen mode Exit fullscreen mode
  • 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;'
Enter fullscreen mode Exit fullscreen mode
  • Using CREDS variable to extract username and password for the connection
  • Reading data table customers

Expected output:

 count
-------
    91
(1 row)
Enter fullscreen mode Exit fullscreen mode

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

vault lease renew $(echo "$CREDS"| grep lease_id| cut -c20-)
Enter fullscreen mode Exit fullscreen mode

Expected output:

Key                Value
---                -----
lease_id           database/creds/approle/lkM5j7NPjIcnMIBI0sg1ZdQT
lease_duration     1h
lease_renewable    true
Enter fullscreen mode Exit fullscreen mode

On database, we should see updated validity time

ysqlsh -c '\du'
Enter fullscreen mode Exit fullscreen mode

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 | {}
Enter fullscreen mode Exit fullscreen mode

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-)
Enter fullscreen mode Exit fullscreen mode

Expected output

All revocation operations queued successfully!
Enter fullscreen mode Exit fullscreen mode

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

vault lease lookup $(echo "$CREDS"| grep lease_id| cut -c20-)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

ysqlsh -c '\du'
Enter fullscreen mode Exit fullscreen mode

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 | {}

Enter fullscreen mode Exit fullscreen mode
  • Dynamically created role (v-token-approle-L65VQiweoYeDxqnMaBF5-171639074) is gone.

Cleanup

To clean up this lab:

  • Exit shell
  exit
Enter fullscreen mode Exit fullscreen mode
  • Delete containers
  docker compose down -v --remove-orphans
Enter fullscreen mode Exit fullscreen mode

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

References

Top comments (0)