T09 · Insecure Skill Coding Practices
- Location
SKILL.md:84- Finding
Audit Trigger Stores Current and Historical Password Hashes
- Content
View full analysis
Vulnerability Details
File Location:
SKILL.md, lines 57 and 84-115
Vulnerability Type: Sensitive data exposure through unrestricted audit logging
Risk Level: MediumThe user schema contains a sensitive authentication field:
sql password_hash VARCHAR(255) NOT NULL,The audit implementation stores complete row representations for every insert, update, and delete:
sql CREATE TABLE audit_log ( id BIGSERIAL PRIMARY KEY, table_name VARCHAR(255) NOT NULL, record_id BIGINT NOT NULL, operation audit_operation NOT NULL, old_values JSONB, new_values JSONB, changed_fields TEXT[], user_id BIGINT REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id); CREATE INDEX idx_audit_user_time ON audit_log(user_id, created_at); -- Trigger function CREATE OR REPLACE FUNCTION audit_trigger_function() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'DELETE' THEN INSERT INTO audit_log (table_name, record_id, operation, old_values) VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD)); RETURN OLD; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values) VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW)); RETURN NEW; ELSIF TG_OP = 'INSERT' THEN INSERT INTO audit_log (table_name, record_id, operation, new_values) VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW)); RETURN NEW; END IF; END; $$ LANGUAGE plpgsql; -- Apply to any table CREATE TRIGGER audit_users AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();Technical Analysis
The generic trigger converts entire
usersrows to JSONB usingto_jsonb(OLD)andto_jsonb(NEW). Because those rows includepassword_hash, the audit table receiv ...[truncated 2013 chars]- Remediation
View remediation
Remediation Suggestions
-
Exclude sensitive fields before writing row data to the audit table:
sql to_jsonb(NEW) - 'password_hash' to_jsonb(OLD) - 'password_hash' -
Prefer an explicit allowlist of auditable columns rather than serializing complete rows. This prevents newly added secrets, tokens, recovery codes, or personal data from silently entering the audit log.
-
Use table-specific audit policies where schemas contain authentication or other sensitive data. A generic trigger should accept a reviewed exclusion list or construct sanitized JSONB.
-
Revoke direct
SELECTaccess toaudit_logfrom ordinary application, reporting, and support roles. Provide restricted views that expose only fields necessary for legitimate audit workflows. -
Establish retention and secure-deletion policies for audit records and ensure those policies cover backups, replicas, exports, and analytics destinations.
-
Remove existing password hashes from stored audit data. For example, after taking appropriate operational precautions:
sql UPDATE audit_log SET old_values = old_values - 'password_hash', new_values = new_values - 'password_hash' WHERE table_name = 'users'; -
Review whether exposed hashes require forced password resets. Prioritize privileged users and verify that the application uses a modern password-hashing algorithm with appropriate work factors and unique salts.
-
Add automated tests asserting that prohibited fields such as
password_hash, password-reset tokens, MFA secrets, and session tokens never appear in audit JSON.
-