Database Security: Encryption, Access Control, and Audit Logging

The database is the crown jewel of most applications — it contains user credentials, financial records, personal information, and business-critical data. Securing the database layer requires encryption, strict access controls, continuous auditing, and defense-in-depth against injection attacks.

Encryption at Rest

Database files stored on disk are vulnerable to physical theft, compromised backups, and unauthorized storage-level access. Encryption at rest renders the data unreadable without the correct keys.

Managed database services offer transparent data encryption (TDE) with automatic key management. For self-hosted databases, enable filesystem-level encryption with LUKS or use the database engine's native encryption features.

-- PostgreSQL: Enable TDE with pg_tde extension
CREATE EXTENSION IF NOT EXISTS pg_tde;

-- Initialize encryption key
SELECT pg_tde_add_global_key_provider('vault', 'https://vault.example.com', 'token');

-- Create an encrypted tablespace
CREATE TABLESPACE encrypted_ts
  OWNER postgres
  LOCATION '/var/lib/postgresql/encrypted'
  WITH (encryption = 'aes_256_gcm');

-- Move sensitive tables to encrypted tablespace
ALTER TABLE users SET TABLESPACE encrypted_ts;
ALTER TABLE payments SET TABLESPACE encrypted_ts;

Key management is the most critical aspect of encryption at rest. Store master keys in a dedicated key management service (AWS KMS, HashiCorp Vault, Azure Key Vault) — never on the same filesystem as the encrypted data. Implement key rotation policies and test the recovery process before going to production.

Encryption in Transit

Database connections must be encrypted to prevent eavesdropping and man-in-the-middle attacks on the network. Enforce TLS for all client connections and disable fallback to unencrypted connections.

# PostgreSQL: Require TLS for all connections in pg_hba.conf
hostssl all             all             0.0.0.0/0       scram-sha-256
hostssl replication     replicator      10.0.0.0/8      scram-sha-256

For MongoDB, enable TLS and disable the non-TLS port entirely. For Redis, configure TLS and use requirepass with a strong password. Connection strings should include TLS parameters:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('./ca-cert.pem').toString(),
  },
});

Role-Based Access Control

Database users should operate with the minimum privileges necessary. Never use the superuser account for application connections — create dedicated roles with scoped permissions.

-- Principle of least privilege in PostgreSQL
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';
CREATE ROLE read_only WITH LOGIN PASSWORD 'different_password';

-- Application user: CRUD on specific tables only
GRANT CONNECT ON DATABASE soninow_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Reporting user: read-only access
GRANT CONNECT ON DATABASE soninow_db TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Revoke truncate and drop on application user
REVOKE TRUNCATE, DROP ON ALL TABLES IN SCHEMA public FROM app_user;

Separate roles for read-write application access, read-only reporting, administrative operations, and migration scripts. Each role connects with its own credentials, and credentials are rotated on a regular schedule or immediately upon personnel changes.

Connection Pooling Security

Connection pools improve performance by reusing database connections, but misconfigured pools can leak data between queries or expose the database to excessive load.

// Secure connection pool configuration
const pool = new Pool({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '5432'),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  // Prevent prepared statement sharing across users
  allowExitOnIdle: false,
});

Set a connection limit per pool, implement connection timeouts, and use PgBouncer or similar connection poolers in transaction mode for applications with variable load. Monitor for connection leaks — every pool.query() must be matched by a returned connection.

SQL Injection Prevention at the Database Layer

While input validation catches most injection vectors, the database layer provides a final line of defense through parameterized queries, stored procedures, and statement-level controls.

// Parameterized query (safe)
const result = await pool.query(
  'UPDATE users SET email = $1 WHERE id = $2',
  [newEmail, userId]
);

Never concatenate user input into raw SQL. Use an ORM with parameterized query generation (Prisma, Drizzle, TypeORM) for complex queries, and verify raw SQL statements in code review. Stored procedures can centralize complex data operations and limit the operations available to application roles.

Audit Logging

Audit logs provide the forensic evidence needed to detect and investigate security incidents. Log every schema change, privilege grant, failed authentication attempt, and data access on sensitive tables.

PostgreSQL's pgaudit extension provides comprehensive session-level and object-level audit logging:

-- Enable pgaudit
shared_preload_libraries = 'pgaudit';
pgaudit.log = 'write,ddl,role';
pgaudit.log_level = 'notice';
pgaudit.log_catalog = off;

-- Object-level audit for sensitive tables
CREATE EXTENSION IF NOT EXISTS pgaudit;
SELECT pgaudit.audit_table('users');
SELECT pgaudit.audit_table('payments');
SELECT pgaudit.audit_table('sessions');

Stream audit logs to a centralized, immutable log store with append-only access. Set up alerts for suspicious patterns — bulk row deletions after midnight, repeated failed login queries, or privilege escalation attempts.

Backup Security

Encrypted backups are essential but often overlooked. Encrypt backup files at rest, transfer them over encrypted channels, and test restoration in an isolated environment.

# Encrypted PostgreSQL backup with pg_dump
PGPASSWORD="$DB_PASSWORD" pg_dump \
  -h $DB_HOST \
  -U $DB_USER \
  --format=custom \
  --compress=9 \
  $DB_NAME | \
gpg --encrypt --recipient [email protected] > \
  /backups/$(date +%Y%m%d_%H%M%S).dump.gpg

Store backups in a separate AWS account or cloud project with restricted cross-account access. Implement a retention policy — keep daily backups for 30 days, weekly for 12 months, and monthly for 7 years for compliance requirements.

Database security is a shared responsibility between the platform, the application, and the operations team. Our <a href="/services/devops-server-management">devops and server management services</a> include database hardening, encryption configuration, and audit pipeline setup. Contact SoniNow to secure your data layer.