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

Hey Peter,

2010/10/22 Peter Bex <Peter(dot)Bex(at)xs4all(dot)nl>

> On Fri, Oct 22, 2010 at 12:21:17AM +0400, Dmitriy Igrishin wrote:
> > Hey Tony,
> >
> > 2010/10/21 Tony Cebzanov <tonyceb(at)andrew(dot)cmu(dot)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.
>
Yes, liberal use of views and rules are the best solutions in this case IMO.

>
> 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
>

--
// Dmitriy.