Redash，make your company data driven，connect to any data source, easily visualize and share your data……

背景

• 当前内部使用的redash版本为 0.10.0+b1774，许多新特性都不支持；
• 新版本的redash已经支持 query params 和 filter；
• 最新版本的redash已经支持 LDAP 登陆；

新特性

Query Params

SELECT DATE, COUNT(*) FROM mytable WHERE DATE = "{{date}}"

参考：https://redash.io/help/queries/writing_queries.html#query_params

Filter

SELECT action AS "action::filter", COUNT (0) AS "actions count" FROM events GROUP BY action

同时也支持  “action::multi-filter” 的写法。

参考：https://redash.io/help/queries/writing_queries.html#query_filters

更新

旧版本的redash采用 docker 方式安装，官方并无提供相对应的文档，数据库的迁移存在困难：

Docker

If you’re using Docker to run Redash, don’t use the upgrade script, but rather update the Docker image you’re using

Docker 的安装方式并不是官方的推荐方法， From Github#1079：

Because there is no documented process for running migrations (and upgrade) for Docker, we don’t recommend it “officially” for usage and assuming that those who still want to use Docker will figure it out.

You should compare the list of migrations between the old image and your current one to see which ones you need to run.

How to run them depends on your Docker setup. Do you use Docker compose?

But in general it’s something along the lines of: PYTHONPATH=. python migrations/<migration filename> from /opt/redash/current for each migration file.

And once this process will be automated, we will make Docker the recommended deployment method and have it run on container startup…

尝试手动更新并未成功，最后放弃；选择重新部署新版本的redash实例。

部署

Redash 稳定版本为 2.0，并未实现对 LDAP 的支持。From Github#633，LDAP特性的支持在 2017-08-10 进行了Merge，因此考虑安装最新版本的Redash v3.0.0+b。

参考：https://github.com/getredash/redash/pull/1836

构建Docker镜像，Dockerfile：

FROM redash/redash:3.0.0.b2998
 
RUN pip install ldap3

docker build -t redash:redash2_ldap .

采用docker-compose启动，docker-compose.yml：

# This is an example configuration for Docker Compose. Make sure to atleast update
# the cookie secret & postgres database password.
#
# Some other recommendations:
# 1. To persist Postgres data, assign it a volume host location.
# 2. Split the worker service to adhoc workers and scheduled queries workers.
version: '2'
services:
  server:
    image: redash:redash2_ldap
    command: server
    ports:
      - "25001:5000"
    environment:
      PYTHONUNBUFFERED: 0
      REDASH_LOG_LEVEL: "INFO"
      REDASH_REDIS_URL: "redis://172.31.0.1:6379/0"
      REDASH_DATABASE_URL: "postgresql://redash:password@172.31.0.1/redash2"
      REDASH_CELERY_BROKER: "amqp://rabbit:password@172.31.0.1/redash2"
      REDASH_CELERY_BACKEND: "redis://172.31.0.1:6379/0"
      REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1
      REDASH_JOB_EXPIRY_TIME: 3600
      REDASH_HOST: "http://redash.chenjiehua.me/"
      REDASH_DATE_FORMAT: "YYYY-MM-DD"
      REDASH_STATSD_HOST: "172.31.0.1"
      REDASH_COOKIE_SECRET: "cookie_secret"
      REDASH_MAIL_SERVER: "172.31.0.1"
      REDASH_MAIL_DEFAULT_SENDER: "redash@chenjiehua.me"
      REDASH_WEB_WORKERS: 4
      REDASH_PASSWORD_LOGIN_ENABLED: "false"
      REDASH_LDAP_CUSTOM_USERNAME_PROMPT: "LDAP Username"
      REDASH_LDAP_LOGIN_ENABLED: "true"
      REDASH_LDAP_URL: "ldaps://172.31.0.1:636"
      REDASH_LDAP_BIND_DN: "uid=admin,ou=system"
      REDASH_LDAP_BIND_DN_PASSWORD: "ladp_bind_password"
      REDASH_LDAP_DISPLAY_NAME_KEY: "uid"
      REDASH_LDAP_EMAIL_KEY: "mail"
      REDASH_LDAP_SEARCH_TEMPLATE: "(uid=%(username)s)"
      REDASH_SEARCH_DN: "ou=users,dc=test,dc=com"
 
  worker:
    image: redash:redash2_ldap
    command: scheduler
    environment:
      PYTHONUNBUFFERED: 0
      REDASH_LOG_LEVEL: "INFO"
      REDASH_REDIS_URL: "redis://172.31.0.1:6379/0"
      REDASH_DATABASE_URL: "postgresql://redash:password@172.31.0.1/redash2"
      REDASH_CELERY_BROKER: "amqp://rabbit:password@172.31.0.1/redash2"
      REDASH_CELERY_BACKEND: "redis://172.31.0.1:6379/0"
      REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1
      REDASH_JOB_EXPIRY_TIME: 3600
      REDASH_DATE_FORMAT: "YYYY-MM-DD"
      REDASH_STATSD_HOST: "172.31.0.1"
      REDASH_MAIL_SERVER: "172.31.0.1"
      QUEUES: "queries,scheduled_queries,celery"
      WORKERS_COUNT: 2

