A lot of postgresql database users authenticating and connecting from an enterprise rails application

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??? :slight_smile: 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 Moros 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??? :slight_smile: 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.