Rails - May 2006 - dynamic setting of username and password in database.yml

Hello

I''ve now read a lot about application-level authentication in Rails,
but
I need to do database-level authentication.

The reason is that my database needs to have the current_user (database 
current_user, not current_user defined in an ActiveRecord Model) set to 
execute triggers for automatically updating audit tables. So it is not 
enough to have a session check against a User table.

How is it possible to set dynamically
:username
:password
values in the database.yml from the application?

I cannot use ActiveRecord::Base.establish_connection in my model because 
it does one connect per request, which I think is inefficient and bad 
because it leaves connections opened.

Ideally, my application needs to set database-level authentication once, 
open one connection and reuse it until the user logs off. Is this 
possible with Rails? I thought maybe of using PGconn as described in:

http://www.ruby-forum.com/topic/40078#7726

but that does look rather like a hack, or am I wrong?

thanks for any feedback


Isabelle

-- 
Posted via http://www.ruby-forum.com/.

Hi Isabelle,

I had a similar problem a few weeks ago, I posted to this list, but
this really didn''t solve anything ... Anyway, I did what is described
below, and it seems to work. It''s not quite what you want to do, but
it''s heading in the same direction I guess.

----------------------------------------------------------------------------------

Hi everyone,

I am posting this again, as I didn''t get any replies ...

I was wondering what the common practice for handling multiple db
users with fine grained privileges on multiple databases is. Against
the often read guideline for rails users to keep with a single db as
"more dbs don''t really make sense anyway", my opinion is that
it DOES
make sense to use more than one db schema for a number of reasons that
I won''t explain here. Anyway, I think a lot of users have to face the
need for multiple dbs, so it should be easy to do it in rails :-)
shouldn''t it?

I''m working on a project with the goal to put an evaluation system
online. Customers must be able to fill out the answers to the
evaluation questions, and Admins must be able to (1) keep track of all
current evaluations and (2) trigger statistic calculations once
customers complete their evaluation. Customers must not be able to
view/change other Customer''s data, Admins must not be able to change
Customer data but are allowed to view it. These rules MUST be enforced
on the database level. Which leaves me with the facts that


I have an admin and a customer user on my mysql db, and i have three
database schemas called authentication, customer_sandbox and
persistent_base in there as well. The db of most interest is
customer_sandbox. On most of the tables in customer_sandbox only Admin
is allowed to perform CRUD operations. However there is a set of
tables where both Admin and Customer users are allowed CRUD
operations. These tables are the ones where the Customer''s answers to
the evaluation questions are stored. They are a bit tricky because
column privileges restrict access in a way that

+ only Admins are granted INSERT into these tables (make place for new
evaluation answers)
+ Admins are granted UPDATE only on some of the columns (foreign keys and stuff)
+ Customers are granted UPDATE on the rest of the colums (answers to
the questions - Admins are not allowed to UPDATE those columns)

I currently do the connection configuration in database.yml and I have
different sections for all combinations of database/user pairs, for
all three rails environments. My database.yml looks roughly like this
(the following entries occur for "test" and "production" as
well, also
I have the obviously necessary sections labelled development, test and
production with a rather randomly chosen copy of one of my real db
configurations - rails apparently needs these sections, however,
connection details are handled in the model classes, there simply IS
NO default db in my (?any?) multiple db setting)


webadmin_authentication_development:
 adapter: mysql
 database: ca_authentication
 username: webadmin
 password:
 host: localhost

webadmin_sandbox_development:
 adapter: mysql
 database: ca_sandbox
 username: webadmin
 password:
 host: localhost

webadmin_persistent_base_development:
 adapter: mysql
 database: ca_sandbox
 username: webadmin
 password:
 host: localhost



customer_sandbox_development:
 adapter: mysql
 database: sandbox
 username: customer
 password:
 host: localhost

customer_authentication_development:
 adapter: mysql
 database: authentication
 username: customer
 password:
 host: localhost


