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

**URL:** <https://www.ruby-forum.com/t/a-lot-of-postgresql-database-users-authenticating-and-connecting-from-an-enterprise-rails-applicati/171741>\
**Category:** Rails\
**Created:** [July 8, 2009, 3:57pm UTC](https://www.ruby-forum.com/t/a-lot-of-postgresql-database-users-authenticating-and-connecting-from-an-enterprise-rails-applicati/171741 "2009-07-08T15:57:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![alfredmart](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alfredmart](https://www.ruby-forum.com/u/alfredmart)\
**Post date:** [July 8, 2009, 3:57pm UTC](https://www.ruby-forum.com/t/a-lot-of-postgresql-database-users-authenticating-and-connecting-from-an-enterprise-rails-applicati/171741/1 "2009-07-08T15:57:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alfredmart](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alfredmart](https://www.ruby-forum.com/u/alfredmart)\
**Post date:** [July 8, 2009, 4:41pm UTC](https://www.ruby-forum.com/t/a-lot-of-postgresql-database-users-authenticating-and-connecting-from-an-enterprise-rails-applicati/171741/2 "2009-07-08T16:41:32Z")

</div>

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](http://www.nedforce.com)