3 ms·

I think the root issue here is that the production database "user" has too many privileges, and the reason for that is migrations. This is compounded by the te

I think the root issue here is that the production database "user" has too many privileges, and the reason for that is migrations. This is compounded by the test user essentially needing to be a db superuser to create and destroy test databases, as well as run the migrations for them. I've noticed this lately with Django, but I'm guessing that it's a general problem. When I design a DB system, Ideally the production 'user' can only do those things that we reasonably expect them to be able to do, and truncate isn't one of them. Also drop tables, potentially delete entries, and any maintenance tasks. DDL modifications are right out. Those tasks can be run from a specific user, and locked down to a certain types of connections that aren't allowed from production.

jsmeaton 9y ago
Totally agree with you, and now that you bring it up in this way I think I want to change how we execute (Django) migrations at work. Create a separate user with higher privileges that can run migrations. Use that connection during the deployment phase that executes migrations. Otherwise, the app gets a standard "user" without DDL access.
twic 9y ago
When i first worked with migrations, they were run manually as part of the release process. Over time, we automated them - by making them part of the release tool. It never occurred to us to make them part of the application itself. For us, it was therefore completely natural that the app's DB credentials did not have DDL permissions. The whole idea of migrations being run by the app still seems really silly to me. I suppose this is the obvious move for developers who (very sensibly!) build applications, but don't build their own release tools, to whom it would never occur to put migrations anywhere except the application.
- zimpenfish 9y ago
  > The whole idea of migrations being run by the app still seems really silly to me. I have one app that kinda runs migrations - there's a helper app which is built at the same time and (normally) runs before the main app. But this is based on a per-user database which means there's no "release" process that could run the migrations. (Similar to iOS apps, really - when you get a new version of Teleappchatbook, it'll often have an "upgrading..." step which I assume is migrations / index updates, etc.)
bpicolo 9y ago
For what it's worth, truncate should not be a required permission for migrations/ddl tasks
ellisv 9y ago
Yeah I think it's interesting that companies will setup completely separate dev/prod databases but then grant all permissions to the the user. Roles are powerful and seem underutilized. Most of the time I only need read access so perhaps it's particular to my work, but why not use a dev role, that only has read access, for developing against production data?

jsmeaton 9y ago

Totally agree with you, and now that you bring it up in this way I think I want to change how we execute (Django) migrations at work. Create a separate user with higher privileges that can run migrations. Use that connection during the deployment phase that executes migrations. Otherwise, the app gets a standard "user" without DDL access.

twic 9y ago

When i first worked with migrations, they were run manually as part of the release process. Over time, we automated them - by making them part of the release tool. It never occurred to us to make them part of the application itself. For us, it was therefore completely natural that the app's DB credentials did not have DDL permissions. The whole idea of migrations being run by the app still seems really silly to me. I suppose this is the obvious move for developers who (very sensibly!) build applications, but don't build their own release tools, to whom it would never occur to put migrations anywhere except the application.

zimpenfish 9y ago
> The whole idea of migrations being run by the app still seems really silly to me. I have one app that kinda runs migrations - there's a helper app which is built at the same time and (normally) runs before the main app. But this is based on a per-user database which means there's no "release" process that could run the migrations. (Similar to iOS apps, really - when you get a new version of Teleappchatbook, it'll often have an "upgrading..." step which I assume is migrations / index updates, etc.)

bpicolo 9y ago

For what it's worth, truncate should not be a required permission for migrations/ddl tasks

ellisv 9y ago

Yeah I think it's interesting that companies will setup completely separate dev/prod databases but then grant all permissions to the the user. Roles are powerful and seem underutilized. Most of the time I only need read access so perhaps it's particular to my work, but why not use a dev role, that only has read access, for developing against production data?