clickhouse与ranger对接

[mdianjun]

架构：

• 在ranger-admin界面上配置策略
• ckman从ranger-admin上拉取策略，缓存在本地；把策略转成sql语句，发给各个ck server实例
• ck server把审计日志导出，存到es上
• ranger从es上拉取审计日志，展示到界面上

ckman与ranger-admin的通信方式是REST API；ranger-admin使用的是jersey框架创建的WebService，提供REST API。api文档地址：http://ranger.incubator.apache.org/apidocs/index.html

如果在ck上执行语句无权限，则query_log错误如下：

ubuntu0 :) select type,event_time,user,query,exception_code,exception from system.query_log where user='demo' order by event_time desc limit 1;\G

type:           ExceptionBeforeStart
event_time:     2021-05-14 14:58:59
user:           demo
query:          select * from demo_shard;
exception_code: 497
exception:      Code: 497, e.displayText() = DB::Exception: demo: Not enough privileges. To execute this query it's necessary to have grant SELECT(name, id, dt) ON default.demo_shard (version 21.4.4.1)

[godliness]

ranger-2.1.0 build报错：

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-assembly-plugin:2.6:single (default) on project ranger-distro: Failed to create assembly: Error creating assembly archive admin: Problem creating TAR: entry 'ranger-2.1.0-admin/ews/webapp/META-INF/MANIFEST.MF' closed at '0' before the '129' bytes specified in the header were written -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException
[ERROR]
[ERROR] After correcting the problems, you can resume the build with the command
[ERROR] mvn <args> -rf :ranger-distro

根据issue中描述更换了build命令，问题解决：
https://issues.apache.org/jira/browse/RANGER-3072

[godliness]

