How can I separate the db DBA user and the db app user

Rails
1

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

2

On Monday, December 30, 2013 5:26:45 PM UTC-5, Marc Munro wrote:

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 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.

3

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.

In your database.yml file, specify the DBA credentials in the
development environment, and the “normal user” credentials in the
production environment. Either don’t run migrations on the production
server, and use a different technique to update the schema there once
you have settled on your DB structure, or change the password in the yml
file to the DBA for installation/upgrades, and return it to “normal”
once you have things working in production.

Walter

4

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

5

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

database as part of our security policy. In every system I have worked on,
database: blog
In order to run migrations I do this:
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.

6

On Jan 9, 2014, at 3:04 PM, Marc Munro wrote:

development:

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?

You can add these extra bits of logic to the migration files, as needed,
using the execute method:

Walter

7

On Thu, 2014-01-09 at 15:45 -0500, Walter Lee D. 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:

Active Record Migrations — Ruby on Rails Guides

Very useful, thanks.

__
Marc