# 初始化数据库　
docker-compose -f docker-compose.yml run --rm server create_db
# 启动
docker-compose -f docker-compose.yml up

至此，在本地测试环境 Docker 17.06.2-ce + OpenLDAP 上测试完成，一切正常运行。

线上部署

线上部署时遇到的问题：LDAP服务器连接失败。

问题1：LDAP连接失败

• 采用 plain 模式连接LDAP服务器，配置为：REDASH_LDAP_URL: “ldap://172.31.0.1:389″；
• 采用 ssl 模式连接LDAP服务器，配置为：REDASH_LDAP_URL: “ldaps://172.31.0.1:636″；

报错：

[2017-09-18 06:59:08,823][PID:17][ERROR][redash] Exception on /ldap/login [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask_restful/__init__.py", line 271, in error_router
    return original_handler(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/app/redash/authentication/ldap_auth.py", line 36, in login
    user = auth_ldap_user(request.form['email'], request.form['password'])
  File "/app/redash/authentication/ldap_auth.py", line 54, in auth_ldap_user
    conn = Connection(server, settings.LDAP_BIND_DN, password=settings.LDAP_BIND_DN_PASSWORD, authentication=SIMPLE, auto_bind=True)
  File "/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py", line 326, in __init__
    self.bind(read_server_info=True)
  File "/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py", line 530, in bind
    response = self.post_send_single_response(self.send('bindRequest', request, controls))
  File "/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/sync.py", line 124, in post_send_single_response
    responses, result = self.get_response(message_id)
  File "/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/base.py", line 342, in get_response
    raise LDAPSessionTerminatedByServerError(self.connection.last_error)
LDAPSessionTerminatedByServerError: session terminated by server

解决方案：对 ldap3 依赖的库 pyasn1 进行降版本（别问我是怎么找到这个解决方案的……）

ldap3 的 requirements.txt 中 pyasn1>=0.1.8，默认安装最新版的 pyasn1==0.3.5，与服务器的LDAP不兼容导致（我猜的 ……）

修改Dockerfile：

FROM redash/redash:3.0.0.b2998
 
RUN pip install pyasn1==0.1.9 ldap3

故障重现：

#!/usr/bin/env python
 
from ldap3 import Server, Connection, SIMPLE
 
def main():
    host = "ldap://172.31.0.1:389"
    bind_dn = "uid=admin,ou=system"
    password = "ladp_bind_password"
    search = "ou=users,dc=test,dc=com"
    template = "(uid=%(username)s)"
    name_key = "uid"
    email_key = "mail"
    username = "chenjiehua"
    server = Server(host)
    print server
    conn = Connection(server, bind_dn, password=password, authentication=SIMPLE, auto_bind=True)
    print conn
    conn.search(search, template % {"username": username}, attributes=[name_key, email_key])
    print conn.entries
 
 
if __name__ == "__main__":
    main()

location /ldap_auth/login {
    rewrite ^/ldap_auth/login$ /ldap/login last;   
}

REDASH_PASSWORD_LOGIN_ENABLED: "false"
REDASH_LDAP_LOGIN_ENABLED: "true"