Askar, Yes, I am exploring whether I could use Calcite as a "frontend" DB that intercepts a SQL query, validates it, rejects queries that attempts to mutate data, and transform the WHERE clauses. After filtering and transformation, the query would be sent to the real database.
I've already done that with native Druid queries. I am now investigating how to do it with Druid SQL. On Sun, Apr 2, 2023 at 11:09 AM Askar Bozcan <askar.mu...@gmail.com> 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 <sros...@gmail.com> 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 <askar.mu...@gmail.com> > 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 <sros...@gmail.com> > 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 > > > > > > > > > >
