Hi to everybody, my name is Alfredo Rico from Venezuela. Please
apologize if
my english is not so good…
My company is beginning a huge enterprise rails project for a
goverment customer.
We have setting up a postgresql database following a number of
considerations that make it a fortress, implementing a lot of bussines
rules requiriments from our goverment customer. I’m talking about an
integration database and not about an application database. Hence a
legacy system.
The database contains a number of features among which we could
mention the following:
- A lot of store procedures and triggers.
- Reporting based on views.
- High granularity database user roles.
- Data integrity constraints.
The matter is the following:
There are a lot of database users which belongs to different kind of
databases roles according to their responsibilities, and when a new
user is incorporated from the web application, actually that new user
is added to the database system users (I meant, there isn’t a database
table called Users but the users belongs to postgresql). What a
hell!!!.. Why??? 
Because we are modeling a database for the
future. Today is rails (and for several years I meant). Tomorow we
won’t know. Besides, another applications (probably developed on
differents programming languages) will use our postgres database.
The shoot:
We would like to develop an elegant and efficient ActiveRecord
solution that let us the following:
When an application user like the beautiful assistant accounting
department, set her username and password to login on the web form,
the authentication process must be performed on a successfully
database establishment connection. Cause her username and password
belong to a postgresql database user.
I suspect that probably we’ll need a kind of special configuration for
a database connection pool which should not degrade our application’s
performance.
Could you give me some suggestions?
Thanks a lot.
Alfredo Rico M. wrote:
There are a lot of database users which belongs to different kind of
databases roles according to their responsibilities, and when a new
user is incorporated from the web application, actually that new user
is added to the database system users (I meant, there isn’t a database
table called Users but the users belongs to postgresql). What a
hell!!!.. Why??? 
Because we are modeling a database for the
future. Today is rails (and for several years I meant). Tomorow we
won’t know. Besides, another applications (probably developed on
differents programming languages) will use our postgres database.
I’m with you on the “now Rails, what tomorrow” issue, but am unsure if
that’s a good rationale for having a high amount of coupling with
PostgreSQL. What about “now PostgreSQL, what tomorrow?”
Having user authentication in a normalized SQL fashion is as close as
you’re going to get to data independence, I’d say.
We would like to develop an elegant and efficient ActiveRecord
solution that let us the following:
When an application user like the beautiful assistant accounting
department, set her username and password to login on the web form,
the authentication process must be performed on a successfully
database establishment connection. Cause her username and password
belong to a postgresql database user.
I suspect that probably we’ll need a kind of special configuration for
a database connection pool which should not degrade our application’s
performance.
I suppose it depends on the pooling software that you’re using. Most
pools will consider different users to the same database as separate
connections and won’t be pooling them, so you should be safe there.
Another thought:
A couple of years ago I was on the development team for a system that
used one schema per organization. A database procedure allowed a user
from that organization to “login” and it set the schema search path. The
schema, in turn, contained views that limited the data based on his
organization_id.
I won’t recommend it now because you can do the same thing with scopes
in Rails.
–
Roderick van Domburg
http://www.nedforce.com
Because we are modeling a database for the