How to allow remote access to databases

Contents

 


 

MySQL/MariaDB

Configure MySQL/MariaDB to listen for remote connections:


For TurnKey v15.0 and later releases:

(Note: v15.x = Debian 9/Stretch - See below for v14.x and earlier)

sed -i "s/^bind-address/#bind-address/" /etc/mysql/mariadb.conf.d/50-server.cnf

Create 'new_user' (password: 'MyNewPassword') with permissions to connect to any MariaDB (drop in MySQL replacement) database on this server from any remote address (from any host ('%')):

NEW_USER=new_user
NEW_PASS='MyNewPassword'
MYSQL_BATCH="mysql --user=root --batch"
$MYSQL_BATCH --execute "CREATE USER $NEW_USER@'localhost' IDENTIFIED BY \"$NEW_PASS\";"
$MYSQL_BATCH --execute "CREATE USER $NEW_USER@'%' IDENTIFIED BY \"$NEW_PASS\";"
$MYSQL_BATCH --execute "GRANT ALL ON *.* TO $NEW_USER@'localhost';"
$MYSQL_BATCH --execute "GRANT ALL ON *.* TO $NEW_USER@'%';"

Restart MySQL/MariaDB for the changes to take effect:

service mysql restart

For TurnKey v14.x and earlier releases:

(Note: v14.x = Debian 8/Jessie)

sed -i "s/^bind-address/#bind-address/" /etc/mysql/my.cnf

Configure MySQL to accept remote root connections (from any host ('%')):
(added line breaks for readability)

MYSQL_BATCH="mysql --user=root --password=$MYSQL_PASS --batch"
$MYSQL_BATCH --execute "INSERT INTO mysql.user ( Host , User , Password , Select_priv ,
    Insert_priv , Update_priv , Delete_priv , Create_priv , Drop_priv , Reload_priv , 
    Shutdown_priv , Process_priv , File_priv , Grant_priv , References_priv , 
    Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , 
    Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , 
    Create_view_priv , Show_view_priv , Create_routine_priv , Alter_routine_priv , 
    Create_user_priv , ssl_type , max_questions , max_updates , max_connections , 
    max_user_connections) VALUES ( '%', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',
    'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y','Y', 'Y', 'Y', 'Y', 'Y',
    'Y', 'Y', 'Y', 'Y', '', '0', '0', '0', '0');"

Restart MySQL for the changes to take effect:

service mysql restart

PostgreSQL

Configure PostgreSQL to listen for remote connections:

CONF=/etc/postgresql/8.3/main/postgresql.conf
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" $CONF

Configure PostgreSQL to accept remote connections (from any host):

# configure postgres to accept remote connections
cat >> /etc/postgresql/8.3/main/pg_hba.conf <<EOF
# Accept all IPv4 connections - CHANGE THIS!!!
host    all         all         0.0.0.0/0             md5
EOF

Restart PostgreSQL for the changes to take effect:

service postgresql restart

 

Comments

Simon O's picture

Hi All,

I'm by no means an expert so please bear in mind this is simply my own experience

I found it far easier to run the sed command, then use the web interface to change the host for the user in question to % (aka all/any).

NB: If you do this via the command line, note that the block above after "(added line breaks for readability)" is actually 2 commands - I'm sure this is obvious to most readers but caught me out for a minute or 2

You can do this either through Webmin or phpMyAdmin)

I still had to restart MySQL (I assume for the binding to apply). The restart command listed above barfed on me...

 

~# /etc/init.d/mysql restart
Rather than invoking init scripts through /etc/init.d, use the service(8)
utility, e.g. service mysql restart

Since the script you are attempting to invoke has been converted to an
Upstart job, you may also use the restart(8) utility, e.g. restart mysql

so in future, I'll be using

restart mysql

Anyway hope this is of help to someone


Marco Ovalle's picture

 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
 
This error appears when Inserting sentece is executed.... Why?... 
L. Arnold's picture

Rather than allowing "any", what is the process for allowing, perhaps "2" external IP's and Localhost?  So far by just adding a "host" in webmin I am still not getting the allowance.

L. Arnold's picture

Rather than messing with the fundamental setup I took some advice out (here) and added 2 Host Files, and 1 new MySQL User.  I gave each pretty much fullrights within WEBMIN MySQL server.

I found I could then access the data with FileMaker via ODBC from one of the IP's where I could not before the setup.  Navicat also has this new option as well as the "no setup" option or the MySQL over SSH option.

I am inclined to recomend against stimply Opening up Access to "all" hosts.  If you assign access to your own Gatewa'ys IP address (if it is fixed) this should work.  With a floating IP ou can likely also get a host.name entry to work with a floating IP if you use TKL's Dynamic DNS or similar service.

Jeremy Davis's picture

However the current TKL (v13.0) is based on Debian Wheezy (aka Debian 7) so anything that applies there should apply to TKL. Google probably has some ideas...

If you find a good solution be great if you could post back as it will help others in the future no doubt! :)

Jeremy Davis's picture

TBH I haven't tested doing it with Webmin but it should be possible.

FWIW if you log in via SSH (using an SSH client like PuTTY if you are on Windows) then it should be as simple as copy pasting the above commands in and then is should all just work.

But if you would rather use Webmin, then when you are logged into Webmin, from the top bar select "Servers" >> "MySQL Database Server". You'll then need to use your MySQL root password to get access. Once you are in select "MySQL Server Configuration". Then next to "MySQL server listening address" select "All" (defaults is 127.0.0.1/localhost).

You'll probably also need to update the permissions for the user but I'm not sure how to do that from Webmin. I would suggest that Adminer would be better for that task (https on port 12322). Hopefully that's enough to get you going.

Also FWIW it's generally better to post in the forums (rather than comment on a doc page) as I only get one email notification when you post here. If I miss that and/or it gets buried in my inbox, then I'll possibly never see it. At least in the forums it shows up in the forum list.