Enabling Remote Access in the Webdock Dashboard

We have tried to make it relatively straightforward to create a database with remote access in Webdock. This approach may be considered insecure, please see note below.

The steps are as follows:

1. Run the "Enable Remote Mysql Access" script on your server (found in the Script Library)
    
2. Create a new database and dabase user which contains the word "remote" in the username. Such as "myremotedatabaseuser"

Please note: When using this method you are opening up port 3306 to the world and anyone who can guess your DB username and password may gain access. If you want to restrict users or your firewall port by e.g. IP - then you need to follow the manual setup method below.

To repeat: Any database user created in the Webdock dashboard with the word "remote" in the username will be granted remote access on % - meaning that user can connect from anywhere. If you have not run our enablement script in order to allow MariaDB to listen on all interfaces and your firewall port is still closed, then this has no security impact.

If you need to grant remote access to an existing database you will need to grant the appropriate privileges to a new or existing user in MySQL using the command line, or alternatively using phpmyadmin - please see instructions on how to achieve this on the command line in the instructions below.

Manual configuration using the command line

Prerequisites

• A fresh Webdock cloud Ubuntu instance with LEMP / LAMP installed.

• You have shell (SSH) access to your VPS.

Note: You can refer to the Webdock DNS Guide to manage the DNS records.

Verify MariaDB Server

Before starting, make sure the MariaDB server is running by using the following command:

$ ps -ef | grep -i mysql

You should get the following output:

mysql        595       1  0 04:17 ?        00:00:00 /usr/sbin/mysqld
root        1350    1337  0 04:22 pts/0    00:00:00 grep --color=auto -i mysql

By default, the MariaDB server is listening on localhost only for security reasons. You can check it with the following command:

$ netstat -ant | grep 3306

In the following output, you should see that the MariaDB server is listening on localhost (127.0.0.1):

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

Configure MariaDB

By default, MariaDB allows connection only from localhost, all connections from a remote server are denied by default.

The first thing you need to do is to configure the MariaDB server to listen to all IP addresses on the system.

You can do it by editing the MariaDB default configuration file. Look for "bind-address" directive in these two locations (make the change in whichever file you find that directive). You can open the file using your favorite text editor:

$ nano /etc/mysql/my.cnf

OR

$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Change the value of the bind-address from 127.0.0.1 to 0.0.0.0 so that MariaDB server accepts connections on all host IPv4 interfaces.

bind-address = 0.0.0.0

Save and close the file when you are finished. Then, restart the MariaDB service to apply the changes:

$ sudo systemctl restart mariadb

You can now verify the MariaDB listening status with the following command:

$ netstat -ant | grep 3306

If everything is fine, you should get the following output:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Now, the MariaDB server is set up to listen to all IP addresses.

Note : If your are running MySQL Server instead of MariaDB, the main configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf. You can change the bind-address by editing this file.

The remaining steps are all the same as for MariaDB.

Grant Access to a User from a Remote System

In this section, we will create a new database named wpdb and a user named wpuser, and grant access to the remote system to connect to the database wpdb as user wpuser.

First, log in to the MariaDB shell with the following command:

$ mysql -u admin -p

Provide your admin (root) password as shown in the Webdock backend and when you get the prompt create a database and user with the following command:

MariaDB [(none)]> CREATE DATABASE wpdb;
MariaDB [(none)]> CREATE USER  'wpuser'@'localhost' IDENTIFIED BY 'password';

Next, you will need to grant permissions to the remote system with IP address 208.117.84.50 to connect to the database named wpdb as user wpuser. You can do it with the following command:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

Next, flush the privileges and exit from the MariaDB shell with the following command:

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

A brief explanation of each parameter is shown below:

• wpdb: It is the name of the MariaDB database that the user wants to connect to.

• wpuser: It is the name of the MariaDB database user.

• 208.117.84.50: It is the IP address of the remote system from which the user wants to connect.

• password: It is the password of the database user.

If you want to grant remote access on all databases for wpuser, run the following command:

MariaDB [(none)]> GRANT ALL ON *.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all remote IP addresses on wpdb as wpuser, use % instead of IP address (208.117.84.50) as shown below:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all IP addresses in the subnet 208.117.84.0/24 on wpdb as user wpuser, run the following command:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Configure Firewall

If your MariaDB server is configured with the UFW firewall (which is by default on all Webdock servers) then you will need to allow traffic on port 3306 from the remote system.

You can grant access to the remote system with IP 208.117.84.50 to connect the port 3306 with the following command:

$ sudo ufw allow from 208.117.84.50 to any port 3306

If you want to grant access from any IP address you would use the following command:

$ sudo ufw allow 3306

Next, reload the firewall with the following command:

$ sudo ufw reload

Once you are finished, you can proceed to the next step.

Test Connection from Remote System

At this point, the MariaDB server is configured to allow connection from the remote system with IP address 208.117.84.50. Now, it's time to test the connection from the client system to the MariaDB server. Here we show how to do this on the command line in Linux, but you can also test this from your desktop (if not on Linux) by utilizing any MySQL remote manager such as the ones listed in the introduction to this article.

First, you will need to install the MariaDB Client package in the remote system. You can install it with the following command:

$ sudo apt-get install mariadb-client -y

Once the installation is completed, connect to the MariaDB server by running the following command on the remote system:

mysql -u wpuser -h 45.148.28.101 -p

You will be asked to provide the password of the wpuser as shown below:

Enter password:

Once the connection has been established, you should get the following output:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.4.10-MariaDB-1:10.4.10+maria~bionic-log 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.

You can now list the databases using the following command:

MariaDB [(none)]> show databases;

You should get the following output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| wpdb               |
+--------------------+
2 rows in set (0.10 sec)

Conclusion

In the above tutorial, we learned how to configure MariaDB for remote connections. We also learned how to grant access on a specific database or all databases to the specific IP address or all IP addresses. We hope this guide helps you to understand how to connect your web application to the database hosted on the remote server and how to secure it properly with IP restrictions.