Thank you for the insights. Sharing a summary of the steps. I was able to create a basic transformer that performs the following steps: 1. Construct a SqlParser. 2. Parse a SQL query provided as input string, using the above SqlParser. 3. Visit every SELECT node using SqlShuffle. Transform the WHERE clause using a custom SqlStdOperatorTable.AND.createCall()
Reading the calcite tutorial and documentation, I saw the calcite library implements a Druid adapter. I initially thought that meant the following: 1. The Druid adapter would implement the *SqlConformance* interface. 2. It would be possible to create a builder that conforms with the Druid SQL dialect. This would be done by invoking ConfigBuilder.setConformance() and ConfigBuilder.setParserFactory(). However, it looks like the Druid conformance is implemented outside the org. apache.calcite library. Instead, it appears the conformance is implemented outside the adapter: https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/PlannerFactory.java . So to get the Druid conformance, the org.apache.druid.sql library is needed. https://github.com/apache/druid/blob/master/sql/pom.xml On Sun, Apr 2, 2023 at 12:25 PM Sebastien Rosset <[email protected]> wrote: > > Also you could give users access to views. A particular user might have > a view with “WHERE tenant = 100” so if they ask for any any other tenant is > they would get no results. > > I'm not sure if you are referring to the Druid View manager > https://docs.imply.io/latest/druid/operations/views/view-manager/, which > is an alpha feature. It would elegantly solve the problem, but it's still > an alpha feature. > > On Sun, Apr 2, 2023 at 11:35 AM Julian Hyde <[email protected]> > wrote: > >> +1 what Askar said >> >> Also you could give users access to views. A particular user might have a >> view with “WHERE tenant = 100” so if they ask for any any other tenant is >> they would get no results. >> >> It’s also possible to use Calcite to check grants (table and column >> access) and fail a query if they access objects they are not allowed to see >> or use. We should add features to support this use case better. >> >> Julian >> >> > On Apr 2, 2023, at 11:09, Askar Bozcan <[email protected]> wrote: >> > >> > (EDIT) >> > ... *For this case *there's also a Druid adapter which uses the native >> JSON >> > intf. >> > >> >> On Sun, 2 Apr 2023 at 21:08, Askar Bozcan <[email protected]> >> wrote: >> >> >> >> What's your use case? Do you want to: >> >> a) Use Calcite as a "frontend" DB of sorts, to accept all queries and >> send >> >> the processed SQL query (from Calcite) only the accepted queries? For >> this >> >> case >> >> b) Just use Calcite as a query processor, and send the processed query >> >> yourself? >> >> >> >> - Askar >> >> >> >>> On Sun, 2 Apr 2023 at 20:38, Sebastien Rosset <[email protected]> >> wrote: >> >>> >> >>> Thank you for the quick response. I am new to Calcite, it's good to >> hear >> >>> there might be a possibility. I will investigate. Regarding the >> tenant_id, >> >>> if the input SQL statement is: >> >>> SELECT a, b, c >> >>> FROM datasource >> >>> WHERE input_expression >> >>> >> >>> Then the tool should add a "security filter" as shown below: >> >>> >> >>> SELECT a, b, c >> >>> FROM datasource >> >>> WHERE tenant_id = 'abcd123' AND input_expression >> >>> >> >>> >> >>> On Sun, Apr 2, 2023 at 9:56 AM Askar Bozcan <[email protected]> >> >>> wrote: >> >>> >> >>>> Hello Sebastien, >> >>>> I'd say it's quite feasible for this purpose by having Calcite >> push-down >> >>>> pre-processed queries to Druid and being a kind of a "front" to >> >>> underlying >> >>>> Druid DB. >> >>>> >> >>>> Regarding rejecting queries other than SELECT: >> >>>> 1) Parse the query and get the SqlNode representing the root of the >> >>> syntax >> >>>> tree. >> >>>> 2) Extend SqlShuttle >> >>>> < >> >>>> >> >>> >> https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/util/SqlShuttle.html >> >>>>> >> >>>> to check & manipulate the syntax tree, returning null for rejected >> >>> nodes & >> >>>> recursively iterating for SELECT sub-queries. >> >>>> Note that you can use SqlSelect's getFrom, getGroup, etc to get all >> of >> >>> the >> >>>> possible nodes which can contain subqueries. Also note that SqlSelect >> >>>> itself is a subtype of SqlCall. >> >>>> >> >>>> Regarding "tenant_id" in WHERE case, I'm not too sure as I'm neither >> >>>> familiar with Druid nor with Druid adapter in Calcite. Regardless, it >> >>>> should be doable on the adapter level AFAIK. >> >>>> >> >>>> Hope that was helpful! >> >>>> >> >>>> Regards, >> >>>> Askar Bozcan >> >>>> >> >>>> On Sun, 2 Apr 2023 at 18:07, Sebastien Rosset <[email protected]> >> >>> wrote: >> >>>> >> >>>>> Would it make sense to use Apache Calcite as a security mediator? >> Has >> >>>>> calcite already been used for that purpose? The mediator would parse >> >>>>> untrusted SQL queries, reject queries other than SELECT (including >> any >> >>>>> sub-queries), and inject multi-tenancy WHERE filters in every SELECT >> >>>> query, >> >>>>> including sub-queries? >> >>>>> >> >>>>> More specifically, consider a Druid database which is configured to >> >>>>> implement multi-tenancy with shared datasources: >> >>>>> >> >>>>> >> >>>> >> >>> >> https://druid.apache.org/docs/latest/querying/multitenancy.html#partitioning-shared-datasources >> >>>>> . >> >>>>> Every Druid datasource would have a "tenant_id" attribute that can >> be >> >>>> used >> >>>>> in a Druid-SQL WHERE clause. >> >>>>> >> >>>>> Thank you. Sebastien >> >>>>> >> >>>> >> >>> >> >> >> >
