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@[...].gov <ara.t.howard@[...].gov>  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@[...].org
>  http://lists.rubyonrails.org/mailman/listinfo/rails
> 
_______________________________________________
Rails mailing list
Rails@[...].org
http://lists.rubyonrails.org/mailman/listinfo/rails