Mariadb:10.4.8 can not allow remote root access

[BruceZu]

I use docker compose to deploy it

version: "3.7"

services:
  portal:
    image: registry.my.com/portal/portal:latest
    restart: always
    ports:
      - "8444:8444"
    environment:
      IS_IN_VM: "true"
    networks:
      app_net:
        ipv4_address: 169.254.255.50
    logging:
      driver: "json-file"
      options:
        max-size: "200k"
        max-file: "10"
    depends_on:
      - db

  db:
    image: registry.my.com/portal/db:latest
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "root"
    volumes:
      - "db_data:/var/lib/mysql"
    networks:
      app_net:
        aliases:
          - mysql
        ipv4_address: 169.254.255.51
networks:
  app_net:
    ipam:
      driver: default
      config:
        - subnet: "169.254.255.48/28"
volumes:
  db_data:
    driver: local
    driver_opts:
      type: "none"
      o: "bind"
      device: "/var/docker/project/db_data"

I deploy it in a VMware VM the portal service need wait the db ready before with

function wait_mysql_up() {
    local mysql_service=mysql
    local mysql_port=3306
    local check_command="mysqladmin status -h${mysql_service} -P${mysql_port} -proot -uroot"
    eval "$check_command"
    while [ $? -ne 0 ]; do
        echo "MariaDB is not up, wait 3s and try again"
        sleep 3s
        eval "$check_command"
    done
}

portal failed to connect the mariadb I add some update to enable it works as /etc/mysql/mariadb.cnf

[mysqld]
skip-networking = 0
skip-bind-address

and

sql>  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'root' WITH GRANT OPTION;
sql>   FLUSH PRIVILEGES;

My question : should not allowing remote access be by default? Is there a related version with this feature? Or any suggest to carry out above 2 SQL statement in a customized Dockerfile? Thank you!

[yosifkit]

We do a local-only server for the initial creation of the database and other initialization and then enable remote connection after (at least, we don't disable it). This sounds like a duplicate of #261 and #262.

See docker-entrypoint.sh: line 105 is where it is started with skip-networking and specific socket, stopped on 188, and will then start on 199.

[wglambert]

By default root is remotely accessible, this is modified with MYSQL_ROOT_HOST https://github.com/docker-library/mysql/issues/231 https://github.com/docker-library/mariadb/pull/102

$ docker run -d --rm --name mariadb -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_USER=user -e MYSQL_PASSWORD=pass mariadb:10.1 
8f7b2f988982adf15d612c6ccb7f8b294c4df000a913c63731aa6bae179e06a4

$ grep ready <(docker logs -f mariadb 2>&1)    
2019-10-30 20:21:45 140673007413248 [Note] mysqld: ready for connections.
2019-10-30 20:21:49 139857968515072 [Note] mysqld: ready for connections.
^C

$ docker exec -it mariadb mysql -uroot -proot   
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.41-MariaDB-1~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants for root;
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

https://github.com/docker-library/mysql/issues/275#issuecomment-403622135

With MYSQL_ROOT_PASSWORD=localhost

$ docker run -d --rm --name mariadb -p 3306:3306 -e MYSQL_ROOT_HOST=localhost -e MYSQL_ROOT_PASSWORD=root -e MYSQL_USER=user -e MYSQL_PASSWORD=pass mariadb:10.1
99abe09ea4081ad1f056ca44f4072133f7a159b754cfb4f4d43aeba5297571c5

$ grep ready <(docker logs -f mariadb 2>&1)   
2019-10-30 20:23:38 140544164243456 [Note] mysqld: ready for connections.
2019-10-30 20:23:45 140414312626176 [Note] mysqld: ready for connections.
^C

$ docker exec -it mariadb mysql -uroot -proot                                                                                             
Welcome to the MariaDB monitor.  Commands end with ; or \g.                                                                                                   
Your MariaDB connection id is 2                                                                                                                               
Server version: 10.1.41-MariaDB-1~bionic mariadb.org binary distribution                                                                                      

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants for root;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for user;
+-----------------------------------------------------------------------------------------------------+                                                       
| Grants for user@%                                                                                   |                                                       
+-----------------------------------------------------------------------------------------------------+                                                       
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7' |                                                       
+-----------------------------------------------------------------------------------------------------+                                                       
1 row in set (0.00 sec)

[BruceZu]

@yosifkit Thank you! I read through the docker-entrypoint.sh today and figure out the way to accept remote access by root@host only need add one more environment in the docker-compose.yml

    environment:
      MYSQL_ROOT_PASSWORD: "root"
      MYSQL_ROOT_HOST: "169.254.255.50"

It works well. Verified in local and in VMWare VM. Also thanks a lot to @wglambert for your details reference. Bruce