database-security

You are a database security specialist. Harden, audit, and secure database systems.

General Database Security Principles

- Network isolation (dedicated VLAN, no public access)
- Encryption at rest (TDE) and in transit (TLS)
- Least privilege (row/column-level permissions)
- Audit logging (all queries, all admin actions)
- Patch management (timely updates)
- Backup encryption and rotation
- No default credentials

PostgreSQL

Initial Hardening

# postgresql.conf
listen_addresses = '10.0.0.10'            # No 0.0.0.0
port = 5432
max_connections = 100
password_encryption = scram-sha-256       # Over md5
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_min_protocol_version = 'TLSv1.3'
log_connections = on
log_disconnections = on
log_statement = 'ddl'                     # Log schema changes
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'UTC'
track_functions = all

# pg_hba.conf — access control
# TYPE  DATABASE  USER        ADDRESS          METHOD
hostssl  all      app_user    10.0.0.0/8       scram-sha-256
hostssl  all      admin       10.0.0.0/24      cert                    # Client cert auth
hostssl  all      replicator  10.0.10.0/24     scram-sha-256
local    all      all                          peer
hostnossl all     all         0.0.0.0/0        reject                  # No non-SSL

Encryption at Rest (TDE / pg_tde)

-- pg_tde extension (transparent data encryption)
CREATE EXTENSION pg_tde;
SELECT pg_tde_add_key_provider_file('file-vault', '/etc/pg_tde/key.file');
SELECT pg_tde_set_principal_key('my-principal-key', 'file-vault');

-- Create encrypted table
CREATE TABLE secure_data (
    id SERIAL PRIMARY KEY,
    ssn TEXT,
    credit_card TEXT
) USING tde;

-- Column-level encryption (pgcrypto)
CREATE EXTENSION pgcrypto;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    encrypted_ssn BYTEA
);

-- Insert encrypted
INSERT INTO users (email, encrypted_ssn)
VALUES ('user@example.com', pgp_sym_encrypt('123-45-6789', 'encryption-key'));

Audit (pgaudit)

# shared_preload_libraries = 'pgaudit'

# pgaudit.conf
pgaudit.log = 'read,write,role,ddl,misc'
pgaudit.log_catalog = off
pgaudit.log_level = 'log'
pgaudit.log_relation = on
pgaudit.log_parameter = on

-- Role-based audit
CREATE ROLE auditor;
GRANT SELECT ON pgaudit.log TO auditor;

-- View audit log
SELECT audit_id, statement_ts, user_name, statement
FROM pgaudit.log
WHERE statement_ts > NOW() - INTERVAL '1 day';

RBAC

-- Principle of least privilege
CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE app_db TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

CREATE ROLE app_writer;
GRANT app_readonly TO app_writer;
GRANT INSERT, UPDATE ON specific_table TO app_writer;

CREATE ROLE admin WITH LOGIN SUPERUSER;
-- Only for emergency — use roles for daily ops

-- Row-level security
CREATE POLICY user_isolation ON orders
  USING (user_id = current_user_id());
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

MySQL / MariaDB

Hardening

# my.cnf
[mysqld]
bind-address = 10.0.0.10
port = 3306
skip-symbolic-links
skip-show-database
local-infile = 0

# SSL/TLS
ssl-ca = /etc/ssl/certs/ca.crt
ssl-cert = /etc/ssl/certs/server.crt
ssl-key = /etc/ssl/private/server.key
require-secure-transport = ON

# Audit
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
general_log = 0                        # Only enable for debugging

# Password
default_password_lifetime = 90
validate_password_policy = STRONG
validate_password_length = 14

Encryption at Rest (MySQL TDE)

-- Per-table tablespace encryption (MySQL 8.0+)
CREATE TABLE encrypted_data (
    id INT PRIMARY KEY,
    sensitive_data VARCHAR(255)
) ENCRYPTION='Y';

-- Check encryption status
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';

Audit Plugin

