search

hackoregon / civic-devops

Master collection point for issues, procedures, and code to manage the HackOregon Civic platform
MIT License
11 stars 4 forks source link

Switch all API containers to run with read-only database credentials #151

Closed MikeTheCanuck closed 5 years ago

MikeTheCanuck commented 6 years ago

Allegedly, all our 2018 APIs are a read-only construct, and have no need to run with read-write db privileges. So we're going to reconfigure the AWS Parameter Store POSTGRES_USER and POSTGRES_PASSWORD to use a read-only account.

To do that, we'll need to:

  1. create a new credential for each project
  2. assign it read-only privileges in its associated database
  3. Get this configuration change embedded in the database backups that developers are using for any future dump-and-restore operations (or else the next dump-and-restore will wipe out this configuration again):
  4. update the Parameter Store values, use the read-only credential in production:
MikeTheCanuck commented 6 years ago

We won't swap in these new credentials until we have a fully-working API whose data is actively being retrieved by endpoints, such that we can test afterwards that data is still coming back through the endpoints.

MikeTheCanuck commented 6 years ago

Message I posted to #devops channel in Slack this morning:

Morning folks. Question for folks like @Moss Drake @Alec Peters @Nathan Miller - are there any database backups you intend to restore in the near future, or any changes you intend to make to your projects’ databases that are started but unfinished?

I would like to implement the switchover for each of the 2018 databases to use read-only SQL credentials from the production API to the corresponding database. However, I have been reluctant to go to the trouble, because the first few times I did this, I discovered that the next database backup I received from the team didn’t include the read-only role, and

So here’s your forewarning: if I don’t hear from you in the next few days, I’ll assume that the database in production is the primary Source of Truth for your project, and I’ll take a few steps (a) add the read-only user (b) backup the database to S3 with the new read-only role embedded (c) switch the API configuration in production to use the new read-only user

From then on, all requested database restores will have to include the new read-only user changes - otherwise the restore (based on a database without those changes) will break the production API (which will still try to authenticate with the read-only role, but will be denied since the user won’t have access to the database resources).

znmeb commented 6 years ago

So do I need to change the owner on the transporatation-systems-main database, create a new backup, validate it somehow, and then hand it off to the data manager or DevOps?