Rails migrations & PostgreSQL: separate user for migration and setting privileges for production user

Question

I have two users in postgresql one of which is called migration and is used when Rails runs migrations on the production server. This user owns the production database. I also have production user who is supposed to have only the following privileges: SELECT, INSERT, UPDATE, DELETE on the production database.

Problem is, every time a new table is created, I have to manually run this in psql:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO production;

-- next line is needed for each new table which has an auto incrementing field, in this case - table `users`
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO production;

because permissions for production user on newly created tables are not set automatically. What's the best way to do it automatically when running migrations? Any script available for Rails/Capistrano?

Answer 1

You could use Postgres' ALTER DEFAULT PRIVILEGES to have it automatically assign the rights to production for all newly created tables.

Alternatively, you could write a custom Capistrano task to set the permissions that is called through the after "deploy:migrate", "mycustomtaskname" hook. This pastie might give you a few good hints on how to interact with pgsql through Capistrano, for example how to provide the password interactively.