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

[Brandon Black]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060921/be8bdcfe/attachment.htm