Following Chad Fowler''s Rails Recipe on multiple database connections
and Dave Thomas'' blogentry at
http://blogs.pragprog.com/cgi-bin/pragdave.cgi/Tech/Ruby/Connections.rdoc
I created a subclass of ActiveRecord::Base to establish a connection
for all models that only webadmin is allowed to CRUD. Although this is
currently not working at the moment (see my previous post to this
list) I''m pretty sure this is the way to go.

The question arises on the set of tables that can be accessed as both
webadmin and customer. I see the following possibilities: (When i say
"default the connection to ..." I mean following the above mentioned
rails recipe)

1) Default the connection of those tables to a customer connection. In
the WebadminController action performing the "webadmin-only"
operations, remember the old connection and manually change it to the
webadmin connection by calling establish_connection. Perform the
"webadmin-only" operations. Change the connection back to the old one

2) Same scenario as 1 but vice versa (i.e. defaulting to webadmin and
changing to customer in the CustomerController)

3) Create the model classes two times, one time with a superclass
using a webadmin connection, and one time with a superclass using a
customer connection. I think it would at least be necessary to give
rails information about the underlying table name since we need names
different from the rails convention in order for the classnames not to
clash (although I think this should be possible to solve using
modules, then again I don''t know about how models behave in different
modules). The Controllers referencing those models would need to
declare precisely which model class they refer to. WebadminController
would refer to  WebadminEvaluationFigureInput and CustomerController
would refer to CustomerEvaluationFigureInput  thus using the same
table with a different db connection. I am aware that this is not
really DRY, but I also don''t know which implications on the number of
open db connections the other 2 possibilities would have, if any. I
even don''t know if this is working anyway. I will try it out after I
got some sleep and let you all know! Here is the proposed code

class WebadminSandboxBase < ActiveRecord::Base
 establish_connection "webadmin_sandbox_#{RAILS_ENV}"
end

class CustomerSandboxBase < ActiveRecord::Base
 establish_connection "customer_sandbox_#{RAILS_ENV}"
end

class WebadminEvaluationFigureInput < WebadminSandboxBase
 set_table_name "evaluation_figure_inputs"
end

class CustomerEvaluationFigureInput < CustomerSandboxBase
 set_table_name "evaluation_figure_inputs"
end

class WebadminController < ApplicationController
 model :webadmin_evaluation_figure_input

 # webadmin operations using webadmin db connection
 # ...

end

class CustomerController < ApplicationController
 model :customer_evaluation_figure_input

 # customer operations using customer db connection
 # ...

end



I would appreciate very much ANY THOUGHTS on how to handle multiple
users on multiple databases with rails! How to properly use the
database.yml to configure connections for multiple users on multiple
database(schemata)? What to do with the default connections needed by
rails (development, test, production sections in the yml) that just
don''t really make sense in such a setting?


On 5/10/06, iphan iphan <iphan@isb-sib.ch> wrote:
> Hello
>
> I''ve now read a lot about application-level authentication in
Rails, but
> I need to do database-level authentication.
>
> The reason is that my database needs to have the current_user (database
> current_user, not current_user defined in an ActiveRecord Model) set to
> execute triggers for automatically updating audit tables. So it is not
> enough to have a session check against a User table.
>
> How is it possible to set dynamically
> :username
> :password
> values in the database.yml from the application?
>
> I cannot use ActiveRecord::Base.establish_connection in my model because
> it does one connect per request, which I think is inefficient and bad
> because it leaves connections opened.
>
> Ideally, my application needs to set database-level authentication once,
> open one connection and reuse it until the user logs off. Is this
> possible with Rails? I thought maybe of using PGconn as described in:
>
> http://www.ruby-forum.com/topic/40078#7726
>
> but that does look rather like a hack, or am I wrong?
>
> thanks for any feedback
>
>
> Isabelle
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Hello Martin

thanks for your prompt reply. Yes, I had read your post, and my problem 
is that if I understand your solution, it would mean my application 
would require one connection definition for *each* user, am I correct?

Isabelle

