Replicating a phpBB MariaDB Database to a FreeBSD Jail (Unix Socket with SSH Tunnel)

Discuss Wi-Fi setups, cybersecurity, and network troubleshooting.
User avatar
ccb056
Site Administrator
Posts: 1006
Joined: January 14th, 2004, 11:36 pm
Location: Texas

Replicating a phpBB MariaDB Database to a FreeBSD Jail (Unix Socket with SSH Tunnel)

Post by ccb056 »

Replicating a phpBB MariaDB Database to a FreeBSD Jail (Unix Socket with SSH Tunnel)

I set up MariaDB replication for my phpBB forum database, computerbb, from a socket-only MariaDB instance on the master server to a MariaDB replica running inside a hardened FreeBSD jail on the slave server.

The goal was to keep a live replicated copy of the phpBB database inside a FreeBSD jail without exposing MariaDB over a public TCP port.

Final design:

Code: Select all

master MariaDB socket
    -> SSH tunnel
    -> slave jail 127.0.0.1:3307
    -> MariaDB replica inside jaildb
The master remains socket-only. The slave connects through an SSH tunnel.

Environment

Master
  • MariaDB 10.6.x
  • Socket-only MariaDB
  • No public TCP listener
  • phpBB database: computerbb
  • Binary logs enabled
Slave
  • FreeBSD 15.x
  • VNET jail named jaildb
  • MariaDB 10.11.x
  • Replica database server
  • Persistent SSH tunnel using autossh

1. FreeBSD jail networking

The jail was created as a thick jail:

Code: Select all

bsdinstall jail /usr/local/jails/jaildb
The jail was configured as a VNET jail with its own network interface.

The slave host uses:

Code: Select all

bridge0
lagg0
epair interface for jaildb
The jail gets its own DHCP lease from the router.

Initially, I had lagg0 using load balancing, but DHCP replies for the jail were not coming back reliably. Switching lagg0 to failover fixed it.

Working rc.conf network section on the slave:

Code: Select all

ifconfig_em0="up"
ifconfig_igb0="up"

cloned_interfaces="lagg0 bridge0"

ifconfig_lagg0="laggproto failover laggport em0 laggport igb0 up"
ifconfig_bridge0="addm lagg0 SYNCDHCP"

kld_list="if_bridge if_epair"

jail_enable="YES"
jail_list="jaildb"
After restarting networking/rebooting, the host IP moved to bridge0 and lagg0 became a bridge member with no IP address.

Useful checks:

Code: Select all

ifconfig bridge0
ifconfig lagg0
netstat -rn -f inet
The jail was then started:

Code: Select all

service jail start jaildb
jls

2. jaildb configuration

The jail configuration file on the slave:

Code: Select all

/etc/jail.conf
The jaildb block:

Code: Select all

jaildb {
    host.hostname = "jaildb";
    path = "/usr/local/jails/jaildb";

    vnet;
    vnet.interface = "jaildb0";

    exec.clean;
    exec.system_user = "root";
    exec.jail_user = "root";

    exec.prestart = "ifconfig epair10 create";
    exec.prestart += "ifconfig epair10a descr jaildb-host";
    exec.prestart += "ifconfig epair10a up";
    exec.prestart += "ifconfig bridge0 addm epair10a";
    exec.prestart += "ifconfig epair10b name jaildb0";

    exec.start = "/bin/sh /etc/rc";
    exec.stop = "/bin/sh /etc/rc.shutdown";

    exec.poststop = "ifconfig bridge0 deletem epair10a";
    exec.poststop += "ifconfig epair10a destroy";

    mount.devfs;
    devfs_ruleset = 10;

    persist;

    allow.raw_sockets = 0;
    allow.set_hostname = 0;
    allow.sysvipc = 0;

    allow.mount = 0;
    allow.mount.devfs = 0;
    allow.mount.nullfs = 0;
    allow.mount.procfs = 0;
    allow.mount.tmpfs = 0;

    allow.chflags = 0;
    enforce_statfs = 2;
    children.max = 0;
}
Because DHCP runs inside the VNET jail, the jail needs access to bpf devices.

On the slave host:

Code: Select all

ee /etc/devfs.rules
Add:

Code: Select all

[devfsrules_jaildb=10]
add include $devfsrules_jail
add path 'bpf*' unhide
Restart devfs:

Code: Select all

