Database Management SystemsDBA Responsibilities

Database Administrator Responsibilities

LevelAdvanced

Duration90 mins

TopicDBA Responsibilities

3 / 5

Security Management

Guardians of the Data

Databases are the crown jewels of modern organizations. They hold customer information, financial records, intellectual property, and operational data that represents immense value—value that makes them prime targets for attackers. Database security management is the discipline that protects these assets from theft, tampering, and unauthorized exposure.

The stakes are enormous. Data breaches result in regulatory fines, legal liability, reputational damage, and lost customer trust. The average cost of a data breach exceeds $4 million, with some incidents costing hundreds of millions. Yet despite these consequences, many databases remain inadequately secured—default passwords unchanged, sensitive data unencrypted, access controls overly permissive.

As a Database Administrator, security is not merely a technical requirement—it's a professional and ethical obligation. You are the guardian of data that people have entrusted to your organization.

What You Will Learn

By the end of this page, you will understand the comprehensive security responsibilities of a DBA, including authentication mechanisms, authorization and access control, encryption strategies, auditing and compliance, security hardening, and incident response. You'll learn the defense-in-depth approach that protects databases against diverse threat vectors.

The Database Threat Landscape

Understanding security starts with understanding threats. Databases face attacks from multiple directions, each requiring different defensive measures.

External Threats:

External Threat Vectors

•SQL Injection — Attackers insert malicious SQL through application inputs. Remains one of the most common and dangerous attack vectors. Proper parameterization is the defense.
•Credential Attacks — Brute force, password spraying, and credential stuffing to gain unauthorized access. Strong authentication and lockout policies are essential.
•Network Attacks — Man-in-the-middle attacks, packet sniffing on unencrypted connections. TLS encryption prevents interception.
•Ransomware — Encryption of database files with ransom demands. Backup integrity and network segmentation are key defenses.
•Vulnerability Exploitation — Attacks against unpatched database software vulnerabilities. Regular patching is non-negotiable.

Internal Threats:

Many breaches originate from within the organization—intentionally or accidentally:

Internal Threat Categories
Threat Type	Description	Mitigation
Malicious Insider	Employee intentionally stealing or sabotaging data	Least privilege, monitoring, audit logging
Negligent Insider	Accidental exposure through misconfiguration or carelessness	Training, process controls, configuration management
Compromised Credentials	Legitimate account hijacked by external attacker	MFA, behavioral analysis, session monitoring
Excessive Privilege	Users with more access than needed for their role	Regular access reviews, role-based access control
Shadow IT	Unauthorized database instances with no security controls	Asset discovery, governance policies

The Insider Threat Reality

Studies consistently show that insider threats—whether malicious or negligent—account for a significant portion of data breaches. Organizations often focus security efforts outward while neglecting internal controls. Defense in depth must protect against insiders as well as external attackers.

The Defense-in-Depth Principle:

Effective database security employs multiple layers of protection. If one layer fails, others remain:

Network Security: Firewalls, segmentation, VPNs
Authentication: Verification of user identity
Authorization: Enforcement of access permissions
Encryption: Protection of data at rest and in transit
Auditing: Detection and investigation of anomalies
Monitoring: Real-time threat detection
Backup: Recovery from compromise

No single measure provides complete protection; the layers work together to minimize risk.

Authentication

Authentication answers the question: "Who are you?" Before granting any access, the database must verify that the connecting user or application is who they claim to be.

Authentication Methods:

Databases support various authentication mechanisms with different security profiles:

Database Authentication Methods
Method	Security Level	Description	Use Case
Trust	None	No authentication; dangerous	Never in production
Password (MD5)	Low	Password hashed with MD5	Legacy systems only
SCRAM-SHA-256	Good	Modern password-based auth	Standard production use
Certificate	High	TLS client certificates	Service-to-service, high security
LDAP/Active Directory	Good	Centralized identity management	Enterprise environments
Kerberos/GSSAPI	High	Single sign-on, ticket-based	Enterprise Windows environments
OAuth/OIDC	Good	Token-based modern auth	Cloud-native applications

pg_hba.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# PostgreSQL Host-Based Authentication Configuration
# TYPE    DATABASE    USER        ADDRESS             METHOD
 
# Reject all connections by default (defense in depth)
# Explicit rules below override this
 
# Local socket: OS authentication for postgres admin
local     all         postgres                        peer
 
# Local socket: password for applications
local     appdb       appuser                         scram-sha-256
 
# Localhost IPv4: development connections
host      all         all         127.0.0.1/32        scram-sha-256
 
# Application servers: specific subnet
host      appdb       appuser     10.0.2.0/24         scram-sha-256
 
# Replication: certificate authentication for standbys
hostssl   replication replicator  10.0.1.100/32       cert
hostssl   replication replicator  10.0.1.101/32       cert
 
# Admin access: require certificate from jump host
hostssl   all         admin       10.0.0.5/32         cert
 
# Reject all other connections (explicit)
host      all         all         0.0.0.0/0           reject
hostssl   all         all         0.0.0.0/0           reject

Password Policies:

When using password authentication, enforce strong password policies:

Password Policy Requirements

•Minimum Length: 14+ characters for service accounts, 12+ for humans
•Complexity Requirements: Mix of character types, but prioritize length over complexity
•No Common Passwords: Check against breach databases and common password lists
•Regular Rotation: 90 days for privileged accounts, though modern guidance favors longer intervals with monitoring
•No Password Reuse: Prevent reusing recent passwords
•Account Lockout: Lock accounts after failed attempts (but be cautious of denial-of-service)

password-management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- PostgreSQL: Create role with password and expiration
CREATE ROLE appuser WITH 
    LOGIN 
    PASSWORD 'SecureP@ssw0rd!'
    VALID UNTIL '2025-06-01'    -- Password expiration
    CONNECTION LIMIT 100;        -- Limit concurrent connections
 
