Thread: permission in the db or in the application?
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. thanks sandro *:-) -- Sandro Dentella *:-) http://www.tksql.org TkSQL Home page - My GPL work
In response to Sandro Dentella <sandro@e-den.it>: > > 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. My big argument is that database applications have a way of outgrowing their original requirements. If you write your application well, it's very likely that someone will want a desktop app version, and others will want direct access to the tables and so forth and so on. If you put all the security in the database itself, you can do these other tools rather quickly, and not duplicate the security code. In addition, you can simply say, "Hire a .NET programmer to write that, we don't have to worry about him implementing the security properly." If you have clients that have some knowledge of SQL, you can give them direct access to the db with impunity, which is a _fantastic_ thing to be able to do. Even the ones who will want to use MS Access will show you interesting stuff that you could be doing with the data. In other words, you application will become more valuable. This is the origin of the term "scale". Otherwise, _every_ time you mess with the application you risk breaking the security. And every time you need to implement and interface in a different form, you have to recreate that security code. Yuck. -- Bill Moran Collaborative Fusion Inc.
Hi Sandro. I think there is no silver bullet here (as in many other fields, too). You could end with a mix of different approaches (simple checks done on the database, while complex one are left in the application logic). Also, most probably you will end moving that logic in the lifetime of the application. If you have time (and money) take a look at Chapter 20 from "Agile Database Techniques" from Scott Ambler: it examines a number of possibilities which could be worthy to you. Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/
Bill makes several valid points, but in spite of them, the app I'm writing has almost no logic in the database. Why? Well, mostly because it would be too much trouble to remove all of it. :) No, seriously... Lack of a good language. Postgresql understands a growing number of languages and that's great, but most business applications (the dominant type of application when discussing security issues) are written in an object oriented language like Java or C# (please don't be offended if I've missed anyone's favourite over-10%-of-the-market OO language). pljava is becoming an alternative, but it's still kind of early to tell if VM-executed languages behave well in a database environment. It's hard to wri...maintain, maintain anything non-trivial written in plpgsql: I face that exact problem right now with a couple of fairly complex import scripts. Lack of a good IDE. I've yet to hear how I can debug a plpgsql script/function. If it's just a matter of ignorance, someone please enlighten me. Code version control. Yes, you can dump a pgsql database schema without the actual data and store it in a repository somewhere, but a) you basically version-control a single large file and b) at least some other manistream RDBMSes (MSSQL, for one) can't make that kind of dump (easily), so the potential user base is much reduced and best (workable?) patterns and practices haven't been established. On the other hand, writing application logic (which includes security) in the application isn't at all that bad. Modular design really helps there: containing the logic in a package/library/module/whatever goes a long way to allow trivial or very easy reuse in a second or third app. And if what you want is a truly heterogeneous application ecosystem built around the same application logic, you can always go the SOA and WS way: "hey, you want to code a GUI in RoR? Here you go..." At that point, you can basically extend your system which ever way you want. ...or at least so I've heard during my knitting class. :) t.n.a.
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/