service devfs restart
Inside the jail rc.conf:

Code: Select all

/usr/local/jails/jaildb/etc/rc.conf
Contents:

Code: Select all

hostname="jaildb"

ifconfig_jaildb0="SYNCDHCP"

sshd_enable="NO"
sendmail_enable="NONE"
syslogd_flags="-ss"
clear_tmp_enable="YES"

dumpdev="NO"
moused_nondefault_enable="NO"
After starting the jail:

Code: Select all

service jail start jaildb
jexec jaildb ifconfig jaildb0
The jail received a DHCP address.


3. Installing MariaDB inside the jail

Enter the jail:

Code: Select all

jexec jaildb /bin/sh
Bootstrap pkg if needed:

Code: Select all

pkg update
Install MariaDB:

Code: Select all

pkg install mariadb1011-server mariadb1011-client
Enable MariaDB:

Code: Select all

sysrc mysql_enable=YES
Create the replica config:

Code: Select all

mkdir -p /usr/local/etc/mysql/conf.d
ee /usr/local/etc/mysql/conf.d/replica.cnf
Contents:

Code: Select all

[mysqld]
server_id = 226
relay_log = jaildb-relay-bin
read_only = ON
skip_name_resolve = ON
# Only replicate the phpBB database.
replicate-wild-do-table=computerbb.%
Start MariaDB:

Code: Select all

service mysql-server start
service mysql-server status
Verify:

Code: Select all

mariadb -e "SHOW VARIABLES WHERE Variable_name IN ('server_id','read_only','relay_log');"
Expected:

Code: Select all

server_id = 226
read_only = ON
relay_log = jaildb-relay-bin

4. Enabling binary logs on the master

The master MariaDB instance was originally socket-only and started with binary logs disabled.

Original startup script included:

Code: Select all

--skip-log-bin
That prevents replication from working.

The minimal change was to remove/comment out skip-log-bin and add binary logging options.

Master startup script:

Code: Select all

#!/bin/bash
# start MariaDB
/usr/bin/mysqld_safe --skip-networking \
--socket=/home/$USER/.config/mysql/mysqld.sock \
--datadir=/home/$USER/.config/mysql/data \
--pid-file=/home/$USER/.config/mysql/mariadb.pid \
--log-error=/home/$USER/.config/mysql/mysqld.err \
--server-id=1 \
--log-bin=/home/$USER/.config/mysql/mariadb-bin \
--binlog-expire-logs-seconds=604800 \
--event-scheduler=ON \
--nowatch > /dev/null
This keeps MariaDB socket-only:

Code: Select all

--skip-networking
But enables binary logs:

Code: Select all

--log-bin=/home/$USER/.config/mysql/mariadb-bin
And keeps seven days of binlogs:

Code: Select all

--binlog-expire-logs-seconds=604800
Restart MariaDB on the master:

Code: Select all

mariadb-admin --socket="$HOME/.config/mysql/mysqld.sock" shutdown
~/.config/mysql/start
Verify:

Code: Select all

mariadb --socket="$HOME/.config/mysql/mysqld.sock" -e "
SHOW VARIABLES WHERE Variable_name IN ('log_bin','server_id','skip_networking','socket','binlog_expire_logs_seconds');
SHOW MASTER STATUS;
"
Expected important values:

Code: Select all

log_bin                    ON
server_id                  1
skip_networking            ON
binlog_expire_logs_seconds 604800
SHOW MASTER STATUS should return a binlog file and position, for example:

Code: Select all

mariadb-bin.000001    4062

5. Creating a replication user on the master

Because the master is socket-only, the replication connection arrives locally through the SSH tunnel. I created the replication user as localhost.

On the master:

Code: Select all

mariadb --socket="$HOME/.config/mysql/mysqld.sock"
Inside MariaDB:

Code: Select all

CREATE USER 'repl'@'localhost' IDENTIFIED BY 'REDACTED_PASSWORD';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';

FLUSH PRIVILEGES;
Verify:

Code: Select all

mariadb --socket="$HOME/.config/mysql/mysqld.sock" -e "SELECT User, Host, plugin FROM mysql.user;"

6. Creating an SSH key inside the jail

On the slave host, enter the jail:

Code: Select all

jexec jaildb /bin/sh
Create an SSH key inside the jail:

Code: Select all

ssh-keygen -t ed25519
I used the default path:

Code: Select all

