Update: There is a short update at the bottom, just in case you think it’s really as simple as it should be.
When I first delved into development with databases, I was taught to always think about security. There was a certain pattern that I was taught to use whenever I was about to start a new database on our then production server.
The pattern was simple. The application had its own user that it always used when connecting to the database. This user had all privileges on the application database(s), but was otherwise completely unprivileged (not even having read privileges on the other databases).
Thus your app could be as insecure as you liked (insofar as SQL injection and within reason), but the other applications running on the server would not be affected. Simple really.
Today, I again tried to be sensible and use the same database system that my application would be using in production - PostgreSQL - for my local development. “Easy!” I hear you say, but this is not so.
I created an unprivileged user (let’s call him ‘my_awesome_app’ for this post), named after my application. This worked.
I then tried to grant privileges to this user for his three databases, my_awesome_app_development, …_test & …_production. This didn’t work.
At this point, I should have caught on that what I was trying to do was not going to work. I got an error saying “the relation ‘my_awesome_app_development” does not exist”.
I went ahead, creating these databases, then ran the commands to grant all privileges on these databases again, and this time it worked. I then migrated my database, and continued setting up my application getting ready to write some specs.
After realising that my specs would not even begin to pass without loading up the schema, I ran rake db:test:load, one of the documented ways that it is suggested you use to make your test database the same as your development database (the others being rake db:test:clone, rake
db:test:clone_structure and rake db:test:prepare). I steered clear of rake
db:migrate RAILS_ENV=test because i have been warned off it previously.
Here starts my world of hate.
I started getting errors to do with being unprivileged. I didn’t have enough privileges to re-create the database after rails had dropped it. This was fair enough. It showed I was using my database security model the right way.
I then tried everything I knew to make the database task work, short of the evil rake db:migrate…
I tried to make ‘my_awesome_app’ the owner of the databases, but they had to exist for me to do that, and then they would get dropped and rails would generate errors trying to recreate them.
I tried the other rake db:test: commands, but to no avail. None worked. Looking back over the source this evening, I can see that they theoretically shouldn’t work with my security model anyway, as they all are very much into dropping and re-creating the database.
Eventually, I gave up. I copied a database.yml from an application using sqlite and started using that.
I am leaving the application in a sorry state. I still am not running the application on the database it should be running on in production, which annoys me.
This begs the question: What am I doing wrong? Is my database security model wrong, as suggested by my co-workers? Should I not really care about security as the database is in development on my own machine anyway (not a question, as I do care a lot - I see my machine as an opportunity to practice for other servers that I control)? Am I making my databases far too clever for rails’ dumb database opinion?
Or, on the other hand, should I blame rails? Surely there’s a better way to clear out the databases ready for re-use? Is dropping all the tables inside the database a more viable option than dropping and re-creating the whole database, as it leaves the privileges in tact?
Answers on a Postcard to sam@lenary.co.uk please. I’ll post the outcome in a follow-up post.
After doing a lot more research, and looking at what had worked for me in the past, I decided to re-visit this problem.
I decided that perhaps “my_awesome_app” should probably not be the owner of the database, so that it couldn’t drop the database. This was working well on a rails 2.3.8 app that I had on my computer. Rails just warned when I tried to drop the database but didn’t really care. Then merrily created all the right tables again.
However in my rails 3.0.0RC app, it decided to error out and abort the rake tasks, thus confusing the hell out of me. I shall be raising a bug report later.