-- Check password strength extension
CREATE EXTENSION IF NOT EXISTS passwordcheck;
 
-- MySQL: Password policy configuration
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.check_user_name = ON;
 
-- Create user with password expiration
CREATE USER 'appuser'@'10.0.2.%' 
    IDENTIFIED BY 'SecureP@ssw0rd!'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;  -- Lock for 1 day after failures

Use Secrets Management

Application passwords should never be hardcoded or stored in configuration files. Use secrets management tools like HashiCorp Vault, AWS Secrets Manager, or Azure Key Vault to securely store and rotate database credentials. Applications retrieve credentials at runtime from the secrets manager.

Authorization and Access Control

Authorization answers the question: "What are you allowed to do?" After authenticating, the database determines what actions the user can perform and on which objects.

The Principle of Least Privilege:

This fundamental security principle states that every user should have only the minimum permissions necessary to perform their job function. Excess permissions create unnecessary risk:

An application that only reads data shouldn't have write access
A developer who works on one schema shouldn't access other schemas
A report user who needs aggregated data shouldn't see individual records

Common Database Privileges
Privilege	Scope	Risk Level	Typical Grant
SELECT	Table/View	Low	Read-only applications, analysts
INSERT/UPDATE/DELETE	Table	Medium	Application service accounts
TRUNCATE	Table	High	ETL processes, carefully controlled
CREATE	Schema/Database	High	Developers, schema migration tools
DROP	Any object	Critical	Rarely; use with extreme caution
GRANT	Any	Critical	Security administrators only
SUPERUSER/DBA	Everything	Maximum	DBAs only, avoid using routinely

Role-Based Access Control (RBAC):

Rather than assigning permissions directly to users, define roles that represent job functions and assign users to roles:

rbac-implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- PostgreSQL RBAC Implementation
 
-- Create functional roles (not login roles)
CREATE ROLE app_readonly;          -- Read-only access
CREATE ROLE app_readwrite;         -- Read and write access
CREATE ROLE schema_developer;      -- Schema modification
CREATE ROLE security_admin;        -- Security management
 
-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;
 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public 
    TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
 
GRANT CREATE ON SCHEMA public TO schema_developer;
GRANT app_readwrite TO schema_developer;  -- Inherit application access
 
-- Ensure future objects inherit permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
 
-- Create login roles and assign functional roles
CREATE ROLE api_service WITH LOGIN PASSWORD 'ServiceP@ssword!';
GRANT app_readwrite TO api_service;
 
CREATE ROLE report_user WITH LOGIN PASSWORD 'ReportP@ssword!';
GRANT app_readonly TO report_user;
 
CREATE ROLE developer_jane WITH LOGIN PASSWORD 'DevP@ssword!';
GRANT schema_developer TO developer_jane;
 
-- Row-Level Security for fine-grained access
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY customer_region_policy ON customer_data
    FOR ALL
    TO app_readwrite
    USING (region = current_setting('app.current_region'));

Row-Level Security (RLS):

For applications requiring fine-grained access control, row-level security restricts which rows users can see or modify based on policy conditions. This is powerful for multi-tenant applications where users should only access their own data.

Avoid the Superuser Trap

Many databases are managed using superuser accounts for routine tasks. This is dangerous—any mistake or compromise has unlimited impact. Create separate accounts for different functions: one for routine maintenance (limited privileges), another for emergency access (superuser). Log and monitor all superuser activity.

Access Control Best Practices

•Use Roles, Not Direct Grants: Assign permissions to roles, users to roles. Easier to audit and modify.
•Separate Application Accounts: Each application should have its own database account with specific permissions.
•Regular Access Reviews: Quarterly review of who has access to what. Remove unused accounts.
•Revoke Default Permissions: Many databases grant public access by default. Remove these grants.
•Document All Grants: Maintain a record of why each permission was granted and who approved it.

Encryption

Encryption protects data from unauthorized access even if other security layers fail. It operates at two levels: data in transit (network communications) and data at rest (stored data).

Encryption in Transit (TLS/SSL):

All database connections should use TLS encryption to prevent eavesdropping and man-in-the-middle attacks:

postgresql-ssl.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# PostgreSQL SSL Configuration
 
# Enable SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgres.crt'
ssl_key_file = '/etc/ssl/private/postgres.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
 
# Use strong ciphers only (TLS 1.2+)
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'ECDHE+AESGCM:DHE+AESGCM:ECDHE+CHACHA20:DHE+CHACHA20'
 
# Prefer server cipher order
ssl_prefer_server_ciphers = on
 
# Enable certificate verification for clients
ssl_client_cert_required = on  # For high-security environments

Encryption at Rest:

Data at rest encryption protects stored data files from unauthorized access if storage media is compromised:

Encryption at Rest Options
Level	Description	Pros	Cons
Full Disk Encryption	Encrypt entire disk/volume (LUKS, BitLocker)	Simple, transparent to database	Only protects against physical theft
Tablespace Encryption	Database encrypts data files (TDE)	Protects against file access	Key management complexity
Column Encryption	Encrypt specific columns	Protects even from DBAs	Query limitations, performance impact
Application Encryption	Application encrypts before storage	Maximum protection, database-agnostic	Cannot query encrypted data

encryption-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- PostgreSQL: Column-level encryption with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
-- Encrypt sensitive data before storing
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    ssn_encrypted BYTEA,  -- Encrypted SSN
    email VARCHAR(255)
);
 
-- Insert with encryption
INSERT INTO customers (name, ssn_encrypted, email)
VALUES (
    'John Doe',
    pgp_sym_encrypt('123-45-6789', 'encryption_key_from_env'),
    'john@example.com'
);
 
-- Query with decryption (requires key)
SELECT name, 
       pgp_sym_decrypt(ssn_encrypted, 'encryption_key_from_env') AS ssn