/root/.ssh/id_ed25519
Display the public key:

Code: Select all

cat /root/.ssh/id_ed25519.pub
Then on the master, add that public key to:

Code: Select all

~/.ssh/authorized_keys
Make sure permissions are correct on the master:

Code: Select all

mkdir -p ~/.ssh
chmod 700 ~/.ssh
chmod 600 ~/.ssh/authorized_keys
Test from inside jaildb:

Code: Select all

ssh [email protected]
It should log in without asking for a password.


7. Testing a manual SSH socket tunnel

Inside jaildb, run:

Code: Select all

ssh -N \
  -L 127.0.0.1:3307:/home/USERNAME/.config/mysql/mysqld.sock \
  [email protected]
This appears to hang. That is normal.

The -N option means SSH opens the tunnel but does not run a shell.

Leave that command running.

Open another shell on the slave and enter the jail again:

Code: Select all

jexec jaildb /bin/sh
Test the tunnel:

Code: Select all

mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"
Expected result:

Code: Select all

10.6.x-MariaDB-log
That means this works:

Code: Select all

jaildb 127.0.0.1:3307
    -> SSH tunnel
    -> master MariaDB Unix socket

8. Running the SSH tunnel in the background

Once the manual test worked, I tested the same tunnel in the background:

Code: Select all

ssh -f -N \
  -L 127.0.0.1:3307:/home/USERNAME/.config/mysql/mysqld.sock \
  [email protected]
Check that something is listening on port 3307 in the jail:

Code: Select all

sockstat -4 -l | grep 3307
Test MariaDB again:

Code: Select all

mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"
To stop the background SSH tunnel manually:

Code: Select all

ps aux | grep '[s]sh.*3307'
pkill -f 'ssh.*3307'

9. Installing autossh for a persistent tunnel

A plain background SSH tunnel works, but if it dies, replication stops.

So I installed autossh inside jaildb:

Code: Select all

pkg install autossh
autossh monitors an SSH session and restarts it if it dies.

Create an rc.d service inside the jail:

Code: Select all

ee /usr/local/etc/rc.d/whatbox_db_tunnel
The service file:

Code: Select all

#!/bin/sh

# PROVIDE: whatbox_db_tunnel
# REQUIRE: NETWORKING
# KEYWORD: shutdown

. /etc/rc.subr

name="whatbox_db_tunnel"
rcvar="whatbox_db_tunnel_enable"

command="/usr/local/bin/autossh"

load_rc_config $name

: ${whatbox_db_tunnel_enable:="NO"}

command_args="-M 0 -f -N \
  -o ExitOnForwardFailure=yes \
  -o ServerAliveInterval=60 \
  -o ServerAliveCountMax=3 \
  -L 127.0.0.1:3307:/home/USERNAME/.config/mysql/mysqld.sock \
  [email protected]"

run_rc_command "$1"
Make it executable:

Code: Select all

chmod +x /usr/local/etc/rc.d/whatbox_db_tunnel
Enable it inside the jail:

Code: Select all

sysrc whatbox_db_tunnel_enable=YES
Start it:

Code: Select all

service whatbox_db_tunnel start
Check the tunnel:

Code: Select all

sockstat -4 -l | grep 3307
Test MariaDB through the tunnel:

Code: Select all

mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"
Restart test:

Code: Select all

service whatbox_db_tunnel restart
mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"
After restarting the jail, verify autossh starts automatically:

Code: Select all

service jail restart jaildb
jexec jaildb sockstat -4 -l | grep 3307
jexec jaildb mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"

10. Dumping the phpBB database from the master

The phpBB database is:

Code: Select all

computerbb
On the master:

Code: Select all

mariadb-dump \
  --socket="$HOME/.config/mysql/mysqld.sock" \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --databases computerbb \
  > computerbb-replication-seed.sql
Check the replication coordinates embedded in the dump:

Code: Select all

grep -m1 "CHANGE MASTER" computerbb-replication-seed.sql
Example:

Code: Select all

-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=21956084;

11. Copying and importing the dump on the slave

On the slave host:

Code: Select all

scp [email protected]:~/computerbb-replication-seed.sql /tmp/
Copy it into the jail:

Code: Select all

cp /tmp/computerbb-replication-seed.sql /usr/local/jails/jaildb/tmp/
Import the dump into the MariaDB instance inside jaildb:

