Database Security Hardening Guide
Database security is a critical component of any organization's security posture. Databases store the most valuable data: customer records, financial data, intellectual property, and credentials. This guide covers the key security practices including encryption, access control, network isolation, and secret management.
Encryption at Rest
Encryption at rest protects data stored on disk. If an attacker gains access to the underlying storage, encrypted data remains unreadable without the encryption key.
Transparent Data Encryption (TDE)
TDE encrypts database files automatically. The database engine handles encryption and decryption transparently.
-- PostgreSQL: Enable TDE with pg_tde extension
CREATE EXTENSION pg_tde;
-- Create an encrypted table
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT,
ssn TEXT
) USING tde;
-- MySQL: Enable InnoDB tablespace encryption
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2)
) ENCRYPTION='Y';
Application-Level Encryption
For maximum protection, encrypt sensitive columns at the application level. The database never sees the plaintext.
# Application-level encryption with AWS KMS
import boto3
from cryptography.fernet import Fernet
def encrypt_column(plaintext, kms_key_id):
# Generate a data key from KMS
kms = boto3.client('kms')
response = kms.generate_data_key(
KeyId=kms_key_id,
KeySpec='AES_256'
)
data_key = response['Plaintext']
encrypted_key = response['CiphertextBlob']
# Encrypt the data with the data key
f = Fernet(base64.urlsafe_b64encode(data_key))
ciphertext = f.encrypt(plaintext.encode())
return ciphertext, encrypted_key
def decrypt_column(ciphertext, encrypted_key):
kms = boto3.client('kms')
response = kms.decrypt(CiphertextBlob=encrypted_key)
data_key = response['Plaintext']
f = Fernet(base64.urlsafe_b64encode(data_key))
return f.decrypt(ciphertext).decode()
Key Management for Encryption at Rest
* Use a dedicated key management service (AWS KMS, Azure Key Vault, GCP Cloud KMS).
* Separate encryption keys by environment (dev, staging, production).
* Enable automatic key rotation (yearly or more frequently).
* Implement key access auditing to detect unauthorized use.
Encryption in Transit
Encryption in transit protects data as it travels between the database and clients.
TLS Configuration
-- PostgreSQL: Require TLS for all connections
-- In 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'
-- In pg_hba.conf
# Require TLS for all connections
hostssl all all 0.0.0.0/0 md5
# MySQL: Require TLS
[mysqld]
require_secure_transport = ON
ssl_ca = /etc/ssl/certs/ca.pem
ssl_cert = /etc/ssl/certs/server-cert.pem
ssl_key = /etc/ssl/private/server-key.pem
**Best practices**:
* Enforce TLS for all database connections.
* Use TLS 1.2 or 1.3. Disable older versions.
* Validate certificates on both client and server.
* Rotate certificates before expiry.
* Use client certificates for mutual TLS authentication.
Row-Level Security (RLS)
RLS restricts which rows a user can access based on a policy. It implements multi-tenancy and data isolation at the database level.
PostgreSQL Row-Level Security
-- Create a table with RLS
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
tenant_id INT NOT NULL,
customer_name TEXT,
amount DECIMAL(10,2)
);
-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Create a policy: users can only see their tenant's orders
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::INT);
-- Grant access to the table
GRANT SELECT, INSERT ON orders TO app_user;
Row-Level Security in Other Databases
-- SQL Server: Row-level security with security predicates
CREATE FUNCTION dbo.tenantAccessPredicate(@tenant_id INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS access_result
WHERE @tenant_id = CAST(SESSION_CONTEXT(N'tenant_id') AS INT);
CREATE SECURITY POLICY dbo.tenantSecurityPolicy
ADD FILTER PREDICATE dbo.tenantAccessPredicate(tenant_id)
ON dbo.orders;
RLS ensures that even if a query accesses rows it should not, the database engine filters them out automatically.
Audit Logging
Audit logging records database activity for security analysis and compliance.
What to Log
* All DDL statements (CREATE, ALTER, DROP).
* Failed login attempts.
* Privilege changes (GRANT, REVOKE).
* Access to sensitive tables or columns.
* Data export operations.
* Schema changes.
PostgreSQL Audit with pgaudit
-- Install pgaudit extension
CREATE EXTENSION pgaudit;
-- Configure audit logging in postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write,ddl,role,function'
pgaudit.log_level = 'notice'
pgaudit.log_relation = on
pgaudit.log_statement_once = off
MySQL Audit Log
-- MySQL Enterprise Audit
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';
-- Query audit logs
SELECT * FROM mysql.audit_log_table
WHERE timestamp >= NOW() - INTERVAL 1 HOUR
AND status = 0 -- Failed operations
ORDER BY timestamp DESC;
Centralized Audit Log Collection
Forward database audit logs to a SIEM for analysis and alerting.
# Filebeat configuration for shipping database audit logs
filebeat.inputs:
- type: log
paths:
- /var/log/postgresql/pgaudit.log
fields:
service: postgresql
environment: production
output.elasticsearch:
hosts: ["https://elasticsearch.example.com:9200"]
Network Isolation
Network isolation limits which systems can reach the database.
VPC and Subnet Design
Place databases in private subnets with no direct internet access. Only application servers in the same VPC should connect.
# Terraform: Database in private subnet
resource "aws_subnet" "db_private" {
vpc_id = aws_vpc.main.id
cidr_block = "10.0.3.0/24"
tags = {
Name = "db-private"
Tier = "database"
}
}
resource "aws_security_group" "db" {
name_prefix = "db-sg-"
vpc_id = aws_vpc.main.id
ingress {
from_port = 5432
to_port = 5432
protocol = "tcp"
security_groups = [aws_security_group.app.id]
description = "PostgreSQL from app servers"
}
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
}
Database Firewall Rules
-- PostgreSQL: Restrict listen address
-- In postgresql.conf
listen_addresses = '10.0.1.0,10.0.2.0'
Least Privilege Access
Apply the principle of least privilege to database access.
Separate Roles by Function
-- Create separate roles for different access patterns
CREATE ROLE read_only;
CREATE ROLE read_write;
CREATE ROLE admin;
-- Grant minimal permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
-- Grant roles to users
GRANT read_only TO reporting_user;
GRANT read_write TO app_user;
GRANT admin TO dba_team;
Application Users vs Admin Users
* Application connections use a dedicated role with only the permissions needed for the application.
* Admin access uses separate accounts with MFA and is only available through a bastion host or VPN.
* Application users should never have DDL permissions.
Secret Rotation
Database credentials need regular rotation. Compromised credentials that are never rotated become permanent backdoors.
Automated Rotation with HashiCorp Vault
# Vault database secrets engine configuration
path "database/creds/my-app" {
capabilities = ["read"]
}
# Dynamic database credentials with Vault
import hvac
client = hvac.Client(url='https://vault.example.com')
client.auth.approle('my-role-id', 'my-secret-id')
# Get short-lived database credentials
creds = client.secrets.database.generate_credentials(
name='my-app'
)
db_user = creds['data']['username'] # v-token-myapp-a1b2c3...
db_pass = creds['data']['password'] # random password
# These credentials expire in 1 hour
**Rotation frequency**:
* Application credentials: Every 24-48 hours (dynamic).
* Admin credentials: Every 90 days.
* Service account credentials: Every 30 days.
* Encryption keys: Annually.
Security Hardening Checklist
* [ ] Encryption at rest enabled for all databases.
* [ ] TLS 1.2+ enforced for all connections.
* [ ] Network isolation: databases in private subnets.
* [ ] Row-level security configured for multi-tenant tables.
* [ ] Audit logging enabled and forwarded to SIEM.
* [ ] Least privilege roles applied for all access patterns.
* [ ] Secret rotation automated via Vault or equivalent.
* [ ] Regular vulnerability scanning of database instances.
* [ ] Database software updated with latest patches.
* [ ] Default admin accounts disabled or renamed.
* [ ] Unused extensions and features removed.
* [ ] Connection limits set per user to prevent resource exhaustion.
Conclusion
Database security requires defense in depth. Encrypt data at rest and in transit. Isolate databases on private networks. Implement row-level security and audit logging. Follow least privilege for database roles. Automate secret rotation. A well-secured database makes it significantly harder for attackers to exfiltrate sensitive data, even if they breach other parts of the infrastructure.