When is database.yml loaded with FCGI?

Rails
1

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.

2

On Sun, 4 Dec 2005, Just S. wrote:

request?
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

3

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.