Comment by sgarland
5 days ago
I’m a DBRE. I spend a good portion of my day with a shell into one or more prod databases. The schema definitions in code are scattered between ORM model definitions, Alembic migrations, and Liquibase migrations, so the only reliable way I have of understanding a schema as it exists is to view it. Plus, I am very comfortable with SQL, and the various system catalogs of both MySQL and Postgres, so it’s a ton easier to work with.
Truly sensitive customer information is encrypted, and on an isolated DB cluster that no one has regular access to. I also operate with a read-only grant, because manual writes to a prod DB is generally a terrible idea.
So what do you do for "okay, we need to run this script that we've decided is a necessary operation". Special account? Everything go through the build server? I've been looking for tooling for "I need to do a production operation but I want it to have proper interlocks and reviews".
You can use something like flyway on top of your existing git/cicd stack. Write the query as a migration, have it reviewed using your git code review process, and merge to run the migration.
If it’s an incident, it’s usually manually run after review, with an audience. If it isn’t, it’s run as a script that goes through normal PR review.
That's what I'm getting at - so in the end, somebody really does need to have access to a regular sysadmin account on the server, even if it's not their default login. I was hoping that there was an option that didn't involve that sort of workflow, or abusing migration tools (since this isn't exactly a migration).