Chapter 16 · Security, Reliability, and Governance
Encryption, Auditing, Masking, and Data Classification
Security controls solve different problems: encryption protects data representations, authorization controls use, masking limits exposure, auditing records relevant actions, and classification tells every other control what deserves protection.
Learning outcomes
Layer controls according to the threat and data class
Distinguish encryption in transit, at rest, in backups, and at field level.
Create a practical data-classification catalog and map controls to classes.
Design audit records that support investigations without becoming a secret leak.
Expose masked data through a controlled relational interface.
Recognize key-management, logging, and privileged-access limitations.
Protection layers are not interchangeable
| Layer | Protects primarily against | Important limitation |
|---|---|---|
| TLS in transit | Network interception and unauthorized modification in transit | Data is plaintext to authenticated endpoints after decryption |
| Disk or volume encryption | Lost media, snapshots, and offline disk access | A running database process can read the mounted data |
| Encrypted backup | Backup-media disclosure | Keys, manifests, and restore process must be protected and tested |
| Field or application encryption | Selected values from some database/storage operators | Search, indexing, rotation, and key access become harder |
| Hashing | Password verification or equality fingerprints | It is one-way and not a substitute when the original value must be recovered |
Encryption succeeds only when key lifecycle, identity, authorization, and recovery are designed together.
Classification catalog
Classification should be machine-readable enough to drive access reviews, masking, retention, and incident response.
DROP TABLE IF EXISTS data_classification;CREATE TABLE data_classification ( object_name TEXT NOT NULL, column_name TEXT NOT NULL, classification TEXT NOT NULL CHECK ( classification IN ('public','internal','confidential','restricted') ), data_owner TEXT NOT NULL, contains_personal INTEGER NOT NULL CHECK (contains_personal IN (0,1)), masking_rule TEXT, retention_policy TEXT NOT NULL, PRIMARY KEY (object_name, column_name)) STRICT;INSERT INTO data_classification VALUES('customer','customer_id','internal','commerce',0,NULL,'account_lifetime'),('customer','email','restricted','privacy',1,'email_partial','account_plus_30_days'),('customer','full_name','confidential','privacy',1,'name_initials','account_plus_30_days'),('customer','date_of_birth','restricted','privacy',1,'year_only','account_plus_30_days'),('sales_order','payment_token','restricted','payments',1,'last_four','seven_year_finance'),('sales_order','total_cents','confidential','finance',0,NULL,'seven_year_finance');SELECT classification, COUNT(*) AS classified_columnsFROM data_classificationGROUP BY classificationORDER BY classification;Transport encryption in PostgreSQL
-- postgresql.confssl = onssl_cert_file = 'server.crt'ssl_key_file = 'server.key'-- pg_hba.conf: require TLS for an application network.hostssl academy checkout_service 10.20.0.0/16 scram-sha-256-- Client configuration should verify the server identity.-- Example connection option: sslmode=verify-fullSELECT ssl, version, cipherFROM pg_stat_sslWHERE pid = pg_backend_pid();Encryption without server-certificate verification can still permit connection to the wrong endpoint. Certificate ownership, private-key permissions, renewal, and monitoring are part of the control.
Masked access surface
Masking reduces routine exposure but does not transform sensitive data into non-sensitive data. Privileged users, joins, inference, and small groups may still reveal individuals.
DROP VIEW IF EXISTS customer_support_view;CREATE VIEW customer_support_view ASSELECT customer_id, substr(full_name, 1, 1) || '***' AS masked_name, substr(email, 1, 2) || '***@' || substr(email, instr(email, '@') + 1) AS masked_email, region, CASE WHEN date_of_birth IS NULL THEN NULL ELSE substr(date_of_birth, 1, 4) END AS birth_year, CASE WHEN deleted_at IS NULL THEN 'active' ELSE 'deleted' END AS lifecycle_stateFROM customer;SELECT *FROM customer_support_viewORDER BY customer_id;Audit meaningful security events
An audit record should answer who, what, when, where, target, outcome, and request context. Avoid copying credentials, complete query parameters, tokens, or unrestricted personal data into logs.
INSERT INTO security_audit ( event_time, actor, action, object_name, record_key, outcome, request_id, details_json) VALUES ( datetime('now'), 'support-agent-17', 'customer.profile.view', 'customer_support_view', 'customer_id=2', 'success', 'req-8f43', json_object('reason_code', 'ticket-investigation', 'ticket_id', 'SUP-441'));SELECT event_time, actor, action, record_key, outcome, request_idFROM security_auditORDER BY audit_id DESC;Audit design boundaries
Tamper resistance
Restrict update/delete rights, ship logs to a separate security boundary, and monitor gaps.
Data minimization
Record identifiers and reason codes instead of secrets or complete sensitive values.
Reliable time
Use synchronized infrastructure time and preserve timezone or UTC semantics.
Review process
Logs that nobody reviews do not provide timely detection or accountability.
-- Illustrative settings; tune volume and privacy before production.logging_collector = onlog_destination = 'jsonlog'log_connections = onlog_disconnections = onlog_line_prefix = '%m [%p] user=%u db=%d app=%a client=%h '-- Be cautious with broad statement logging: SQL text can contain secrets,-- personal data, or large payloads. Prefer structured application events and-- focused database auditing for high-value actions.Key-management questions
Cryptographic review
- Which component may decrypt each data class?
- Where are keys stored, rotated, backed up, and revoked?
- Can a database administrator also access the encryption keys?
- How will historical ciphertext be re-encrypted after rotation?
- Can the recovery team restore data and keys during an incident?
Review the answers
The answers define the actual security boundary. Encryption provides little separation when every privileged operator, backup process, and application shares the same unrestricted key access. Recovery must be tested without distributing keys broadly.
Summary and references
- Classify data before selecting masking, audit, retention, and cryptographic controls.
- Use TLS with endpoint verification for client/server transport.
- Protect data files, snapshots, and backups at rest and govern the keys separately.
- Mask routine access through views or application contracts, but retain authorization controls.
- Audit high-value events with trustworthy actor and request context while excluding secrets.