Code: Select all

jexec jaildb mysql < /tmp/computerbb-replication-seed.sql
Verify the database exists:

Code: Select all

jexec jaildb mariadb -e "SHOW DATABASES;"
Verify the phpBB tables:

Code: Select all

jexec jaildb mariadb computerbb -e "SHOW TABLES;"

12. Starting MariaDB replication on the slave

Enter MariaDB inside the jail:

Code: Select all

jexec jaildb mariadb
Start replication using the coordinates from the dump:

Code: Select all

STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_PORT=3307,
  MASTER_USER='repl',
  MASTER_PASSWORD='REDACTED_PASSWORD',
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=21956084,
  MASTER_CONNECT_RETRY=10;

START SLAVE;

SHOW SLAVE STATUS\G
The important healthy values are:

Code: Select all

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:
Master_Server_Id: 1

13. Useful replication health checks

From the slave host:

Code: Select all

jexec jaildb mariadb -e "SHOW SLAVE STATUS\G"
Shorter health check:

Code: Select all

jexec jaildb mariadb -e "SHOW SLAVE STATUS\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_IO_Error|Last_SQL_Error'
Expected:

Code: Select all

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:
Check that the autossh tunnel is still listening:

Code: Select all

jexec jaildb sockstat -4 -l | grep 3307
Test the tunnel manually:

Code: Select all

jexec jaildb mysql -h 127.0.0.1 -P 3307 -u repl -p -e "SELECT VERSION();"

14. Optional replication test table

On the master:

Code: Select all

mariadb --socket="$HOME/.config/mysql/mysqld.sock" computerbb -e "
CREATE TABLE replication_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  msg VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO replication_test (msg) VALUES ('replication works');
"
On the slave:

Code: Select all

jexec jaildb mariadb computerbb -e "SELECT * FROM replication_test;"
If the row appears, replication is working end-to-end.

Clean it up from the master:

Code: Select all

mariadb --socket="$HOME/.config/mysql/mysqld.sock" computerbb -e "DROP TABLE replication_test;"
The DROP should replicate to the slave.


15. Current final state

The final replication status showed:

Code: Select all

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:
The final design:

Code: Select all

computerbb on master MariaDB
    -> master binary logs
    -> SSH socket tunnel
    -> slave jail port 127.0.0.1:3307
    -> MariaDB replica inside jaildb
The master remains socket-only and does not expose MariaDB over TCP.

The slave has a live replicated copy of the phpBB database inside a FreeBSD jail.
Top
User avatar
ccb056
Site Administrator
Posts: 1006
Joined: January 14th, 2004, 11:36 pm
Location: Texas

Setting up phpMyAdmin in a Separate FreeBSD Thick Jail for Read-Only Access to a MariaDB Replica

Post by ccb056 »

Setting up phpMyAdmin in a Separate FreeBSD Thick Jail for Read-Only Access to a MariaDB Replica

I set up a second FreeBSD thick jail named jailwww to run Nginx, PHP-FPM, and phpMyAdmin. This jail connects to my existing MariaDB replica jail, jaildb, over the jail LAN using DNS/DHCP hostnames.

The goal was to keep database administration separated from the database jail itself, while allowing phpMyAdmin to browse the replicated phpBB database in read-only mode.

Final design:

Code: Select all

browser
    -> jailwww nginx/php-fpm/phpMyAdmin
    -> jaildb:3306
    -> MariaDB replica database computerbb
The master MariaDB server remains socket-only and is not exposed over TCP. The replica inside jaildb listens on TCP only so jailwww can connect to it.

Environment

Existing database jail
  • Jail name: jaildb
  • MariaDB replica
  • Database: computerbb
  • Replica is read-only
  • VNET jail using DHCP
  • Hostname resolves as jaildb
New web/phpMyAdmin jail
  • Jail name: jailwww
  • Nginx
  • PHP 8.4
  • PHP-FPM
  • phpMyAdmin 5.x
  • VNET jail using DHCP
  • Hostname resolves as jailwww
1. Create the thick jail

On the FreeBSD host:

Code: Select all

bsdinstall jail /usr/local/jails/jailwww
Copy DNS configuration into the jail:

Code: Select all

cp /etc/resolv.conf /usr/local/jails/jailwww/etc/resolv.conf
2. jailwww configuration

Edit the host jail configuration:

Code: Select all

