3.1.2. Database Schema

This schema defines the various database tables and fields for the objects managed by the King Phisher server. These are exposed over the GraphQL interface with the exception of fields which are restricted based on permissions.

3.1.2.1. Tables

alert_subscriptions

Subscriptions to alerts for campaigns that users are interested in receiving notifications for.

expiration

The expiration for which the user can set to no longer receive notifications.

Nullable

True

Type

DateTime

id
Primary Key

True

Type

Integer

user_id

The identifier of the user which created the alert subscription.

Nullable

False

Foreignkey

users.id

campaign_id

The identifier of the campaign the user is interested in receiving notifications for.

Nullable

False

Foreignkey

campaigns.id

authenticated_sessions

An authenticated session associated with a user that has logged into the server over RPC.

id
Primary Key

True

Type

String

created

The time at which the session was created.

Nullable

False

Type

DateTime

last_seen

The time at which the last authenticated request associated with this session was seen. Used to support session timeouts.

Nullable

False

Type

DateTime

user_id

The identifier of the authenticated user who established this session.

Nullable

False

Foreignkey

users.id

campaign_types

The type information for a particular campaign. This information is useful for determining the success metrics. For example, a campaign type can be set as “Credentials” for a campaign intending to collect credentials from users while a campaign which does not can have the type set to “Visits”. This will ensure that the campaign of type “Visits” is not considered to be less successful due to it having not collected any credentials.

id
Primary Key

True

Type

Integer

name

A short name for the campaign type, e.g. “Credentials”.

Nullable

False

Type

String

description

A description of the campaign type, e.g. “Campaigns that intend to collect credentials from target users”.

Nullable

True

Type

String

campaigns

A logical testing unit representing a single campaign.

expiration

The time at which the server should cease collection of testings information.

Nullable

True

Type

DateTime

id
Primary Key

True

Type

Integer

name

A short, human-readable name for the campaign.

Nullable

False

Type

String

description

A field to store any descriptive information regarding the campaign such as why or how it was conducted.

Nullable

True

Type

String

user_id

The identifier of the user who originally created the campaign.

Nullable

False

Foreignkey

users.id

created

The time at which the campaign was created.

Nullable

True

Type

DateTime

max_credentials

The maximum number of credentials to collect per user. This setting can be used to alter how the server behaves when a target submits multiple credentials during the course of a campaign.

Nullable

True

Type

Integer

campaign_type_id

The identifier for the campaign’s type.

Nullable

True

Foreignkey

campaign_types.id

company_id

The identifier for the company for which this campaign performs testing.

Nullable

True

Foreignkey

companies.id

credential_regex_username

A regular expression that can be used to determine the validity of a credential’s username field.

Nullable

True

Type

String

credential_regex_password

A regular expression that can be used to determine the validity of a credential’s password field.

Nullable

True

Type

String

credential_regex_mfa_token

A regular expression that can be used to determine the validity of a credential’s mfa token field.

Nullable

True

Type

String

companies

An entity for which a campaign’s test is conducted for.

id
Primary Key

True

Type

Integer

name

A short, human-readable name for the entity.

Nullable

False

Type

String

description

A field to store any descriptive information regarding the entity.

Nullable

True

Type

String

industry_id

The identifier of the primary industry in which the entity operates.

Nullable

True

Foreignkey

industries.id

url_main

The URL to the entity’s main web site, useful for incorporation into site templates.

Nullable

True

Type

String

url_email

The URL to the entity’s email portal, useful for incorporation into site templates.

Nullable

True

Type

String

url_remote_access

The URL for the entity’s remote access solution, useful for incorporation into site templates.

Nullable

True

Type

String

company_departments

A subdivision of a company used to group targets with similar roles together.

id
Primary Key

True

Type

Integer

name

A short, human-readable name for the subdivision.

Nullable

False

Type

String

description

A field to store any descriptive information regarding the subdivision.

