[Rails] Re: How can I get migrate to grant privs to another db user? (was: how can I separate DBA and app users)

On Thursday, January 9, 2014 3:04:59 PM UTC-5, Marc Munro wrote:

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:

  development:
    adapter: postgresql
    database: blog
    username: blog

  development_dba:
    adapter: postgresql
    database: blog
    username: blog_owner

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?

__
Marc

You might want to take a look at postgres' GRANT SELECT ON TABLE in the PostgreSQL manual.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/8d5d29e8-0d17-4ff1-8e49-979c39b0a7ed%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.