How do I secure PostgreSQL or MySQL in production?

PostgreSQL authentication

scram-sha-256, never md5 or trust

Listen address

127.0.0.1 or the private-network IP

Application account

own role, no SUPERUSER, no DROP

Backups

daily pg_dump / mysqldump plus PITR from WAL or binlog

Network and authentication: the first two layers

A database server does not need to be reachable from the internet, even with a strong password. Bind it to a private address and do remote administration through an SSH tunnel or a VPN. The second layer is authentication: pg_hba.conf decides who may connect, from where, to which database and by what method. Remove trust and md5 from every network line. password_encryption has defaulted to scram-sha-256 since PostgreSQL 14, and hostssl rejects unencrypted connections. MySQL's equivalents are bind-address, accounts tied to an exact host instead of %, and require_secure_transport = ON.

Least privilege: the application is not the administrator

The most common mistake is an application that connects as postgres or root so that migrations just work. When that application has a SQL injection, the attacker inherits everything the account can do: every database, extensions, reading files from disk. Create one role for the application with SELECT, INSERT, UPDATE and DELETE on its own tables, and a separate role for migrations that only the deployment job uses. Reporting roles get SELECT and nothing else. Each role has its own password, rotated when someone leaves and kept in your secrets manager, not in the code.

• One role per application and per environment; staging never uses the production password
• ALTER DEFAULT PRIVILEGES, so new tables inherit the right rights
• log_connections on, with the logs shipped to a central system

What Romanian law and regulators expect

A database is almost always where personal data lives, so GDPR Article 32 applies directly: encryption, access control and the ability to restore. After a leak, ANSPDCP must be notified within 72 hours, and its first question is who had access and how. For entities under OUG 155/2024, access management and backup are explicit minimum measures. Invoices issued through e-Factura have legal retention periods, which makes a tested database backup an obligation rather than an option.

Commands and configuration

# postgresql.conf (fragment)
listen_addresses = '10.0.0.10'
password_encryption = scram-sha-256
ssl = on
log_connections = on

# pg_hba.conf
# TYPE     DATABASE  USER      ADDRESS        METHOD
local      all       postgres                 peer
hostssl    app_db    app_user  10.0.0.5/32    scram-sha-256
host       all       all       0.0.0.0/0      reject

CREATE ROLE app_user LOGIN PASSWORD 'password-from-the-secrets-manager';
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

# /etc/mysql/mysql.conf.d/mysqld.cnf (fragment)
[mysqld]
bind-address = 10.0.0.10
require_secure_transport = ON
local_infile = 0

-- in the mysql client
DROP USER IF EXISTS 'root'@'%';
CREATE USER 'app'@'10.0.0.5' IDENTIFIED BY 'password-from-the-secrets-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'10.0.0.5';
FLUSH PRIVILEGES;

Practical examples

Common mistakes

• Leaving listen_addresses = '*' or bind-address = 0.0.0.0 and relying on the password alone.
• Connecting the application as postgres or root because migrations 'worked that way'.
• Having a backup that was never restored and no point-in-time recovery for an accidental DELETE.
• Keeping the database password in the repository, identical on staging and production.

How 4hosting helps

Related questions

• Do I need TLS between the application and the database on the same server?

  Over a Unix socket or 127.0.0.1 the traffic never leaves the machine, so TLS is optional. Once the database sits on another server, even in the same private network, TLS becomes mandatory: the provider's network is shared, and a single misconfiguration can expose it.

• What is PITR and why is pg_dump not enough?

  pg_dump gives you the state at the hour it ran; everything after is lost. Point-in-time recovery uses the WAL archive, or the binlog in MySQL, to rebuild the database to the minute before a mistaken DELETE. You use the two together.

• Should I encrypt the disk or the database?

  Disk encryption protects you from physical theft or a retired drive, not from an attacker connected to the running database. For sensitive columns such as national ID numbers or payment data, application-level encryption with keys kept outside the database is the layer that counts.