Database hardening

Why Database Hardening Matters

Databases store the most sensitive data on a server (credentials, customer data, logs, financial info). Because of that, they are prime targets. Hardening is everything you do to reduce the attack surface and limit damage if an attacker gets in.

In this chapter, “database” mainly means MySQL/MariaDB and PostgreSQL, but the principles apply to most RDBMS.

Key goals:

• Minimize who can connect and from where
• Minimize what each account can do
• Protect data in transit and at rest
• Detect and limit abuse quickly

Threat Model and Principles

Before tweaking configs, think about typical threats:

• Stolen or guessed database credentials
• Application SQL injection using valid DB credentials
• Lateral movement from another compromised service on the same host
• Misconfigured remote access (e.g., DB open to the Internet)
• Abuse of overly-privileged accounts (e.g., root or postgres)
• Data exfiltration via backups or replication

Basic principles you’ll follow:

• Least privilege: each account has only what it strictly needs.
• Defense in depth: combine network, OS, DB, and app layers.
• Secure by default: disable unused features; prefer deny-over-allow.
• Auditability: be able to see who did what and when.

Network Exposure and Access Control

Bind Addresses and Listening Ports

Databases should not listen on all interfaces unless absolutely required.

MySQL/MariaDB example (usually /etc/mysql/my.cnf or /etc/my.cnf):

[mysqld]
bind-address = 127.0.0.1
port = 3306

PostgreSQL example (postgresql.conf):

listen_addresses = 'localhost'
port = 5432

Guidelines:

• If the DB is used only by apps on the same host, bind to 127.0.0.1 or a Unix socket only.
• If remote access is required, bind to a specific private IP (e.g., 10.0.0.10) and restrict via firewall and pg_hba.conf/MySQL grants.
• Never expose DB ports directly to the public Internet without strict controls (VPN, jump host, etc.).

Firewalls and Network Segmentation

Use the host firewall and, where possible, network segmentation:

• Allow DB port only from specific app servers or admin networks.
• Deny all other inbound connections.
• For high-security setups, place the DB in a private subnet not directly reachable from the Internet.

Example with ufw:

sudo ufw allow from 10.0.1.20 to any port 5432 proto tcp   # app server
sudo ufw deny 5432/tcp

Authentication Hardening

Account Design

Avoid using a single all-powerful account for everything.

Create:

• Application accounts: one per application (and ideally per environment: dev, test, prod).
• Human admin accounts: named accounts for DBAs and admins.
• Service-specific accounts: for backup tools, monitoring, replication, etc.

Never let the application use:

• root (MySQL/MariaDB)
• postgres (PostgreSQL superuser)

These should be reserved for maintenance and emergency tasks.

Password Policies and Authentication Methods

Basic password hardening:

• Enforce minimum length and complexity (length is more important).
• Disable or lock unused default accounts.
• Rotate passwords regularly, especially for shared service accounts.
• Store DB credentials securely (e.g., environment variables managed by a secrets manager, not in code or public repos).

MySQL/MariaDB (plugin-based password policies):

INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.policy = MEDIUM;

PostgreSQL authentication is controlled in pg_hba.conf; choose strong methods (scram-sha-256 where available) instead of md5 or weaker.

Example pg_hba.conf line:

# TYPE  DATABASE USER    ADDRESS        METHOD
host    appdb    appusr  10.0.1.20/32   scram-sha-256

Limiting Login Sources

Restrict accounts to specific hosts or networks.

MySQL/MariaDB:

• Accounts include a host part, e.g. appusr@'10.0.1.20', backupusr@'10.0.2.%', admin@'localhost'.

Example:

CREATE USER 'appusr'@'10.0.1.20' IDENTIFIED BY 'StrongPassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appusr'@'10.0.1.20';

PostgreSQL:

• Use pg_hba.conf to tie users to IP ranges.
• Avoid host all all 0.0.0.0/0 trust or similar overly-permissive rules.

Authorization and Least Privilege

Principle of Least Privilege

Grant only what the role needs:

• Read-only roles: SELECT only.
• Read-write roles: SELECT, INSERT, UPDATE, DELETE only on specific schemas/tables.
• No DROP, ALTER, CREATE for normal app roles unless strictly necessary.
• No superuser-level or admin-level privileges for applications.

