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?

Did default privileges work for you? I'm having a very similar issue, except it has to do with my backup software. Every time a new table is created, backup user can't read it... yet I AM using default privs, seen clearly from \ddp. — J M
– J M, Commented Sep 3, 2015 at 14:19

janfoeh · Accepted Answer · 2012-05-23 09:15:31Z

4

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.

answered May 23, 2012 at 9:15

janfoeh

10.3k3 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

orion3 Over a year ago

Thanks, looks like ALTER DEFAULT PRIVILIGES is something that would save me from writing a custom Capistrano task and would replace very well both GRANT statements in my question. I will mark the answer correct as soon as I test how good the first solution works.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related