Database Security Hardening

Database Security Hardening is the process of strengthening a database system against threats at every layer—network, transport, storage, authentication, authorization, and query execution. It matters when data is valuable, sensitive, or subject to regulatory scrutiny: during deployment of a new service, after a security incident, or as part of a compliance audit. This guide details the actions, configuration, and validation steps required to secure a database system from initial setup through ongoing operations.

Network-Level Security

Protect the database’s exposure to the network. The database should be accessible only to trusted services and users, with minimal attack surface.

Bind to Specific Interfaces and Ports

Configure the database to listen only on designated network interfaces and ports. Use listen_addresses in PostgreSQL, bind in MySQL, or network.bind in CockroachDB.

listen_addresses = '192.168.1.10,10.0.0.100'  # Only two IPs
port = 5433

Ensure firewalls restrict access to only the required ports and IPs.

Use a Reverse Proxy or Gateway

Place a reverse proxy (e.g., NGINX, HAProxy) in front of the database. This centralizes TLS, rate limiting, and access logging.

# NGINX configuration for PostgreSQL
upstream db_backend {
    server 192.168.1.10:5433;
    keepalive 32;
}

server {
    listen 443 ssl;
    server_name db.example.com;

    ssl_certificate_file /etc/ssl/certs/db.crt;
    ssl_certificate_key_file /etc/ssl/private/db.key;

    location / {
        proxy_pass http://db_backend;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_cache_bypass $http_upgrade;
    }
}

Enable session reuse and TLS 1.3 with strong ciphers.

Enforce TLS with Certificate Validation

Require all clients to present a valid TLS certificate. Use ssl_require_peer in PostgreSQL and ssl_verify_server_certificate in MySQL.

# PostgreSQL postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'
ssl_require_peer = on
ssl_min_protocol_version = 'TLSv1.2'

Validate client certificates against a trusted CA. Fail connections if the client’s certificate is not in the trusted set.

Restrict Access by IP and Application

Use firewall rules to allow only known application servers, load balancers, and monitoring agents to connect.

# iptables rule: only allow app servers and monitoring
sudo iptables -A INPUT -p tcp --dport 5433 \
    -s 192.168.1.0/24 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 5433 \
    -s 10.0.0.10 -j ACCEPT  # Prometheus
sudo iptables -A INPUT -p tcp --dport 5433 \
    -s 10.0.0.11 -j ACCEPT  # Grafana
sudo iptables -A INPUT -p tcp --dport 5433 \
    -j REJECT --reject-with icmp-port-unreachable

Authentication and Authorization

Secure how users and services access the database.

Configure Strong Authentication Methods

Use password, certificate, or SCRAM-SHA-256 authentication. Prefer SCRAM-SHA-256 over plain password.

-- Create user with SCRAM-SHA-256
CREATE USER analytics
    WITH PASSWORD 's3cr3t123'
    VALID UNTIL '2026-01-01'
    LOGIN
    ENCRYPTED PASSWORD
    SCRAM SHA256;

Require certificate-based authentication for high-privilege roles.

Use Role-Based Access Control (RBAC)

Define roles with granular privileges. Assign roles to users and groups.

-- Define roles
CREATE ROLE reporting;
CREATE ROLE etl;
CREATE ROLE admin;

-- Grant privileges
GRANT SELECT ON TABLE sales TO reporting;
GRANT INSERT ON TABLE sales TO etl;
GRANT ALL PRIVILEGES ON DATABASE analytics_db TO admin;

-- Assign roles
GRANT reporting TO user_a;
GRANT etl TO user_b;
GRANT admin TO dba_user;

-- Allow role inheritance
ALTER ROLE reporting INHERITS etl;

Implement Application-Level Authentication

Use JWT, OAuth2, or API keys to authenticate application clients. Store tokens in the application_name field of database connections.

{
  "application_name": "order-service",
  "application_user": "order-service",
  "jwt_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.xxxx.yyyy"
}

Use pg_hba.conf to map application names to roles via auth_method = jwt.

Enforce Password Policies

Apply password complexity, expiration, and lockout via database configuration and triggers.

-- Password policy: change password every 90 days
ALTER ROLE ALL
    SET password_expiration = '90 days';

-- Lock account after 5 failed attempts
CREATE FUNCTION lock_account() RETURNS TRIGGER AS $$
BEGIN
    UPDATE users
    SET login_attempts = login_attempts + 1,
        last_login_failure = now()
    WHERE username = NEW.username;

    IF (SELECT login_attempts FROM users WHERE username = NEW.username) >= 5 THEN
        UPDATE users SET locked = true WHERE username = NEW.username;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER lock_user_trigger
    AFTER INSERT ON login_attempts
    FOR EACH ROW EXECUTE FUNCTION lock_account();

Query and Data Layer Security

Protect data at the query level: from injection, privilege escalation, and unauthorized access.

Enable Row-Level Security (RLS)

Define policies that restrict access to rows based on user context.

-- Enable RLS on orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Define policy: users see only their orders
CREATE POLICY user_orders_policy ON orders
    FOR ALL
    USING (user_id = current_user::regrole)
    WITH CHECK (user_id = current_user::regrole);

-- Enable for specific roles
ALTER POLICY user_orders_policy ON orders
    TO analyst, manager;

Use current_user, current_role, or session_user in USING and WITH CHECK clauses.

Use Prepared Statements with Parameterized Queries

Eliminate SQL injection by using prepared statements.