MySQL/MariaDB:

GRANT SELECT, INSERT, UPDATE, DELETE
ON appdb.*
TO 'appusr'@'10.0.1.20';

PostgreSQL (using roles):

CREATE ROLE app_readwrite LOGIN PASSWORD 'StrongPassword';
GRANT CONNECT ON DATABASE appdb TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

Avoiding Superusers

Only dedicated DBA accounts should have:

• SUPER, RELOAD, SHUTDOWN (MySQL/MariaDB)
• SUPERUSER, REPLICATION, BY PASS RLS (PostgreSQL)

Check for overly-privileged accounts regularly:

MySQL/MariaDB:

SELECT user, host, Super_priv, Grant_priv
FROM mysql.user
ORDER BY Super_priv DESC;

PostgreSQL:

SELECT rolname, rolsuper, rolreplication, rolcreaterole, rolcreatedb
FROM pg_roles
ORDER BY rolsuper DESC;

Remove or reduce privileges where possible.

Separation of Duties

In larger environments, use separate roles:

• Application role: minimal data access.
• Maintenance role: schema changes, index management.
• Backup/restore role: can read all data and manage backups, but cannot change schema or configuration.
• Monitoring role: read-only access to statistics and system views.

Secure Configuration

Disabling Unused Features

Review and disable:

• Unused plugins and extensions (e.g., test/demo plugins, sample database packages).
• Unnecessary features that allow OS-level access (e.g., LOAD DATA LOCAL INFILE in MySQL if not needed).
• Command execution capabilities from within SQL (e.g., untrusted procedural languages, COPY ... PROGRAM in PostgreSQL in untrusted contexts).

MySQL/MariaDB example:

[mysqld]
local_infile = 0

PostgreSQL:

• Only install necessary extensions in each database.
• Be careful with untrusted languages or extensions that can execute OS commands.

Secure Defaults and Limits

Set conservative limits to reduce impact of abuse:

• Connection limits per role or IP to mitigate brute-force or DoS attempts.
• Statement timeouts to avoid runaway queries.

PostgreSQL examples (postgresql.conf):

max_connections = 200
statement_timeout = '1min'
idle_in_transaction_session_timeout = '5min'

MySQL/MariaDB examples:

[mysqld]
max_connections = 200
wait_timeout = 600

Ensure error messages do not leak sensitive information (schema names, internal details) to untrusted clients; handle detailed error logging server-side.

Encryption: In Transit and At Rest

Encryption in Transit (TLS)

Enable TLS between clients and the database:

• Generate server certificates and keys (and CA if using mutual TLS).
• Configure the DB to use them.
• Enforce TLS for client connections where possible.

MySQL/MariaDB (my.cnf):

[mysqld]
ssl-ca   = /etc/mysql/certs/ca.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key  = /etc/mysql/certs/server-key.pem

Then, for users:

CREATE USER 'appusr'@'10.0.1.20'
  IDENTIFIED BY 'StrongPassword'
  REQUIRE SSL;

PostgreSQL (postgresql.conf):

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

pg_hba.conf can require hostssl (SSL-only connections):

hostssl appdb appusr 10.0.1.20/32 scram-sha-256

Use current TLS versions and secure cipher suites.

Encryption at Rest

Common approaches:

• Full-disk or partition encryption (e.g., LUKS at OS level).
• Database-native transparent data encryption (TDE) where available.
• Application-level encryption for particularly sensitive fields (keys managed outside DB).

Hardening points:

• Protect key material: keep encryption keys off the same disk as the encrypted data.
• Limit which processes and users can access decrypted data (filesystem permissions and OS-level controls).
• Ensure backups are also encrypted and keys are managed consistently.

Logging, Auditing, and Monitoring

Database Logs

Enable and protect logs:

• General query log (for debugging; use sparingly in production).
• Slow query log (performance and potential abuse).
• Error logs.
• Audit logs (if supported).

MySQL/MariaDB examples:

[mysqld]
general_log = OFF
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

Consider:

