Re: Advice needed on application/database authentication/authorization/auditing model

On Fri, Oct 22, 2010 at 12:21:17AM +0400, Dmitriy Igrishin wrote:
> Hey Tony,
>
> 2010/10/21 Tony Cebzanov <tonyceb@andrew.cmu.edu>
>
> > I have a web application with a Postgres backend.  In my initial
> > prototype, I decided not to have a Postgres database user created for
> > each application user, opting instead to use my own users table.
> >
> IMO, you are trying to reinvent the wheel. Although, you may do it just for
> fun. :-)

This is an interesting statement and I've wondered about this a couple
of times before.  It seems very common practice (at least with web
applications) to access a database from one user and do all
authentication and authorization checking in the application.

Is this a bad idea?  At first glance, it would indeed seem very
logical to make an application user match a database user, but how do
you do row-level permission checking?

For instance, very often I've had the situation where a web app stores
users, companies and <whatever>s.  The whatevers can be accessed only
to either the user who created them, or when they are either company-wide
assets or the user has company-wide privileges, can be accessed only
when the whatever was made by a user from the same company.

As far as I can see, this would imply either creating views on the
<whatever> for every user (or company?), or manually crafting queries
to do the same. The latter is of course what most webapps do, and it is
a frequent cause of errors and, hence, vulnerabilities.

I'd love to hear some good advice on what has worked well for others.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth