4 ms·

This is a great point, so how do you handle this? Rails doesn't have any separate facility for migration account vs production account, by default. Which becau

by yebyen 6y ago

This is a great point, so how do you handle this? Rails doesn't have any separate facility for migration account vs production account, by default. Which because our deployment paradigm also doesn't have any such separation, resulted in an awkward conversation with our DBAs where we explained that our production login requires the DDL permissions they thought should be reserved for an admin user. I think they're right, but there's only one place for setting the value of DATABASE_USER in each environment. So do you handle migrations by hand, out of band with a different user than production uses? (How should a regular Heroku user handle this issue, I guess is where I'm looking for a straightforward answer to this common issue that I think you're absolutely right about...)

rgoulter 6y ago
The problem came from fat-fingering a command for DB maintenance while running as an admin. This then ran scripts intended for a development environment. (Development environments don't need to be careful about destroying stuff). I suggest ideally all the maintenance tasks can be done automatically, without needing the command to be run manually. Such scripts can then run in staging before in prod; or if the scripts necessarily only apply to prod, they should be reviewed carefully. If prod admin must be done manually, then make you could make it harder to run the development scripts with such admin privileges.
- yebyen 6y ago
  But there is a release phase in Heroku, and reasonably that's the only time you should expect to need DDL permissions... The principle of least required privilege seems to suggest that the production web server shouldn't have permission to perform DDL when it doesn't need it (so that fat-finger would have likely never happened, or never caused a problem, so long as it was only running in the context of the prod web service.) It's just a little bit surprising that there don't seem to be any concrete solutions for this, I find it hard to believe I'm the first to whine about this issue related to commonly understood DBA best-practices. I don't know how you solve it, (maybe add a production_migrations environment?)
vinceguidry 6y ago
The answer is proper 12 factor separation of concerns. The last Rails project I had a role in, I complained mightily about the lack of proper config separation and not keeping your effing config in the codebase. They fell on deaf ears, devs would edit the .env file, it would invariably make it to the repo, and database commands would invariably get run on the wrong database, including prod in some instances. Worse, the CI/CD pipeline would break when I tried pulling the .env file out of the codebase. It took me a while to figure out why and finally keep all the config in the environment and not the codebase, I breathed a great sigh of relief when I was finally able to add .env to the gitignore. Set DATABASE_USER and everything else to ENV['DATABASE_USER']. Better yet use connection strings instead, they're easier to manage. Devs need to hand-manage their own environment, I prefer direnv, but you can use dotenv if you have to, just dictate that .env has to be in the gitignore. Every single CI/CD platform and PaaS lets you set environment variables. So no .env files get pulled down on git pull, messing everyone's day up.
- kenhwang 6y ago
  Yup, with .envs that can be overrided from the command line, the migration command gets a more privileged user set in its environment for just that command, everything else falls back to the file configured production user. Then you can't even fat finger a migration command because you have to set up the privileged environment first.
  - yebyen 6y ago
    This intuitively is exactly what I thought was (should be) the answer, after spending some thought on it. But is it documented anywhere? One of the Rails 6 bounties is for multiple database configurations to be made clear, IOW in configurations involving a partitioned database cluster, or a cluster with separate main and secondary streaming replicas for read and write locking separately; those configurations are all being made clearer in the latest Rails guides, as I understand it. There were always people that knew the right way to do it, but there was no one blessed right way to do it. This seems like it belongs in the rails guides as a regular part of the instruction manual. Then again .env support itself is still separately provided by add-on gems, so maybe not. (Thanks for sharing!)
tuatoru 6y ago
> Rails doesn't have any separate facility for migration account vs production account. Writing your migrations in anything but SQL is one problem. (OK, perhaps pl/pgsql too.) Running it with a system user and database role that has access to the web app code is another. But the major one is that web apps should use very limited database roles that only have execute permission for specific stored procedures. In any environment. Anything else will lead to security exposures, as we continually see. Yes, it takes longer and requires you to become proficient in SQL. So what? You might learn to make better use of your RDBMS. If you're working with data, you need to know SQL. And you especially need to know the access control features of your RDBMS. From the perspective of data security, the answer to "when should you not use Rails?" is always.
Fire-Dragon-DoL 6y ago
What about removing authorization to drop a database? Creating and destroying databases happens so sparingly, it can be done by some "root" user that is allowed to create the other users. Granted that ot won't fix the problem if the next operation is to recreate all tables. I'd argue the main question is: how is possible the test suite runs on the same db as the application? Even for local development it's a nightmare.

by yebyen 6y ago

rgoulter 6y ago

The problem came from fat-fingering a command for DB maintenance while running as an admin. This then ran scripts intended for a development environment. (Development environments don't need to be careful about destroying stuff). I suggest ideally all the maintenance tasks can be done automatically, without needing the command to be run manually. Such scripts can then run in staging before in prod; or if the scripts necessarily only apply to prod, they should be reviewed carefully. If prod admin must be done manually, then make you could make it harder to run the development scripts with such admin privileges.

yebyen 6y ago
But there is a release phase in Heroku, and reasonably that's the only time you should expect to need DDL permissions... The principle of least required privilege seems to suggest that the production web server shouldn't have permission to perform DDL when it doesn't need it (so that fat-finger would have likely never happened, or never caused a problem, so long as it was only running in the context of the prod web service.) It's just a little bit surprising that there don't seem to be any concrete solutions for this, I find it hard to believe I'm the first to whine about this issue related to commonly understood DBA best-practices. I don't know how you solve it, (maybe add a production_migrations environment?)

vinceguidry 6y ago

The answer is proper 12 factor separation of concerns. The last Rails project I had a role in, I complained mightily about the lack of proper config separation and not keeping your effing config in the codebase. They fell on deaf ears, devs would edit the .env file, it would invariably make it to the repo, and database commands would invariably get run on the wrong database, including prod in some instances. Worse, the CI/CD pipeline would break when I tried pulling the .env file out of the codebase. It took me a while to figure out why and finally keep all the config in the environment and not the codebase, I breathed a great sigh of relief when I was finally able to add .env to the gitignore. Set DATABASE_USER and everything else to ENV['DATABASE_USER']. Better yet use connection strings instead, they're easier to manage. Devs need to hand-manage their own environment, I prefer direnv, but you can use dotenv if you have to, just dictate that .env has to be in the gitignore. Every single CI/CD platform and PaaS lets you set environment variables. So no .env files get pulled down on git pull, messing everyone's day up.

kenhwang 6y ago
Yup, with .envs that can be overrided from the command line, the migration command gets a more privileged user set in its environment for just that command, everything else falls back to the file configured production user. Then you can't even fat finger a migration command because you have to set up the privileged environment first.
- yebyen 6y ago
  This intuitively is exactly what I thought was (should be) the answer, after spending some thought on it. But is it documented anywhere? One of the Rails 6 bounties is for multiple database configurations to be made clear, IOW in configurations involving a partitioned database cluster, or a cluster with separate main and secondary streaming replicas for read and write locking separately; those configurations are all being made clearer in the latest Rails guides, as I understand it. There were always people that knew the right way to do it, but there was no one blessed right way to do it. This seems like it belongs in the rails guides as a regular part of the instruction manual. Then again .env support itself is still separately provided by add-on gems, so maybe not. (Thanks for sharing!)

tuatoru 6y ago

> Rails doesn't have any separate facility for migration account vs production account. Writing your migrations in anything but SQL is one problem. (OK, perhaps pl/pgsql too.) Running it with a system user and database role that has access to the web app code is another. But the major one is that web apps should use very limited database roles that only have execute permission for specific stored procedures. In any environment. Anything else will lead to security exposures, as we continually see. Yes, it takes longer and requires you to become proficient in SQL. So what? You might learn to make better use of your RDBMS. If you're working with data, you need to know SQL. And you especially need to know the access control features of your RDBMS. From the perspective of data security, the answer to "when should you not use Rails?" is always.

Fire-Dragon-DoL 6y ago

What about removing authorization to drop a database? Creating and destroying databases happens so sparingly, it can be done by some "root" user that is allowed to create the other users. Granted that ot won't fix the problem if the next operation is to recreate all tables. I'd argue the main question is: how is possible the test suite runs on the same db as the application? Even for local development it's a nightmare.