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.