| 1 | """Initial database migration |
| 2 | |
| 3 | Revision ID: bdf40d064ed1 |
| 4 | Revises: |
| 5 | Create Date: 2024-04-22 19:48:15.129902 |
| 6 | |
| 7 | """ |
| 8 | from typing import Sequence |
| 9 | from typing import Union |
| 10 | |
| 11 | import sqlalchemy as sa |
| 12 | import sqlalchemy.dialects.mysql as mysql |
| 13 | |
| 14 | from alembic import op |
| 15 | |
| 16 | # revision identifiers, used by Alembic. |
| 17 | revision: str = "bdf40d064ed1" |
| 18 | down_revision: Union[str, None] = None |
| 19 | branch_labels: Union[str, Sequence[str], None] = None |
| 20 | depends_on: Union[str, Sequence[str], None] = None |
| 21 | |
| 22 | |
| 23 | def upgrade() -> None: |
| 24 | # ### commands auto generated by Alembic - please adjust! ### |
| 25 | op.create_table( |
| 26 | "available_integrations", |
| 27 | sa.Column("id", sa.Integer(), nullable=False), |
| 28 | sa.Column("integration_name", sa.String(length=255), nullable=False), |
| 29 | sa.Column("description", sa.String(length=1024), nullable=False), |
| 30 | sa.Column("integration_details", mysql.TEXT(length=1000000), nullable=False), |
| 31 | sa.PrimaryKeyConstraint("id"), |
| 32 | ) |
| 33 | op.create_table( |
| 34 | "connectors", |
| 35 | sa.Column("id", sa.Integer(), nullable=False), |
| 36 | sa.Column("connector_name", sa.String(length=256), nullable=False), |
| 37 | sa.Column("connector_type", sa.String(length=256), nullable=False), |
| 38 | sa.Column("connector_url", sa.String(length=750), nullable=False), |
| 39 | sa.Column("connector_last_updated", sa.DateTime(), nullable=False), |
| 40 | sa.Column("connector_username", sa.String(length=256), nullable=True), |
| 41 | sa.Column("connector_password", sa.String(length=256), nullable=True), |
| 42 | sa.Column("connector_api_key", sa.String(length=750), nullable=True), |
| 43 | sa.Column("connector_description", sa.String(length=1000), nullable=True), |
| 44 | sa.Column("connector_supports", sa.String(length=1000), nullable=True), |
| 45 | sa.Column("connector_configured", sa.Boolean(), nullable=False), |
| 46 | sa.Column("connector_verified", sa.Boolean(), nullable=False), |
| 47 | sa.Column("connector_accepts_host_only", sa.Boolean(), nullable=False), |
| 48 | sa.Column("connector_accepts_api_key", sa.Boolean(), nullable=False), |
| 49 | sa.Column("connector_accepts_username_password", sa.Boolean(), nullable=False), |
| 50 | sa.Column("connector_accepts_file", sa.Boolean(), nullable=False), |
| 51 | sa.Column("connector_accepts_extra_data", sa.Boolean(), nullable=False), |
| 52 | sa.Column("connector_extra_data", sa.String(length=1000), nullable=True), |
| 53 | sa.Column("connector_enabled", sa.Boolean(), nullable=False), |
| 54 | sa.PrimaryKeyConstraint("id"), |
| 55 | ) |
| 56 | op.create_table( |
| 57 | "custom_alert_creation_settings", |
| 58 | sa.Column("id", sa.Integer(), nullable=False), |
| 59 | sa.Column("customer_code", sa.String(length=50), nullable=False), |
| 60 | sa.Column("customer_name", sa.String(length=50), nullable=False), |
| 61 | sa.Column("excluded_wazuh_rules", sa.String(length=1024), nullable=True), |
| 62 | sa.Column("excluded_suricata_rules", sa.String(length=1024), nullable=True), |
| 63 | sa.Column("timefield", sa.String(length=1024), nullable=True), |
| 64 | sa.Column("office365_organization_id", sa.String(length=1024), nullable=True), |
| 65 | sa.Column("iris_customer_id", sa.Integer(), nullable=True), |
| 66 | sa.Column("iris_customer_name", sa.String(length=1024), nullable=True), |
| 67 | sa.Column("iris_index", sa.String(length=1024), nullable=True), |
| 68 | sa.Column("grafana_url", sa.String(length=1024), nullable=True), |
| 69 | sa.Column("misp_url", sa.String(length=1024), nullable=True), |
| 70 | sa.Column("opencti_url", sa.String(length=1024), nullable=True), |
| 71 | sa.Column("custom_message", sa.String(length=1024), nullable=True), |
| 72 | sa.Column("shuffle_endpoint", sa.String(length=1024), nullable=True), |
| 73 | sa.Column("nvd_url", sa.String(length=1024), nullable=True), |
| 74 | sa.PrimaryKeyConstraint("id"), |
| 75 | ) |
| 76 | op.create_table( |
| 77 | "customer_integrations", |
| 78 | sa.Column("id", sa.Integer(), nullable=False), |
| 79 | sa.Column("customer_code", sa.String(length=50), nullable=False), |
| 80 | sa.Column("customer_name", sa.String(length=255), nullable=False), |
| 81 | sa.Column("integration_service_id", sa.Integer(), nullable=False), |
| 82 | sa.Column("integration_service_name", sa.String(length=255), nullable=False), |
| 83 | sa.Column("deployed", sa.Boolean(), nullable=False), |
| 84 | sa.PrimaryKeyConstraint("id"), |
| 85 | ) |
| 86 | op.create_table( |
| 87 | "customer_integrations_meta", |
| 88 | sa.Column("id", sa.Integer(), nullable=False), |
| 89 | sa.Column("customer_code", sa.String(length=50), nullable=False), |
| 90 | sa.Column("integration_name", sa.String(length=255), nullable=False), |
| 91 | sa.Column("graylog_input_id", sa.String(length=1024), nullable=True), |
| 92 | sa.Column("graylog_index_id", sa.String(length=1024), nullable=False), |
| 93 | sa.Column("graylog_stream_id", sa.String(length=1024), nullable=False), |
| 94 | sa.Column("grafana_org_id", sa.String(length=1024), nullable=False), |
| 95 | sa.Column("grafana_dashboard_folder_id", sa.String(length=1024), nullable=False), |
| 96 | sa.PrimaryKeyConstraint("id"), |
| 97 | ) |
| 98 | op.create_table( |
| 99 | "customer_provisioning_default_settings", |
| 100 | sa.Column("id", sa.Integer(), nullable=False), |
| 101 | sa.Column("cluster_name", sa.String(length=50), nullable=False), |
| 102 | sa.Column("cluster_key", sa.String(length=1000), nullable=False), |
| 103 | sa.Column("master_ip", sa.String(length=50), nullable=False), |
| 104 | sa.Column("grafana_url", sa.String(length=1024), nullable=False), |
| 105 | sa.Column("wazuh_worker_hostname", sa.String(length=100), nullable=False), |
| 106 | sa.PrimaryKeyConstraint("id"), |
| 107 | ) |
| 108 | op.create_table( |
| 109 | "customers", |
| 110 | sa.Column("id", sa.Integer(), nullable=False), |
| 111 | sa.Column("customer_code", sa.String(length=50), nullable=False), |
| 112 | sa.Column("parent_customer_code", sa.String(length=11), nullable=True), |
| 113 | sa.Column("customer_name", sa.String(length=50), nullable=False), |
| 114 | sa.Column("contact_last_name", sa.String(length=50), nullable=True), |
| 115 | sa.Column("contact_first_name", sa.String(length=50), nullable=True), |
| 116 | sa.Column("phone", sa.String(length=50), nullable=True), |
| 117 | sa.Column("address_line1", sa.String(length=1024), nullable=True), |
| 118 | sa.Column("address_line2", sa.String(length=1024), nullable=True), |
| 119 | sa.Column("city", sa.String(length=50), nullable=True), |
| 120 | sa.Column("state", sa.String(length=50), nullable=True), |
| 121 | sa.Column("postal_code", sa.String(length=15), nullable=True), |
| 122 | sa.Column("country", sa.String(length=50), nullable=True), |
| 123 | sa.Column("customer_type", sa.String(length=50), nullable=True), |
| 124 | sa.Column("logo_file", sa.String(length=64), nullable=True), |
| 125 | sa.Column("created_at", sa.DateTime(), nullable=False), |
| 126 | sa.PrimaryKeyConstraint("id"), |
| 127 | ) |
| 128 | op.create_index(op.f("ix_customers_customer_code"), "customers", ["customer_code"], unique=False) |
| 129 | op.create_table( |
| 130 | "integration_services", |
| 131 | sa.Column("id", sa.Integer(), nullable=False), |
| 132 | sa.Column("service_name", sa.String(length=255), nullable=False), |
| 133 | sa.Column("auth_type", sa.String(length=50), nullable=False), |
| 134 | sa.PrimaryKeyConstraint("id"), |
| 135 | ) |
| 136 | op.create_table( |
| 137 | "license", |
| 138 | sa.Column("id", sa.Integer(), nullable=False), |
| 139 | sa.Column("license_key", sa.String(length=1024), nullable=False), |
| 140 | sa.Column("customer_name", sa.String(length=1024), nullable=False), |
| 141 | sa.Column("customer_email", sa.String(length=1024), nullable=False), |
| 142 | sa.Column("company_name", sa.String(length=1024), nullable=False), |
| 143 | sa.PrimaryKeyConstraint("id"), |
| 144 | ) |
| 145 | op.create_table( |
| 146 | "log_entries", |
| 147 | sa.Column("id", sa.Integer(), nullable=False), |
| 148 | sa.Column("timestamp", sa.DateTime(), nullable=False), |
| 149 | sa.Column("event_type", sa.String(length=256), nullable=False), |
| 150 | sa.Column("user_id", sa.Integer(), nullable=True), |
| 151 | sa.Column("route", sa.String(length=256), nullable=True), |
| 152 | sa.Column("method", sa.String(length=256), nullable=True), |
| 153 | sa.Column("status_code", sa.Integer(), nullable=False), |
| 154 | sa.Column("message", sa.String(length=5024), nullable=True), |
| 155 | sa.Column("additional_info", sa.String(length=5024), nullable=True), |
| 156 | sa.PrimaryKeyConstraint("id"), |
| 157 | ) |
| 158 | op.create_table( |
| 159 | "monitoring_alerts", |
| 160 | sa.Column("id", sa.Integer(), nullable=False), |
| 161 | sa.Column("alert_id", sa.String(length=1024), nullable=False), |
| 162 | sa.Column("alert_index", sa.String(length=1024), nullable=False), |
| 163 | sa.Column("customer_code", sa.String(length=50), nullable=False), |
| 164 | sa.Column("alert_source", sa.String(length=1024), nullable=False), |
| 165 | sa.PrimaryKeyConstraint("id"), |
| 166 | ) |
| 167 | op.create_table( |
| 168 | "role", |
| 169 | sa.Column("id", sa.Integer(), nullable=False), |
| 170 | sa.Column("name", sa.String(length=256), nullable=False), |
| 171 | sa.Column("description", sa.String(length=256), nullable=False), |
| 172 | sa.PrimaryKeyConstraint("id"), |
| 173 | ) |
| 174 | op.create_table( |
| 175 | "scheduled_job_metadata", |
| 176 | sa.Column("id", sa.Integer(), nullable=False), |
| 177 | sa.Column("job_id", sa.String(length=256), nullable=False), |
| 178 | sa.Column("last_success", sa.DateTime(), nullable=True), |
| 179 | sa.Column("time_interval", sa.Integer(), nullable=False), |
| 180 | sa.Column("extra_data", sa.String(length=256), nullable=True), |
| 181 | sa.Column("enabled", sa.Boolean(), nullable=False), |
| 182 | sa.PrimaryKeyConstraint("id"), |
| 183 | ) |
| 184 | op.create_index(op.f("ix_scheduled_job_metadata_job_id"), "scheduled_job_metadata", ["job_id"], unique=False) |
| 185 | op.create_table( |
| 186 | "schedulerjob", |
| 187 | sa.Column("next_run_time", sa.Float(), nullable=True), |
| 188 | sa.Column("job_state", sa.LargeBinary(), nullable=False), |
| 189 | sa.Column("id", sa.String(length=255), nullable=False), |
| 190 | sa.PrimaryKeyConstraint("id"), |
| 191 | ) |
| 192 | op.create_index(op.f("ix_schedulerjob_next_run_time"), "schedulerjob", ["next_run_time"], unique=False) |
| 193 | op.create_table( |
| 194 | "agents", |
| 195 | sa.Column("id", sa.Integer(), nullable=False), |
| 196 | sa.Column("agent_id", sa.String(length=256), nullable=False), |
| 197 | sa.Column("ip_address", sa.String(length=256), nullable=False), |
| 198 | sa.Column("os", sa.String(length=256), nullable=False), |
| 199 | sa.Column("hostname", sa.String(length=256), nullable=False), |
| 200 | sa.Column("label", sa.String(length=256), nullable=False), |
| 201 | sa.Column("critical_asset", sa.Boolean(), nullable=False), |
| 202 | sa.Column("wazuh_last_seen", sa.DateTime(), nullable=False), |
| 203 | sa.Column("velociraptor_id", sa.String(length=256), nullable=False), |
| 204 | sa.Column("velociraptor_last_seen", sa.DateTime(), nullable=False), |
| 205 | sa.Column("wazuh_agent_version", sa.String(length=256), nullable=False), |
| 206 | sa.Column("wazuh_agent_status", sa.String(length=256), nullable=False), |
| 207 | sa.Column("velociraptor_agent_version", sa.String(length=256), nullable=False), |
| 208 | sa.Column("customer_code", sa.String(length=256), nullable=True), |
| 209 | sa.Column("quarantined", sa.Boolean(), nullable=False), |
| 210 | sa.ForeignKeyConstraint( |
| 211 | ["customer_code"], |
| 212 | ["customers.customer_code"], |
| 213 | ), |
| 214 | sa.PrimaryKeyConstraint("id"), |
| 215 | ) |
| 216 | op.create_index(op.f("ix_agents_agent_id"), "agents", ["agent_id"], unique=False) |
| 217 | op.create_table( |
| 218 | "available_integrations_auth_keys", |
| 219 | sa.Column("id", sa.Integer(), nullable=False), |
| 220 | sa.Column("integration_id", sa.Integer(), nullable=True), |
| 221 | sa.Column("integration_name", sa.String(length=255), nullable=False), |
| 222 | sa.Column("auth_key_name", sa.String(length=255), nullable=False), |
| 223 | sa.ForeignKeyConstraint( |
| 224 | ["integration_id"], |
| 225 | ["available_integrations.id"], |
| 226 | ), |
| 227 | sa.PrimaryKeyConstraint("id"), |
| 228 | ) |
| 229 | op.create_table( |
| 230 | "connectorhistory", |
| 231 | sa.Column("id", sa.Integer(), nullable=False), |
| 232 | sa.Column("connector_id", sa.Integer(), nullable=False), |
| 233 | sa.Column("change_timestamp", sa.DateTime(), nullable=False), |
| 234 | sa.Column("change_description", sa.String(length=10000), nullable=False), |
| 235 | sa.ForeignKeyConstraint( |
| 236 | ["connector_id"], |
| 237 | ["connectors.id"], |
| 238 | ), |
| 239 | sa.PrimaryKeyConstraint("id"), |
| 240 | ) |
| 241 | op.create_table( |
| 242 | "custom_alert_creation_event_order", |
| 243 | sa.Column("id", sa.Integer(), nullable=False), |
| 244 | sa.Column("alert_creation_settings_id", sa.Integer(), nullable=True), |
| 245 | sa.Column("order_label", sa.String(length=255), nullable=False), |
| 246 | sa.ForeignKeyConstraint( |
| 247 | ["alert_creation_settings_id"], |
| 248 | ["custom_alert_creation_settings.id"], |
| 249 | ), |
| 250 | sa.PrimaryKeyConstraint("id"), |
| 251 | ) |
| 252 | op.create_table( |
| 253 | "customersmeta", |
| 254 | sa.Column("id", sa.Integer(), nullable=False), |
| 255 | sa.Column("customer_code", sa.String(length=255), nullable=False), |
| 256 | sa.Column("customer_name", sa.String(length=255), nullable=False), |
| 257 | sa.Column("customer_meta_graylog_index", sa.String(length=1024), nullable=False), |
| 258 | sa.Column("customer_meta_graylog_stream", sa.String(length=1024), nullable=False), |
| 259 | sa.Column("customer_meta_grafana_org_id", sa.String(length=1024), nullable=False), |
| 260 | sa.Column("customer_meta_wazuh_group", sa.String(length=1024), nullable=False), |
| 261 | sa.Column("customer_meta_index_retention", sa.String(length=1024), nullable=True), |
| 262 | sa.Column("customer_meta_wazuh_registration_port", sa.String(length=1024), nullable=True), |
| 263 | sa.Column("customer_meta_wazuh_log_ingestion_port", sa.String(length=1024), nullable=True), |
| 264 | sa.Column("customer_meta_wazuh_api_port", sa.String(length=1024), nullable=True), |
| 265 | sa.Column("customer_meta_wazuh_auth_password", sa.String(length=1024), nullable=True), |
| 266 | sa.Column("customer_meta_iris_customer_id", sa.Integer(), nullable=True), |
| 267 | sa.Column("customer_meta_office365_organization_id", sa.String(length=1024), nullable=True), |
| 268 | sa.ForeignKeyConstraint( |
| 269 | ["customer_code"], |
| 270 | ["customers.customer_code"], |
| 271 | ), |
| 272 | sa.PrimaryKeyConstraint("id"), |
| 273 | ) |
| 274 | op.create_table( |
| 275 | "integration_configs", |
| 276 | sa.Column("id", sa.Integer(), nullable=False), |
| 277 | sa.Column("integration_service_id", sa.Integer(), nullable=True), |
| 278 | sa.Column("config_key", sa.String(length=255), nullable=False), |
| 279 | sa.Column("config_value", sa.String(length=1024), nullable=False), |
| 280 | sa.ForeignKeyConstraint( |
| 281 | ["integration_service_id"], |
| 282 | ["integration_services.id"], |
| 283 | ), |
| 284 | sa.PrimaryKeyConstraint("id"), |
| 285 | ) |
| 286 | op.create_table( |
| 287 | "integration_subscriptions", |
| 288 | sa.Column("id", sa.Integer(), nullable=False), |
| 289 | sa.Column("customer_id", sa.Integer(), nullable=True), |
| 290 | sa.Column("integration_service_id", sa.Integer(), nullable=True), |
| 291 | sa.ForeignKeyConstraint( |
| 292 | ["customer_id"], |
| 293 | ["customer_integrations.id"], |
| 294 | ), |
| 295 | sa.ForeignKeyConstraint( |
| 296 | ["integration_service_id"], |
| 297 | ["integration_services.id"], |
| 298 | ), |
| 299 | sa.PrimaryKeyConstraint("id"), |
| 300 | ) |
| 301 | op.create_table( |
| 302 | "user", |
| 303 | sa.Column("id", sa.Integer(), nullable=False), |
| 304 | sa.Column("username", sa.String(length=256), nullable=False), |
| 305 | sa.Column("password", sa.String(length=256), nullable=False), |
| 306 | sa.Column("email", sa.String(length=1024), nullable=False), |
| 307 | sa.Column("created_at", sa.DateTime(), nullable=False), |
| 308 | sa.Column("role_id", sa.Integer(), nullable=True), |
| 309 | sa.ForeignKeyConstraint( |
| 310 | ["role_id"], |
| 311 | ["role.id"], |
| 312 | ), |
| 313 | sa.PrimaryKeyConstraint("id"), |
| 314 | ) |
| 315 | op.create_index(op.f("ix_user_username"), "user", ["username"], unique=False) |
| 316 | op.create_table( |
| 317 | "custom_alert_creation_condition", |
| 318 | sa.Column("id", sa.Integer(), nullable=False), |
| 319 | sa.Column("event_order_id", sa.Integer(), nullable=True), |
| 320 | sa.Column("field_name", sa.String(length=1024), nullable=False), |
| 321 | sa.Column("field_value", sa.String(length=1024), nullable=False), |
| 322 | sa.ForeignKeyConstraint( |
| 323 | ["event_order_id"], |
| 324 | ["custom_alert_creation_event_order.id"], |
| 325 | ), |
| 326 | sa.PrimaryKeyConstraint("id"), |
| 327 | ) |
| 328 | op.create_table( |
| 329 | "custom_alert_creation_event_config", |
| 330 | sa.Column("id", sa.Integer(), nullable=False), |
| 331 | sa.Column("event_order_id", sa.Integer(), nullable=True), |
| 332 | sa.Column("event_id", sa.String(length=255), nullable=False), |
| 333 | sa.Column("field", sa.String(length=1024), nullable=False), |
| 334 | sa.Column("value", sa.String(length=1024), nullable=False), |
| 335 | sa.ForeignKeyConstraint( |
| 336 | ["event_order_id"], |
| 337 | ["custom_alert_creation_event_order.id"], |
| 338 | ), |
| 339 | sa.PrimaryKeyConstraint("id"), |
| 340 | ) |
| 341 | op.create_table( |
| 342 | "integration_auth_keys", |
| 343 | sa.Column("id", sa.Integer(), nullable=False), |
| 344 | sa.Column("subscription_id", sa.Integer(), nullable=True), |
| 345 | sa.Column("auth_key_name", sa.String(length=255), nullable=False), |
| 346 | sa.Column("auth_value", sa.String(length=1024), nullable=False), |
| 347 | sa.ForeignKeyConstraint( |
| 348 | ["subscription_id"], |
| 349 | ["integration_subscriptions.id"], |
| 350 | ), |
| 351 | sa.PrimaryKeyConstraint("id"), |
| 352 | ) |
| 353 | op.create_table( |
| 354 | "smtp", |
| 355 | sa.Column("id", sa.Integer(), nullable=False), |
| 356 | sa.Column("email", sa.String(length=1024), nullable=False), |
| 357 | sa.Column("smtp_password", sa.String(length=256), nullable=False), |
| 358 | sa.Column("smtp_server", sa.String(length=256), nullable=False), |
| 359 | sa.Column("smtp_port", sa.Integer(), nullable=False), |
| 360 | sa.Column("user_id", sa.Integer(), nullable=False), |
| 361 | sa.ForeignKeyConstraint( |
| 362 | ["user_id"], |
| 363 | ["user.id"], |
| 364 | ), |
| 365 | sa.PrimaryKeyConstraint("id"), |
| 366 | ) |
| 367 | # ### end Alembic commands ### |
| 368 | |
| 369 | |
| 370 | def downgrade() -> None: |
| 371 | # ### commands auto generated by Alembic - please adjust! ### |
| 372 | op.drop_table("smtp") |
| 373 | op.drop_table("integration_auth_keys") |
| 374 | op.drop_table("custom_alert_creation_event_config") |
| 375 | op.drop_table("custom_alert_creation_condition") |
| 376 | op.drop_index(op.f("ix_user_username"), table_name="user") |
| 377 | op.drop_table("user") |
| 378 | op.drop_table("integration_subscriptions") |
| 379 | op.drop_table("integration_configs") |
| 380 | op.drop_table("customersmeta") |
| 381 | op.drop_table("custom_alert_creation_event_order") |
| 382 | op.drop_table("connectorhistory") |
| 383 | op.drop_table("available_integrations_auth_keys") |
| 384 | op.drop_index(op.f("ix_agents_agent_id"), table_name="agents") |
| 385 | op.drop_table("agents") |
| 386 | op.drop_index(op.f("ix_schedulerjob_next_run_time"), table_name="schedulerjob") |
| 387 | op.drop_table("schedulerjob") |
| 388 | op.drop_index(op.f("ix_scheduled_job_metadata_job_id"), table_name="scheduled_job_metadata") |
| 389 | op.drop_table("scheduled_job_metadata") |
| 390 | op.drop_table("role") |
| 391 | op.drop_table("monitoring_alerts") |
| 392 | op.drop_table("log_entries") |
| 393 | op.drop_table("license") |
| 394 | op.drop_table("integration_services") |
| 395 | op.drop_index(op.f("ix_customers_customer_code"), table_name="customers") |
| 396 | op.drop_table("customers") |
| 397 | op.drop_table("customer_provisioning_default_settings") |
| 398 | op.drop_table("customer_integrations_meta") |
| 399 | op.drop_table("customer_integrations") |
| 400 | op.drop_table("custom_alert_creation_settings") |
| 401 | op.drop_table("connectors") |
| 402 | op.drop_table("available_integrations") |
| 403 | # ### end Alembic commands ### |