Following up on my original post, with another question. Thanks to Walter and Rick for replying earlier.
To clarify: my database is PostgreSQL. By database objects I mean tables, views, schemas, triggers, functions, roles, etc.
The application should have no rights to perform DDL: it should not be able to create or modify any database objects. It can only perform queries and run DML (ie CRUD operations). I want to enforce this within the database as part of our security policy. In every system I have worked on, this is considered a minimal best practice for security. And I’ve been doing database administration and security for a good number of years.
So, the database user for the app must not have the rights to perform migrations. Yet I still want to perform migrations.
This is what I have so far. In config/database.yml I have 2 stanzas:
My default database is development, so my rails app connects using the blog user (role). This user has minimal privileges.
In order to run migrations I do this:
$ rake db:migrate RAILS_ENV=development_dba
This uses the more privileged blog_owner account which will own all of the database objects it creates and has the rights necessary to create them.
This works fine, except that the migration does not give any privileges to the blog user, so it cannot see the tables. I can manually grant the necessary privileges after the migration is run but that’s dumb.
My question now is: how can I tell the migration process to grant privileges, on the objects it creates, to the blog user?