How can I separate the db DBA user and the db app user
67 views
Skip to first unread message
Marc Munro
Dec 30, 2013, 5:26:45 PM12/30/13
to rubyonra...@googlegroups.com
How can I separate the database DBA user and app access user in rails? The app user will be able to run the app but perform no DDL. The DBA user will be used for migrations.
I do not want the user that runs the rails app to be able to create, drop or modify database objects. This type of user access-rights separation is a pretty minimal best practice and I am concerned that this does not seem to be the norm in the rails world. What am I missing?
My current thinking is that I should create 2 stanzas per database in the database.yml file. One for the dba user and one for the normal app user. Does anyone have any better suggestions?
__
Marc
I do not want the user that runs the rails app to be able to create, drop or modify database objects. This type of user access-rights separation is a pretty minimal best practice and I am concerned that this does not seem to be the norm in the rails world. What am I missing?
My current thinking is that I should create 2 stanzas per database in the database.yml file. One for the dba user and one for the normal app user. Does anyone have any better suggestions?
__
Marc
Rick
Dec 31, 2013, 1:53:32 PM12/31/13
to rubyonra...@googlegroups.com
You need to be a little more specific re what you mean by 'database objects'.
When talking about db/migrate type activities, you are concerned with creating, modifying, and dropping tables. These actions are usually (except for sqlite) controlled by permissions associated with a database role. If the db role (username in the database.yml file) has create or alter privileges associated with it that user will be able to run migrations. That said, migrations are not typically (or should they be) run from within a rails app.
When talking about db record level activities, which correspond to ActiveRecord model CRUD actions, you are again faced with the need to modify an existing table. These changes, made in the context of a running rails app, are typically limited to table content (rather than form). The db role (username in the database.yml) must be able to modify the database tables.
You get finer grained control within the rails app by using an authorization scheme (declarative_authorization and cancan gems) together with model associations (user has profile, profile belongs to user) to control access to data.
When talking about db/migrate type activities, you are concerned with creating, modifying, and dropping tables. These actions are usually (except for sqlite) controlled by permissions associated with a database role. If the db role (username in the database.yml file) has create or alter privileges associated with it that user will be able to run migrations. That said, migrations are not typically (or should they be) run from within a rails app.
When talking about db record level activities, which correspond to ActiveRecord model CRUD actions, you are again faced with the need to modify an existing table. These changes, made in the context of a running rails app, are typically limited to table content (rather than form). The db role (username in the database.yml) must be able to modify the database tables.
You get finer grained control within the rails app by using an authorization scheme (declarative_authorization and cancan gems) together with model associations (user has profile, profile belongs to user) to control access to data.
Walter Lee Davis
Dec 31, 2013, 2:01:48 PM12/31/13
to rubyonra...@googlegroups.com
On Dec 30, 2013, at 5:26 PM, Marc Munro wrote:
> How can I separate the database DBA user and app access user in rails? The app user will be able to run the app but perform no DDL. The DBA user will be used for migrations.
Walter
>
> I do not want the user that runs the rails app to be able to create, drop or modify database objects. This type of user access-rights separation is a pretty minimal best practice and I am concerned that this does not seem to be the norm in the rails world. What am I missing?
>
> My current thinking is that I should create 2 stanzas per database in the database.yml file. One for the dba user and one for the normal app user. Does anyone have any better suggestions?
>
> __
> Marc
>
>
> 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-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/add2b834-6dd6-4cfd-b99f-a8eeab5bdcd2%40googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
Marc Munro
Jan 9, 2014, 3:04:59 PM1/9/14
to rubyonra...@googlegroups.com
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
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
Walter Lee Davis
Jan 9, 2014, 3:45:19 PM1/9/14
to rubyonra...@googlegroups.com
http://guides.rubyonrails.org/migrations.html#when-helpers-aren-t-enough
Walter
>
> __
> Marc
>
>
> --
> 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-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.
Rick
Jan 10, 2014, 4:25:09 PM1/10/14
to rubyonra...@googlegroups.com
You might want to take a look at postgres' GRANT SELECT ON TABLE in the PostgreSQL manual.
Marc Munro
Jan 10, 2014, 4:47:47 PM1/10/14
to rubyonra...@googlegroups.com
On Thu, 2014-01-09 at 15:45 -0500, Walter Lee Davis wrote:
> On Jan 9, 2014, at 3:04 PM, Marc Munro wrote:
> You can add these extra bits of logic to the migration files, as needed, using the execute method:
>
> http://guides.rubyonrails.org/migrations.html#when-helpers-aren-t-enough
>
Very useful, thanks.
> On Jan 9, 2014, at 3:04 PM, Marc Munro wrote:
> You can add these extra bits of logic to the migration files, as needed, using the execute method:
>
> http://guides.rubyonrails.org/migrations.html#when-helpers-aren-t-enough
>
__
Marc
Reply all
Reply to author
Forward
0 new messages