ee /etc/jail.conf
Add a block for jailwww. My existing database jail uses epair10, so this jail uses epair11.

Code: Select all

jailwww {
    host.hostname = "jailwww";
    path = "/usr/local/jails/jailwww";

    vnet;
    vnet.interface = "jailwww0";

    exec.clean;
    exec.system_user = "root";
    exec.jail_user = "root";

    exec.prestart = "ifconfig epair11 create";
    exec.prestart += "ifconfig epair11a descr jailwww-host";
    exec.prestart += "ifconfig epair11a up";
    exec.prestart += "ifconfig bridge0 addm epair11a";
    exec.prestart += "ifconfig epair11b name jailwww0";

    exec.start = "/bin/sh /etc/rc";
    exec.stop = "/bin/sh /etc/rc.shutdown";

    exec.poststop = "ifconfig bridge0 deletem epair11a";
    exec.poststop += "ifconfig epair11a destroy";

    mount.devfs;
    devfs_ruleset = 11;

    persist;

    allow.raw_sockets = 0;
    allow.set_hostname = 0;
    allow.sysvipc = 0;

    allow.mount = 0;
    allow.mount.devfs = 0;
    allow.mount.nullfs = 0;
    allow.mount.procfs = 0;
    allow.mount.tmpfs = 0;

    allow.chflags = 0;
    enforce_statfs = 2;
    children.max = 0;
}
3. devfs rules for DHCP inside the jail

Because DHCP runs inside the VNET jail, the jail needs access to bpf devices.

Edit:

Code: Select all

ee /etc/devfs.rules
Add:

Code: Select all

[devfsrules_jailwww=11]
add include $devfsrules_jail
add path 'bpf*' unhide
Restart devfs:

Code: Select all

service devfs restart
4. Configure rc.conf inside jailwww

Edit:

Code: Select all

ee /usr/local/jails/jailwww/etc/rc.conf
Contents:

Code: Select all

hostname="jailwww"

ifconfig_jailwww0="SYNCDHCP"

sshd_enable="NO"
sendmail_enable="NONE"
syslogd_flags="-ss"
clear_tmp_enable="YES"

dumpdev="NO"
moused_nondefault_enable="NO"
Add jailwww to the host jail list:

Code: Select all

sysrc jail_list+=" jailwww"
Start the jail:

Code: Select all

service jail start jailwww
Verify it received a DHCP address:

Code: Select all

jexec jailwww ifconfig jailwww0
Verify hostname resolution works:

Code: Select all

jexec jailwww host jaildb
jexec jaildb host jailwww
In my case, both jails could ping each other by hostname.

5. Install Nginx, PHP, PHP-FPM, and phpMyAdmin

Enter the jail:

Code: Select all

jexec jailwww /bin/sh
Update packages:

Code: Select all

pkg update
pkg upgrade
Check available PHP branches:

Code: Select all

pkg search '^php[0-9]+-[0-9]'
I used PHP 8.4:

Code: Select all

pkg install nginx php84 php84-extensions php84-mysqli php84-mbstring php84-session php84-xml php84-zip php84-curl php84-filter php84-ctype php84-gd php84-intl php84-opcache
Note: I did not install php84-openssl because OpenSSL support was already built into the main PHP package.

Verify PHP modules:

Code: Select all

php -m | egrep -i 'mysqli|mbstring|session|xml|zip|curl|filter|ctype|gd|intl|opcache|openssl'
Install phpMyAdmin 5 for PHP 8.4:

Code: Select all

pkg search -i phpmyadmin
pkg install phpMyAdmin5-php84
phpMyAdmin was installed here:

Code: Select all

/usr/local/www/phpMyAdmin
Enable services:

Code: Select all

sysrc nginx_enable=YES
sysrc php_fpm_enable=YES
6. Configure PHP

Create the production PHP config:

Code: Select all

cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini
Edit:

Code: Select all

ee /usr/local/etc/php.ini
Recommended values:

Code: Select all

cgi.fix_pathinfo=0
date.timezone=America/Chicago
memory_limit=256M
upload_max_filesize=64M
post_max_size=64M
session.cookie_httponly=1
If using HTTPS, also consider:

Code: Select all

session.cookie_secure=1
For plain HTTP testing, leave session.cookie_secure off.

7. Configure PHP-FPM

Edit:

Code: Select all

