On Dec 18, 1:10 pm, san...@e-den.it (Sandro Dentella) wrote:
> Hi all,
>
> I'm starting a project in which I will use PostgreSQL in which I need to
> check permissions at different levels (eg.: status of a record, hierarchy
> and so on). The application needs to run with a web interface (sigh!).
>
> At first I thought i'd like to put as much permission logic as possible in
> the database, and I was willing to evaluate veil for that.
>
> The reasons where mainly two:
>
> 1. to be sure that those permission where observed independently from the
> way I was accessing the data. No way to create security 'holes'
>
> 2. simplicity in the code
>
> Some days ago I read an e-mail of somebody that strongly opposed to using
> a db other than for ACID features.
>
> I'd like to hear from this list some thoughts on this subjects.
I've recently faced a similar problem for an application with a web
interface as the primary GUI and decided to stick all security in the
PostgresSQL layer. My decision was based on the fact that the
application interface to my data is really not nearly as important as
the consistency and the security of my data.[1]
There's the problem that many web application frameworks always connect
as the same user, but this doesn't have to be a problem thanks to "set
session authorization" and "set role". For Rails, I've written a very
simple plugin[2] which, for every request, does a "set session
authorization" to a less privileged user and, if a user tries to login,
checks a user's password against the pg_catalog and does a "set session
authorization" if the supplied password is correct. That's how easy it
can be to have reliable, deep authentication in a web application.
But, pick the right tool for the right job. Your requirements probably
aren't the same as mine. ;-)
[1] Of course, you could consider web services enough of an interface,
but I think I like to have ODBC access to my DB. Also I now have
well-performing LDAP auto-completion in our email clients. For things
like an LDAP access layer performance has proven quite critical and I
doubt if I could have done this without sticking as much logic in the
DB layer as possible.
[2] http://rubyforge.org/projects/rails-psql-auth/