Password security for memSQL (Docker)

docker
nginx
memsql
linux
Tags: #<Tag:0x00007f389dff6680> #<Tag:0x00007f389dff6540> #<Tag:0x00007f389dff6400> #<Tag:0x00007f389dff6270>

#1

A simple solution for simple times

memSQL is a fast DBMS, but when you are a master of convenience aeh… efficiency… like /me you tend to keep it simple. I set it up via Docker and found that this way it lacks password security. Let’s change this.

Port bindings for Docker and a DB password

The following command is from the official readme. It’s changed to bind the administration interface and performance monitor to 127.0.0.1 - so that it’s not reachable externally.

docker run -d -p 3306:3306 -p 127.0.0.1:9000:9000 --name=memsql memsql/quickstart

I keep port 3306 bound to the external interface so that I can login from Tableau for example.

Check the listeners:

netstat -tulpen
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       User       Inode       PID/Program name
tcp        0      0 127.0.0.1:9000          0.0.0.0:*               LISTEN      0          26696       2585/docker-proxy

I need a login. To do this, you need to get into the Docker container and run the command:

docker ps -a
CONTAINER ID        IMAGE                            COMMAND                  CREATED             STATUS                    PORTS                                              NAMES
10a0d5446a30        sequenceiq/hadoop-docker:2.7.1   "/etc/bootstrap.sh -b"   21 hours ago        Exited (0) 21 hours ago                                                      pedantic_hawking
62a675457d5c        memsql/quickstart                "/memsql-entrypoint.s"   25 hours ago        Up 25 hours               0.0.0.0:3306->3306/tcp, 127.0.0.1:9000->9000/tcp   memsql

Now we have our container id. You see that I also ran Hadoop in a docker container… efficiently… You can also see the issued port forwards. These happen via iptables on Linux.

docker exec -i -t 62a675457d5c /bin/bash
memsql-ops memsql-list -q | xargs -n 1 memsql-ops memsql-update-root-password --no-confirmation -p

And the iptables:

iptables -L
...
Chain DOCKER (1 references)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             172.17.0.2           tcp dpt:9000
ACCEPT     tcp  --  anywhere             172.17.0.2           tcp dpt:mysql

But the memSQL OPS web UI is practical. I want it. With authentication.

memSQL OPS password

I chose Nginx as a reverse proxy server, because it’s able to

  • to pass Websockt traffic to the configured backend
  • to provide Basic Auth, also via LDAP / AD
  • easy to configure and common standard

Here is my config:

server {
    listen 1.2.3.4:80;
    server_name  memsql.internal;
    location / {
        root /var/www;
        proxy_set_header X-Forwarded-Host $host:$server_port;
        proxy_set_header X-Forwarded-Server $host;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

        auth_basic "Restricted";
        auth_basic_user_file /etc/nginx/.htpasswd;

        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_pass http://127.0.0.1:9000/;
    }
}

You see that I proxify Websockt traffic in the last segment. You can also:

  • enable SSL for the Login
  • use LDAP / AD

Results

We set a password for the memSQL DB and Web UI, and keep using the Docker container. We can redeploy the Docker container whenever we like, by using the correct port parameters. It’s convenient and secure enough, given that we also setup SSL when this becomes a production system.