ee /usr/local/etc/php-fpm.d/www.conf
Important settings:

Code: Select all

user = www
group = www

listen = /var/run/php-fpm.sock
listen.owner = www
listen.group = www
listen.mode = 0660

security.limit_extensions = .php
Start PHP-FPM:

Code: Select all

service php_fpm start
service php_fpm status
Verify the socket:

Code: Select all

sockstat | grep php-fpm
Expected:

Code: Select all

/var/run/php-fpm.sock
8. Configure Nginx

Back up the default config:

Code: Select all

cp /usr/local/etc/nginx/nginx.conf /usr/local/etc/nginx/nginx.conf.default
Edit:

Code: Select all

ee /usr/local/etc/nginx/nginx.conf
Use this minimal phpMyAdmin config:

Code: Select all

worker_processes  1;

events {
    worker_connections  1024;
}

http {
    include       mime.types;
    default_type  application/octet-stream;

    sendfile on;
    keepalive_timeout 65;

    server {
        listen 80;
        server_name jailwww;

        root /usr/local/www/phpMyAdmin;
        index index.php index.html;

        location / {
            try_files $uri $uri/ /index.php?$query_string;
        }

        location ~ \.php$ {
            try_files $uri =404;

            fastcgi_pass unix:/var/run/php-fpm.sock;
            fastcgi_index index.php;

            include fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        }

        location ~ /\.(ht|git|svn) {
            deny all;
        }
    }
}
Test and start Nginx:

Code: Select all

nginx -t
service nginx start
service nginx status
9. Test PHP through Nginx

Create a temporary test file:

Code: Select all

cat > /usr/local/www/phpMyAdmin/info.php <<'EOF'
<?php phpinfo();
EOF
Browse to:

Code: Select all

http://jailwww/info.php
PHP should load using FPM/FastCGI.

Important: remove this test file immediately after testing:

Code: Select all

rm /usr/local/www/phpMyAdmin/info.php
10. Configure phpMyAdmin

Copy the sample config:

Code: Select all

cp /usr/local/www/phpMyAdmin/config.sample.inc.php /usr/local/www/phpMyAdmin/config.inc.php
Generate a blowfish secret:

Code: Select all

openssl rand -hex 16
Edit:

Code: Select all

ee /usr/local/www/phpMyAdmin/config.inc.php
Use the jail hostname instead of a hardcoded DHCP IP:

Code: Select all

<?php

$cfg['blowfish_secret'] = 'PASTE_OPENSSL_SECRET_HERE';

$i = 0;
$i++;

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'jaildb';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

$cfg['TempDir'] = '/tmp';
Set permissions:

Code: Select all

chown -R root:www /usr/local/www/phpMyAdmin
find /usr/local/www/phpMyAdmin -type d -exec chmod 755 {} \;
find /usr/local/www/phpMyAdmin -type f -exec chmod 644 {} \;
Reload Nginx:

Code: Select all

service nginx reload
11. Make MariaDB inside jaildb reachable from jailwww

Initially, MariaDB inside jaildb only listened on localhost:

Code: Select all

127.0.0.1:3306
That prevented jailwww from connecting.

Check from the host:

Code: Select all

jexec jaildb sockstat -4 -l | grep 3306
If it shows only:

Code: Select all

127.0.0.1:3306
then edit the MariaDB config inside jaildb.

The default FreeBSD/MariaDB config had this file:

Code: Select all

/usr/local/etc/mysql/conf.d/server.cnf
It contained:

Code: Select all

bind-address = 127.0.0.1
Comment it out:

Code: Select all

# bind-address = 127.0.0.1
Then edit the replica config:

Code: Select all

jexec jaildb ee /usr/local/etc/mysql/conf.d/replica.cnf
My final replica config:

Code: Select all

[mysqld]
server_id = 226
relay_log = jaildb-relay-bin
read_only = ON
skip_name_resolve = ON
bind-address = 0.0.0.0
replicate-wild-do-table=computerbb.%
Notes:
  • bind-address = 0.0.0.0 allows MariaDB to listen on the jail network interface without hardcoding a DHCP IP.
  • skip_name_resolve = ON means MariaDB does not use hostnames in grants.
  • replicate-wild-do-table=computerbb.% prevents non-phpBB databases from being applied on the replica.
Restart MariaDB:

Code: Select all

jexec jaildb service mysql-server restart
Verify:

