T09 · Insecure Skill Coding Practices
Warning
- Location
- SKILL.md:590
- Finding
- Generic Audit Trigger Stores Complete Sensitive Database Rows<![CDATA[ ## Vulnerability Details **File Location**: `SKILL.md`, lines 590–620 **Vulnerability Type**: Excessive sensitive-data retention in audit logs **Risk Level**: Medium ### Vulnerable Code ```sql -- Audit trail table CREATE TABLE audit_log ( id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100) NOT NULL, record_id BIGINT NOT NULL, action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE old_data JSONB, new_data JSONB, changed_by BIGINT REFERENCES users(id), changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), ip_address INET ); -- Generic audit trigger CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by) VALUES ( TG_TABLE_NAME, COALESCE(NEW.id, OLD.id), TG_OP, CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END, CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END, current_setting('app.user_id', true)::bigint ); RETURN COALESCE(NEW, OLD); END; $$ LANGUAGE plpgsql; ``` ### Technical Analysis The generic trigger serializes the complete `OLD` and `NEW` records into JSONB. It does not exclude or redact sensitive columns, so applying it to authentication, session, payment, user, or tenant tables can duplicate password hashes, access tokens, reset tokens, API keys, financial information, personal data, and other regulated records. The audit table also lacks explicit access restrictions, row-level security, encryption controls, and retention or deletion requirements. Sensitive values may therefore remain in the audit history after they have been rotated, changed, or deleted from their original tables. This conflicts with data-minimization principles and can undermine credential rotation and data-erasure procedures. The issue does not independently grant elevated database privileges. Exploitation requires an attacker or unauthorized database principal to obtain read acc ...[truncated 1683 chars]
- Remediation
- <![CDATA[ ## Remediation Suggestions 1. Replace whole-row serialization with an explicit allowlist of fields that are necessary for auditing. 2. Exclude password hashes, session identifiers, access and refresh tokens, API keys, encryption material, payment data, and sensitive personal information. 3. Where sensitive values must be tracked, record a non-reversible digest, classification label, or indication that the value changed rather than the value itself. 4. Place the audit table in a dedicated restricted schema and revoke default access: ```sql CREATE SCHEMA audit; REVOKE ALL ON SCHEMA audit FROM PUBLIC; REVOKE ALL ON ALL TABLES IN SCHEMA audit FROM PUBLIC; GRANT INSERT ON audit.audit_log TO audit_writer; GRANT SELECT ON audit.audit_log TO dedicated_auditor; ``` 5. Ensure the application role can append audit events without reading or modifying existing audit records. 6. Apply row-level security or equivalent tenant isolation if audit records from multiple tenants share a table. 7. Define and enforce retention periods appropriate to legal and operational requirements. Ensure deletion and erasure workflows also address retained audit data. 8. Encrypt audit records and backups where required, with keys stored outside the database and access restricted separately. 9. Document that the generic trigger must not be attached unchanged to tables containing credentials, tokens, cryptographic material, payment information, or highly sensitive personal data. 10. Add tests that insert recognizable secret values and verify that those values do not appear in `old_data`, `new_data`, logs, exports, or backups. ]]>