On 9/20/06, Jon Warbrick <[EMAIL PROTECTED]> wrote:
Well, Cat+DBIC will basically create one database connection per process and/or thread. If you're using apache, it will be one per child/thread/proc for whichever mpm you're using. For FastCGI, its one connection per FastCGI backend process. For the standard testing server, it will just be one connection.
The best approach would of course be to stop using the database's concept of users to implement your actual end-users. That's nearly always a bad idea in my book. Ideally you want to just create a few database role accounts for the various different functional chunks of code that will be accessing the database on behalf of the users. For instance, I have a user name app_readonly, one named app_web, one named app_daemon, and one named app_super. They have slightly different access permissions to the tables based on the maximal set of permissions any of these roles should need in the course of normal operation, with app_super being the database superuser that owns all of the tables, etc. Real end-users should be a part of your application architecture, rather than real database users.
However, if you're stuck with this kind of arrangement, SET SESSION AUTHORIZATION isn't a bad workaround. You'll need to make absolutely sure that you call this once per request. Perhaps a root-level Catalyst "begin" or "auto" action which calls $c->model('Foo')->storage->dbh->do('SET SESSION AUTHORIZATION ' . $c->user->name) if and only if there is a logged in user, and sets it to some default username like "readonly" if there is no valid logged in user?
If you don't do in a central auto/begin action for every request, you'll be left wondering if you've inserted that statement everywhere that it should be inserted to cover all of the cases of forwarding and so on.
-- Brandon
I'm evaluating DBIx::Class and Catalyst for an application accessing an
existing PostgreSQL database that contains its own buisness logic and
which implements its own per-user access control. All potential users of
the application will also be users in the PostgreSQL database. I believe
that I will need to arrange for all interaction with the database during
any one request to take place as if it was using a database connection
created using the id of the user as established by Catalyst.
The suggested approach is to use SET SESSION AUTHORIZATION before starting
to process each request so that all database interaction is processed in
the name of the appropriate user. The question is, is this safe with
DBIx::Class? In a DBIx::Class/Catalyst, is there any danger that something
cached during one request may end up being used in a subsequent one? Can
anyone suggest a better approach? Apologies if this is covered in
documentation that I haven't yet found.
[I admit that I don't yet entirely understand when a DBIx::Class/Catalyst
actually creates new database connections, but I'm assuming that this
isn't on every request and that some sort of connection caching takes
place. Please correct me if I'm wrong...]
Well, Cat+DBIC will basically create one database connection per process and/or thread. If you're using apache, it will be one per child/thread/proc for whichever mpm you're using. For FastCGI, its one connection per FastCGI backend process. For the standard testing server, it will just be one connection.
The best approach would of course be to stop using the database's concept of users to implement your actual end-users. That's nearly always a bad idea in my book. Ideally you want to just create a few database role accounts for the various different functional chunks of code that will be accessing the database on behalf of the users. For instance, I have a user name app_readonly, one named app_web, one named app_daemon, and one named app_super. They have slightly different access permissions to the tables based on the maximal set of permissions any of these roles should need in the course of normal operation, with app_super being the database superuser that owns all of the tables, etc. Real end-users should be a part of your application architecture, rather than real database users.
However, if you're stuck with this kind of arrangement, SET SESSION AUTHORIZATION isn't a bad workaround. You'll need to make absolutely sure that you call this once per request. Perhaps a root-level Catalyst "begin" or "auto" action which calls $c->model('Foo')->storage->dbh->do('SET SESSION AUTHORIZATION ' . $c->user->name) if and only if there is a logged in user, and sets it to some default username like "readonly" if there is no valid logged in user?
If you don't do in a central auto/begin action for every request, you'll be left wondering if you've inserted that statement everywhere that it should be inserted to cover all of the cases of forwarding and so on.
-- Brandon
_______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class Wiki: http://dbix-class.shadowcatsystems.co.uk/ IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ Searchable Archive: http://www.mail-archive.com/[email protected]/