FROM customers
WHERE id = 1;
 
-- MySQL: Transparent Data Encryption (TDE)
-- Enable in my.cnf: early-plugin-load=keyring_file.so
 
-- Create encrypted tablespace
CREATE TABLESPACE encrypted_space
    ADD DATAFILE 'encrypted_space.ibd'
    ENCRYPTION='Y';
 
-- Create table in encrypted tablespace
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) TABLESPACE encrypted_space;

Key Management is Critical

Encryption is only as strong as key management. Keys stored in plaintext configuration files provide no real protection. Use Hardware Security Modules (HSMs), cloud key management (AWS KMS, Azure Key Vault), or dedicated key management systems. Implement key rotation and secure backup of key material.

Data Masking:

For non-production environments, data masking provides sensitive data protection without full encryption:

Static Masking: Create masked copies of production data for development/testing
Dynamic Masking: Database applies masking rules in real-time based on user permissions

-- PostgreSQL: Simple dynamic masking with views
CREATE VIEW customer_masked AS
SELECT 
    id,
    name,
    CASE WHEN current_user = 'admin' 
         THEN ssn 
         ELSE 'XXX-XX-' || right(ssn, 4) 
    END AS ssn,
    regexp_replace(email, '(.{2})(.*)(@.*)', '\1***\3') AS email
FROM customers;

Auditing and Compliance

Auditing provides the evidence trail necessary for security investigation, compliance verification, and accountability. It answers the crucial questions: What happened? When? Who did it?

What to Audit:

Database Audit Categories
Category	Events to Capture	Retention	Use Case
Authentication	Logins, logouts, failed attempts	90 days+	Security investigation
Authorization	Permission grants, role changes	2 years+	Compliance, access review
DDL Statements	CREATE, ALTER, DROP	2 years+	Change tracking
DML on Sensitive Data	INSERT/UPDATE/DELETE on key tables	Varies by regulation	Compliance, forensics
SELECT on Sensitive Data	Queries accessing sensitive columns	90 days+	Data access monitoring
Administrative Actions	Configuration changes, maintenance	2 years+	Operations audit

postgresql-audit.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# PostgreSQL Audit Configuration with pgaudit extension
 
# Load the extension
shared_preload_libraries = 'pgaudit'
 
# Basic logging configuration
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'audit-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 0
 
# pgAudit settings
pgaudit.log = 'ddl, role, misc_set'     # Log DDL, role changes, SET commands
pgaudit.log_catalog = on                 # Include system catalog access
pgaudit.log_client = off                 # Don't show audit in client output
pgaudit.log_level = log                  # Log level for audit messages
pgaudit.log_statement_once = off         # Log statement for each substatement
 
# For sensitive tables, configure object-level audit
# (Done via GRANT to audit role)

audit-setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- PostgreSQL: pgaudit object-level auditing
 
CREATE EXTENSION IF NOT EXISTS pgaudit;
 
-- Create audit role
CREATE ROLE auditor NOINHERIT;
 
-- Grant audit permission on sensitive tables
-- Any query by auditor or against these objects will be logged
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_pii TO auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON financial_transactions TO auditor;
 
-- Set audit role for the database
ALTER DATABASE production SET pgaudit.role = 'auditor';
 
-- Custom audit trigger for detailed logging
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    client_ip INET
);
 
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, 
                          changed_by, client_ip)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD) END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW) END,
        current_user,
        inet_client_addr()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER audit_customer_pii
    AFTER INSERT OR UPDATE OR DELETE ON customer_pii
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Compliance Requirements:

Various regulations mandate specific database security and audit controls:

Common Compliance Frameworks

•GDPR (EU): Right to access, right to erasure, data protection by design. Requires tracking of personal data access.
•HIPAA (US Healthcare): Audit controls, access management, encryption for electronic Protected Health Information (ePHI).
•PCI-DSS (Payment Card): Strong access control, encryption of cardholder data, audit trails, regular security testing.
•SOX (Financial): Controls over financial reporting data, audit trails, access controls, segregation of duties.
•SOC 2 (Service Organizations): Security, availability, processing integrity, confidentiality, privacy controls.

Protect the Audit Trail

Audit logs are useless if attackers can modify or delete them. Store audit logs separately from the database being audited. Use write-once storage, ship logs to a central SIEM in real-time, and ensure DBAs cannot modify historical audit records.

Security Hardening

Security hardening reduces the attack surface by removing unnecessary functionality, applying secure defaults, and implementing defense-in-depth measures.

Hardening Checklist:

Database Hardening Steps

•Change Default Ports — While security through obscurity isn't sufficient alone, changing default ports reduces automated scanning success.
•Rename/Disable Default Accounts — Remove sample users, rename obvious accounts (postgres, sa, root).
•Remove Sample Databases — Delete template databases and example schemas that may contain vulnerabilities.
•Disable Unused Features — Turn off utilities, extensions, and services not required for your workload.
•Restrict Network Binding — Listen only on required interfaces; never bind to 0.0.0.0 without firewall.
•Secure File Permissions — Data files, configuration files, and logs should have restrictive permissions.
•Remove Dangerous Functions — Limit access to functions that can execute OS commands or access the filesystem.
•Apply Security Patches Promptly — Subscribe to security advisories; patch critical vulnerabilities immediately.

hardening-script.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#!/bin/bash
# PostgreSQL Hardening Script
 
