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