Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey Peter,

2010/10/22 Peter Bex <Peter.Bex@xxxxxxxxx>
On Fri, Oct 22, 2010 at 12:21:17AM +0400, Dmitriy Igrishin wrote:
> Hey Tony,
>
> 2010/10/21 Tony Cebzanov <tonyceb@xxxxxxxxxxxxxx>
>
> > 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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux