# RLS (Row-Level Security) Configuration for QueryMind
# This file defines security policies for SQL query execution

# ============================================================
# Territory-Based Row-Level Security (RLS)
# ============================================================
# Controls which sales territories users can access based on group membership

territory_rls:
  enabled: true
  
  # Mapping: User Group -> Allowed TerritoryIDs
  # AdventureWorks has 10 territories (1-10):
  #   1-3:   Northwest
  #   4-6:   Northeast  
  #   7-9:   Southwest
  #   10:    Canada (special)
  group_territory_mapping:
    # Sales regional managers - access to their assigned regions
    sales_west: [1, 2, 3, 7, 8, 9]      # West coast: Northwest + Southwest
    sales_central: [4, 5, 6, 10]          # Central + Canada
    sales_east: []                        # East coast - no direct sales
    
    # Territory-specific sales
    sales_northwest: [1, 2, 3]
    sales_southwest: [7, 8, 9]
    sales_northeast: [4, 5, 6]
    sales_canada: [10]
    
    # Management - full access
    sales_manager: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    admin: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    
    # Analytics - read all for reporting
    analytics: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    
    # Finance - access to all for financial reporting
    finance: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
    
    # User - regular users can only access US domestic territories (excluding Canada)
    user: [1, 2, 3, 4, 5, 6, 7, 8, 9]
    
    # Guest - no territory access by default
    guest: []

  # Tables that require Territory-based RLS filtering
  # For each table, specify the column that holds TerritoryID
  # If territory_column is empty, RLS will be applied via JOIN
  protected_tables:
    # Direct territory column
    - table: "Sales.SalesOrderHeader"
      territory_column: "TerritoryID"
      description: "Main sales order table - direct territory access"
    
    - table: "Sales.SalesTerritory"
      territory_column: "TerritoryID"
      description: "Territory reference table"
    
    - table: "Sales.Customer"
      territory_column: "TerritoryID"
      description: "Customer table - their assigned sales territory"
    
    - table: "Sales.SalesPerson"
      territory_column: "TerritoryID"
      description: "Sales person - their assigned territory"
    
    # Indirect - requires JOIN with SalesOrderHeader
    - table: "Sales.SalesOrderDetail"
      territory_column: ""  # No direct column, use SalesOrderID JOIN
      join_table: "Sales.SalesOrderHeader"
      join_column: "SalesOrderID"
      via_territory_column: "TerritoryID"
      description: "Order details - filtered via parent order's territory"
    
    - table: "Sales.SalesPersonQuotaHistory"
      territory_column: ""  # No direct column
      join_table: "Sales.SalesPerson"
      join_column: "BusinessEntityID"
      via_territory_column: "TerritoryID"
      description: "Sales quota history - filtered via salesperson's territory"
    
    - table: "Sales.SalesTerritoryHistory"
      territory_column: "TerritoryID"
      description: "Territory assignment history"

# ============================================================
# SQL Injection Prevention
# ============================================================
# Patterns that will cause query rejection

sql_injection:
  enabled: true

  # Read-only metadata queries that QueryMind relies on for schema discovery.
  # These are allowed even if they touch catalog/meta views.
  allowed_metadata_patterns:
    - pattern: "\\binformation_schema\\.(tables|columns|table_constraints|key_column_usage|referential_constraints|schemata|views|routines|parameters)\\b"
      description: "Read-only PostgreSQL metadata introspection"
      case_sensitive: false
    - pattern: "\\b(?:pg_catalog\\.)?(pg_namespace|pg_database|pg_tables|pg_indexes|pg_class|pg_attribute|pg_constraint|pg_type)\\b"
      description: "Read-only PostgreSQL catalog introspection"
      case_sensitive: false
  
  # Forbidden SQL patterns - any match = immediate rejection
  forbidden_patterns:
    # Comment injection
    - pattern: ";--"
      description: "Comment injection to truncate query"
    
    - pattern: "/\\*.*\\*/"
      description: "Block comment injection"
    
    # Statement termination + dangerous commands
    - pattern: ";\\s*(DROP|Delete|TRUNCATE|ALTER|CREATE)"
      description: "Statement termination followed by destructive DDL"
      case_sensitive: false
    
    - pattern: ";\\s*(INSERT|UPDATE|REPLACE)"
      description: "Statement termination followed by data modification"
      case_sensitive: false
    
    - pattern: "UNION\\s+DROP"
      description: "UNION with DROP attempt"
      case_sensitive: false
    
    # Stacked queries
    - pattern: ";\\s*;"
      description: "Double statement termination (stacked queries)"
    
    # System catalog access
    # NOTE: read-only metadata views are allowlisted above for schema discovery.
    - pattern: "(pg_|pg_catalog|information_schema|sys\\.)"
      description: "Direct system catalog access attempt"
      case_sensitive: false
    
    - pattern: "xp_"
      description: "SQL Server extended stored procedure attempt"
      case_sensitive: false
    
    # Execution functions
    - pattern: "(EXEC|EXECUTE)\\s*\\("
      description: "Execute function/procedure injection"
      case_sensitive: false
    
    # Load file / into outfile
    - pattern: "(LOAD_FILE|INTO\\s+OUTFILE|INTO\\s+DUMPFILE)"
      description: "File read/write injection"
      case_sensitive: false
    
    # Sleep/bombing
    - pattern: "(SLEEP\\(|BENCHMARK\\()"
      description: "Timing-based injection (SLEEP/BENCHMARK)"
      case_sensitive: false
    
    # Hex encoding bypass attempts
    - pattern: "0x[0-9a-f]+"
      description: "Hex-encoded content (potential encoding bypass)"
    
    # Shell command via xp_cmdshell equivalent
    - pattern: "xp_cmdshell"
      description: "OS command execution attempt"
      case_sensitive: false

# ============================================================
# SQL Semantics Harness
# ============================================================
# Conservative structural checks used by the tool registry before run_sql.

sql_semantics:
  enabled: true
  check_row_grain: true
  check_window: true
  check_navigation_window: true
  check_aggregation: true
  check_rollup: true
  check_join: true
  check_partition_constant: true
  check_outer_join_null_filter: true
  require_partition_by_for_window_cues: true

# ============================================================
# Query Complexity Limits
# ============================================================
# Limits to prevent resource exhaustion

query_limits:
  enabled: true
  
  max_query_length: 10000          # Maximum characters in SQL
  max_subqueries: 5                # Maximum nested subqueries
  max_cte_depth: 3                 # Maximum CTE (WITH clause) depth
  max_joins: 15                    # Maximum number of JOINs
  max_result_rows: 10000           # Maximum rows to return (safety limit)
  
  # Tables that should NEVER be directly queried by non-admin
  forbidden_tables:
    - "HumanResources.JobCandidate"  # Contains PII/resume data
    - "Person.Password"              # Password hashes
    - "Sales.CreditCard"             # Credit card data (masked anyway)
    - "Production.Document"          # Internal documents

# ============================================================
# Logging & Audit
# ============================================================
audit:
  log_sql_transformations: true     # Log all SQL rewrites
  log_rejected_queries: true        # Log rejected queries with reason
  log_rls_blocks: true             # Log RLS territory blocks
