3.2. Database

3.2.1. Table Relationships

The following diagram outlines the relationships of the various tables in the database. Nodes are connected by foreign key constraints. The arrow head references the object which has the constraint.

// diagram of database table relationships digraph { AlertSubscription AuthenticatedSession Campaign CampaignType Company CompanyDepartment Credential DeaddropConnection DeaddropDeployment Industry LandingPage StorageData Message User Visit // style=bold for one to one relationships // style=dashed for foreign key constraints which are nullable Campaign -> AlertSubscription Campaign -> Credential Campaign -> DeaddropDeployment Campaign -> DeaddropConnection Campaign -> LandingPage Campaign -> Message Campaign -> Visit CampaignType -> Campaign [style=dashed] Company -> Campaign [style=dashed] CompanyDepartment -> Message [style=dashed] DeaddropDeployment -> DeaddropConnection Industry -> Company [style=dashed] LandingPage -> Visit [style=dashed] Message -> Credential Message -> Visit User -> AlertSubscription User -> AuthenticatedSession [style=bold] User -> Campaign Visit -> Credential }

3.2.2. Schema Versioning

The King Phisher database uses an internal version number defined as SCHEMA_VERSION which is used by the initialization code to determine whether or not the stored database schema (the one existing in the database) matches the running schema (the one defined in the source code). When the schemas are not the same, the database is considered to be incompatible. The King Phisher server process will then automatically attempt to upgrade the stored database schema.

If the stored database schema is newer than the running schema, the King Phisher process can not downgrade it. This would happen for example if a developer were to use version control to revert the project code to an older version. In this case the older version would have no knowledge of the newer schema and would therefor be unable to “downgrade” it to a compatible version. In this case the developer must use the included database schema migration utilities to update the stored database schema to a compatible version before switchign to the older project revision.

3.2.2.1. Alembic

King Phisher uses Alembic to manage its database schema versions. This can be used to explicitly upgrade and downgrade the schema version from the command line. The Alembic environment files are stored with the server data files at data/server/king_phisher/alembic.

The King Phisher version of the Alembic env file is modified to support two ways for the database connection string to be passed from the command line. This removes the need to store the credentials int the alembic.ini file. The two supported options are “config” and “database”. Both are supplied as settings to the -x option in the form -x SETTING=VALUE with no spaces between the settings and their values.

config
The config= option takes a path to the King Phisher server configuration file where the database connection string will be used.
database
The database= option takes an explicit database connection string on the command line. The syntax is the same as how it would be stored in the server configuration file.

Example running Alembic’s current subcommand with the database connection string taken from the server’s configuration file.

# run from data/server/king_phisher
alembic -x config=../../../server_config.yml current

3.2.2.1.1. Schema Version Identifiers

Alembic and King Phisher must keep separate version identifiers. This is because Alembic uses revision strings in it’s internal, linked format while King Phisher uses simple numeric versioning to easily identify newer schemas. When creating a new Alembic migration file, it’s important to set the King Phisher schema version as well which must be explicitly done by the developer. The King Phisher stored database schema version exists in the storage_data in the metadata namespace with the key schema_version. See set_metadata() for a convenient way to set this value. The Alembic revision identifier is stored as a single record in the alembic_version table under the version_num column.