Nullable

True

Type

String

credentials

A table storing authentication information collected from a target during the course of a campaign.

id
Primary Key

True

Type

Integer

visit_id

The identifier of the visit which submitted the credential information.

Nullable

False

Foreignkey

visits.id

message_id

The identifier of the message which submitted the credential information.

Nullable

False

Foreignkey

messages.id

campaign_id

The identifier campaign the information was collected as a part of.

Nullable

False

Foreignkey

campaigns.id

username

The username submitted by the target.

Nullable

True

Type

String

password

The password submitted by the target.

Nullable

True

Type

String

mfa_token

The multi-factor authentication (MFA) token submitted by the target. This may, for example be a Time-Based One-Time Password (TOTP) code.

Nullable

True

Type

String

submitted

The time at which the credential information was submitted.

Nullable

True

Type

DateTime

regex_validated

Whether or not the fields passed validation with the regular expressions defined by the campaign at the time the credentials information was submitted. If no validation took place because no regular expressions were defined by the campaign, this field is null. If a regular expression for validation was defined for a field that was not submitted, validation fails and this field is false. See validate_credential() for more information.

Nullable

True

Type

Boolean

deaddrop_connections

A connection instance of an agent which has sent information to the server to prove that the agent was executed.

id
Primary Key

True

Type

Integer

deployment_id

The deployment identifier of agent which initiated the connection.

Nullable

False

Foreignkey

deaddrop_deployments.id

campaign_id

The identifier campaign the information was collected as a part of.

Nullable

False

Foreignkey

campaigns.id

count

The number of times the agent made the connection with the same information, implying that the agent was executed multiple times.

Nullable

True

Type

Integer

ip

The external IP address from which this information was submitted and collected from.

Nullable

True

Type

String

local_username

The username that executed the agent.

Nullable

True

Type

String

local_hostname

The hostname the agent was executed on.

Nullable

True

Type

String

local_ip_addresses

The local IP addresses the agent identified on the system from which it was executed.

Nullable

True

Type

String

first_seen

The first time the information was submitted to the server.

Nullable

True

Type

DateTime

last_seen

The last time the information was submitted to the server.

Nullable

True

Type

DateTime

deaddrop_deployments

An instance of a generated agent which can be distributed as part of testing to identify users that are susceptible to executing arbitrary programs.

id
Primary Key

True

Type

String

campaign_id

The identifier of the campaign the deaddrop agent was generated for.

Nullable

False

Foreignkey

campaigns.id

destination

A descriptive field describing where the agent was deployed to. Used for reporting and tracking purposes.

Nullable

True

Type

String

industries

An industry in which a company operates in.

id
Primary Key

True

Type

Integer

name

A short, human-readable name for the industry.

Nullable

False

Type

String

description

A field to store any descriptive information regarding the industry.

Nullable

True

Type

String

landing_pages

A page that is intended to be visited during the course of a test to be qualified as a failure. Visits to the landing page will increment the visits.count field, while requests to non-landing pages will not. A campaign may have one or more landing pages, and they are automatically identified from the Target URL when messages are sent.

id
Primary Key

True

Type

Integer

campaign_id

The identifier of the campaign this landing page is associated with.

Nullable

False

Foreignkey

campaigns.id

hostname

The hostname component of the URL this landing page uses.

Nullable

False

Type

String

page

The path component of the URL this landing page uses.

Nullable

False

Type

String

messages

A message that was sent to a target user to test their susceptibility to phishing attempts.

id
Primary Key

True

Type

String

campaign_id

The identifier of the campaign which this message was sent as a part of.

Nullable

False

Foreignkey

campaigns.id

target_email

The email address of the user who this message was sent to.

Nullable

True

Type

String

first_name

The first name of the user who this message was sent to.

Nullable

True

Type

String

last_name

The last name of the user who this message was sent to.

Nullable

True

Type

String

opened