Martin Gamsjaeger wrote:
> Hi Isabelle,
> 
> I had a similar problem a few weeks ago, I posted to this list, but
> this really didn''t solve anything ... Anyway, I did what is
described
> below, and it seems to work. It''s not quite what you want to do,
but
> it''s heading in the same direction I guess.
>
-- 
Posted via http://www.ruby-forum.com/.

Hi Isabelle,

Yes, that was the way i had to go ... This is working out ok actually,
i just find this solution a bit verbose and not really DRY. One could
at least argue, that different model class implementations for
different users make sense in a way that most probably these classes
don''t just repeat (duplicate) their definitions, but in fact will have
different access restrictions (thinking of attr_accessible and the
likes, also for authentication libs like ModelSecurity (the one I use)
the declarations will differ). So actually different aspects are
clearly separated, at the cost of being a little WET, not so DRY :-) I
must say, that I begin to "like" this approach, from a design point of
view, as it moves away from if/else/switch statements to determine
whick connection to use. What do you think?

On 5/10/06, iphan iphan <iphan@isb-sib.ch> wrote:
> Hello Martin
>
> thanks for your prompt reply. Yes, I had read your post, and my problem
> is that if I understand your solution, it would mean my application
> would require one connection definition for *each* user, am I correct?
>
> Isabelle
>
> Martin Gamsjaeger wrote:
> > Hi Isabelle,
> >
> > I had a similar problem a few weeks ago, I posted to this list, but
> > this really didn''t solve anything ... Anyway, I did what is
described
> > below, and it seems to work. It''s not quite what you want to
do, but
> > it''s heading in the same direction I guess.
> >
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

I forgot something :)

The problem with the database.yml still remains. You could configure
this statically for your different user(credentials), like I did (at
the moment). Still, I think that this situation could be enhanced,
keeping in mind 2 things: The database.yml will be preprocessed by
rails, so any ruby code is allowed in there to make things a bit more
dynamic. Also the YAML language is quite powerful, so you could check
out the spec to see if something in there fits the situation. Please
let me know if you find out something!

LG
Martin

On 5/10/06, Martin Gamsjaeger <gamsnjaga@gmail.com>
wrote:
> Hi Isabelle,
>
> Yes, that was the way i had to go ... This is working out ok actually,
> i just find this solution a bit verbose and not really DRY. One could
> at least argue, that different model class implementations for
> different users make sense in a way that most probably these classes
> don''t just repeat (duplicate) their definitions, but in fact will
have
> different access restrictions (thinking of attr_accessible and the
> likes, also for authentication libs like ModelSecurity (the one I use)
> the declarations will differ). So actually different aspects are
> clearly separated, at the cost of being a little WET, not so DRY :-) I
> must say, that I begin to "like" this approach, from a design
point of
> view, as it moves away from if/else/switch statements to determine
> whick connection to use. What do you think?
>
> On 5/10/06, iphan iphan <iphan@isb-sib.ch> wrote:
> > Hello Martin
> >
> > thanks for your prompt reply. Yes, I had read your post, and my
problem
> > is that if I understand your solution, it would mean my application
> > would require one connection definition for *each* user, am I correct?
> >
> > Isabelle
> >
> > Martin Gamsjaeger wrote:
> > > Hi Isabelle,
> > >
> > > I had a similar problem a few weeks ago, I posted to this list,
but
> > > this really didn''t solve anything ... Anyway, I did what
is described
> > > below, and it seems to work. It''s not quite what you
want to do, but
> > > it''s heading in the same direction I guess.
> > >
> >
> > --
> > Posted via http://www.ruby-forum.com/.
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>

Chad Fowler uses ERb to set the socket in his dynamic database config, 
but the socket parameter is not set by the rails application:

database.yml
------------
development:
  adapter: mysql
  database: DynamicDBConfig_dev
  username: root
  password:
  socket: <% ["/tmp/mysqld.sock",
              "/tmp/mysql.sock",
              "/some/other/path/to/sock"].detect{|socket|
              File.exist?(socket)
              } %>

