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
James B. 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
Justin F. 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