145 lines
5.3 KiB
Python
145 lines
5.3 KiB
Python
"""
|
|
Database schema updates for session management
|
|
"""
|
|
from sqlalchemy import text
|
|
from sqlalchemy.orm import Session
|
|
|
|
from app.database.base import get_db, engine
|
|
from app.models.sessions import UserSession, SessionActivity, SessionConfiguration, SessionSecurityEvent
|
|
from app.utils.logging import get_logger
|
|
|
|
logger = get_logger(__name__)
|
|
|
|
|
|
def create_session_tables():
|
|
"""Create session management tables"""
|
|
try:
|
|
# Import and create all tables
|
|
from app.models.sessions import UserSession
|
|
UserSession.metadata.create_all(bind=engine)
|
|
|
|
logger.info("Session management tables created successfully")
|
|
return True
|
|
|
|
except Exception as e:
|
|
logger.error(f"Failed to create session tables: {str(e)}")
|
|
return False
|
|
|
|
|
|
def create_session_indexes():
|
|
"""Create additional indexes for session management performance"""
|
|
|
|
indexes = [
|
|
# UserSession indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_user_sessions_user_status ON user_sessions(user_id, status)",
|
|
"CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_status ON user_sessions(expires_at, status)",
|
|
"CREATE INDEX IF NOT EXISTS idx_user_sessions_last_activity ON user_sessions(last_activity)",
|
|
"CREATE INDEX IF NOT EXISTS idx_user_sessions_ip_address ON user_sessions(ip_address)",
|
|
"CREATE INDEX IF NOT EXISTS idx_user_sessions_risk_score ON user_sessions(risk_score)",
|
|
|
|
# SessionActivity indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_session_activities_user_timestamp ON session_activities(user_id, timestamp)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_activities_session_timestamp ON session_activities(session_id, timestamp)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_activities_activity_type ON session_activities(activity_type)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_activities_suspicious ON session_activities(is_suspicious)",
|
|
|
|
# SessionSecurityEvent indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_session_security_events_user_timestamp ON session_security_events(user_id, timestamp)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_security_events_severity ON session_security_events(severity)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_security_events_resolved ON session_security_events(resolved)",
|
|
"CREATE INDEX IF NOT EXISTS idx_session_security_events_event_type ON session_security_events(event_type)",
|
|
|
|
# SessionConfiguration indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_session_configurations_user_id ON session_configurations(user_id)"
|
|
]
|
|
|
|
try:
|
|
db = next(get_db())
|
|
|
|
for index_sql in indexes:
|
|
try:
|
|
db.execute(text(index_sql))
|
|
logger.debug(f"Created index: {index_sql.split('idx_')[1].split(' ')[0] if 'idx_' in index_sql else 'unknown'}")
|
|
except Exception as e:
|
|
logger.warning(f"Failed to create index: {str(e)}")
|
|
|
|
db.commit()
|
|
logger.info("Session management indexes created successfully")
|
|
return True
|
|
|
|
except Exception as e:
|
|
logger.error(f"Failed to create session indexes: {str(e)}")
|
|
return False
|
|
finally:
|
|
db.close()
|
|
|
|
|
|
def create_default_session_configuration():
|
|
"""Create default global session configuration"""
|
|
try:
|
|
db = next(get_db())
|
|
|
|
# Check if global config already exists
|
|
existing_config = db.query(SessionConfiguration).filter(
|
|
SessionConfiguration.user_id.is_(None)
|
|
).first()
|
|
|
|
if not existing_config:
|
|
# Create default global configuration
|
|
global_config = SessionConfiguration(
|
|
user_id=None, # Global configuration
|
|
max_concurrent_sessions=3,
|
|
session_timeout_minutes=480, # 8 hours
|
|
idle_timeout_minutes=60, # 1 hour
|
|
require_session_renewal=True,
|
|
renewal_interval_hours=24,
|
|
force_logout_on_ip_change=False,
|
|
suspicious_activity_threshold=5
|
|
)
|
|
|
|
db.add(global_config)
|
|
db.commit()
|
|
|
|
logger.info("Created default global session configuration")
|
|
else:
|
|
logger.info("Global session configuration already exists")
|
|
|
|
return True
|
|
|
|
except Exception as e:
|
|
logger.error(f"Failed to create default session configuration: {str(e)}")
|
|
return False
|
|
finally:
|
|
db.close()
|
|
|
|
|
|
def setup_session_management():
|
|
"""Complete setup of session management system"""
|
|
logger.info("Setting up session management system...")
|
|
|
|
success = True
|
|
|
|
# Create tables
|
|
if not create_session_tables():
|
|
success = False
|
|
|
|
# Create indexes
|
|
if not create_session_indexes():
|
|
success = False
|
|
|
|
# Create default configuration
|
|
if not create_default_session_configuration():
|
|
success = False
|
|
|
|
if success:
|
|
logger.info("Session management system setup completed successfully")
|
|
else:
|
|
logger.error("Session management system setup completed with errors")
|
|
|
|
return success
|
|
|
|
|
|
if __name__ == "__main__":
|
|
# Run setup when script is executed directly
|
|
setup_session_management()
|