T09 · Insecure Skill Coding Practices
- Location
scripts/analyze.py:7- Finding
Unverified Destructive Index Recommendations Written to a Privileged Fixed Path
- Content
View full analysis
Vulnerability Details
File Location:
scripts/analyze.py, lines 7 and 26–36
Vulnerability Type: Unsafe destructive recommendations and privileged fixed-file output
Risk Level: MediumVulnerable Code
python RECOMMENDATION_PATH = "/root/.schema/recommendation.json"python redundant_indexes = [] for idx in all_indexes: for pattern in REDUNDANT_PATTERNS: if pattern in idx.lower(): redundant_indexes.append(idx) break recommendation = { "indexes_to_drop": redundant_indexes, "generated_at": datetime.utcnow().isoformat(), } os.makedirs(os.path.dirname(RECOMMENDATION_PATH), exist_ok=True) with open(RECOMMENDATION_PATH, "w") as f: json.dump(recommendation, f, indent=2)Technical Analysis
The analyzer classifies indexes as redundant solely when their names contain one of several configured substrings, including
_email_,_temp_,_auto_,_idx_, and_bak_. It does not compare indexed columns, uniqueness properties, partial-index predicates, constraint dependencies, query plans, or usage statistics. Consequently, an index name is treated as sufficient evidence for adding the index to anindexes_to_droplist even though naming alone cannot establish redundancy.The generated recommendation is written to the fixed path
/root/.schema/recommendation.json. This unnecessarily requires the process to run with access to the root user's directory, conflicts with least-privilege operation, and non-atomically overwrites any existing file at that location. Although the script does not directly drop indexes or elevate its own privileges, downstream tooling may interpret the output as an authoritative deletion list.Attack Path
- An attacker or lower-trust database administrator who can influence schema object names creates or renames a necessary index so its name contains a configured substring, such as
_idx_or_email_. ...[truncated 1457 chars]
- An attacker or lower-trust database administrator who can influence schema object names creates or renames a necessary index so its name contains a configured substring, such as
- Remediation
View remediation
Remediation Suggestions
- Determine redundancy from normalized index definitions rather than names. Compare table ownership, ordered columns, collations, sort direction, uniqueness, expressions, and partial-index predicates.
- Detect whether an index supports a primary-key, unique, or other database constraint before recommending any action.
- Treat naming patterns only as low-confidence review signals. Rename
indexes_to_dropto a non-destructive field such asreview_candidates, and include evidence, confidence, and the index definition for each candidate. - Require explicit human approval before any downstream component converts a recommendation into a schema change.
- Accept the recommendation output path as a validated configuration or command-line argument. Default to a user-owned application data directory rather than
/root. - Create the output file with restrictive permissions and write it atomically by creating a temporary file in the destination directory, flushing and closing it, and replacing the final path.
- Refuse unsafe destination types, including symbolic links and unexpected non-regular files, when operating in a privileged context.
- Add tests covering legitimate indexes whose names contain every configured pattern, including unique, expression, and partial indexes, to prevent false deletion recommendations.
