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