Following up on my original post, with another question. Thanks to
and Rick for replying earlier.
To clarify: my database is PostgreSQL. By database objects I mean
views, schemas, triggers, functions, roles, etc.
The application should have no rights to perform DDL: it should not be
to create or modify any database objects. It can only perform queries
run DML (ie CRUD operations). I want to enforce this within the
as part of our security policy. In every system I have worked on, this
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
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
database objects it creates and has the rights necessary to create them.
This works fine, except that the migration does not give any privileges
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?