tl;dr Complicated SQLAlchemy SQL expression for permission filtering Pyramid ACLs
Hi all. I’ve been noodling around for a while on traversal/location-awareness/hierarchies in SQLAlchemy. During the coming month I’m going to work on a package for this: https://github.com/pauleveritt/pyramid_sqltraversal <https://github.com/pauleveritt/pyramid_sqltraversal> Right now it exists as 6 tutorial steps in Sphinx, gradually adding some of the pieces. If anybody is interested in such a thing, let me know. This question is about non-hierarchical permission filtering SQLAlchemy query results. With this, permission filtering needs to happen on thousands of rows. We can’t do it in Python (has_permission) as each row would become a different query. (Or with hierarchies, several queries for each parent.) 1) What should it look like? SQLAlchemy gives a bunch of extensibility angles. I’m choosing a hybrid method with the work done in an expression, so it can be done in the generated SQL. It would mean a query like: request.dbsession.query(Folder).filter_by(Node.has_permission([‘paul’, ‘group:editors’], ‘view’).etc. Does this seem natural, and is a hybrid method expression the right angle? Ultimately I think this will wind up as a custom column type with mutation tracking. 2) Array operations in SQL I need the ACL to be addressable in SQL, so I’m using a JSONB column type. This pins to a database, but I can live with this. My hybrid method, though, needs to do some operations using sqlalchemy.sql expression constructs: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html …in order to generate the permission filtering logic into SQL. If an ACL is a sequence of ACEs: [ (‘Allow’, [‘paul’, ‘group:editors’], ‘view’), (‘Allow’, [‘group:admin’], ‘add’) ] ..then I need some SQLAlchemy Expression logic to perform the following: - Given the current user’s principals and the to-be-filtered permission…. - ..iterate through each ACE... - …see if the 3rd element (the permission) matches the passed-in permission. - If so, see if any of the passed-in principals are in the ACE’s permissions array - If so, bail out of the loop, returning the valued of the matching ACE’s Allow/Deny I have a feeling this is going to get into some inner queries. I don’t think the JSONB Comparator has any operations for working with this. Although perhaps the stored JSONB could be re-organized to better promote such operations: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator.contains Or perhaps I am wrong to do JSONB and should be doing Array.contained_by: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.ARRAY.Comparator.contained_by Note: I’m leaving out class-based ACLs, hierarchies, optimizations, and some other things. —Paul -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
