KNOWNHOST WIKI

User Tools

Site Tools


developmental:configure-mysql-mariadb-postgresql-remote-connections

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
developmental:configure-mysql-mariadb-postgresql-remote-connections [2019/10/11 07:10]
Karson N.
developmental:configure-mysql-mariadb-postgresql-remote-connections [2020/06/11 07:50] (current)
Karson N.
Line 1: Line 1:
 ====== How to Configure MySQL/MariaDB and Postgres to Listen For Remote Connections ====== ====== How to Configure MySQL/MariaDB and Postgres to Listen For Remote Connections ======
  
----- +\\
 ===== MySQL/MariaDB ===== ===== MySQL/MariaDB =====
  
Line 13: Line 12:
  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):  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+[[https://yourHostName.tld/whm|https://yourHostName.tld/whm]]
  
 Navigate to the "SQL Services" section and click on "Additional MySQL Access Hosts". Navigate to the "SQL Services" section and click on "Additional MySQL Access Hosts".
- 
  
 {{:developmental:accesshosts.png?nolink&1200|}} {{:developmental:accesshosts.png?nolink&1200|}}
- 
  
 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 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):
Line 40: Line 37:
  
 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: 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:
 +<code>
   Home »Account Information »List Accounts   Home »Account Information »List Accounts
 +</code>
  
 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.  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. 
Line 48: Line 46:
  
 Once inside the user's cPanel, navigate as follows: Once inside the user's cPanel, navigate as follows:
 +<code>
   cPanel >> Home >> Databases >> Remote MySQL   cPanel >> Home >> Databases >> Remote MySQL
 +</code>
  
 {{:developmental:cpanel-remote-mysql.png?nolink&800|}} {{:developmental:cpanel-remote-mysql.png?nolink&800|}}
Line 56: Line 55:
  
 {{:developmental:remotemysqlcpanel.png?nolink&1200|}} {{:developmental:remotemysqlcpanel.png?nolink&1200|}}
- 
  
 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! ;-) 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 ===== ===== Postgres =====
----- 
- 
  
 While cPanel offers an interface for configuring MySQL for remote connections, it offers no such interface to accomplish the same with Postgresql.  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:+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:
  
 {{:developmental:postrgres-default-conf.png?nolink&1000|}} {{:developmental:postrgres-default-conf.png?nolink&1000|}}
Line 74: Line 70:
  
 First, you need to log into the server as the root user via SSH (remember that Knownhost host uses a non-default port, 2200): First, you need to log into the server as the root user via SSH (remember that Knownhost host uses a non-default port, 2200):
 +<code>
 +  ssh root@<IP> -p2200
 +</code>
  
-  ssh root@<IP> -p2200 
-   
 Next edit the pg_hba.conf file: Next edit the pg_hba.conf file:
-  +<code>
   nano /var/lib/pgsql/data/pg_hba.conf   nano /var/lib/pgsql/data/pg_hba.conf
- +</code> 
 Then add the following to the bottom of the file: Then add the following to the bottom of the file:
 +<code>
   host all all IP.Add.re.ss/32 md5   host all all IP.Add.re.ss/32 md5
 +</code>
  
 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.  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. 
Line 94: Line 93:
  
 {{:developmental:cpanelinterfacepostgres.png?nolink&1200|}} {{:developmental:cpanelinterfacepostgres.png?nolink&1200|}}
- 
  
 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: 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:
 +<code>
   host <database name> <database user> <remote access IP address /32> md5   host <database name> <database user> <remote access IP address /32> md5
 +</code>
  
 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: 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:
 +<code>
     host alice_whiterabbit alice_wonderland 123.45.67.89/32 md5     host alice_whiterabbit alice_wonderland 123.45.67.89/32 md5
 +</code>
  
 The next file that must be edited is the postgresql.conf file.  The next file that must be edited is the postgresql.conf file. 
- +<code> 
-    nano /var/lib/pgsql/data/postgresql.conf+  nano /var/lib/pgsql/data/postgresql.conf 
 +</code>
  
 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: 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:
- +<code>
   listen_addresses= '*'   listen_addresses= '*'
 +</code>
  
 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: 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:
 +<code>
   listen_addresses='127.0.0.200, localhost,123.45.67.89'   listen_addresses='127.0.0.200, localhost,123.45.67.89'
 +</code>
  
 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: 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:
 +<code>
   port = 5432   port = 5432
 +</code>
  
 Restart the PostgreSQL service with the following command: Restart the PostgreSQL service with the following command:
 +<code>
   service postgresql restart   service postgresql restart
 +</code>
  
 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: 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:
 +<code>
   nano /etc/csf/csf.conf   nano /etc/csf/csf.conf
 +</code>
  
 You will append the port number '5432' to the TCP_IN and TCP_OUT settings. Then, restart the firewall: You will append the port number '5432' to the TCP_IN and TCP_OUT settings. Then, restart the firewall:
 +<code>
 +  csf -ra
 +</code>
  
-  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: 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:
 +<code>
   grep postgres /etc/csf/csf.pignore   grep postgres /etc/csf/csf.pignore
 +</code>
  
 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: 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:
  
 {{:developmental:postgrespignore.png?nolink&800|}} {{:developmental:postgrespignore.png?nolink&800|}}
 +<code>
   nano /etc/csf/csf.pignore   nano /etc/csf/csf.pignore
 +</code>
  
 Add the following to the bottom of the file: Add the following to the bottom of the file:
 +<code>
   exe:/usr/bin/postgres   exe:/usr/bin/postgres
 +</code>
  
 Restart the firewall: Restart the firewall:
 +<code>
   csf -ra   csf -ra
 +</code>
  
 Now, you can test your connection from the remote IP!  Now, you can test your connection from the remote IP! 
  
-----+\\
 ===== Restricting Remote Access Further Via the Firewall ===== ===== Restricting Remote Access Further Via the Firewall =====
----- 
- 
  
 You can further secure your remote connection 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): You can further secure your remote connection 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):
 +<code>
   tcp|in|d=3306|s=XXX.XX.XX.XXX   tcp|in|d=3306|s=XXX.XX.XX.XXX
 +</code>
  
 And you would add the following to accomplish this for Postgres: And you would add the following to accomplish this for Postgres:
 +<code>
   tcp|in|d=5432|s=XXX.XX.XX.XXX   tcp|in|d=5432|s=XXX.XX.XX.XXX
- +</code>
developmental/configure-mysql-mariadb-postgresql-remote-connections.1570795854.txt.gz · Last modified: 2019/10/11 07:10 by Karson N.