vault.psql

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}}\";"