The time at which the message was confirmed to have been opened. This field is prone to false negatives due to many email clients not automatically loading remote images.

Nullable

True

Type

DateTime

opener_ip

The IP address which opened the message.

Nullable

True

Type

String

opener_user_agent

The user agent of the request sent when the message was opened.

Nullable

True

Type

String

sent

The time at which the message was sent to the target.

Nullable

True

Type

DateTime

reported

The time at which the message was reported by the target.

Nullable

True

Type

DateTime

trained

Whether or not the taget agreed to any training provided during the course of the testing.

Nullable

True

Type

Boolean

delivery_status

A short, human-readable status regarding the state of delivery of the message such as delivered, rejected or deferred.

Nullable

True

Type

String

delivery_details

Any additional details regarding the state of the message delivery status.

Nullable

True

Type

String

testing

Whether or not the message was intended for testing and should be omitted from the overall results.

Nullable

False

Type

Boolean

company_department_id

The identifier of the company subdivision that the target is a member of.

Nullable

True

Foreignkey

company_departments.id

storage_data

Storage for internal server data that is generated at run time.

id
Primary Key

True

Type

Integer

created

The time at which the data unit was created.

Nullable

True

Type

DateTime

modified

The time at which the data unit was modified.

Nullable

True

Type

DateTime

namespace

The namespace in which the data unit exists to allow the same storage_data.key to be used multiple times while remaining uniquely identifiable.

Nullable

True

Type

String

key

The key by which the data unit is retrieved. This value must be unique within the defined storage_data.namespace.

Nullable

False

Type

String

value

The readable and writable data unit itself, serialized as a binary object to be loaded and unloaded from the database.

Nullable

True

Type

Binary

users

An authorized user as loaded through the server’s authentication mechanism.

expiration

The time at which the user should no longer be able to authenticate to the server.

Nullable

True

Type

DateTime

id
Primary Key

True

Type

Integer

name

The name of the user.

Nullable

False

Type

String

description

A field to store any descriptive information regarding the user.

Nullable

True

Type

String

phone_carrier

The service provider of the user’s cell phone. This information is used to send text messages via the providers email to SMS gateway.

Nullable

True

Type

String

phone_number

The user’s cell phone number. This information is used to provide the user with alerts regarding campaigns to which they have subscribed.

Nullable

True

Type

String

email_address

The user’s email address. This information is used to provide the user with alerts regarding campaigns to which they have been subscribed.

Nullable

True

Type

String

otp_secret

A secret value used when prompting for Multi Factor Authentication (MFA) to the server.

Nullable

True

Type

String

last_login

The time at which the user last authenticated.

Nullable

True

Type

DateTime

access_level

The level of access available to a users, where a higher number represents less access than a lower number.

Nullable

False

Type

Integer

visits

An instance where a targeted user has failed their testing attempt by visiting the link provided to them from a message.

id
Primary Key

True

Type

String

message_id

The identifier of the message that was sent to the target which initiated the visit.

Nullable

False

Foreignkey

messages.id

campaign_id

The identifier of the campaign that this visit is associated with.

Nullable

False

Foreignkey

campaigns.id

count

The number of times the user visited a landing page associated with the campaign. This would be the case when the user visits the link they were provided multiple times from the same browser.

Nullable

True

Type

Integer

ip

The IP address from which the user visited the server.

Nullable

True

Type

String

details

Any applicable details regarding the visist.

Nullable

True

Type

String

user_agent

The user agent of the visist request.

Nullable

True

Type

String

first_landing_page_id

The identifier of the first landing page the visit was made. This is used to determine which landing page a user visited if multiple landing pages are associated with the campaign.

Nullable

True

Foreignkey

landing_pages.id

first_seen

The time at which the first visit was made to the server.

Nullable

True

Type

DateTime

last_seen

The time at which the last visit was made to the server.

Nullable

True

Type

DateTime