Database Security: Protecting the Crown Jewels

Databases are where the valuable data actually lives. Penetrating the perimeter — the web application, the API layer — ultimately means nothing to an attacker unless they can reach the data behind it. Database security is about making sure that even if the application layer is compromised, the data has additional protection layers.

Principle of Least Privilege in Databases

Application code almost never needs full database access. Applying least privilege to database credentials is foundational.

In PostgreSQL:

-- Create application user with restricted permissions
CREATE USER crm_app WITH PASSWORD 'strong_password_here';

-- Grant only what's needed
GRANT SELECT, INSERT, UPDATE ON customers TO crm_app;
GRANT SELECT, INSERT ON interaction_logs TO crm_app;
GRANT SELECT ON products TO crm_app;

-- Explicitly deny dangerous operations
REVOKE DELETE ON customers FROM crm_app;
REVOKE ALL ON pg_user FROM crm_app;

-- Read-only reporting user
CREATE USER reporting_user WITH PASSWORD 'another_strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

Separate credentials for separate application components: the API server, the background job processor, the analytics pipeline, the admin panel. Each gets only what it needs. A compromised API key can't truncate tables.

Encryption at Rest

Data encrypted at rest is protected even if an attacker gains access to the physical storage or a database backup file.

PostgreSQL pgcrypto — column-level encryption:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Store encrypted PII
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name_encrypted BYTEA,
    email_encrypted BYTEA,
    ssn_encrypted BYTEA,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert with encryption
INSERT INTO customers (name_encrypted, email_encrypted, ssn_encrypted)
VALUES (
    pgp_sym_encrypt('Jane Doe', 'encryption_key_here'),
    pgp_sym_encrypt('jane@example.com', 'encryption_key_here'),
    pgp_sym_encrypt('123-45-6789', 'encryption_key_here')
);

-- Retrieve with decryption
SELECT 
    id,
    pgp_sym_decrypt(name_encrypted, 'encryption_key_here') as name,
    pgp_sym_decrypt(email_encrypted, 'encryption_key_here') as email
FROM customers;

For column-level encryption, never store the encryption key in the same database. Use a secrets management service (AWS KMS, HashiCorp Vault) to retrieve keys at runtime.

Database Auditing

Know who accessed what and when. PostgreSQL's pgaudit extension provides comprehensive audit logging:

-- Install pgaudit
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure in postgresql.conf:
-- pgaudit.log = 'read, write, ddl, role'
-- pgaudit.log_relation = on

-- Or configure per-role:
ALTER ROLE admin_user SET pgaudit.log = 'all';
ALTER ROLE app_user SET pgaudit.log = 'write, ddl';

Audit logs should capture:

• All SELECT on sensitive tables (customer PII, financial data, health records)
• All INSERT, UPDATE, DELETE operations
• Schema changes (CREATE, ALTER, DROP)
• Authentication events (successful and failed logins)
• Permission changes

Feed these logs into your SIEM. Alerts I configure:

• Any DROP TABLE or TRUNCATE statement
• Large SELECT operations (more than 10,000 rows returned)
• Any schema changes outside of a maintenance window
• Authentication failures exceeding 5 per minute

SQL Injection Prevention (Database Layer)

I covered application-layer parameterized queries in my SQL injection post. At the database layer, additional controls:

Stored procedures with no dynamic SQL:

CREATE OR REPLACE FUNCTION get_customer_by_email(p_email VARCHAR)
RETURNS TABLE(id BIGINT, name VARCHAR, email VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT c.id, c.name, c.email
    FROM customers c
    WHERE c.email = p_email  -- Parameter binding, not concatenation
    AND c.active = TRUE;
END;
$$ LANGUAGE plpgsql;

Row Level Security (RLS) — enforce multi-tenancy at the database level:

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

-- Policy: users can only see their own org's data
CREATE POLICY org_isolation ON customer_records
    USING (org_id = current_setting('app.current_org_id')::BIGINT);

With RLS, even if the application has a logic bug that doesn't filter by org, the database enforces the isolation. Defense in depth.

Backup Security

Database backups are as sensitive as the live database — often more so, because they're frequently stored in less-secure locations.

• Encrypt all backups before storage
• Store encryption keys separately from backups
• Test restoration regularly — an encrypted backup you can't restore is worthless
• Apply same access controls to backup storage as to the database
• Retain backups in geographically separate locations (but remember: more copies = more exposure surface)

In PostgreSQL with pg_dump:

# Encrypted backup
pg_dump -Fc mydb | openssl enc -aes-256-cbc -pbkdf2 -k "$BACKUP_ENCRYPTION_KEY" > mydb_backup.enc

# Restore
openssl enc -d -aes-256-cbc -pbkdf2 -k "$BACKUP_ENCRYPTION_KEY" -in mydb_backup.enc | pg_restore -d mydb

Connection Security

All database connections should be encrypted in transit:

# 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'

# pg_hba.conf — require SSL for all remote connections
hostssl all all 0.0.0.0/0 scram-sha-256

Require client certificate authentication for privileged accounts — not just username/password.

Databases are the crown jewels. Treat them accordingly: least privilege access, encryption at rest and in transit, comprehensive auditing, and defense in depth across every layer.