we need:

development:
  adapter: mysql
  database: DynamicDBConfig_dev
  username: <% myusername %>
  password: <% mypassword %>


somebody posted a similar request before:

http://www.ruby-forum.com/topic/40078#7726

but did not get a straight answer. What I would like to do:

1 -rails starts with no database connection
2 -login page prompts user for authentication
3 -login controller tries to set myusername,mypassword parameters for 
database.yml, raising error if connection cannot be established
4 -on success, control is passed to rails ModelController
5 -we have a logoff action that calls remove_connection(klass=self)
6 -and a timout mechanism that calls remove_connection(klass=self) to 
close the connection if the application is idle

this supposes that the Login class is not an ActivRecord, as we are not 
authenticating against a rails Model but against the database native 
user management system.


A possible alternative would be to set a default user:

  username: <% username ? username : "root" %>
  password: <% password ? password : "" %>

and get rails to switch the connection, but that is a bit messy, as we 
would always have 2 connections opened.

Rails Gurus, anyone?



-- 
Posted via http://www.ruby-forum.com/.

On May 10, 2006, at 5:42 AM, iphan iphan wrote:
> 1 -rails starts with no database connection
> 2 -login page prompts user for authentication
> 3 -login controller tries to set myusername,mypassword parameters for
> database.yml, raising error if connection cannot be established
> 4 -on success, control is passed to rails ModelController
> 5 -we have a logoff action that calls remove_connection(klass=self)
> 6 -and a timout mechanism that calls remove_connection(klass=self) to
> close the connection if the application is idle
>
> this supposes that the Login class is not an ActivRecord, as we are  
> not
> authenticating against a rails Model but against the database native
> user management system.
>
>
> A possible alternative would be to set a default user:
>
>   username: <% username ? username : "root" %>
>   password: <% password ? password : "" %>
>
> and get rails to switch the connection, but that is a bit messy, as we
> would always have 2 connections opened.
>
> Rails Gurus, anyone?
The issue discussed in this thread sounds like a lot of work with  
little benefit.

Rather than implement support for this requirement, I''d work to  
eliminate it.

Best,
jeremy

Jeremy Kemper wrote:
> The issue discussed in this thread sounds like a lot of work with
> little benefit.
The world I program in is set up so that the database administrators 
create the user and group accounts and access privileges. Triggers for 
audit tables are generated upon schema creation by a procedure shared by 
all schemas.

All database applications, whether their are webapps, executables, 
pipelines etc... have to adapt to this environment.
> Rather than implement support for this requirement, I''d work to
> eliminate it.
how? Do you mean I should use rails to access the RDMS user table? But 
how do I call the triggers without setting current_user?


Isabelle

-- 
Posted via http://www.ruby-forum.com/.

Hi Martin,

haven''t had time to test that on the webapp level yet, but from the 
rails console this seems to be doing what we need:

$ ruby script/console
# check default config from database.yml
>>
ActiveRecord::Base.configurations["development"]["username"]
=> "view_only_account"

# close current connection (not sure this is needed)
ActiveRecord::Base.connection.disconnect!

# switch to new user
>>
ActiveRecord::Base.configurations["development"]["username"]="isabelle"
=> "isabelle"
>>
ActiveRecord::Base.configurations["development"]["password"]="bozos1"
=> "bozos1"

# open connection with new credentials
>> ActiveRecord::Base.establish_connection
=> #<ActiveRecord::Base::ConnectionSpecification:0xb79721c8 
@config={:username=>"isabelle", :password=>"bozos1", 
:database=>"keyword_dev", :host=>"bosnix",
:adapter=>"postgresql"},
@adapter_method="postgresql_connection">

# now check my application Model has picked up the new
credentials
>> Keyword.connection
=> #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter:0xb796c0d4 
@config={:username=>"isabelle", :password=>"bozos1",
etc...}...>


Isabelle

-- 
Posted via http://www.ruby-forum.com/.