How to configure cPanel MySQL/MariaDB or PostgreSQL for remote connections?

MySQL/MariaDB

It is very simple to set up remote connections in MySQL/MariaDB thanks to the WHM and cPanel interfaces.

You will need to known advance what IP/hostname, or IPs/hostnames, require database access because you will need to specify them in the panel. If your IP is dynamic, you may be able to inquire your ISP about the CDIR range that your IPs are assigned from, and then whitelist that instead. You must use % as a wildcard character if you require the ability to allow connections for all IPs or for a particular subset (you can not use the CDIR notation for a subset of IPs but must use the ‘%’ wildcard notation instead).

You would first log into WHM as the root user using the following URL first (make sure to replace yourHostName.tld with your actual server’s hostname or IP address):

https://yourHostName.tld/whm

Navigate to the “SQL Services” section and click on “Additional MySQL Access Hosts”.

You can enter the hostnames or IP addresses of the allowed hosts here. You may use a percent sign as a wildcard, so you could add ‘192.168.%’ to this list to allow all IPs that begin with ‘192.168’. Click “Save” when you have added the IP or fully qualified hostname. The following image shows how to permit remote connections for all IPs (not recommended due to security concerns and shown for demonstration purposes only):

You will notice at the bottom of this screenshot, the following is stated:

So, we can either grant the remote location access to all cPanel account’s databases, or we must then navigate to the cPanel for the cPanel user that requires remote access and allow access to only those databases.

To add remote access for all cPanel accounts, simply click “click here” in the statement at the bottom of the “Additional MySQL Access Hosts” page as shown below:

After clicking the ‘click here’ option to update the remote access mysql for all cPanel accounts, you will see a confirmation of the users that were updated. On this demonstration server, only two users exist, so only two users were updated:

If we only want to allow access for one cPanel user’s databases, then we must log into the cPanel account for that user and add the IP(s)/FQDN(s) there. We can redirect to the cPanel from within WHM here:

Home >> Account Information >> List Accounts

Locate the cPanel account you need to enter in the table and click the CP icon for that user. This will open a new window for that cPanel account.

Once inside the user’s cPanel, navigate as follows:

  cPanel >> Home >> Databases >> Remote MySQL

This will open the following interface, which shows where to add a new remote MySQL host, and then beneath lists the current hosts that exist already (there may already be one listed and it will be the server’s main IP):

You will simply enter the same IP(s)/FQDN(s) here that you entered in WHM’s “Additional MySQL Access Hosts”, and then click ” Add Host”. Now, this cPanel account can access MySQL on this server from a remote location!

Postgres

While cPanel offers an interface for configuring MySQL for remote connections, it offers no such interface to accomplish the same with Postgresql.

PostgreSQL is a powerful, open source object-relational database system that is known for its ability to scale well. It is configured to listed only on the localhost by default, however, you can simply edit 2 files to specify a remote IP that you’d also like it to listen for connections from. Here are the default settings that are permitting it to only listen on localhost:

We will need to edit these in addition to specifying the remote IP to allow access for in another file.

First, you need to log into the server as the root user via SSH (remember that Knownhost host uses a non-default port, 2200):

  ssh root@<IP> -p2200

Next edit the pg_hba.conf file:

  nano /var/lib/pgsql/data/pg_hba.conf

Then add the following to the bottom of the file:

  host all all IP.Add.re.ss/32 md5

Replace IP.Add.re.ss with the remote IP that you want to allow. Also, please do not forget to add the CIDR value (block /32 specifies only a single IP). This CIDR notation is useful when you have a dynamic IP and know the exact range from which IPs are assigned to you. You can add your CIDR here so that those IPs within that specified range are allowed, thus being allowed the remote connection despite the constant reassignment of IPs to you.

You specify md5 if you only want that host to be able to log in using an MD5 encrypted password. Replacing ‘md5’ with ‘trust’ allows them to connect unconditionally without a password. You may not be able to connect using md5 without having first set up password authentication and a database user, which can be done in cPanel » Home » Databases » PostgreSQL Databases OR in cPanel » Home » Databases » PostgreSQL Database Wizard. The PostgreSQL Database Wizard cPanel inteface will walk you through creating and configuring your Postgres database and user.

Once you are comfortable with this, you can use the cPanel interface Home » Databases » PostgreSQL Databases to do this instead. This interface contains all functionality on one page and may be quicker to use.

You can specify only a single database or a single user that you’d wish to allow remote access for as well. The overall syntax of this entry should be as follows:

  host <database name> <database user> <remote access IP address /32> md5

So, if you wanted database user ‘alice_whiterabbit’ to be the only user allowed remote access, and then only allow here access to her database ‘alice_wonderland’ from her IP 123.45.67.89 via md5 password authentication, you would use the following:

    host alice_whiterabbit alice_wonderland 123.45.67.89/32 md5

The next file that must be edited is the postgresql.conf file.

  nano /var/lib/pgsql/data/postgresql.conf

Change the line listen_addresses= ‘localhost’ to the following. You can edit it so that Postgresql accepts connection from any IP address by using a wildcard (*), or you can use comma-separated list of addresses that you want to allow the connections from. I’d recommend keeping access restricted and only allowing certain IPs access. The following example allows remote connections from all IPs:

  listen_addresses= '*'

If these edits were being made for alice_whiterabbit, we would append her IP to the default list of addresses that Postgres is already listening on like so:

  listen_addresses='127.0.0.200, localhost,123.45.67.89'

Make sure to open the port if it is not already open. If a ‘#’ character is at the beginning of the line, remove the ‘#’ character to enable the port specification:

  port = 5432

Restart the PostgreSQL service with the following command:

  service postgresql restart

Also, you need to add the PostgreSQL port 5432 on your firewall to allow the connections. You’ll do this by editing the firewall configuration:

  nano /etc/csf/csf.conf

You will append the port number ‘5432’ to the TCP_IN and TCP_OUT settings. Then, restart the firewall:

  csf -ra

Lastly, you will want to ensure that your firewall doesn’t start sending false positive alerts about the postgresql process. This is set by default on Knownhost servers, but you may want to confirm. If so, you can search for the entry in the file, and if present, then PG is already ignored. If not, then you will want to add it. Use the following command to see if it is already present in the file:

  grep postgres /etc/csf/csf.pignore

If you don’t get any output from that command, then you will need to add the entry. The image below depicts an ignored Postgres executable:

  nano /etc/csf/csf.pignore

Add the following to the bottom of the file:

  exe:/usr/bin/postgres

Restart the firewall:

  csf -ra

Now, you can test your connection from the remote IP!

Restricting Remote Access Further Via the Firewall

You can further secure your remote connections via advanced CSF/LFD firewall filtering if so desired. You could limit access via the port to that one IP by adding a custom rule like the following to the /etc/csf/csf.allow file for MySQL (replace XXX.XX.XX.XXX with the IP that you want to allow remote access for):

  tcp|in|d=3306|s=XXX.XX.XX.XXX

And you would add the following to accomplish this for Postgres:

  tcp|in|d=5432|s=XXX.XX.XX.XXX