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

rubyonrails-talk
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?

2

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

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?

5

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?

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

Walter

6

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

7

Very useful, thanks.