• Who can read these logs (filesystem permissions).
• Log rotation and retention.
• Sending logs to a central syslog/SIEM system.

Auditing Data Access

Audit capabilities vary by engine, but you aim to track:

• Logins (success/failure) with timestamps and sources.
• Privilege changes (GRANT, REVOKE, role changes).
• Schema changes (CREATE TABLE, ALTER TABLE, DROP).
• Security-relevant operations (e.g., LOAD DATA, use of superuser powers).

For engines lacking built-in auditing, you can:

• Use triggers to log sensitive table access to an audit table (careful with performance).
• Use proxy-based auditing (e.g., application-layer logging, DB proxies that log queries).

Monitoring and Alerting

Hook the DB into your monitoring stack:

• Track login failures, unusual spikes in connections, or anomalous query patterns.
• Alert on changes to privileged roles or accounts.
• Alert on unexpected network sources accessing the DB port.

Combine DB metrics with system-level monitoring from the rest of your Linux stack.

Backup, Restore, and Replication Security

Securing Backup Processes

Backups often contain full database contents and must be hardened:

• Use separate backup accounts with SELECT and necessary privileges, not full superuser rights.
• Ensure backup files are encrypted (e.g., gpg, encrypted storage, or backup tool encryption).
• Protect backup storage (permissions, network isolation, access logs).
• Avoid storing backup decryption keys in the same place as the backups.

Example MySQL/MariaDB backup account:

CREATE USER 'backup'@'10.0.2.10' IDENTIFIED BY 'StrongBackupPassword';
GRANT SELECT, SHOW VIEW, EVENT, TRIGGER, LOCK TABLES
  ON *.* TO 'backup'@'10.0.2.10';

Securing Replication

Replication users often have broad privileges:

• Create a dedicated replication user.
• Limit allowed hosts.
• Use TLS for replication channels.
• Ensure replication credentials are stored securely (in config files with restrictive permissions, or in a dedicated secrets mechanism).

MySQL/MariaDB example:

CREATE USER 'repl'@'10.0.2.%' IDENTIFIED BY 'StrongReplPassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.2.%';

Protect configuration files that contain these credentials (chmod 600 where appropriate).

OS-Level Hardening Around the Database

Although this chapter is about the DB layer, a few OS-level measures are critical specifically for databases:

• Run the DB under a dedicated system user (e.g., mysql, postgres) with minimal rights.
• Ensure DB data directories are owned by and accessible only to that user.
• Use Mandatory Access Control (SELinux/AppArmor) profiles tuned for your DB engine.
• Lock down shell access for DB service accounts (no interactive shell, no direct login).

Example /etc/passwd entry for a service account:

mysql:x:27:27:MySQL Server:/var/lib/mysql:/usr/sbin/nologin

Secure Deployment and Maintenance Practices

Configuration Management

Use configuration management and version control for:

• DB configuration files (my.cnf, postgresql.conf, pg_hba.conf).
• Schema migrations (SQL scripts, migration tools).
• User and role creation scripts.

Benefits:

• Auditable changes.
• Repeatability across environments.
• Ability to review changes for security regressions.

Patching and Upgrades

Keep your database engine patched:

• Track vendor security advisories (e.g., CVEs for MySQL/PostgreSQL).
• Plan and test upgrades in staging before production.
• Include DB in your regular patching cycle, with rollback plans.

Baseline and Regular Reviews

Hardening is not “set and forget”:

• Maintain a baseline of expected users, roles, and privileges.
• Periodically review accounts, privileges, and network rules.
• Remove stale accounts (old apps, former employees, unused services).
• Re-validate password policies and encryption settings over time.

Testing Your Hardening

To validate hardening:

• Attempt to connect from unauthorized hosts and confirm denial.
• Try using app accounts to perform admin/superuser tasks (should fail).
• Confirm that connections are using TLS (check client and server).
• Verify that logs capture security-relevant events.
• Run vulnerability scanners that include database checks and verify/triage findings.

Testing should be done in non-production first, then carefully in production with clear rollback steps.

Database hardening is about layering controls: network, authentication, authorization, configuration, encryption, logging, and operational discipline. Properly applied, these controls significantly reduce both the likelihood and impact of database compromise.