[Dbix-class] DBIx::Class and SET SESSION AUTHORIZATION

[Zbigniew Lukasiak]

There is one more thing here - if you set the authorisation by the Apache
thread/process then how do you make sure that this process will only server
this one user? The only obvious solution would be to make the thread/process
serve only one request.

--
Zbyszek

On 9/21/06, Brandon Black <blblack at gmail.com> wrote:
>
>
>
> On 9/20/06, Jon Warbrick <jw35 at cam.ac.uk> wrote:
> >
> > 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/dbix-class@lists.rawmode.org/
>
>


-- 
Zbigniew Lukasiak
http://brudnopis.blogspot.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060925/2a7ea57b/attachment.htm