PCRUD search works by constructing an executing a query, then scanning the results and skipping any rows that the user does not have permission to retrieve.
In cases where the user is likely to have permission to access all of the rows retrieved by the query, this isn't a problem as far as accuracy of the results are concerned.
However, it's often the case that the user may not have permission to retrieve a significant number of rows in the results. This can lead to incomplete results in AngularJS and Angular grids, which directly or (via Flattener) indirectly run broad PCRUD queries with offsets and limits. It is particularly problematic for grids (e.g., the EDI messages viewer) that don't have an OU filter that's automatically set to the user's workstation.
For example, imagine a consortium where one system generates 95% of the EDI message traffic and another generates only 5%. A user from the second system whose provider retrieval permissions are restricted to only their own system might open the EDI message page... and not see any results because all of the rows in the initial query window belong to the first system.
It would be better if PCRUD search could construct the base SQL query so it includes the joins and filter conditions necessary to restrict the results to rows that the user has permission to access. While this probably wouldn't obviate the need for post-query permissions checks for each row in the results, it would make it much less likely that the user wouldn't see results that they are entitled to.
Evergreen 3.2+
Some brainstorming Mike Rylander and I had about this:
<miker> gmcharlt: off my call now, looking at your bug. yeah, empty first page... gotcha. another option is to 1) move to cursors (so we don't retrieve ALL THE COPIES into pcrud RAM up front) 2) fetch a row or 10 at a time 3) implement limit/offset in pcrud, post-perm-check optimization ... we're introducing a significant variable to PG planning that we haven't really done before
<miker> some pros/cons for that:
<miker> pros: generally PG picks a "fast-start" plan for cursors (conditions apply), first page of results never empty, first page generally faster, lower RAM use (no chance of blowing out the backend osrf object slab allocator)
<miker> cons: subsequent pages /might/ take longer to return, especially when perm+location caching is defeated by a very broad object ownership spread
<gmcharlt> as well as the degenerate case that only a handful of rows meet the permissions conditions and you've effectively done a full scan of the table, though at least not without pooching pcrud's memory usage unnecessarily
<gmcharlt> so the cursor-based approach might be a win regardless
<miker> biggest problem I see with embedding perm checking in the query is plan stability/
<gmcharlt> yeah, at minimum I figure we'd turn up cases where we would want additonal indexes on context OU columns
<miker> aye. and user-ownership is tricky to OR into that...
<miker> see: subscription buckets :)
<gmcharlt> and as yet another edge case, object-owership
<gmcharlt> not, fortunately, that we have a ton of it in active use, but we do have some
<gmcharlt> of course, for specific cases, a workaround is to embed the additional OU-ownership conditions in the client's source pcrud query
<gmcharlt> but I'd hate for that to be done more than absolutely necessary
<miker> yeah, not a fan of that... we can make the tools better