Code: Select all

jexec jaildb mariadb -e "SHOW VARIABLES LIKE 'bind_address';"
jexec jaildb sockstat -4 -l | grep 3306
Expected:

Code: Select all

bind_address = 0.0.0.0
And:

Code: Select all

*:3306
12. Why the replication filter is important

My master also has phpMyAdmin. phpMyAdmin can write interface preferences to its own database, for example:

Code: Select all

phpmyadmin.pma__userconfig
Before I added the replication filter, the replica SQL thread stopped with:

Code: Select all

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Error 'Table 'phpmyadmin.pma__userconfig' doesn't exist' on query.
The replica only needs the phpBB database, so this fixed it:

Code: Select all

replicate-wild-do-table=computerbb.%
After restarting MariaDB, replication was healthy again.

Check replication status:

Code: Select all

jexec jaildb mariadb -e "SHOW SLAVE STATUS\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_IO_Error|Last_SQL_Error'
Healthy output:

Code: Select all

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error:
Last_SQL_Error:
13. Create a read-only phpMyAdmin user on the replica

Because skip_name_resolve = ON is enabled, I did not create the account as:

Code: Select all

'pma_ro'@'jailwww'
Instead I used:

Code: Select all

'pma_ro'@'%'
The account is still read-only and password protected.

Inside jaildb:

Code: Select all

jexec jaildb mariadb
Create the user:

Code: Select all

CREATE USER 'pma_ro'@'%' IDENTIFIED BY 'REDACTED_STRONG_PASSWORD';

GRANT SELECT, SHOW VIEW, TRIGGER
ON computerbb.*
TO 'pma_ro'@'%';

GRANT PROCESS, REPLICATION CLIENT
ON *.*
TO 'pma_ro'@'%';

FLUSH PRIVILEGES;
Verify grants:

Code: Select all

jexec jaildb mariadb -e "SHOW GRANTS FOR 'pma_ro'@'%';"
Change the password later if needed:

Code: Select all

ALTER USER 'pma_ro'@'%' IDENTIFIED BY 'NEW_STRONG_PASSWORD';
FLUSH PRIVILEGES;
14. Test the connection from jailwww

Install a MariaDB client inside jailwww if needed:

Code: Select all

jexec jailwww pkg install mariadb1011-client
Test the TCP port:

Code: Select all

jexec jailwww nc -vz jaildb 3306
Expected:

Code: Select all

Connection to jaildb 3306 port [tcp/mysql] succeeded!
Test the database login:

Code: Select all

jexec jailwww mysql -h jaildb -P 3306 -u pma_ro -p computerbb
If successful, the prompt should show:

Code: Select all

MariaDB [computerbb]>
Exit:

Code: Select all

exit
15. Access phpMyAdmin

Open:

Code: Select all

http://jailwww/
or the DHCP address of jailwww.

Log in with:

Code: Select all

Username: pma_ro
Password: the password created above
The server should show as:

Code: Select all

Server: jaildb:3306
The visible database should include:

Code: Select all

computerbb
Because the user is limited, phpMyAdmin may show some buttons such as Insert, Drop, or Empty, but the database privileges should prevent write operations.

16. Troubleshooting

Nginx shows 404

Check the Nginx document root:

Code: Select all

grep -n 'root\|server_name\|listen\|fastcgi_pass' /usr/local/etc/nginx/nginx.conf
The root should be:

Code: Select all

root /usr/local/www/phpMyAdmin;
PHP file downloads instead of executing

Check the PHP location block:

Code: Select all

location ~ \.php$ {
    try_files $uri =404;

    fastcgi_pass unix:/var/run/php-fpm.sock;
    fastcgi_index index.php;

    include fastcgi_params;
    fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
}
Reload Nginx:

Code: Select all

nginx -t
service nginx reload
phpMyAdmin cannot connect to MySQL/MariaDB

Check MariaDB listener inside jaildb:

Code: Select all

jexec jaildb sockstat -4 -l | grep 3306
Good:

Code: Select all

*:3306
Bad:

Code: Select all

127.0.0.1:3306
If it is bound only to localhost, check:

Code: Select all

/usr/local/etc/mysql/conf.d/server.cnf
/usr/local/etc/mysql/conf.d/replica.cnf
Replication stopped

Check:

Code: Select all

jexec jaildb mariadb
Top

Return to “Networking & Security”