-- Use prepared statements in PostgreSQL
PREPARE get_user_orders (int) AS
    SELECT * FROM orders
    WHERE user_id = $1
    ORDER BY created_at DESC
    LIMIT 10;

EXECUTE get_user_orders(42);

-- In application code
const query = 'PREPARE get_user_orders (int) AS SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2';
await client.query(query, [42, 10]);

Monitor and Log Query Activity

Log all queries, including execution time, user, and parameters.

# PostgreSQL: log slow queries
log_min_duration_statement = 500  -- log queries taking >500ms
log_statement = 'mod'  -- log DDL, DML, DCL
log_temp_files = 1024  -- log temp files >1MB
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

Use pg_stat_statements extension to track query performance.

-- Enable query statistics
CREATE EXTENSION pg_stat_statements;

-- View top 10 slowest queries
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Use Database-Level Encryption

Encrypt data at rest and in transit.

Transparent Data Encryption (TDE)

Use native database encryption.

-- Enable TDE in PostgreSQL with pgcrypto
CREATE EXTENSION pgcrypto;

-- Encrypt sensitive columns
ALTER TABLE users ADD COLUMN encrypted_ssn bytea;
UPDATE users
SET encrypted_ssn = pgp_encrypt(social_security_number::bytea, 'secret-key-123');

-- Decrypt on read
SELECT
    name,
    pgp_dearmor(encrypted_ssn) AS ssn
FROM users;

Column-Level Encryption

Use application-level encryption or database functions.

-- Use AES encryption in PostgreSQL
CREATE TABLE sensitive_data (
    id int PRIMARY KEY,
    data jsonb,
    encrypted_data bytea
);

-- Encrypt data before insertion
INSERT INTO sensitive_data (id, data, encrypted_data)
VALUES (
    1,
    '{"name": "Alice", "ssn": "123-45-6789"}',
    pgp_sym_encrypt(
        '{
            "name": "Alice",
            "ssn": "123-45-6789"
        }'::jsonb::text,
        'application-encryption-key'
    )
);

Configuration and Operations

Ensure security settings are applied consistently and validated.

Use Configuration Management Tools

Define database configurations in code and apply them declaratively.

# Ansible playbook: secure PostgreSQL
- name: Secure PostgreSQL
  hosts: databases
  become: yes
  vars:
    postgresql_conf:
      listen_addresses: '192.168.1.10,10.0.0.100'
      port: 5433
      ssl: true
      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_require_peer: true
      log_min_duration_statement: 500
      log_statement: 'mod'
      log_line_prefix: '%t [%p]: user=%u,db=%d,app=%a,client=%h'

  tasks:
    - name: Install PostgreSQL
      apt:
        name: postgresql-15
        state: present

    - name: Configure PostgreSQL
      template:
        src: postgresql.conf.j2
        dest: /etc/postgresql/15/main/postgresql.conf
      notify: restart postgresql

    - name: Ensure user roles exist
      postgresql_user:
        name: "{{ item.name }}"
        password: "{{ item.password }}"
        role_attr_flags: "{{ item.role_attr_flags }}"
        state: present
      loop: "{{ db_users }}"

  handlers:
    - name: restart postgresql
      systemd:
        name: postgresql
        state: restarted
        enabled: yes

Validate Security Configuration

Write scripts to verify that security settings are applied.

#!/bin/bash
# check-db-security.sh

set -euo pipefail

DB_HOST="db.example.com"
DB_PORT=5433
DB_NAME="analytics_db"
DB_USER="monitor"

# Check if SSL is required
if ! psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -c "SHOW ssl_require_peer" | grep -q 'on'; then
    echo "ERROR: SSL requirement is not enabled"
    exit 1
fi

# Check if all required roles exist
REQUIRED_ROLES=("reporting" "etl" "admin")
for role in "${REQUIRED_ROLES[@]}"; do
    if ! psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -c "SELECT 1 FROM pg_roles WHERE rolname = '$role'" | grep -q '1'; then
        echo "ERROR: Role $role is missing"
        exit 1
    fi
done

# Check row-level security on orders table
if ! psql -h "$DB_HOST" -p "$DB_PORT" -d "$DB_NAME" -U "$DB_USER" -c "SELECT pg_is_other_temp_table('orders')" | grep -q 't'; then
    echo "ERROR: Row-level security not enabled on orders table"
    exit 1
fi

echo "All security checks passed"

Monitor for Security Events

Set up alerts for common failure modes.

• Connection failure: pg_is_in_recovery() returns true during replication lag.
• Authentication failure: login_attempts table exceeds threshold.
• Slow query: total_time > 1s in pg_stat_statements.
• TDE key rotation: check that pg_pkey table has updated keys.

Use systemctl or crontab to run health checks.

# Run security checks every 15 minutes
*/15 * * * * /opt/scripts/check-db-security.sh

Failure Modes and Common Pitfalls

• TLS handshake failure: client presents invalid certificate, server rejects it. Verify sslmode and sslrootcert in connection strings.
• Role inheritance breaks: a user gains unexpected access when a role is added to another role, but INHERITS is not set.
• RLS policies not applied: USING clause returns NULL for a user, so all rows are visible; fix with COALESCE(current_user, 'anonymous').
• Prepared statement not reused: application prepares statement every request, but PREPARE is not idempotent.
• Column-level encryption fails silently: application uses wrong key or encoding; pgp_decrypt returns NULL due to mismatched pgp_encrypt parameters.

Security hardening is not a one-time task. It must be part of the database lifecycle: from provisioning, to deployment, to daily operations.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.