Using Python set type to implement ACL

Question

Currently I have tables like: Pages, Groups, GroupPage, Users, UserGroup. With pickled sets I can implement the same thing with only 3 tables: Pages, Groups, Users.

set seems a natural choice for implementing ACL, as group and permission related operations can be expressed very naturally with sets. If I store the allow/deny lists as pickled sets, it can eliminate few intermediate tables for many-to-many relationship and allow permission editing without many database operations.

If human readability is important, I can always use json instead of cPickle for serialization and use set when manipulating the permission list in Python. It is highly unlikely that permissions will ever be edited directly using SQL. So is it a good design idea?

We're using SQLAlchemy as ORM, so it's likely to be implemented with PickleType column. I'm not planning to store the whole pickled "resource" recordset, only the set object made out of "resource" primary key values.

Answer 1

You need to consider what it is that a DBMS provides you with, and which of those features you'll need to reimplement. The issue of concurrency is a big one. There are a few race conditions to be considered (such as multiple writes taking place in different threads and processes and overwriting the new data), performance issues (write policy? What if your process crashes and you lose your data?), memory issues (how big are your permission sets? Will it all fit in RAM?).

If you have enough memory and you don't have to worry about concurrency, then your solution might be a good one. Otherwise I'd stick with a databases -- it takes care of those problems for you, and lots of work has gone into them to make sure that they always take your data from one consistent state to another.

Answer 2

If you're going to pickle sets, you should find a good object database (like ZODB). In a pure-relational world, your sets are stored as BLOBS, which works out well. Trying to pickle sets in an ORM situation may lead to confusing problems with the ORM mappings, since they mostly assume purely relational mappings without any BLOBs that must be decoded.

Sets and other first-class objects are really what belongs in a database. The ORM is a hack because some folks think relational databases are "better", so we hack in a mapping layer.

Go with an object database and you'll find that things are often much smoother.

---

Edit

SQLAlchemy has it's own serializer.

http://www.sqlalchemy.org/docs/05/reference/ext/serializer.html

This is neither pickle or cPickle. However, because it needs to be extensible, it will behave like pickle. Which -- for your purposes -- will be as fast as you need. You won't be deserializing ACL's all the time.

Answer 3

If it simplifies things and you won't be editing the file a whole lot (or it will be edited infrequently), I say go for it. Of course, a third option to consider is using a sqlite database to store this stuff. There are tools to make these easily human-readable.

Answer 4

Me, I'd stick with keeping persistent info in the relational DB in a form that's independent from a specific programming language used to access it -- much as I love Python (and that's a lot), some day I may want to access that info from some other language, and if I went for Python-specific formats... boy would I ever regret it...