I am building a site in zope that interfaces an SQL database (postgresql) for all its data, including users, which are stored in a "Persons" table.

I am not to sure on the best development model to use for authorisation. Authentication is relatively easy and can be implemented using an out of the box product such as exUserFolder, or something I hack up myself.

However, I don't think Zopes internal security mechanism is really suitable for defining permissions on the sites interaction with the database...unless somebody can prove me wrong!

For example, the person who is authenticated to the site (will be authenticated via my user folder looking at the credentials in the person record in the person table), is only allowed to update records in a specific table that they own, as determined by a foreign key link to the person record. The only way I can see to implement security is to explicity code in my python form validation script, a check that makes sure the person is infact allowed to edit the record...this in itself would require a database query to check the foreign key link against the authenticated user id. My authorization gets more complicated than that though. Some records can only be updated if the person has a specific level of access, that is defined by another record with a foreign key link in an "access_level" table. Given that my site will have hundreds of such actions, its a cumbersome task to implement security in this way. I would also need to implement the security for the form itself, rather than the user only getting an 'unauthorised' error after submitting the form...this would involve similar coding in each form page template!

Another approach maybe to implement the authorization at the database level by using GRANT, REVOKE, rules on tables, functions, views, etc. If the Zope database connector could connect as the authenticated user, then the rules would apply. Then all I would to do is handle the postgres unauthorized error gracefully in my application. However, as far as I can tell, all Zope database adapters, well the Postgres ones anyway, maintain a pool of connections to the database, and each is connected with the same database user. So this approach doesn't seem to be possible. Not to mension the performance hit I assume exists of having to open a new database connection with each page request. I notice a previous discussion about this issue in this list at http://gossamer-threads.com/lists/zope/db/48032?search_string=authorisation;#48032

Do people have any related experiences they can share?

Zope-DB mailing list

Reply via email to