Find us on social media

Client area
MySQLPostgreSQLDatabase5 min read

Secure MySQL/PostgreSQL databases

Bind to localhost, strong passwords, least privilege, encrypted connections and backup encryption.

Database security principles

  • Least privilege: each user only accesses what they need
  • Bind to localhost: don't expose to the world
  • Strong, unique passwords
  • Encrypted connections when possible
  • Regular encrypted backups

MySQL: Secure configuration

Run mysql_secure_installation

bash
sudo mysql_secure_installation

This allows you to:

  • Set root password
  • Remove anonymous users
  • Disable remote root login
  • Remove test database

Bind to localhost

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

ini
[mysqld]
bind-address = 127.0.0.1
bash
sudo systemctl restart mysql

Create users with minimal privileges

sql
-- Create user for a specific app
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

-- Never use GRANT ALL unless strictly necessary

Remove test databases and users

sql
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.user WHERE User='';
FLUSH PRIVILEGES;

PostgreSQL: Secure configuration

Configure authentication

Edit /etc/postgresql/16/main/pg_hba.conf:

terminal
local   all   all                 scram-sha-256
host    all   all   127.0.0.1/32  scram-sha-256

Bind to localhost

Edit /etc/postgresql/16/main/postgresql.conf:

terminal
listen_addresses = 'localhost'
bash
sudo systemctl restart postgresql

Create roles with minimal privileges

sql
CREATE ROLE myapp WITH LOGIN PASSWORD 'StrongPassword123!';
GRANT CONNECT ON DATABASE myapp_db TO myapp;
GRANT USAGE ON SCHEMA public TO myapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp;

Encrypted connections (SSL)

MySQL with SSL

sql
SHOW VARIABLES LIKE '%ssl%';
ALTER USER 'myapp'@'%' REQUIRE SSL;

PostgreSQL with SSL

In postgresql.conf:

terminal
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

Encrypted backups

bash
# MySQL: encrypted backup with gpg
mysqldump -u root -p myapp_db | gpg --symmetric --cipher-algo AES256 -o backup_$(date +%Y%m%d).sql.gpg

# PostgreSQL: encrypted backup
pg_dump myapp_db | gpg --symmetric --cipher-algo AES256 -o backup_$(date +%Y%m%d).sql.gpg

# Restore
gpg -d backup_20260115.sql.gpg | mysql -u root -p myapp_db

Recommendations

  • Never expose database ports to the internet
  • If you need remote access, use an SSH tunnel
  • Rotate database passwords every 3-6 months
  • Monitor slow queries and active connections
  • Keep the database updated with security patches
Was this guide helpful?
← Back to Security