I''m trying to implement a Postgres based Rails system with multiple schemas (one per user for security). For that I need to set a different login user for each of the connecting users (based on a central authentication system that sets the login to the session info). SO I need to login as a different user for each connection coming in. 1) When running a Rails app through FCGI/SCGI, is the database.yml file loaded only when one of the runners launches, or on every request? 2) Can I change database.yml so that when it''s read with erb it''ll replace the user with something from the session automatically? In other words, lets say my database.yml looks like: production: adapter: postgresql database: myapp_production host: localhost username: <%= session[:db_user] %> password: <%= session[:db_pass] %> 1) Will this even work? 2) Will it change with each connection? 3) If not, where should I call establish_connection() when a session starts, so that I can assign the user I want to? Once I have this figured out I''ll write a short wiki page on how to use postgres schemas to insulate one user from the other, while not messing with all the rails way of doing things. Bye, Guy.
ara.t.howard-32lpuo7BZBA@public.gmane.org
2005-Dec-04 21:54 UTC
Re: When is database.yml loaded with FCGI?
On Sun, 4 Dec 2005, Just Someone wrote:> I''m trying to implement a Postgres based Rails system with multiple > schemas (one per user for security). > > For that I need to set a different login user for each of the > connecting users (based on a central authentication system that sets > the login to the session info). SO I need to login as a different user > for each connection coming in. > > 1) When running a Rails app through FCGI/SCGI, is the database.yml > file loaded only when one of the runners launches, or on every > request? > > 2) Can I change database.yml so that when it''s read with erb it''ll > replace the user with something from the session automatically? > > In other words, lets say my database.yml looks like: > production: > adapter: postgresql > database: myapp_production > host: localhost > username: <%= session[:db_user] %> > password: <%= session[:db_pass] %> > > 1) Will this even work? > 2) Will it change with each connection? > 3) If not, where should I call establish_connection() when a session > starts, so that I can assign the user I want to? > > Once I have this figured out I''ll write a short wiki page on how to > use postgres schemas to insulate one user from the other, while not > messing with all the rails way of doing things. >you may want to look at the postgresql docs - you can switch users on a given connection without reconnecting: [ahoward@localhost ~]$ ruby a.rb --- - ahoward --- - www [ahoward@localhost ~]$ cat a.rb require ''postgres'' require ''yaml'' pgconn = PGconn::new y pgconn.query(''select current_user'').first pgconn.exec ''set session authorization www'' y pgconn.query(''select current_user'').first one of the whole points of fastcgi is that is allows you to do things like keep your connection open. better to adjust the authorization than setup/teardown for each request if possible. because fastcgi is process based this is safe and possible. regards. -a -- ==============================================================================| ara [dot] t [dot] howard [at] noaa [dot] gov | all happiness comes from the desire for others to be happy. all misery | comes from the desire for oneself to be happy. | -- bodhicaryavatara ===============================================================================
Hi, Actually, what postgres does doesn''t fit the model I want to implement. In the postgres model (at least from my understanding) a user can drop to a lower privileges user. A regular user can''t pretend to be someone else if it was authenticated at a lower level (SET SESSION AUTHORIZATION). What I''m trying to do is have a low privileges user do the initial login to the database to get the info for the actual login, save it in session (it''ll use the login password to unencrypt the stored password so that even if you break into the low privileges user you can''t get it), and then have the rest of the connections authenticated agains a higher privilege user that have access only to a specific schema. I know it might cause some performance hit because of requiring more connections, but I''m willing to trade that for security. Bye, Guy. On 12/4/05, ara.t.howard-32lpuo7BZBA@public.gmane.org <ara.t.howard-32lpuo7BZBA@public.gmane.org> wrote:> On Sun, 4 Dec 2005, Just Someone wrote: > > > I''m trying to implement a Postgres based Rails system with multiple > > schemas (one per user for security). > > > > For that I need to set a different login user for each of the > > connecting users (based on a central authentication system that sets > > the login to the session info). SO I need to login as a different user > > for each connection coming in. > > > > 1) When running a Rails app through FCGI/SCGI, is the database.yml > > file loaded only when one of the runners launches, or on every > > request? > > > > 2) Can I change database.yml so that when it''s read with erb it''ll > > replace the user with something from the session automatically? > > > > In other words, lets say my database.yml looks like: > > production: > > adapter: postgresql > > database: myapp_production > > host: localhost > > username: <%= session[:db_user] %> > > password: <%= session[:db_pass] %> > > > > 1) Will this even work? > > 2) Will it change with each connection? > > 3) If not, where should I call establish_connection() when a session > > starts, so that I can assign the user I want to? > > > > Once I have this figured out I''ll write a short wiki page on how to > > use postgres schemas to insulate one user from the other, while not > > messing with all the rails way of doing things. > > > > you may want to look at the postgresql docs - you can switch users on a given > connection without reconnecting: > > [ahoward@localhost ~]$ ruby a.rb > --- > - ahoward > --- > - www > > > [ahoward@localhost ~]$ cat a.rb > require ''postgres'' > require ''yaml'' > > pgconn = PGconn::new > > y pgconn.query(''select current_user'').first > pgconn.exec ''set session authorization www'' > y pgconn.query(''select current_user'').first > > > one of the whole points of fastcgi is that is allows you to do things like keep > your connection open. better to adjust the authorization than setup/teardown > for each request if possible. > > because fastcgi is process based this is safe and possible. > > regards. > > -a > -- > ==============================================================================> | ara [dot] t [dot] howard [at] noaa [dot] gov > | all happiness comes from the desire for others to be happy. all misery > | comes from the desire for oneself to be happy. > | -- bodhicaryavatara > ==============================================================================> > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >