Hello all! When designing web applications, I usually set up (at least) two database users in PostgreSQL: one is the admin who has full access to the database, and another is the web user who has the minimum access necessary. Often I will limit the web user to select on views (rather than base tables) and updates are only possible through stored procedure calls. I recognize this will make some of the out-of-the-box magic of Rails nigh impossible (e.g., the basic CRUD scaffolding), but I''m optimistic that I''ll be able to use this separation of admin and web user privileges by redefining or adding methods that will select from the views and call the stored procedures. I''m new to Rails (and Ruby), and am interested in others'' thoughts on this. Is this possible with Rails? Am I asking for problems? Any suggestions on how to go about this and where to look for more information? Thanks for your time and thoughts! I''m looking forward to my Rails experience! Michael Glaesemann grzm myrealbox com
Michael Glaesemann wrote:> When designing web applications, I usually set up (at least) two > database users in PostgreSQL: one is the admin who has full access to > the database, and another is the web user who has the minimum access > necessary. Often I will limit the web user to select on views (rather > than base tables) and updates are only possible through stored procedure > calls.You can do it as described into this howto: http://wiki.rubyonrails.com/rails/show/HowtoUseMultipleDatabases The only difference is that you''re connecting to the same database, just with a different user name depending on the request. Expect a performance hit as Rails needs to make a new database connection on every request. Alexander.
Alexander Staubo wrote:> Michael Glaesemann wrote: > >> When designing web applications, I usually set up (at least) two >> database users in PostgreSQL: one is the admin who has full access to >> the database, and another is the web user who has the minimum access >> necessary. Often I will limit the web user to select on views (rather >> than base tables) and updates are only possible through stored >> procedure calls. > > > You can do it as described into this howto: > > http://wiki.rubyonrails.com/rails/show/HowtoUseMultipleDatabases > > The only difference is that you''re connecting to the same database, > just with a different user name depending on the request.I don''t think that is exactly what he was asking. The web user is the user that rails will be using. He just wants to make it so ActiveRecord accesses views instead of tables. The hard part is that any modifications to the database will need to go through a stored procedure or something, which I don''t believe ActiveRecord was designed to support. Carl
> I don''t think that is exactly what he was asking. The web user is the > user that rails will be using. He just wants to make it so ActiveRecord > accesses views instead of tables. The hard part is that any > modifications to the database will need to go through a stored procedure > or something, which I don''t believe ActiveRecord was designed to support.ActiveRecord issues UPDATE statements when you call save, so the ''all-updates-through-stored-procedures'' approach isn''t possible. You''ll need to create your own model implementations for this, however you should be able to include ActiveRecord::Validation et. al. My suggestion would be to abandon the stored-procedure focussed approach and GRANT the necessary priviledges (CRUD) to the active record user.> Carl > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
On Feb 8, 2005, at 9:39, Michael Koziarski wrote:>> I don''t think that is exactly what he was asking. The web user is the >> user that rails will be using. He just wants to make it so >> ActiveRecord >> accesses views instead of tables. The hard part is that any >> modifications to the database will need to go through a stored >> procedure >> or something, which I don''t believe ActiveRecord was designed to >> support. > > ActiveRecord issues UPDATE statements when you call save, so the > ''all-updates-through-stored-procedures'' approach isn''t possible. > You''ll need to create your own model implementations for this, however > you should be able to include ActiveRecord::Validation et. al. > > My suggestion would be to abandon the stored-procedure focussed > approach and GRANT the necessary priviledges (CRUD) to the active > record user.Sorry for the late reply. This was indeed what I was asking. Thanks for you for your thoughts. I''ll have to think on this a bit more, as I''m reluctant to give such wide access to the web user. Is it true that the "all-updates-through-stored-procedures'' (which, in the wider definition of "update" includes inserts and deletes) is really impossible? Or is it that I''d need to define all of the "save" and other methods to use the procedures (which, I concede, could be a major undertaking). Thanks for your tolerance of this eager newbie. :) Michael Glaesemann grzm myrealbox com
* Carl Youngblood <carlwork-0CEYHQKyN7s@public.gmane.org> [0225 00:25]:> Alexander Staubo wrote: > > >Michael Glaesemann wrote: > > > >>When designing web applications, I usually set up (at least) two > >>database users in PostgreSQL: one is the admin who has full access to > >>the database, and another is the web user who has the minimum access > >>necessary. Often I will limit the web user to select on views (rather > >>than base tables) and updates are only possible through stored > >>procedure calls. > > > > > >You can do it as described into this howto: > > > > http://wiki.rubyonrails.com/rails/show/HowtoUseMultipleDatabases > > > >The only difference is that you''re connecting to the same database, > >just with a different user name depending on the request. > > I don''t think that is exactly what he was asking. The web user is the > user that rails will be using. He just wants to make it so ActiveRecord > accesses views instead of tables. The hard part is that any > modifications to the database will need to go through a stored procedure > or something, which I don''t believe ActiveRecord was designed to support.I am too bleary eyed to read this early in the day (11am), but does http://wiki.rubyonrails.com/rails/show/HowtoUsePostgresViewsAsTables help? I have just skimmed the url, so it might be intstructions on how to make deep fried banana and peanut butter sandwiches for all I know. If it''s any good let me know (or if its rubbish, saves me reading it then). -- ''I should have been a plumber.'' -- Albert Einstein Rasputin :: Jack of All Trades - Master of Nuns
Michael Glaesemann wrote:> Sorry for the late reply. This was indeed what I was asking. Thanks > for you for your thoughts. I''ll have to think on this a bit more, as > I''m reluctant to give such wide access to the web user. Is it true > that the "all-updates-through-stored-procedures'' (which, in the wider > definition of "update" includes inserts and deletes) is really > impossible? Or is it that I''d need to define all of the "save" and > other methods to use the procedures (which, I concede, could be a > major undertaking). > > Thanks for your tolerance of this eager newbie. :)I think the main point of ActiveRecord is that it saves you all the effort of putting so much business logic in the database. By putting it in the model classes you decrease coupling and improve cohesion, avoiding repeating yourself in more than one layer (the app and the db). The trade-off of placing more of the access control in your app and less of it in the database is considered worth it. Carl
> I think the main point of ActiveRecord is that it saves you all the > effort of putting so much business logic in the database. By putting it > in the model classes you decrease coupling and improve cohesion, > avoiding repeating yourself in more than one layer (the app and the > db). The trade-off of placing more of the access control in your app > and less of it in the database is considered worth it.While it would certainly be possible to override all the ActiveRecord methods which work directly with the tables, I think you''ll find it''s going to be much more pain than just using your own model classes. As Carl says, the reason ActiveRecord and Rails is such a productive development environment is that it values "convention over configuration". Fighting the convention is definitely possible, but I''d question its value. In theory you can include some of the other valuable modules in your own model classes, just like ActiveRecord itself does. ActiveRecord::Base.class_eval do include ActiveRecord::Validations include ActiveRecord::Callbacks include ActiveRecord::Locking include ActiveRecord::Timestamp include ActiveRecord::Associations include ActiveRecord::Aggregations include ActiveRecord::Transactions include ActiveRecord::Reflection include ActiveRecord::Acts::Tree include ActiveRecord::Acts::List end -- Cheers Koz