How to Manage Static Database Credentials

How to manage static database credentials

This runbook describes the process for creating, rotating, and otherwise managing static BFD database credentials, where static describes traditional username+password authentication. This type of authentication is mostly used by external database analysts. This runbook should not be used to manage RDS IAM authentication credentials, which are used by the core BFD team using their EUA with MFA.

Table of Contents

• Prerequisites
• Generate a new SCRAM password
• Creating a new static user
• Granting/revoking privileges
• Rotating or resetting passwords
• Extending expiration dates

Prerequisites

• Cloud VPN access to the bfd vpn profile
• AWS CLI or AWS Console access to identify RDS endpoints
• Appropriate database privileges to manage users

Generate a new SCRAM password (needed for new users and password resets)

This section describes how to generate a new SCRAM (one-way hashed password) using a BFD helper script. Ideally, you would have the user run this script and send you the hashed credential. This is the ideal method as it prevents us from ever knowing the user's password, and the hashed part can be sent to us clear text (Slack, etc). However, this is not always possible due to the user's technical ability or local workstation constraints. In these cases, you will need to run the script for them and send them the unhashed password securely (Box, encrypted zip, etc).

Talk to the user and gauge their technical ability and workstation constraints. If they are able to run the script, have them follow the script's README and ask them to send you the hashed part via Slack. If not, run the script for them.

The helper script is currently located in ops/ccs-ops-misc/pg-password-generator and generates two outputs: a hashed password and a clear text password.

The unhashed supersecret password is what you will send if you are running the script, the hashed password is what they will send if they run the script. Again, the unhashed password should be treated as a secret.

1. Clone the beneficiary-fhir-db repository
2. Navigate to ops/ccs-ops-misc/pg-password-generator/
3. Create a virtual environment and install dependencies
   1. python3 -m venv venv
   2. source venv/bin/activate
   3. pip install -r requirements.txt
4. Follow the README instructions to generate a SCRAM password

Creating a new static user

Until we fully automate db user provisioning, we need to manually create users. For static users, we use the following naming convention: firstname_lastname (joe_doe). If there are multiple users with the same name, we will append a digit.

To create a new user (note: in postgres, users, groups, and roles are all the same thing):

1. Ensure VPN is up and you have access to the database's writer node
2. Download the RDS combined CA bundle (rds-ca-rsa4096-g1 as the time of this writing) from AWS to a location on disk (~/.aws/rds-combined-ca-bundle.pem for example).
3. Connect to the database using the psql command line tool (or your preferred client)
   1. psql -h <writer-node-endpoint> -U <admin-user> -p 5432 --sslmode=verify-full --sslrootcert=<path-to-rds-combined-ca-bundle.pem> fhirdb
4. Create the user role with an expiration date matching the user's expected access duration (no more than 60 days)
   1. CREATE ROLE <username> WITH LOGIN PASSWORD '<scram-password>' VALID UNTIL '<expiration-date>';
5. Make a note of the expiration date, the user's team, and update the description
   1. ALTER ROLE <username> SET description = '<team-name> <expiration-date>';

Granting/revoking privileges

User access is locked down by default and managed via role based access (groups in this case). To grant a user access to a schema, you will need to grant them access to the appropriate group. The two groups used to manage db analysts are the bfd_analyst_group and the paca_analyst_group. Some users may need access to both groups.

To add a user to a group, run the following command(s):

• grant bfd_analyst_group to <username>;
• grant paca_analyst_group to <username>;

To remove a user from a group, simply replace grant with revoke.

• revoke bfd_analyst_group from <username>;
• revoke paca_analyst_group from <username>;

Rotating or resetting passwords

Rotating or resetting passwords follows the same process as creating a new user. The only difference is that you will need to update the user's password instead of creating a new user. You should also update the user's description with the new expiration date. Start by generating a new SCRAM password (see above) and then run the following commands:

1. ALTER ROLE <username> WITH LOGIN PASSWORD '<scram-password>' VALID UNTIL '<expiration-date>';
2. ALTER ROLE <username> SET description = '<team-name> <expiration-date>';

Extending expiration dates

If the user's expiration date in their description is < 60 days, or if otherwise necessary, you can extend their account by running the following command:

1. ALTER ROLE <username> VALID UNTIL '<new-expiration-date>';
2. ALTER ROLE <username> SET description = '<team-name> <new-expiration-date>';

If the user's expiration date is > 60 days, follow the Rotating or resetting passwords section above.