"""
Blacklist for dangerous Athena query templates.
This module defines templates that are FORBIDDEN to execute via MCP server
due to their destructive or schema-modifying nature.
## Why blacklist is necessary
Even though ad-hoc SQL queries are validated with regex (preventing DROP/DELETE/etc),
registered templates bypass that validation. Templates are pre-approved SQL code
loaded from files, so an attacker could call execute_template(name='DROP_TABLE', ...)
and destroy data.
Defense-in-depth approach:
1. Ad-hoc queries → regex whitelist (only SELECT)
2. Registered templates → explicit blacklist (block known dangerous templates)
3. Results → sanitization (redact sensitive columns)
## Blacklist contents
### DESTRUCTIVE OPERATIONS (full ban)
- DROP_TABLE: Permanently deletes table and all data
- DROP_TABLE_IF_EXISTS: Conditional drop (still destructive)
### SCHEMA MODIFICATIONS (can be added to blacklist if needed)
- MSCK_REPAIR_TABLE: Repairs table partitions (modifies Glue Catalog metadata)
Currently ALLOWED but risky - can corrupt partition metadata if used incorrectly
### TABLE CREATION (currently allowed)
- All TABLE_* templates: Create external tables in Athena (CREATE EXTERNAL TABLE)
These are allowed for exploratory analysis but should be monitored
(49 templates total: TABLE_PROVIDER_*, TABLE_PROMO_*, TABLE_PLATFORM_*, etc)
## Usage
from blacklist import is_template_blacklisted, BLACKLISTED_TEMPLATES
if is_template_blacklisted(template_name):
raise PermissionError(f'Template {template_name} is blacklisted')
## Extending blacklist
To ban additional templates, add them to BLACKLISTED_TEMPLATES set below.
Template names must match enum values in app/lib/enums.py::AthenaQueryNames
"""
# Full ban - destructive operations and schema modifications
BLACKLISTED_TEMPLATES: set[str] = {
'DROP_TABLE', # Permanently deletes table
'DROP_TABLE_IF_EXISTS', # Conditional drop (still destructive)
'MSCK_REPAIR_TABLE', # Schema metadata repair - can corrupt if misused
}
# Optional: Templates that require extra caution (logged but not blocked)
# MSCK_REPAIR_TABLE moved to BLACKLISTED_TEMPLATES for MCP security
RESTRICTED_TEMPLATES: set[str] = set()
# Informational: TABLE_* patterns that create new tables (not blocked)
# These templates contain CREATE EXTERNAL TABLE statements
# Currently allowed for exploratory analysis
# Pattern: TABLE_PROVIDER_*, TABLE_PROMO_*, TABLE_PLATFORM_*, TABLE_COMMON_*
# Total: ~49 templates (see app/model/athena/athena_config.py)
def is_template_blacklisted(template_name: str) -> bool:
"""
Check if template is blacklisted (forbidden to execute).
Args:
template_name: Template name from AthenaQueryNames enum (e.g. 'DROP_TABLE')
Returns:
True if template is blacklisted, False otherwise
"""
return template_name in BLACKLISTED_TEMPLATES
def is_template_restricted(template_name: str) -> bool:
"""
Check if template is restricted (requires extra caution, logged as warning).
Args:
template_name: Template name from AthenaQueryNames enum
Returns:
True if template is restricted, False otherwise
"""
return template_name in RESTRICTED_TEMPLATES
def get_blacklist_reason(template_name: str) -> str:
"""
Get human-readable reason why template is blacklisted.
Args:
template_name: Template name from AthenaQueryNames enum
Returns:
Reason string for user-facing error messages
"""
reasons = {
'DROP_TABLE': 'Destructive operation: permanently deletes table and all data',
'DROP_TABLE_IF_EXISTS': 'Destructive operation: conditionally deletes table',
'MSCK_REPAIR_TABLE': 'Schema modification: can corrupt partition metadata if misused',
}
return reasons.get(
template_name, f'Template {template_name} is blacklisted for security reasons'
)