ranger-2.1.0的hdfs plugin安装时无需执行 cp lib/*.jar /home/servers/hadoop-3.1.4/share/hadoop/hdfs/lib操作，因为在执行./enable-hdfs-plugin.sh时就会在/home/servers/hadoop-3.1.4/share/hadoop/hdfs/lib目录下创建相应软连接了。

另外，hdfs的superuser：hadoop，ranger的权限策略是无法限制superuser的。

[godliness]

问题点记录：

1. clickhouse中没有user group的概念, 所以我们无需做group的映射
2. clickhouse的user是脱离linux用户的，那么我们在使用usersync同步用户，需要直接从ckman来同步
3. ckman ranger plugin要实现lookupResource和policyListener, 用来给ranger-admin提供前端显示以及策略的拉取
4. ckman ranger plugin将拉取下来的policy转换成对应的clickhouse acls语句分发，同时ranger-admin的mysql中也会存储相关ranger policy
5. ckman ranger plugin 没有拦截发给ck request的功能，仅需要做的事情就是同步策略后转换成ck acls语言分发执行就可以，另外等其他ck实例新添加时需要重新被执行这些acls语句。
6. 参照hive plugin的convertRangerACLsToHiveACLs， ckman plugin也可以照此转换，hive plugin的row filter可以对应clickhouse的row policy
7. 像hive hbase这些插件本身是有默认策略的，但是对于clickhouse，是否需要支持默认策略？集群原有的策略需要同步到ranger-admin.
8. ck新节点扩容 ckman需要将新策略同步给新节点

[godliness]

架构图：

[mdianjun]

ranger auditlog

{
  "_index": "ranger-audit",
  "_type": "_doc",
  "_id": "a1b6227e-092c-4866-97d8-516cafb75668-0",
  "_version": 1,
  "_score": null,
  "_source": {
    "logType": "RangerAudit",
    "reason": null,
    "cluster": "",
    "policyVersion": 1,
    "agent": "hiveServer2",
    "access": "SELECT",
    "event_count": 1,
    "repo": "hivedev",
    "sess": "e3b9611c-3a08-47bc-aa95-6e213607d9ae",
    "reqUser": "hadoop", //
    "seq_num": 1,
    "event_dur_ms": 0,
    "reqData": "select * from default.test",
    "result": 1,
    "action": "select",
    "id": "a1b6227e-092c-4866-97d8-516cafb75668-0",
    "zoneName": null,
    "agentHost": "centos01",
    "policy": 7,
    "cliIP": "127.0.0.1",
    "resource": "default/test/color,name",
    "resType": "@column",
    "evtTime": "2021-05-26T09:51:18.932Z",
    "tags": [],
    "repoType": 3,
    "enforcer": "ranger-acl"
  },
  "fields": {
    "id": [
      "a1b6227e-092c-4866-97d8-516cafb75668-0"
    ],
    "evtTime": [
      "2021-05-26T09:51:18.932Z"
    ]
  },
  "highlight": {
    "reqData": [
      "select * from @kibana-highlighted-field@default.test@/kibana-highlighted-field@"
    ]
  },
  "sort": [
    1622022678932
  ]
}

logstash采集的ck query log

{
    "used_storages": [],
    "input": {
      "type": "log"
    },
    "client_hostname": "",
    "stack_trace": "",
    "client_version_minor": 0,
    "user": "default",
    "port": 44018,
    "used_dictionaries": [],
    "used_formats": [],
    "client_version_patch": 0,
    "used_database_engines": [],
    "host": {
      "name": "centos01"
    },
    "initial_port": 44018,
    "client_revision": 0,
    "memory_usage": "0",
    "is_initial_query": 1,
    "initial_query_id": "a45020bf-4c49-4d11-9ff7-14095ebcc7e1",
    "event_date": "2021-05-27",
    "forwarded_for": "",
    "ProfileEvents.Names": [],
    "query_duration_ms": 0,
    "columns": [
      "system.columns.database",
      "system.columns.table",
      "system.parts.active",
      "system.parts.bytes_on_disk",
      "system.parts.database",
      "system.parts.rows",
      "system.parts.table"
    ],
    "initial_user": "default",
    "http_user_agent": "Go-http-client/1.1",
    "revision": 54448,
    "log_comment": "",
    "thread_ids": [],
    "used_table_functions": [],
    "event_time": "2021-05-27 09:23:59",
    "current_database": "default",
    "ecs": {
      "version": "1.5.0"
    },
    "query_start_time": "2021-05-27 09:23:59",
    "event_time_microseconds": "2021-05-27 09:23:59.232383",
    "ProfileEvents.Values": [],
    "http_method": 1,
    "Settings.Names": [
      "use_uncompressed_cache",
      "load_balancing",
      "readonly",
      "max_memory_usage",
      "insert_distributed_one_random_shard"
    ],
    "tags": [
      "beats_input_codec_json_applied"
    ],
    "client_name": "",
    "written_bytes": "0",
    "used_aggregate_function_combinators": [],
    "read_bytes": "0",
    "quota_key": "",
    "address": "::1",
    "type": "QueryStart",
    "written_rows": "0",
    "fields": {
      "tag": "clickhouse-audit"
    },
    "result_rows": "0",
    "exception": "",
    "query_id": "a45020bf-4c49-4d11-9ff7-14095ebcc7e1",
    "normalized_query_hash": "9981967856571257151",
    "os_user": "",
    "http_referer": "",
    "databases": [
      "system"
    ],
    "read_rows": "0",
    "Settings.Values": [
      "0",
      "random",
      "2",
      "10000000000",
      "1"
    ],
    "@timestamp": "2021-05-27T01:24:00.044Z",
    "initial_address": "::1",
    "used_data_type_families": [],
    "result_bytes": "0",
    "client_version_major": 0,
    "used_functions": [],
    "query_kind": "Select",
    "interface": 2,
    "query": "select p.database as database, p.table as table, p.bytes as bytes, p.parts as parts, p.rows as rows, c.cols as columns from \n\t(select concat(database, table) as rtable, database, table, sum(bytes) as bytes, count() as parts, sum(rows) as rows from system.parts where active = 1 and database!= 'system' group by database, table ) p　\n\tjoin　\n\t(select concat(database, table) as rtable , count() as cols from system.columns where database!= 'system' group by database, table) c　\n\ton p.rtable=c.rtable order by p.database, p.table\n",
    "used_aggregate_functions": [],
    "agent": {
      "id": "190b09b0-d819-4729-89de-07c82046489b",
      "ephemeral_id": "9156994d-546c-49ae-9e6e-6d0822348cad",
      "version": "7.8.0",
      "name": "centos01",
      "type": "filebeat",
      "hostname": "centos01"
    },
    "query_start_time_microseconds": "2021-05-27 09:23:59.232383",
    "log": {
      "file": {
        "path": "/home/servers/clickhouse/data/data/system/audit_log/data.JSONEachRow"
      },
      "offset": 10751085
    },
    "@version": "1",
    "exception_code": 0,
    "agentHost": [
      "system.columns",
      "system.parts"
    ]
  }

​

[Cas-pian]

测试发现两个问题：

1. 一个Policies里有user和role时，若删除user，则刚才role也被从policies里删除了，ranger的bug；
2. 创建的用户名是标识符，建议带上反引号，否则部分情况下会当做关键字而报错。eg: create user if not exists `123`

[Cas-pian]

测试发现两个问题：

1. 一个Policies里有user和role时，若删除user，则刚才role也被从policies里删除了，ranger的bug；
2. 创建的用户名是标识符，建议带上反引号，否则部分情况下会当做关键字而报错。eg: create user if not exists `123`

问题2已在ckman里修复：https://github.com/cloudnativecube/ckman/pull/2/commits/cd20762aa7b219bfd52c0eed012915fe4b11d76f https://github.com/cloudnativecube/ckman/pull/2/commits/f181777508c32f1f1100a5f89efb0046229a82d2

[godliness]

@mdianjun

ranger ckservice 当新建service时候，我们需要去掉默认策略。

ranger的user和role没有cluster归属的字段，目前创建一个user和role，所有集群都创建

[godliness]

关于grant replace的支持提了issue： ClickHouse/ClickHouse#25395