Yep, that looks interesting. Couple of comments: - You can certainly do traversal to a resource without doing location-awareness (__name__, __parent__ etc.) as that is a one-direction operation. But if you want to go back up for any reason, e.g. hierarchical ACLs, you’ll need that. Or, if you want to generate a URL to a resource that wasn’t part of traversal using request.resource_url.
- I hope we can get to the point where resource tree has to mean ZODB. Kotti, for example, is a large, mature, successful Pyramid project with hierarchies in SQL. They’ve made it really easy make types without you having to know the scary stuff, just like SQLAlchemy asks you to subclass from Base and it magikizes everything. —Paul > On Oct 21, 2015, at 5:15 PM, Steve Piercy <[email protected]> wrote: > > 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 > <https://botbot.me/freenode/pyramid/2015-10-21/?msg=52400847&page=2> > > --steve > > > On 10/21/15 at 4:28 PM, [email protected] > <mailto:[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> >> <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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/pylons-discuss > <http://groups.google.com/group/pylons-discuss>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- 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.
