VRO Partner Team Database Migrations - department-of-veterans-affairs/abd-vro GitHub Wiki

VRO currently supports database migrations using flyway by default, see more. VRO also wants to help enable autonomy of partner teams to use their own approved favorite database migration tools that fit the stack they are working in. This documentation outlines the steps required for the partner teams to independently handle their database migrations. It focuses on setting up the necessary database schema and user, storing credentials securely, and ensuring the partner team is equipped to take ownership of the process. For example, CC team currently uses Alembic with Python to manage their database migrations

The following steps are taken by VRO Engineers:

1. Database Schema and User Creation

Create a dedicated PostgreSQL schema and a user with appropriate privileges for the partner team.

Procedure:

  • Access the RDS PostgreSQL instance using the dev-tools pod within each LHDI env supported by VRO.
  • Connect to the database using psql, see more.
  • Execute SQL commands to create the new schema and user, ensure the user has the necessary privileges limited to the new schema.

Note: Replace cc with the appropriate parter team name. Note: Replace your_password with appropriate secure random generated password.

CREATE USER domain_cc_user WITH PASSWORD 'your_password';

CREATE SCHEMA domain_cc;

# Grant the domain_cc_user user access to the domain_*partner_team_name* schema
GRANT ALL PRIVILEGES ON SCHEMA domain_cc TO domain_cc_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA domain_cc TO domain_cc_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA domain_cc GRANT ALL PRIVILEGES ON TABLES TO domain_cc_user;

2. Storing and Propagating Secrets

Securely store the new database user's credentials and make them available within the Kubernetes environment.

  • Add the user credentials to HashiCorp Vault.
  • Propagate these credentials to the Kubernetes cluster, example.
  • Verify that the secrets are accessible and correctly configured.

3. Communication and Training

Inform the partner team about the new setup and provide them with the resources to manage it.

  • Message the partner team references to the credentials and their location in HashiCorp Vault.
  • Arrange a pairing session to walk through the new setup and address any questions.

4. Replication Across Environments

Ensure that the setup is consistently replicated across all necessary environments.

  • Repeat the creation, storage, and communication steps in each environment.
  • Document any environment-specific variations or considerations.