Database Security Hardening: Complete Protection Guide

# Set up encrypted partition for PostgreSQL data
sudo cryptsetup luksFormat /dev/xvdf
sudo cryptsetup open /dev/xvdf pgdata_crypt
sudo mkfs.ext4 /dev/mapper/pgdata_crypt
sudo mount /dev/mapper/pgdata_crypt /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/<a href="/databases/postgresql/">PostgreSQL</a>/16/main

-- Enable TDE (PostgreSQL 17+)
CREATE EXTENSION pg_tde;
SELECT pg_tde_add_key_provider_file('provider1', '/etc/postgresql/tde_key');
SELECT pg_tde_set_key('my_key', 'provider1');

-- Now create encrypted tables
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    ssn VARCHAR(11),
    balance DECIMAL(10,2)
) USING tde_heap;

-- Enable encryption for tablespace (MySQL 8.0+)
CREATE TABLESPACE secure_ts ADD DATAFILE 'secure.ibd'
  ENCRYPTION='Y' ENGINE=InnoDB;

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    ssn VARCHAR(11),
    balance DECIMAL(10,2)
) TABLESPACE secure_ts;

-- Or per-table encryption
ALTER TABLE accounts ENCRYPTION='Y';

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_min_protocol_version = 'TLSv1.3'
ssl_ciphers = 'HIGH:!aNULL:!eNULL:!LOW'

# my.cnf
[mysqld]
require_secure_transport = ON
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
tls_version = TLSv1.2,TLSv1.3

-- Principle of least privilege

-- Create read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Create read-write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydb TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- Create admin role (limited number of users)
CREATE ROLE db_admin WITH LOGIN SUPERUSER;
GRANT db_admin TO admin_user;

-- Assign roles to application users
CREATE USER app_service WITH PASSWORD 'strong_password';
GRANT readwrite TO app_service;

CREATE USER reporting_service WITH PASSWORD 'another_password';
GRANT readonly TO reporting_service;

-- MySQL role-based access
CREATE ROLE 'readonly', 'readwrite';

GRANT SELECT ON mydb.* TO 'readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'readwrite';

CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
GRANT 'readwrite' TO 'app_user'@'%';
SET DEFAULT ROLE 'readwrite' TO 'app_user'@'%';

-- Revoke PROCESS privilege from non-admin users
REVOKE PROCESS ON *.* FROM 'app_user'@'%';

// VULNERABLE: String concatenation
String query = "SELECT * FROM users WHERE email = '" + email + "'";

// SAFE: Parameterized query
PreparedStatement stmt = connection.prepareStatement(
    "SELECT * FROM users WHERE email = ?"
);
stmt.setString(1, email);
ResultSet rs = stmt.executeQuery();

# VULNERABLE: f-string
query = f"SELECT * FROM users WHERE email = '{email}'"

# SAFE: Parameterized query with psycopg2
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

# SAFE: Parameterized query with SQLAlchemy
result = session.execute(
    text("SELECT * FROM users WHERE email = :email"),
    {"email": email}
)

-- PostgreSQL: Block fragile superuser functions from apps
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM readonly, readwrite;

-- Enable statement timeout to prevent runaway queries
ALTER ROLE app_service SET statement_timeout = '30s';

-- MySQL: Enable sql_mode to reject dangerous patterns
SET GLOBAL sql_mode = 'STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

# Restrict PostgreSQL to application servers only
sudo ufw allow from 10.0.1.0/24 to any port 5432
sudo ufw deny 5432

# Or use iptables
sudo iptables -A INPUT -p tcp --dport 5432 -s 10.0.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5432 -j DROP

# AWS Security Group (Terraform example)
resource "aws_security_group" "database" {
  name        = "database-sg"
  description = "Database security group"

  ingress {
    description     = "PostgreSQL from app tier"
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = [aws_security_group.app.id]
  }

  ingress {
    description     = "MySQL from app tier"
    from_port       = 3306
    to_port         = 3306
    protocol        = "tcp"
    security_groups = [aws_security_group.app.id]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write,ddl,role'
pgaudit.log_catalog = off
pgaudit.log_level = 'notice'
pgaudit.log_relation = on

-- Create audit role
CREATE ROLE auditor WITH LOGIN PASSWORD 'audit_password';

-- Audit all DDL and DML on sensitive tables
CREATE TABLE audit_test(id INT);
SELECT pgAuditSetRole('auditor');

SELECT * FROM audit_test;  -- This is logged

# my.cnf
[mysqld]
plugin-load-add=audit_log.so
audit_log_format=JSON
audit_log_file=/var/log/mysql/audit.log
audit_log_rotate_on_size=100M