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 -- Posted via http://www.ruby-forum.com/.
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?> 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.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 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 -- Posted via http://www.ruby-forum.com/.