There was a discussion in #pyramid early this morning that might relate to this. https://botbot.me/freenode/pyramid/2015-10-21/?msg=52400847&page=2
--steve On 10/21/15 at 4:28 PM, [email protected] (Paul Everitt) pronounced: > 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 > ------------------------ Steve Piercy, Soquel, CA -- 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.
