Manage FAM DB Schema, users, roles

[ConradBoydElliottGustafson]

@rpardofigueroa and @ConradBoydElliottGustafson did a small design session on July 6. We decided on how to set up the DB users and roles for now.

FlywayDB will always run as sysadmin (for now), so we don't need a separate user for the "script runner". We need to set up a role called APP_FAM that will serve as the namespace for all the FAM objects. No login privileges for this role. Since flyway runs all the sql scripts as sysadmin, APP_FAM does not need any CREATE or DROP privileges (and should be restricted). We need to set up a role FAM_API (with login privileges) and grant CRUD privileges to the objects in APP_FAM.

The scripts to create schemas, users, roles will be run by flyway.

AC for this ticket:

• [ ] The correct scripts are checked into the FAM repository (no passwords in code please!!)
• [ ] When we run the db migrations as part of a terraform apply, the scripts run and achieve the objectives (above)
• [ ] Terraform PLAN does not execute any scripts !!

[rparfig]

Updated the FAM data model in ER Studio, to address the following:

• [x] Change references to DATE Domains, given that they were associated to the reserved word SYSDATE and not to CURRENT_DATE, which is the reserved word used by PostreSQL
• [x] Adapt the data model to allow for the definition of IDENTITY columns, used for primary keys
• [x] Adapt the data model to add pre/post scripts to create users and roles as well as ALTER TABLE statements for IDENTITY
• [x] Expose these findings with Cody Crocker and brainstorm solutions

[rparfig]

Discussion with Data Architects regarding delta DDLs led to some investigation and collaboration on how to produce them, using data model versioning. More work required on this, once the first version of the model is deployed and stable.