How to allow remote access to databases

Contents

• MySQL/MariaDB
  • Current - v15.0 and later
  • Older releases (no longer supported) - v14.x and earlier
• PostgeSQL

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