Skip to content
Switch branches/tags

Latest commit


Git stats


Failed to load latest commit information.
Latest commit message
Commit time

Database Schema

Confirmed VPN uses two databases: one Postgres and one Redis. The following documents each database's schema and purpose.


The Postgres database stores Admin Users, Certificates, Partner Snapshots, Partner Users, Partners, Subscriptions, Support Users, and Users.



This table stores client certificates, which are sent to clients on Main /get-key. These certificates allow the client to authenticate with and use the VPNs.

Name Type Description
serial text Serial of the certificate.
source_id text The Source ID that this certificate belongs to.
user_id text The User ID associated with this certificate.
revoked boolean Whether or not this certificate has been revoked. If evidence is found of this certificate being abusive, this is set to true and the user is throttled heavily.
assigned boolean Whether or not this certificate has been assigned. When a new user is created and confirmed, it is assigned a certificate and this value changes from false to true.
p12_encrypted text The client certificate AES encrypted with AES_P12_KEY. This is decrypted and sent to user on Main POST /get-key.


These are consumers/users. A user is created by either IAP receipt or email/password. A user cannot sign in using email until their email is confirmed.

The users table has both email and email_encrypted columns because email allows fast checking of whether or not an email exists in the database (frequently done), and email_encrypted allows slow actual retrieval of the email (rarely done). The email is not stored in plaintext as an added layer of security.

Name Type Description
id text User ID
email text SHA512 hash of user email with EMAIL_SALT.
email_encrypted text AES encrypted user email with AES_EMAIL_KEY.
password text BCrypted user password.
stripe_id text Stripe ID of the user.
email_confirmed boolean Whether or not the email is confirmed.
email_confirm_code text Code to confirm an email.
change_email text Hash of email to change to.
password_reset_code text Code to reset password.
month_usage_megabytes integer Monthly usage in megabytes.
month_usage_update timestamp Last time monthly usage was updated.


Each row is a subscription. Subscriptions can be of receipt_type ios, android, or stripe. Subscriptions are updated by Main in various APIs (/signin, /subscription-event, etc), but also updated by Renewer when expiration_date is coming up.

Name Type Description
user_id text User ID
receipt_id text iOS, Android, or Stripe receipt ID
receipt_type receipt_type Can be ios, android, or stripe
plan_type plan_type Can be ios-monthly, ios-annual, all-monthly, all-annual, android-monthly, or android-annual
expiration_date timestamp Time that user's subscription expires
cancellation_date timestamp Time that user's subscription is cancelled
receipt_data text Receipt data encrypted with AES_RECEIPT_KEY
in_trial boolean Whether or not subscription is in trial (unreliable flag sometimes)
failed_last_check boolean Whether or not last Renewer update check for this entry failed.
renew_enabled boolean Whether or not subscription's renewal is enabled (unreliable flag sometimes)
updated timestamp Time this subscription was last updated, either through Main API (like /subscription-event) or Renewer

admin_users & support_users

These are super slimmed down versions of users. These track Admin and Support users. Email is kept in plaintext.

Name Type Description
email text Plaintext email.
password text BCrypted user password.
email_confirmed boolean Whether or not the email is confirmed.
email_confirm_code text Code to confirm an email.
password_reset_code text Code to reset password.


The Postgres database is accessible by all Confirmed servers except VPN. Security Groups prevent external direct access to the database.

Each server type (Main, Renewer, Helper, etc) can only use a specific database ROLE with the bare minimum permissions scoped to only what the server type needs. For example, renewer can do SELECT and UPDATE on the subscriptions table, but has no access at all to the users table.

Specific grants are located in schema.sql.


The Redis database is used for Brute Force Tracking, User Sessions, and Admin/Support User Sessions.


Redis on AWS is hosted in a private VPC not accessible by the internet. All Confirmed servers access Redis with encrypted transit (TLS) and a Redis AUTH secret stored in REDIS_PASSWORD. Encryption at rest is enabled.


Brute Force restrictions can be looked up and cleared in the Admin Dashboard.


Prefix Description
a: Admin User Sessions
u: User Sessions
erl: Rate Limiting Tracking
s: Support User Sessions


If you have any questions, concerns, or other feedback, please let us know any feedback in Github issues or by e-mail.

We also have a bug bounty program -- please email for details.


This project is licensed under the GPL License - see the file for details



No description, website, or topics provided.




No releases published


No packages published