Secure a database

A poorly configured database causes more incidents than a clever exploit: a service listening on every interface, an application connecting as administrator, or an unencrypted dump copied to a share. The fix is a short list of checks. This article covers Debian 13 with MariaDB and PostgreSQL.

Check the listening socket

Start by observing what actually listens:

sudo ss -lntp | grep -E ':(3306|5432)\b'

A line on 0.0.0.0:3306 or [::]:5432 means the database accepts remote connections. A local application has no reason to be reachable from the internet.

Restart the service (sudo systemctl restart mariadb or postgresql@17-main) and confirm the port only listens locally. Add an explicit firewall deny: sudo ufw deny 3306/tcp or sudo ufw deny 5432/tcp. If another server must reach the database, restrict the source address and require TLS.

Trim privileges

The application must never connect as root or postgres. Create a dedicated account with a long, unique password:

CREATE DATABASE appdb;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'mot-de-passe-fort';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

Grant extra rights (CREATE, ALTER, INDEX) one at a time when a migration needs them, not in advance. On PostgreSQL:

CREATE ROLE app LOGIN PASSWORD 'mot-de-passe-fort';
CREATE DATABASE appdb OWNER app;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

A database owner is not a superuser, which is enough for an application. Verify with SHOW GRANTS FOR 'app'@'localhost'; or \du app.

Encrypt traffic and backups

On the loopback interface, the Unix socket is enough. As soon as traffic crosses the network, enable TLS: require_secure_transport=ON on MariaDB, ssl=on plus a hostssl line in pg_hba.conf on PostgreSQL. Use a dedicated certificate, not the website one.

Backups contain the same sensitive data, so encrypt them before they leave the server:

mysqldump --single-transaction --routines appdb | age -r AGE_PUBLIC_KEY -o appdb.sql.age
pg_dump -Fc appdb | age -r AGE_PUBLIC_KEY -o appdb.dump.age

Keep the private key away from the backed-up machine and test a restore before relying on it. The VPS root disk runs inside a system container, so LUKS on the root filesystem is not available. Compensate with encrypted backups and application-level encryption for highly sensitive columns.

Verify

From another machine, a connection to port 3306 or 5432 should time out or be refused. On the server, check listening sockets, grants and accounts:

sudo ss -lntp | grep -E ':(3306|5432)\b'
sudo mariadb -e "SHOW GRANTS FOR 'app'@'localhost';"
sudo -u postgres psql -c "\du"

Restore an encrypted dump on a test host. After a departure or a leak, rotate the application password, revoke the old one and make sure no open session survives.