Install MariaDB or PostgreSQL and create a database

Pick a database and keep it private

Install one database server, not both, unless you need both. MariaDB is a MySQL-compatible server often used with PHP applications such as WordPress. PostgreSQL is a general-purpose server with strong standards support, and many modern applications use it.

In both cases the database should accept connections from the server itself only. Your application on the same VPS connects to localhost, and you reach the database from your own computer through an SSH tunnel when needed. Do not open database ports in the firewall.

MariaDB on Ubuntu

Install the server from the Ubuntu archive. It starts automatically. On Ubuntu the administrative account uses socket authentication, so sudo mysql logs in as the database administrator without a password.

sudo apt update
sudo apt install mariadb-server
sudo systemctl status mariadb --no-pager
sudo mysql_secure_installation

MariaDB on AlmaLinux

Install the package, enable the service and run the same hardening script. On AlmaLinux the server may listen on every network interface by default, so check the listener and restrict it to the loopback address if needed.

sudo dnf install mariadb-server
sudo systemctl enable --now mariadb
sudo mysql_secure_installation
sudo ss -lntp | grep -E "3306|mariadbd|mysqld"

printf "[mysqld]\nbind-address=127.0.0.1\n" | sudo tee /etc/my.cnf.d/99-local.cnf
sudo systemctl restart mariadb
sudo ss -lntp | grep 3306

Create a MariaDB database and user

Give each application its own database and its own user with rights on that database only. Replace the names and use a long random password. This example creates appdb and appuser, restricted to connections from localhost.

openssl rand -base64 24

sudo mysql <<'EOF'
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'PASTE_THE_GENERATED_PASSWORD';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EOF
mysql -u appuser -p appdb -e "SELECT 1;"

PostgreSQL on Ubuntu

Install the server and client tools from the Ubuntu archive. The cluster is created and started automatically and listens on localhost only.

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl status postgresql --no-pager
sudo ss -lntp | grep 5432

PostgreSQL on AlmaLinux

Install the server, initialise the data directory, then enable the service. The package from the default repositories is an older long-term release, which is fine for most applications. PostgreSQL's own repository provides newer versions if you need one.

AlmaLinux allows ident authentication on the loopback address by default. For password logins from an application, change the 127.0.0.1/32 and ::1/128 lines in pg_hba.conf from ident to scram-sha-256, then reload.

When using scram-sha-256 in pg_hba.conf, also ensure new passwords are stored as SCRAM hashes. The older default package may otherwise create MD5 hashes, which cannot authenticate through a SCRAM rule. Change password_encryption before creating the application role; existing roles need their passwords reset afterward.

sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql
sudo ss -lntp | grep 5432

sudo -u postgres psql -Atc "SHOW hba_file;"
sudo nano "$(sudo -u postgres psql -Atc 'SHOW hba_file;')"
sudo systemctl reload postgresql

sudo -u postgres psql -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256';"
sudo systemctl reload postgresql
sudo -u postgres psql -Atc "SHOW password_encryption;"

Create a PostgreSQL role and database

Create a login role with a password and a database it owns. The password prompt avoids putting the secret in your shell history.

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb --owner=appuser appdb
psql -h 127.0.0.1 -U appuser appdb -c "SELECT 1;"

Take and test a dump

A dump is a text or archive copy of the database that you can restore on another server. Take one before upgrades and on a schedule, store it off the server, and practise a restore into a scratch database. A backup you have not restored is a guess.

Keep the PostgreSQL dump in a postgres-owned directory; a file in a private administrator home may be unreadable by postgres. Compare actual row counts and sample records after restoring. The PostgreSQL backup guide shows the checks.

umask 077
sudo mysqldump --single-transaction appdb | gzip > appdb-$(date +%F).sql.gz
sudo mysql -e "CREATE DATABASE restore_test;"
gunzip -c appdb-$(date +%F).sql.gz | sudo mysql restore_test

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
DUMP=/var/backups/postgresql/appdb-$(date +%F).dump
sudo -u postgres pg_dump -Fc appdb -f "$DUMP"
sudo chmod 600 "$DUMP"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test "$DUMP"
sudo -u postgres psql -d restore_test -c '\dt'

sudo mysql -e "DROP DATABASE restore_test;"
sudo -u postgres dropdb restore_test

MariaDB restored rows: 3; matching content SHA256: a4758c246d76c52c611f48abdc421ce8fa944d9443a26ec0d34c811f36e6eb9f
PostgreSQL restored rows: 3; matching content SHA256: 7ad8e0beb0cfa5de3119a973d7f7779f902cbc62bcbcd523197be170327aadcf