# Secure file permissions
chmod 700 /var/lib/postgresql/data
chmod 600 /var/lib/postgresql/data/*.conf
chown -R postgres:postgres /var/lib/postgresql/data
 
# Remove unnecessary extensions
psql -c "DROP EXTENSION IF EXISTS adminpack;"
psql -c "DROP EXTENSION IF EXISTS file_fdw;"
 
# Revoke public permissions
psql -c "REVOKE ALL ON SCHEMA public FROM PUBLIC;"
psql -c "REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;"
psql -c "REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC;"
 
# Restrict dangerous functions
psql -c "REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;"
psql -c "REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;"
 
# Enable connection limits
psql -c "ALTER USER postgres CONNECTION LIMIT 2;"
 
# Configure password encryption
psql -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256';"
 
echo "Hardening complete. Restart PostgreSQL and verify access."

Network Segmentation:

Database servers should be isolated in dedicated network segments:

Converting Mermaid diagram...

The Patch Management Dilemma

DBAs often delay patches fearing downtime or compatibility issues. This creates a dangerous window where known vulnerabilities remain exploitable. Implement a patch management process: test patches in non-production, schedule maintenance windows, have rollback procedures ready. The risk of not patching exceeds the risk of patching.

Vulnerability Assessment

Regular vulnerability assessment identifies security weaknesses before attackers exploit them. This includes both automated scanning and manual review.

Assessment Types:

Database Security Assessment Methods
Method	Description	Frequency	Who Performs
Configuration Audit	Review settings against security benchmarks	Monthly/After changes	DBA / Security Team
Vulnerability Scan	Automated scanning for known vulnerabilities	Monthly minimum	Security Team / Automated
Penetration Test	Simulated attack to test defenses	Annually / After major changes	External specialists
Access Review	Verify user permissions are appropriate	Quarterly	DBA / Data Owners
Code Review	Review stored procedures, triggers for security issues	On change	Developers / Security

CIS Benchmarks:

The Center for Internet Security (CIS) publishes detailed security benchmarks for major databases. These provide authoritative guidance for secure configuration:

Key CIS Benchmark Areas

•Installation and Patches: Version currency, patch level
•Authentication and Authorization: Password policies, privilege management
•Auditing and Logging: Audit configuration, log protection
•Encryption: TLS configuration, data-at-rest encryption
•Network: Binding, firewall, network access control
•Operating System: File permissions, process isolation

security-assessment.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL Security Assessment Queries
 
-- Check for users without passwords (should be none)
SELECT usename, passwd 
FROM pg_shadow 
WHERE passwd IS NULL OR passwd = '';
 
-- Find users with superuser privilege
SELECT usename, usesuper 
FROM pg_user 
WHERE usesuper = true;
 
-- Check default search path (shouldn't include untrusted schemas)
SELECT usename, useconfig 
FROM pg_user 
WHERE useconfig::text LIKE '%search_path%';
 
-- Review tables accessible to PUBLIC
SELECT n.nspname, c.relname, c.relacl
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND c.relacl::text LIKE '%=r%';  -- PUBLIC has read access
 
-- Check for unencrypted connections (require ssl)
SELECT datname, usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE ssl = false AND client_addr IS NOT NULL;
 
-- Verify password encryption method
SHOW password_encryption;
-- Should return 'scram-sha-256'
 
-- Check log settings for security
SELECT name, setting 
FROM pg_settings 
WHERE name LIKE 'log%' 
   OR name LIKE '%audit%'
ORDER BY name;

Automate Assessments

Security assessments should be automated and run continuously through CI/CD pipelines. Tools like ScubaDB (PostgreSQL), MySQLTuner security checks, and database security scanners can identify configuration drift and misconfigurations automatically.

Incident Response

Despite best efforts, security incidents may occur. Having a prepared incident response plan minimizes damage and accelerates recovery.

Incident Response Phases:

Incident Response Process

•Detection — Identify that an incident has occurred through monitoring, alerts, or reports. Time to detection is critical.
•Containment — Limit the damage by isolating affected systems. May include disconnecting from network, disabling accounts.
•Evidence Preservation — Before making changes, capture evidence: logs, snapshots, memory dumps. Chain of custody for legal proceedings.
•Eradication — Remove the threat: patch vulnerabilities, remove malware, close unauthorized access.
•Recovery — Restore normal operations from clean backups. Verify integrity of restored data.
•Post-Incident Review — Analyze what happened, why detection/prevention failed, what improvements to implement.

incident-response-playbook.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Database Incident Response Queries
 
-- CONTAINMENT: Terminate all sessions from suspicious source
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE client_addr = '192.168.1.100';  -- Suspicious IP
 
-- CONTAINMENT: Disable compromised account immediately
ALTER USER compromised_account WITH NOLOGIN;
 
-- CONTAINMENT: Revoke all privileges
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public 
    FROM compromised_account;
 
-- EVIDENCE: Capture current session state
COPY (
    SELECT pid, usename, client_addr, application_name,
           query_start, state, query
    FROM pg_stat_activity
) TO '/tmp/incident_sessions.csv' WITH CSV HEADER;
 
-- EVIDENCE: Preserve recent authentication logs
-- (Also preserve OS-level logs before rotation)
SELECT * FROM pg_stat_activity 
WHERE state = 'active' OR state = 'idle in transaction';
 
-- ANALYSIS: Recent DDL changes
SELECT * FROM pg_stat_user_tables
WHERE n_tup_del > 0 OR n_tup_upd > 0
ORDER BY greatest(last_vacuum, last_autovacuum) DESC NULLS LAST;
 
-- ANALYSIS: Check for unauthorized users or role changes
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb,
       rolvaliduntil
FROM pg_roles
ORDER BY oid DESC  -- Recently created
LIMIT 20;

Don't Panic, Don't Improvise

During a security incident, the pressure to 'do something' leads to mistakes. Follow the documented incident response plan. Hasty actions can destroy evidence, extend damage, or create new vulnerabilities. Have the plan ready before you need it.

Communication:

Incident response includes communication responsibilities:

Internal escalation: Security team, management, legal, communications
External notification: Regulatory bodies (GDPR requires 72-hour notification), affected customers
Documentation: Maintain detailed timeline and actions taken for post-incident review and potential legal proceedings

Summary

Database security management is a continuous responsibility that protects organizational assets, maintains compliance, and builds customer trust. It requires vigilance, systematic processes, and defense-in-depth thinking.

Key Takeaways:

Security Management Essentials

•Understand the threat landscape. Databases face external attacks (SQL injection, ransomware) and internal threats (malicious/negligent insiders). Defend against both.
•Implement strong authentication. Use modern methods (SCRAM-SHA-256, certificates), enforce password policies, implement secrets management.
•Apply least privilege rigorously. Users and applications should have minimum required permissions. Use roles, not direct grants.
•Encrypt everywhere. TLS for connections, encryption at rest for sensitive data, proper key management.
•Enable comprehensive auditing. Log authentication, authorization changes, and access to sensitive data. Protect audit logs.
•Maintain compliance. Understand applicable regulations (GDPR, HIPAA, PCI-DSS) and implement required controls.
•Harden the system. Remove defaults, disable unnecessary features, patch promptly, segment networks.
•Assess vulnerabilities regularly. Configuration audits, vulnerability scans, penetration tests, access reviews.
•Prepare for incidents. Have documented response plans, practice them, preserve evidence, communicate appropriately.

What's Next:

With security protecting the database from threats, the next critical DBA responsibility is Backup and Recovery—ensuring that data can be restored when hardware fails, data is corrupted, or disasters strike.

Page Complete

You now understand comprehensive database security management, from authentication and authorization through encryption, auditing, hardening, vulnerability assessment, and incident response. These skills protect the data entrusted to your organization and are essential for any DBA role.

3 / 5

Loading learning content...

Database Management SystemsDBA Responsibilities

Database Administrator Responsibilities

LevelAdvanced

Duration90 mins

TopicDBA Responsibilities

3 / 5

Security Management

Guardians of the Data

As a Database Administrator, security is not merely a technical requirement—it's a professional and ethical obligation. You are the guardian of data that people have entrusted to your organization.

What You Will Learn

The Database Threat Landscape

Understanding security starts with understanding threats. Databases face attacks from multiple directions, each requiring different defensive measures.

External Threats:

External Threat Vectors

•SQL Injection — Attackers insert malicious SQL through application inputs. Remains one of the most common and dangerous attack vectors. Proper parameterization is the defense.
•Credential Attacks — Brute force, password spraying, and credential stuffing to gain unauthorized access. Strong authentication and lockout policies are essential.
•Network Attacks — Man-in-the-middle attacks, packet sniffing on unencrypted connections. TLS encryption prevents interception.
•Ransomware — Encryption of database files with ransom demands. Backup integrity and network segmentation are key defenses.
•Vulnerability Exploitation — Attacks against unpatched database software vulnerabilities. Regular patching is non-negotiable.

Internal Threats:

Many breaches originate from within the organization—intentionally or accidentally:

Internal Threat Categories
Threat Type	Description	Mitigation
Malicious Insider	Employee intentionally stealing or sabotaging data	Least privilege, monitoring, audit logging
Negligent Insider	Accidental exposure through misconfiguration or carelessness	Training, process controls, configuration management
Compromised Credentials	Legitimate account hijacked by external attacker	MFA, behavioral analysis, session monitoring
Excessive Privilege	Users with more access than needed for their role	Regular access reviews, role-based access control
Shadow IT	Unauthorized database instances with no security controls	Asset discovery, governance policies

The Insider Threat Reality

The Defense-in-Depth Principle:

Effective database security employs multiple layers of protection. If one layer fails, others remain:

Network Security: Firewalls, segmentation, VPNs
Authentication: Verification of user identity
Authorization: Enforcement of access permissions
Encryption: Protection of data at rest and in transit
Auditing: Detection and investigation of anomalies
Monitoring: Real-time threat detection
Backup: Recovery from compromise

No single measure provides complete protection; the layers work together to minimize risk.

Authentication

Authentication answers the question: "Who are you?" Before granting any access, the database must verify that the connecting user or application is who they claim to be.

Authentication Methods:

Databases support various authentication mechanisms with different security profiles:

Database Authentication Methods
Method	Security Level	Description	Use Case
Trust	None	No authentication; dangerous	Never in production
Password (MD5)	Low	Password hashed with MD5	Legacy systems only
SCRAM-SHA-256	Good	Modern password-based auth	Standard production use
Certificate	High	TLS client certificates	Service-to-service, high security
LDAP/Active Directory	Good	Centralized identity management	Enterprise environments
Kerberos/GSSAPI	High	Single sign-on, ticket-based	Enterprise Windows environments
OAuth/OIDC	Good	Token-based modern auth	Cloud-native applications

pg_hba.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# PostgreSQL Host-Based Authentication Configuration
# TYPE    DATABASE    USER        ADDRESS             METHOD
 
# Reject all connections by default (defense in depth)
# Explicit rules below override this
 
# Local socket: OS authentication for postgres admin
local     all         postgres                        peer
 
# Local socket: password for applications
local     appdb       appuser                         scram-sha-256
 
# Localhost IPv4: development connections
host      all         all         127.0.0.1/32        scram-sha-256
 
# Application servers: specific subnet
host      appdb       appuser     10.0.2.0/24         scram-sha-256
 
# Replication: certificate authentication for standbys
hostssl   replication replicator  10.0.1.100/32       cert
hostssl   replication replicator  10.0.1.101/32       cert
 
# Admin access: require certificate from jump host
hostssl   all         admin       10.0.0.5/32         cert
 
# Reject all other connections (explicit)
host      all         all         0.0.0.0/0           reject
hostssl   all         all         0.0.0.0/0           reject

Password Policies:

When using password authentication, enforce strong password policies:

Password Policy Requirements

•Minimum Length: 14+ characters for service accounts, 12+ for humans
•Complexity Requirements: Mix of character types, but prioritize length over complexity
•No Common Passwords: Check against breach databases and common password lists
•Regular Rotation: 90 days for privileged accounts, though modern guidance favors longer intervals with monitoring
•No Password Reuse: Prevent reusing recent passwords
•Account Lockout: Lock accounts after failed attempts (but be cautious of denial-of-service)

password-management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- PostgreSQL: Create role with password and expiration
CREATE ROLE appuser WITH 
    LOGIN 
    PASSWORD 'SecureP@ssw0rd!'
    VALID UNTIL '2025-06-01'    -- Password expiration
    CONNECTION LIMIT 100;        -- Limit concurrent connections
 
-- Check password strength extension
CREATE EXTENSION IF NOT EXISTS passwordcheck;
 
-- MySQL: Password policy configuration
SET GLOBAL validate_password.length = 14;
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.check_user_name = ON;
 
-- Create user with password expiration
CREATE USER 'appuser'@'10.0.2.%' 
    IDENTIFIED BY 'SecureP@ssw0rd!'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;  -- Lock for 1 day after failures

Use Secrets Management

Authorization and Access Control

Authorization answers the question: "What are you allowed to do?" After authenticating, the database determines what actions the user can perform and on which objects.

The Principle of Least Privilege:

This fundamental security principle states that every user should have only the minimum permissions necessary to perform their job function. Excess permissions create unnecessary risk:

An application that only reads data shouldn't have write access
A developer who works on one schema shouldn't access other schemas
A report user who needs aggregated data shouldn't see individual records

Common Database Privileges
Privilege	Scope	Risk Level	Typical Grant
SELECT	Table/View	Low	Read-only applications, analysts
INSERT/UPDATE/DELETE	Table	Medium	Application service accounts
TRUNCATE	Table	High	ETL processes, carefully controlled
CREATE	Schema/Database	High	Developers, schema migration tools
DROP	Any object	Critical	Rarely; use with extreme caution
GRANT	Any	Critical	Security administrators only
SUPERUSER/DBA	Everything	Maximum	DBAs only, avoid using routinely

Role-Based Access Control (RBAC):

Rather than assigning permissions directly to users, define roles that represent job functions and assign users to roles:

rbac-implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- PostgreSQL RBAC Implementation
 
-- Create functional roles (not login roles)
CREATE ROLE app_readonly;          -- Read-only access
CREATE ROLE app_readwrite;         -- Read and write access
CREATE ROLE schema_developer;      -- Schema modification
CREATE ROLE security_admin;        -- Security management
 
-- Grant privileges to roles
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;
 
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public 
    TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
 
GRANT CREATE ON SCHEMA public TO schema_developer;
GRANT app_readwrite TO schema_developer;  -- Inherit application access
 
-- Ensure future objects inherit permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
 
-- Create login roles and assign functional roles
CREATE ROLE api_service WITH LOGIN PASSWORD 'ServiceP@ssword!';
GRANT app_readwrite TO api_service;
 
CREATE ROLE report_user WITH LOGIN PASSWORD 'ReportP@ssword!';
GRANT app_readonly TO report_user;
 
CREATE ROLE developer_jane WITH LOGIN PASSWORD 'DevP@ssword!';
GRANT schema_developer TO developer_jane;
 
-- Row-Level Security for fine-grained access
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY customer_region_policy ON customer_data
    FOR ALL
    TO app_readwrite
    USING (region = current_setting('app.current_region'));

Row-Level Security (RLS):

Avoid the Superuser Trap

Access Control Best Practices

•Use Roles, Not Direct Grants: Assign permissions to roles, users to roles. Easier to audit and modify.
•Separate Application Accounts: Each application should have its own database account with specific permissions.
•Regular Access Reviews: Quarterly review of who has access to what. Remove unused accounts.
•Revoke Default Permissions: Many databases grant public access by default. Remove these grants.
•Document All Grants: Maintain a record of why each permission was granted and who approved it.

Encryption

Encryption protects data from unauthorized access even if other security layers fail. It operates at two levels: data in transit (network communications) and data at rest (stored data).

Encryption in Transit (TLS/SSL):

All database connections should use TLS encryption to prevent eavesdropping and man-in-the-middle attacks:

postgresql-ssl.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# PostgreSQL SSL Configuration
 
# Enable SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgres.crt'
ssl_key_file = '/etc/ssl/private/postgres.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
 
# Use strong ciphers only (TLS 1.2+)
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'ECDHE+AESGCM:DHE+AESGCM:ECDHE+CHACHA20:DHE+CHACHA20'
 
# Prefer server cipher order
ssl_prefer_server_ciphers = on
 
# Enable certificate verification for clients
ssl_client_cert_required = on  # For high-security environments

Encryption at Rest:

Data at rest encryption protects stored data files from unauthorized access if storage media is compromised:

Encryption at Rest Options
Level	Description	Pros	Cons
Full Disk Encryption	Encrypt entire disk/volume (LUKS, BitLocker)	Simple, transparent to database	Only protects against physical theft
Tablespace Encryption	Database encrypts data files (TDE)	Protects against file access	Key management complexity
Column Encryption	Encrypt specific columns	Protects even from DBAs	Query limitations, performance impact
Application Encryption	Application encrypts before storage	Maximum protection, database-agnostic	Cannot query encrypted data

encryption-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- PostgreSQL: Column-level encryption with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
-- Encrypt sensitive data before storing
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    ssn_encrypted BYTEA,  -- Encrypted SSN
    email VARCHAR(255)
);
 
-- Insert with encryption
INSERT INTO customers (name, ssn_encrypted, email)
VALUES (
    'John Doe',
    pgp_sym_encrypt('123-45-6789', 'encryption_key_from_env'),
    'john@example.com'
);
 
-- Query with decryption (requires key)
SELECT name, 
       pgp_sym_decrypt(ssn_encrypted, 'encryption_key_from_env') AS ssn
FROM customers
WHERE id = 1;
 
-- MySQL: Transparent Data Encryption (TDE)
-- Enable in my.cnf: early-plugin-load=keyring_file.so
 
-- Create encrypted tablespace
CREATE TABLESPACE encrypted_space
    ADD DATAFILE 'encrypted_space.ibd'
    ENCRYPTION='Y';
 
-- Create table in encrypted tablespace
CREATE TABLE sensitive_data (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) TABLESPACE encrypted_space;

Key Management is Critical

Data Masking:

For non-production environments, data masking provides sensitive data protection without full encryption:

Static Masking: Create masked copies of production data for development/testing
Dynamic Masking: Database applies masking rules in real-time based on user permissions

-- PostgreSQL: Simple dynamic masking with views
CREATE VIEW customer_masked AS
SELECT 
    id,
    name,
    CASE WHEN current_user = 'admin' 
         THEN ssn 
         ELSE 'XXX-XX-' || right(ssn, 4) 
    END AS ssn,
    regexp_replace(email, '(.{2})(.*)(@.*)', '\1***\3') AS email
FROM customers;

Auditing and Compliance

Auditing provides the evidence trail necessary for security investigation, compliance verification, and accountability. It answers the crucial questions: What happened? When? Who did it?

What to Audit:

Database Audit Categories
Category	Events to Capture	Retention	Use Case
Authentication	Logins, logouts, failed attempts	90 days+	Security investigation
Authorization	Permission grants, role changes	2 years+	Compliance, access review
DDL Statements	CREATE, ALTER, DROP	2 years+	Change tracking
DML on Sensitive Data	INSERT/UPDATE/DELETE on key tables	Varies by regulation	Compliance, forensics
SELECT on Sensitive Data	Queries accessing sensitive columns	90 days+	Data access monitoring
Administrative Actions	Configuration changes, maintenance	2 years+	Operations audit

postgresql-audit.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# PostgreSQL Audit Configuration with pgaudit extension
 
# Load the extension
shared_preload_libraries = 'pgaudit'
 
# Basic logging configuration
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'audit-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 0
 
# pgAudit settings
pgaudit.log = 'ddl, role, misc_set'     # Log DDL, role changes, SET commands
pgaudit.log_catalog = on                 # Include system catalog access
pgaudit.log_client = off                 # Don't show audit in client output
pgaudit.log_level = log                  # Log level for audit messages
pgaudit.log_statement_once = off         # Log statement for each substatement
 
# For sensitive tables, configure object-level audit
# (Done via GRANT to audit role)

audit-setup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- PostgreSQL: pgaudit object-level auditing
 
CREATE EXTENSION IF NOT EXISTS pgaudit;
 
-- Create audit role
CREATE ROLE auditor NOINHERIT;
 
-- Grant audit permission on sensitive tables
-- Any query by auditor or against these objects will be logged
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_pii TO auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON financial_transactions TO auditor;
 
-- Set audit role for the database
ALTER DATABASE production SET pgaudit.role = 'auditor';
 
-- Custom audit trigger for detailed logging
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    client_ip INET
);
 
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data, 
                          changed_by, client_ip)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD) END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN row_to_json(NEW) END,
        current_user,
        inet_client_addr()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER audit_customer_pii
    AFTER INSERT OR UPDATE OR DELETE ON customer_pii
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

Compliance Requirements:

Various regulations mandate specific database security and audit controls:

Common Compliance Frameworks

•GDPR (EU): Right to access, right to erasure, data protection by design. Requires tracking of personal data access.
•HIPAA (US Healthcare): Audit controls, access management, encryption for electronic Protected Health Information (ePHI).
•PCI-DSS (Payment Card): Strong access control, encryption of cardholder data, audit trails, regular security testing.
•SOX (Financial): Controls over financial reporting data, audit trails, access controls, segregation of duties.
•SOC 2 (Service Organizations): Security, availability, processing integrity, confidentiality, privacy controls.

Protect the Audit Trail

Security Hardening

Security hardening reduces the attack surface by removing unnecessary functionality, applying secure defaults, and implementing defense-in-depth measures.

Hardening Checklist:

Database Hardening Steps

•Change Default Ports — While security through obscurity isn't sufficient alone, changing default ports reduces automated scanning success.
•Rename/Disable Default Accounts — Remove sample users, rename obvious accounts (postgres, sa, root).
•Remove Sample Databases — Delete template databases and example schemas that may contain vulnerabilities.
•Disable Unused Features — Turn off utilities, extensions, and services not required for your workload.
•Restrict Network Binding — Listen only on required interfaces; never bind to 0.0.0.0 without firewall.
•Secure File Permissions — Data files, configuration files, and logs should have restrictive permissions.
•Remove Dangerous Functions — Limit access to functions that can execute OS commands or access the filesystem.
•Apply Security Patches Promptly — Subscribe to security advisories; patch critical vulnerabilities immediately.

hardening-script.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#!/bin/bash
# PostgreSQL Hardening Script
 
# Secure file permissions
chmod 700 /var/lib/postgresql/data
chmod 600 /var/lib/postgresql/data/*.conf
chown -R postgres:postgres /var/lib/postgresql/data
 
# Remove unnecessary extensions
psql -c "DROP EXTENSION IF EXISTS adminpack;"
psql -c "DROP EXTENSION IF EXISTS file_fdw;"
 
# Revoke public permissions
psql -c "REVOKE ALL ON SCHEMA public FROM PUBLIC;"
psql -c "REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;"
psql -c "REVOKE ALL ON LANGUAGE plpgsql FROM PUBLIC;"
 
# Restrict dangerous functions
psql -c "REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM PUBLIC;"
psql -c "REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM PUBLIC;"
 
# Enable connection limits
psql -c "ALTER USER postgres CONNECTION LIMIT 2;"
 
# Configure password encryption
psql -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256';"
 
echo "Hardening complete. Restart PostgreSQL and verify access."

Network Segmentation:

Database servers should be isolated in dedicated network segments:

Converting Mermaid diagram...

The Patch Management Dilemma

Vulnerability Assessment

Regular vulnerability assessment identifies security weaknesses before attackers exploit them. This includes both automated scanning and manual review.

Assessment Types:

Database Security Assessment Methods
Method	Description	Frequency	Who Performs
Configuration Audit	Review settings against security benchmarks	Monthly/After changes	DBA / Security Team
Vulnerability Scan	Automated scanning for known vulnerabilities	Monthly minimum	Security Team / Automated
Penetration Test	Simulated attack to test defenses	Annually / After major changes	External specialists
Access Review	Verify user permissions are appropriate	Quarterly	DBA / Data Owners
Code Review	Review stored procedures, triggers for security issues	On change	Developers / Security

CIS Benchmarks:

The Center for Internet Security (CIS) publishes detailed security benchmarks for major databases. These provide authoritative guidance for secure configuration:

Key CIS Benchmark Areas

•Installation and Patches: Version currency, patch level
•Authentication and Authorization: Password policies, privilege management
•Auditing and Logging: Audit configuration, log protection
•Encryption: TLS configuration, data-at-rest encryption
•Network: Binding, firewall, network access control
•Operating System: File permissions, process isolation

security-assessment.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL Security Assessment Queries
 
-- Check for users without passwords (should be none)
SELECT usename, passwd 
FROM pg_shadow 
WHERE passwd IS NULL OR passwd = '';
 
-- Find users with superuser privilege
SELECT usename, usesuper 
FROM pg_user 
WHERE usesuper = true;
 
-- Check default search path (shouldn't include untrusted schemas)
SELECT usename, useconfig 
FROM pg_user 
WHERE useconfig::text LIKE '%search_path%';
 
-- Review tables accessible to PUBLIC
SELECT n.nspname, c.relname, c.relacl
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND c.relacl::text LIKE '%=r%';  -- PUBLIC has read access
 
-- Check for unencrypted connections (require ssl)
SELECT datname, usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE ssl = false AND client_addr IS NOT NULL;
 
-- Verify password encryption method
SHOW password_encryption;
-- Should return 'scram-sha-256'
 
-- Check log settings for security
SELECT name, setting 
FROM pg_settings 
WHERE name LIKE 'log%' 
   OR name LIKE '%audit%'
ORDER BY name;

Automate Assessments

Incident Response

Despite best efforts, security incidents may occur. Having a prepared incident response plan minimizes damage and accelerates recovery.

Incident Response Phases:

Incident Response Process

•Detection — Identify that an incident has occurred through monitoring, alerts, or reports. Time to detection is critical.
•Containment — Limit the damage by isolating affected systems. May include disconnecting from network, disabling accounts.
•Evidence Preservation — Before making changes, capture evidence: logs, snapshots, memory dumps. Chain of custody for legal proceedings.
•Eradication — Remove the threat: patch vulnerabilities, remove malware, close unauthorized access.
•Recovery — Restore normal operations from clean backups. Verify integrity of restored data.
•Post-Incident Review — Analyze what happened, why detection/prevention failed, what improvements to implement.

incident-response-playbook.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Database Incident Response Queries
 
-- CONTAINMENT: Terminate all sessions from suspicious source
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE client_addr = '192.168.1.100';  -- Suspicious IP
 
-- CONTAINMENT: Disable compromised account immediately
ALTER USER compromised_account WITH NOLOGIN;
 
-- CONTAINMENT: Revoke all privileges
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public 
    FROM compromised_account;
 
-- EVIDENCE: Capture current session state
COPY (
    SELECT pid, usename, client_addr, application_name,
           query_start, state, query
    FROM pg_stat_activity
) TO '/tmp/incident_sessions.csv' WITH CSV HEADER;
 
-- EVIDENCE: Preserve recent authentication logs
-- (Also preserve OS-level logs before rotation)
SELECT * FROM pg_stat_activity 
WHERE state = 'active' OR state = 'idle in transaction';
 
-- ANALYSIS: Recent DDL changes
SELECT * FROM pg_stat_user_tables
WHERE n_tup_del > 0 OR n_tup_upd > 0
ORDER BY greatest(last_vacuum, last_autovacuum) DESC NULLS LAST;
 
-- ANALYSIS: Check for unauthorized users or role changes
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb,
       rolvaliduntil
FROM pg_roles
ORDER BY oid DESC  -- Recently created
LIMIT 20;

Don't Panic, Don't Improvise

Communication:

Incident response includes communication responsibilities:

Internal escalation: Security team, management, legal, communications
External notification: Regulatory bodies (GDPR requires 72-hour notification), affected customers
Documentation: Maintain detailed timeline and actions taken for post-incident review and potential legal proceedings

Summary

Key Takeaways:

Security Management Essentials

•Understand the threat landscape. Databases face external attacks (SQL injection, ransomware) and internal threats (malicious/negligent insiders). Defend against both.
•Implement strong authentication. Use modern methods (SCRAM-SHA-256, certificates), enforce password policies, implement secrets management.
•Apply least privilege rigorously. Users and applications should have minimum required permissions. Use roles, not direct grants.
•Encrypt everywhere. TLS for connections, encryption at rest for sensitive data, proper key management.
•Enable comprehensive auditing. Log authentication, authorization changes, and access to sensitive data. Protect audit logs.
•Maintain compliance. Understand applicable regulations (GDPR, HIPAA, PCI-DSS) and implement required controls.
•Harden the system. Remove defaults, disable unnecessary features, patch promptly, segment networks.
•Assess vulnerabilities regularly. Configuration audits, vulnerability scans, penetration tests, access reviews.
•Prepare for incidents. Have documented response plans, practice them, preserve evidence, communicate appropriately.

What's Next:

Page Complete

3 / 5