[keycloak-user] Data filtering in SQL

[Dmitry Telegin]

Hello Rob,

If I get it right, it's all about generating SQL WHERE clause from Keycloak policies? I think this is doable, as Keycloak has a well-defined object model for authorization policies, and it's easy to obtain policy definitions in JSON format. I think Pedro Igor will tell you more about that.

You should pay attention to the following:
- there are differences in semantics between OPA and Keycloak policies. For example, Keycloak policies do not operate HTTP methods but rather use more generic notion of scopes;
- not every policy type can be easily converted to a WHERE clause. It should be trivial for User/Group/Role policies, but is virtually impossible for Script and Rules, as they are just blackboxes that evaluate to true or false. Unless of course your DBMS has a built-in JavaScript engine :)

Good luck!
Dmitry Telegin
CTO, Acutus s.r.o.
Keycloak Consulting and Training

Pod lipami street 339/52, 130 00 Prague 3, Czech Republic
+42 (022) 888-30-71
E-mail: info at acutus.pro

On Thu, 2018-11-01 at 21:39 +0000, Byrd, Rob M wrote:
> I am comparing OPA authorization to Keycloak - how could I enforce Keycloak policy in the SQL closest to the data for good performance, including returning subsets of lists?  OPA discusses this at https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4.
> 
> Thanks!
> 
> Rob Byrd
> DST
> Solutions Lead
> SS&C Technologies Inc.   |   1055 Broadway, Kansas City, MO 64105
> t: (816) 435-7286  | m (816) 509-0119
> rmbyrd at dstsystems.com<mailto:rmbyrd at dstsystems.com>  |  www.ssctech.com<http://www.ssctech.com/>;
> > > Follow us: [cid:image001.png at 01D412C1.A14C5770] <https://www.linkedin.com/company/ss-c-technologies/>  |  [cid:image002.png at 01D412C1.A14C5770] <https://twitter.com/ssctechnologies>  |  [cid:image003.png at 01D412C1.A14C5770] <https://www.facebook.com/ssctechnologies/>
> 
> 
> 
> Please consider the environment before printing this email and any attachments.
> 
> This e-mail and any attachments are intended only for the individual or company to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure or unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user