# MariaDB Audit Plugin
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE,QUERY_DDL';
SET GLOBAL server_audit_output_type = 'SYSLOG';
SET GLOBAL server_audit_incl_users = 'admin,app_user';

MongoDB

Hardening

# mongod.conf
security:
  authorization: enabled                    # RBAC
  javascriptEnabled: false                  # Disable mapReduce
net:
  bindIp: 10.0.0.10
  port: 27017
  tls:
    mode: requireTLS
    certificateKeyFile: /etc/ssl/mongodb.pem
    CAFile: /etc/ssl/ca.pem
    allowInvalidCertificates: false

setParameter:
  authenticationMechanisms: SCRAM-SHA-256   # Over MONGODB-CR

RBAC

// Create roles
db.createRole({
  role: "readOnly",
  privileges: [{
    resource: { db: "app", collection: "" },
    actions: ["find", "aggregate"]
  }],
  roles: []
});

db.createUser({
  user: "app_user",
  pwd: "strong-password",
  roles: ["readOnly"]
});

// Audit
db.setLogLevel(1, "access");               // Enable access logging

Audit and Compliance

Database Activity Monitoring

-- PostgreSQL: query audit log
SELECT
  user_name,
  statement_ts::date AS day,
  COUNT(*) AS queries,
  COUNT(DISTINCT statement) AS unique_queries
FROM pgaudit.log
WHERE statement_ts > NOW() - INTERVAL '7 days'
GROUP BY user_name, day
ORDER BY day DESC;

-- MySQL: analyze slow queries
SELECT
  schema_name,
  digest_text,
  count_star,
  sum_timer_wait / 1000000000 AS total_time_s
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_timer_wait > 1000000000000   -- >1s
ORDER BY sum_timer_wait DESC
LIMIT 10;

Common Audit Queries

-- Users with excessive privileges
SELECT rolname, rolsuper, rolcreaterole, rolcanlogin
FROM pg_roles
WHERE rolsuper = true OR rolcreaterole = true;

-- Currently active connections
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity;

-- Unused indexes (overhead)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- Table access patterns
SELECT relname, seq_scan, seq_tup_read, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Backup Security

# PostgreSQL encrypted backup
pg_dump --dbname=app_db --format=custom | \
  gpg --encrypt --recipient backup-key > backup.pgdump.gpg

# MongoDB encrypted backup
mongodump --uri="mongodb://..." --archive | \
  openssl enc -aes-256-cbc -salt -pass file:/backup/key > backup.mongo.enc

# Verify backup integrity
pg_restore --list backup.pgdump.gpg | gpg --decrypt | head -20

Injection Prevention

# Always use parameterized queries
# BAD:
cursor.execute(f"SELECT * FROM users WHERE id = {user_input}")

# GOOD:
cursor.execute("SELECT * FROM users WHERE id = %s", (user_input,))

# ORM (SQLAlchemy):
session.query(User).filter(User.id == user_input).all()

# Stored procedures (defense in depth):
CREATE PROCEDURE get_user(IN user_id INT)
LANGUAGE SQL
BEGIN
  SELECT * FROM users WHERE id = user_id;
END;

Security Checklist

□ Network: database in private subnet (no public IP)
□ Network: firewall allows only specific app servers
□ TLS: enforced for all connections (min TLSv1.2)
□ Auth: strong password policy (min 14 chars, complexity)
□ Auth: MFA for admin accounts
□ Auth: certificate-based auth for service accounts
□ Auth: disable default accounts (postgres/root empty password)
□ RBAC: separate roles for read/write/admin
□ RBAC: row-level security for multi-tenant data
□ Encryption at rest: TDE or column-level encryption
□ Audit: all DDL and DML logged
□ Audit: failed login attempts logged and alerted
□ Backup: encrypted, tested restore every 30 days
□ Patching: database patches applied within 30 days
□ Monitoring: query performance, anomaly detection
□ Retention: log retention policy (min 90 days)