vault.md

Microservice:

• API based
• Use vault to get token with admin DB privileges to:
  • create new database
  • create a new readonly role where new users can be created from
  • create a new fullaccess role where new users can be created from

For microservice API - Manual? steps to after spinning up Vault

Revoke public privileges to connect to any database

REVOKE ALL ON DATABASE template0 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM PUBLIC; ... to be found what is the actual commands to be ran

Mount the backend for postgresql specific database

vault mount postgresql -path postgres/admin

Create generic backend for postgres for admin management (create databases!)

vault write postgresql/admin/config/connection connection_url="postgresql://root:[email protected]:5432/public"

Configure the lease

vault write postgresql/{{db_name}}/config/lease lease=5m lease_max=5m

Configure a role for admin

vault write postgresql/admin/roles/admin sql=" CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT ALL PERMISSIONS TO "{{name}}";"

For each database to be managed by Vault

This will happen from the actual API ops microservice living on nomad

Maybe needs access to the static secret with the root password, so it can configure the Vault connection

Connect to postgres:

• create the DB
• create a role with read access only to the {{db_name}}: {{db_name}}_users_readonly
• create a role with read/write access only to the {{db_name}}: {{db_name}}_users_fullaccess

Mount the backend for postgresql specific database

vault mount postgresql -path postgres/{{db_name}}

Configure the connection (The user should have grant permissions to create new users on that DB)

vault write postgresql/{{db_name}}/config/connection connection_url="postgresql://root:[email protected]:5432/{{db_name}}"

Configure the lease

vault write postgresql/{{db_name}}/config/lease lease=1h lease_max=24h

Configure a role to read only data

vault write postgresql/{{db_name}}/roles/readonly sql=" CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT {{db_name}}_users TO {{name}}; GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";"

Configure a role to read/write data

vault write postgresql/{{db_name}}/roles/admin sql="CREATE ROLE "{{name}}" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{{name}}";"