Automate and standardize the creation of the database application users

[jimleroyer]

Description

As a system ops for GCNotify, I need to automate the creation of application database users, So that I can automate the creation of environments easily.

As a developer for GCNotify, I need the application database users to share the same naming pattern, So that I can easily develop for the stack and reduce potential mistakes.

WHY are we building?

We do not have the application database users automated at the moment. Creating these users are a manual step (when creating a new environment). This could lead to mistakes during a BCP emergency.

WHAT are we building?

Automating the creation of the database users that connect to our apps. This naming should follow a standardized pattern (opposite to having different patterns for naming per environment).

VALUE created by our solution

More automation, less mistakes, more standards.

Acceptance Criteria

• [ ] The application database user creation is automated.
• [ ] The application database user naming pattern follows the same standard.

QA Steps

• [ ] Check the users follow the same naming standard.
• [ ] Review the means of automation.

[P0NDER0SA]

Need to account for quicksight_db_user and app_db_user

create user with older password encryption

set password_encryption = 'md5'; create role quicksight_db_user with login password 'cds-secret-.'**; Grant rds_superuser to quicksight_db_user;

looking at creating these as a migration script.

[jimleroyer]

Pond and Pat talked yesterday, Pond and Steve talked yesterday; all about how to go on with this task which currently is blocking Pat on 100% unleashing audits.

[P0NDER0SA]

PR is merged! https://github.com/cds-snc/notification-api/pull/2179

any future DB users could be integrated in here if necessary.

[P0NDER0SA]

fixing bug where the roles needed to be swawpped (users needed to be switched around)

[P0NDER0SA]

Did a personal QA with Pat on this one in production and all looks well.

[P0NDER0SA]

looking to optimize/enhance the downgrade path for this code

[ben851]

Ben to take a look at doing this in TF

[ben851]

Did not get to this yesterday, will start looking today hopefully

[P0NDER0SA]

Technically this is done! moving to done.