Allowed or Disallowed Query List (Words) Feature

[contentsquare]

Allowed or Disallowed Query List (Words) Feature

[valyala]

Currently chproxy doesn't parse the incoming query at all - it just proxies it as-is to clickhouse. The requested feature would require adding non-trivial query parser into chproxy. There is a hacky solution - just scan the first word of the query and determine whether it is allowed. It is already used when determining whether to cache the response - see canCacheQuery. Such hacky solution may fail when the query starts with unexpected syntax, for instance with a -- comment or WITH statement. So the solution may break at any time.

As you already mentioned, clickhouse already provides readonly mechanism for disabling non-readonly queries, thus lowering the importance of the feature.

@alivelimeli , could your provide use cases for this feature?

[alivelimeli]

Yes its possible to make a hacky solution like that. I've thought that maybe chproxy can be behaves like a WAF (Web Application Firewall) to limit some kind of queries. Thanks

[valyala]

From security POV it would be better to implement only allowed_query_prefixes, because disallowed_query_prefixes can be easily misconfigured.

But even the simplest allowed_query_prefixes: SELECT gives false sense of security, since:

• SELECT query may be specially crafted the way that exhausts all the available RAM or CPU resources;
• SELECT syntax may be extended in the future in unsafe ways allowing writing output files on the server side, creating new tables or doing other nasty things;
• ClickHouse may contain security bugs that may be easily triggered via specially crafted SELECT queries.

It is bad practice from security POV to allow untrusted direct SELECT access to any DBMS, not only ClickHouse. So even allowed_query_prefixes has little sense :(