Clickhouse: Table functions are forbidden in readonly mode...

[mcarbonneaux]

i've configured user readonly to be used to only request data...
i've created un view on clickhouse to external mysql database.
when try to request (only read only request!) to clickhouse to this view with readonly user i receive this error:
"Table functions are forbidden in readonly mode"

there a way to use view to external db with readonly user ?

[alexey-milovidov]

Yes. You can use MySQL table engine (CREATE TABLE ... ENGINE = MySQL(...)) instead of mysql table function.

Table function is just a way to dynamically create a table for single query.

[mcarbonneaux]

ok fine !
i've created table with MySQL engine and created view on it...

there no way to do direct view on mysql function ?

like that:

CREATE VIEW clikhdb.clickhview
AS
SELECT
   mysqlcolumn,
FROM  mysql('<mysqlhost>:<mysqlport>','mymysqldbs', 'mymysqltable', 'mysqluser', 'mysqlpass')
GROUP BY
   mysqlcolumn
ORDER BY
   mysqlcolumn

they are readonly why not authorised to select from this view ?

[alexey-milovidov]

Ok, I understand - the user should be allowed to do a query if a table function is used indirectly via view.

[arctica]

I am also running into this problem because I am implementing replication and sharding outside of ClickHouse as we don't want to rely on Zookeeper. To read data, we need to use the remote() table function in order to aggregate data from several shards. For security purposes, it would be great if the user could run only read queries but currently usage of remote() is prohibited. Please consider allowing remote() for read only users.

[ztlpn]

BTW you don't need ZooKeeper to use builtin ClickHouse sharding (Distributed tables etc.)

[arctica]

@ztlpn interesting, thanks for raising that point. But I can't change the sharding e.g. add new servers without adjusting the config file on each server and restart ClickHouse right? That might be not ideal in a quite dynamic setting. It would be cool if the cluster settings could be stored in a ClickHouse table which can be dynamically adjusted.

[arctica]

Actually I see there is already a system.clusters table but doesn't allow writes to it.

[ztlpn]

@arctica Yes, you need to update the config files, but you don't need to restart servers because cluster configuration is updated on the fly.

[arctica]

@ztlpn Thanks for that information. That's a situation that albeit sub-optimal, I can make it work in our use-case.

[filimonov]

BTW - it's quite silly that we can't also use numbers(...), numbers_mt(...), zeros(...) etc. in readonly mode...