Forum: Ruby on Rails RoR, pg_connections, and pg_users

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
171ea139761951336b844e708d1547ab?d=identicon&s=25 James Byrne (byrnejb)
on 2006-03-24 21:32
In a RoR application, is it possible to have individual userids
associated with particular sessions employed to establish their related
connections to the postgresql DBMS backend and, if so, is this design
desireable?  What are the implications for performance and future
deployment limitations?

The idea behind this question is that in our application certain tables
require that the last accessor to a row be positively identified along
with the date/time stamp.  It is also desired that such identification
be enabled outside the scope of application code, that the DBMS itself
should take care of this need using userids and triggers provided by the
DBMS itself, so that the requirement cannot be easily bypassed by
writing custom programs or using utility software.

My readings do not provide me with any clear understanding of this
situation with respect to 3-tier apps.  It is evident that DB
connections are not actually established between the client and the
database but are rather between the application server and the DBMS
backend, so it may be that this capability is foreclosed by the design
itself.  But I would like to have an informed opinion on the matter
rather than proceed in ignorance.

I am taking this matter up here because the default arrangement for
Rails as evidenced in the config/database.yml file is to have a single
DB userid establish the connection for an entire application
environment.  This leads me to suspect that multiple DB userids are not
a common feature of Rails applications.

However, it also seems to me that one should be able to inject userids
into the database.yml using Ruby itself to provide the variable
information from the session data.  So, one can conceive to a two-stage
session setup where the initial authentication takes place over an
administative userid based DB connection followed in step 2 by the
actual application DB connection established with the credentials
provided by the client.  The question then is what will be the
performance hit?


Regards,
Jim
Eea7ad39737b0dbf3de38874e0a6c7d8?d=identicon&s=25 Justin Forder (Guest)
on 2006-03-25 09:16
(Received via mailing list)
James Byrne wrote:
> In a RoR application, is it possible to have individual userids
> associated with particular sessions employed to establish their related
> connections to the postgresql DBMS backend and, if so, is this design
> desireable?  What are the implications for performance and future
> deployment limitations?

Possible, perhaps. Desirable, no - it would mean opening, using and
closing a user-specific connection on every HTTP request. (Successive
requests from a given user may be handled by different FastCGI
processes.)

The web application would have to hold userid and password in session
state to enable this (presumably user authentication would be done by
attempting to connect to the DB using these credentials).

> The idea behind this question is that in our application certain tables
> require that the last accessor to a row be positively identified along
> with the date/time stamp.  It is also desired that such identification
> be enabled outside the scope of application code, that the DBMS itself
> should take care of this need using userids and triggers provided by the
> DBMS itself, so that the requirement cannot be easily bypassed by
> writing custom programs or using utility software.

If you are prepared to trust the application to hold the userid and
password and to use them correctly to connect to the DB, why not trust
the application to provide the userid in each database update?

> DB userid establish the connection for an entire application
> environment.  This leads me to suspect that multiple DB userids are not
> a common feature of Rails applications.

Correct.

> However, it also seems to me that one should be able to inject userids
> into the database.yml using Ruby itself to provide the variable
> information from the session data.  So, one can conceive to a two-stage
> session setup where the initial authentication takes place over an
> administative userid based DB connection followed in step 2 by the
> actual application DB connection established with the credentials
> provided by the client.  The question then is what will be the
> performance hit?

You wouldn't do it by varying the database.yml content. That file is a
configuration file which is read once on application start.

You would do it by explicitly opening and closing a connection within
the application. The performance hit would be substantial - make your
own measurements of the time taken to open a database connection. This
varies greatly between databases. CGI applications have to accept this
overhead, but FastCGI applications don't.

regards

   Justin
171ea139761951336b844e708d1547ab?d=identicon&s=25 James Byrne (byrnejb)
on 2006-03-25 23:22
Justin Forder wrote:

> If you are prepared to trust the application to hold the userid and
> password and to use them correctly to connect to the DB, why not trust
> the application to provide the userid in each database update?
>

The issue is to record access to the rows regardless of how it
accomplished. It is always possible to bypass application software
logging one way or another.  Logging at the DBMS level seems to provide
the most reliablly enforced method, but  this may not be feasible as you
point out.

>
> You wouldn't do it by varying the database.yml content. That file is a
> configuration file which is read once on application start.
>

Well, that clears away one misconception.

> You would do it by explicitly opening and closing a connection within
> the application. The performance hit would be substantial - make your
> own measurements of the time taken to open a database connection. This
> varies greatly between databases. CGI applications have to accept this
> overhead, but FastCGI applications don't.
>

We are contemplating using mod_ruby to begin with and then mod_fcigd
later.  I will need to reconsider how to accomplish our audit
requirements within the limits you outline.

Thank you.
Jim
This topic is locked and can not be replied to.