PostgreSQL setup

important

This is needed only if you are running the SuperTokens core yourself.

blog

We also have a blog post writeup highlighting all the steps in more detail for different scenarios.

1) Create a database 🛠️#

CREATE DATABASE supertokens;

You can skip this step if you want SuperTokens to write to your own database. In this case, you will need to provide your database name as shown in the step below.

2) Connect SuperTokens to your database 🔌#

With Docker
Without Docker

caution

Host being localhost / 127.0.0.1 will not work in a docker image. Instead, please provide the database's local / public hostname or IP address.

You also need to make the database listen on all the IP's of the local machine. This can be done by editing the postgresql.conf config file and setting the value of listen_addresses to 0.0.0.0.


docker run \
    -p 3567:3567 \
    -e POSTGRESQL_CONNECTION_URI="postgresql://username:pass@host/dbName" \
    -d registry.supertokens.io/supertokens/supertokens-postgresql

# OR

docker run \
    -p 3567:3567 \
    -e POSTGRESQL_USER="username" \
    -e POSTGRESQL_PASSWORD="password" \
    -e POSTGRESQL_HOST="host" \
    -e POSTGRESQL_PORT="5432" \
    -e POSTGRESQL_DATABASE_NAME="supertokens" \
    -d registry.supertokens.io/supertokens/supertokens-postgresql

tip

You can also provide the table schema by providing the POSTGRESQL_TABLE_SCHEMA option.

# You need to add the following to the config.yaml file.
# The file path can be found by running the "supertokens --help" command

postgresql_connection_uri: "postgresql://username:pass@host/dbName"

# OR

postgresql_user: "username"

postgresql_password: "password"

postgresql_host: "host"

postgresql_port: "5432"

postgresql_database_name: "supertokens"

You can also provide the table schema by providing the postgresql_table_schema option.

3) Create tables 👩‍💻👨‍💻#

note

This happens automatically, unless you provide a PostgreSQL user that doesn't have table creation permission.

CREATE TABLE IF NOT EXISTS key_value (
    name VARCHAR(128),
    value TEXT,
    created_at_time BIGINT,
    CONSTRAINT key_value_pkey PRIMARY KEY(name)
);

CREATE TABLE IF NOT EXISTS all_auth_recipe_users(
    user_id CHAR(36) NOT NULL,
    recipe_id VARCHAR(128) NOT NULL,
    time_joined BIGINT NOT NULL,
    CONSTRAINT all_auth_recipe_users_pkey PRIMARY KEY (user_id)
);
CREATE INDEX all_auth_recipe_users_pagination_index ON all_auth_recipe_users (time_joined DESC, user_id DESC);

CREATE TABLE IF NOT EXISTS userid_mapping(
    supertokens_user_id CHAR(36) NOT NULL CONSTRAINT userid_mapping_supertokens_user_id_key UNIQUE,
    external_user_id VARCHAR(128) NOT NULL CONSTRAINT userid_mapping_external_user_id_key UNIQUE,
    external_user_id_info TEXT,
    CONSTRAINT userid_mapping_pkey PRIMARY KEY (supertokens_user_id, external_user_id),
    CONSTRAINT userid_mapping_supertokens_user_id_fkey FOREIGN KEY (supertokens_user_id) REFERENCES all_auth_recipe_users(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS dashboard_users(
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256) NOT NULL CONSTRAINT dashboard_users_email_key UNIQUE,
    password_hash VARCHAR(256) NOT NULL,
    time_joined BIGINT NOT NULL,
    CONSTRAINT dashboard_users_pkey PRIMARY KEY (user_id) 
);

CREATE TABLE IF NOT EXISTS dashboard_user_sessions(
    session_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    time_created BIGINT NOT NULL,
    expiry BIGINT NOT NULL,
    CONSTRAINT dashboard_user_sessions_pkey PRIMARY KEY(session_id),
    CONSTRAINT dashboard_user_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES dashboard_users(user_id) ON DELETE CASCADE
);

CREATE INDEX dashboard_user_sessions_expiry_index ON dashboard_user_sessions(expiry);

CREATE TABLE session_access_token_signing_keys (
    created_at_time BIGINT NOT NULL,
    value TEXT,
    CONSTRAINT session_access_token_signing_keys_pkey PRIMARY KEY (created_at_time)
);

CREATE TABLE IF NOT EXISTS session_info (
    session_handle VARCHAR(255) NOT NULL,
    user_id VARCHAR(128) NOT NULL,
    refresh_token_hash_2 VARCHAR(128) NOT NULL,
    session_data TEXT,
    expires_at BIGINT NOT NULL,
    created_at_time BIGINT NOT NULL,
    jwt_user_payload TEXT,
    CONSTRAINT session_info_pkey PRIMARY KEY(session_handle)
);

CREATE TABLE IF NOT EXISTS user_last_active (
    user_id VARCHAR(128),
    last_active_time BIGINT,
    PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS emailpassword_users (
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256) NOT NULL CONSTRAINT emailpassword_users_email_key UNIQUE,
    password_hash VARCHAR(256) NOT NULL,
    time_joined BIGINT NOT NULL,
    CONSTRAINT emailpassword_users_pkey PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS emailpassword_pswd_reset_tokens (
    user_id CHAR(36) NOT NULL,
    token VARCHAR(128) NOT NULL CONSTRAINT emailpassword_pswd_reset_tokens_token_key UNIQUE,
    token_expiry BIGINT NOT NULL,
    CONSTRAINT emailpassword_pswd_reset_tokens_pkey PRIMARY KEY (user_id, token),
    CONSTRAINT emailpassword_pswd_reset_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES emailpassword_users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX emailpassword_password_reset_token_expiry_index ON emailpassword_pswd_reset_tokens(token_expiry);

CREATE TABLE IF NOT EXISTS emailverification_verified_emails (
    user_id VARCHAR(128) NOT NULL,
    email VARCHAR(256),
    CONSTRAINT emailverification_verified_emails_pkey PRIMARY KEY (user_id, email)
);

CREATE TABLE IF NOT EXISTS emailverification_tokens (
    user_id VARCHAR(128) NOT NULL,
    email VARCHAR(256),
    token VARCHAR(128) NOT NULL CONSTRAINT emailverification_tokens_token_key UNIQUE,
    token_expiry BIGINT NOT NULL,
    CONSTRAINT emailverification_tokens_pkey PRIMARY KEY (user_id, email, token)
);

CREATE INDEX emailverification_tokens_index ON emailverification_tokens(token_expiry);

CREATE TABLE IF NOT EXISTS thirdparty_users (
    third_party_id VARCHAR(28) NOT NULL,
    third_party_user_id VARCHAR(256) NOT NULL,
    user_id CHAR(36) NOT NULL CONSTRAINT thirdparty_users_user_id_key UNIQUE,
    email VARCHAR(256) NOT NULL,
    time_joined BIGINT NOT NULL,
    CONSTRAINT thirdparty_users_pkey PRIMARY KEY (third_party_id, third_party_user_id)
);

CREATE TABLE IF NOT EXISTS passwordless_users (
    user_id CHAR(36) NOT NULL,
    email VARCHAR(256) CONSTRAINT passwordless_users_email_key UNIQUE,
    phone_number VARCHAR(256) CONSTRAINT passwordless_users_phone_number_key UNIQUE,
    time_joined BIGINT NOT NULL,
    CONSTRAINT passwordless_users_pkey PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS passwordless_devices (
    device_id_hash CHAR(44) NOT NULL,
    email VARCHAR(256),
    phone_number VARCHAR(256),
    link_code_salt CHAR(44) NOT NULL,
    failed_attempts INT NOT NULL,
    CONSTRAINT passwordless_devices_pkey PRIMARY KEY (device_id_hash)
);
CREATE INDEX passwordless_devices_email_index ON passwordless_devices USING HASH (email);
CREATE INDEX passwordless_devices_phone_number_index ON passwordless_devices USING HASH (phone_number);

CREATE TABLE IF NOT EXISTS passwordless_codes (
    code_id CHAR(36) NOT NULL,
    device_id_hash CHAR(44) NOT NULL,
    link_code_hash CHAR(44) NOT NULL CONSTRAINT passwordless_link_code_hash_key UNIQUE,
    created_at BIGINT NOT NULL,
    CONSTRAINT passwordless_codes_pkey PRIMARY KEY (code_id),
        CONSTRAINT passwordless_codes_device_id_hash_fkey FOREIGN KEY (device_id_hash)
          REFERENCES passwordless_devices(device_id_hash) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX passwordless_codes_created_at_index ON passwordless_codes(created_at);
CREATE INDEX IF NOT EXISTS passwordless_codes_device_id_hash_index ON passwordless_codes(device_id_hash);

CREATE TABLE IF NOT EXISTS jwt_signing_keys (
    key_id VARCHAR(255) NOT NULL,
    key_string TEXT NOT NULL,
    algorithm VARCHAR(10) NOT NULL,
    created_at BIGINT,
    CONSTRAINT jwt_signing_keys_pkey PRIMARY KEY(key_id)
);

CREATE TABLE IF NOT EXISTS user_metadata (
    user_id VARCHAR(128) NOT NULL,
    user_metadata TEXT NOT NULL,
    CONSTRAINT user_metadata_pkey PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS roles (
  role VARCHAR(255) NOT NULL,
  CONSTRAINT roles_pkey PRIMARY KEY (role)
);

CREATE TABLE IF NOT EXISTS role_permissions (
  role VARCHAR(255) NOT NULL,
  permission VARCHAR(255) NOT NULL,
  CONSTRAINT role_permissions_pkey PRIMARY KEY (role, permission),
  CONSTRAINT role_permissions_role_fkey FOREIGN KEY (role) REFERENCES roles(role) ON DELETE CASCADE
);

CREATE INDEX role_permissions_permission_index ON role_permissions(permission);

CREATE TABLE IF NOT EXISTS user_roles (
  user_id VARCHAR(128) NOT NULL,
  role VARCHAR(255) NOT NULL,
  CONSTRAINT user_roles_pkey PRIMARY KEY (user_id, role),
  CONSTRAINT user_roles_role_fkey FOREIGN KEY (role) REFERENCES roles (role) ON DELETE CASCADE
);

CREATE INDEX user_roles_role_index ON user_roles(role);

CREATE TABLE IF NOT EXISTS totp_users (
    user_id VARCHAR(128) NOT NULL,
    PRIMARY KEY (user_id)
);

CREATE TABLE IF NOT EXISTS totp_user_devices (
    user_id VARCHAR(128) NOT NULL,
    device_name VARCHAR(256) NOT NULL,
    secret_key VARCHAR(256) NOT NULL,
    period INTEGER NOT NULL,
    skew INTEGER NOT NULL,
    verified BOOLEAN NOT NULL,
    PRIMARY KEY (user_id, device_name),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS totp_used_codes (
    user_id VARCHAR(128) NOT NULL,
    code VARCHAR(8) NOT NULL,
    is_valid BOOLEAN NOT NULL,
    expiry_time_ms BIGINT NOT NULL,
    created_time_ms BIGINT NOT NULL,
    PRIMARY KEY (user_id, created_time_ms),
    FOREIGN KEY (user_id) REFERENCES totp_users(user_id) ON DELETE CASCADE
);

CREATE INDEX totp_used_codes_expiry_time_ms_index ON totp_used_codes(expiry_time_ms);

CREATE TABLE IF NOT EXISTS oauth2_client(
    app_id VARCHAR(64) DEFAULT 'public',
    client_id  VARCHAR(128) NOT NULL,
    name TEXT NOT NULL,
    client_secret_hash  VARCHAR(128) NOT NULL,
    redirect_uris  TEXT NOT NULL,
    created_at_ms  BIGINT NOT NULL,
    updated_at_ms BIGINT NOT NULL,

    CONSTRAINT oauth2_client_pkey PRIMARY KEY(app_id, client_id),
    CONSTRAINT oauth2_client_app_id_fkey FOREIGN KEY(app_id) REFERENCES apps(app_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS oauth2_scopes(
    app_id VARCHAR(64) DEFAULT 'public',
    scope TEXT NOT NULL,
    
    CONSTRAINT oauth2_scopes_pkey PRIMARY KEY(app_id, scope),
    CONSTRAINT oauth2_scopes_app_id_fkey FOREIGN KEY(app_id) REFERENCES apps(app_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS oauth2_client_allowed_scopes(
    app_id VARCHAR(64) DEFAULT 'public',
    client_id VARCHAR(128) NOT NULL,
    scope TEXT NOT NULL,
    requires_consent BOOLEAN NOT NULL,

    CONSTRAINT oauth2_client_allowed_scopes_pkey PRIMARY KEY(app_id, client_id, scope),
    CONSTRAINT oauth2_client_allowed_scopes_client_id_fkey FOREIGN KEY(app_id, client_id) references oauth2_client(app_id, client_id) ON DELETE CASCADE,
    CONSTRAINT oauth2_client_allowed_scopes_scope_fkey FOREIGN KEY(app_id, scope) references oauth2_scopes(app_id, scope) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT oauth2_client_allowed_scopes_app_id_fkey FOREIGN KEY(app_id) REFERENCES apps(app_id) ON DELETE CASCADE
);

CREATE INDEX oauth2_client_allowed_scopes_client_id_index ON oauth2_client_allowed_scopes(app_id, client_id);

CREATE TABLE IF NOT EXISTS oauth2_authcode(
    app_id VARCHAR(64) DEFAULT 'public',
    tenant_id VARCHAR(64) DEFAULT 'public',
    authorization_code_hash VARCHAR(128) NOT NULL CONSTRAINT oauth2_authcode_authorization_code_hash_key UNIQUE,
    session_handle VARCHAR(255) NOT NULL,
    client_id VARCHAR(128) NOT NULL,
    created_at_ms BIGINT NOT NULL,
    expires_at_ms BIGINT NOT NULL,
    scope TEXT NOT NULL,
    redirect_uri TEXT NOT NULL,
    access_type VARCHAR(10) NOT NULL,
    code_challenge VARCHAR(128),
    code_challenge_method VARCHAR(10),

    CONSTRAINT oauth2_authcode_pkey PRIMARY KEY(app_id, tenant_id, authorization_code_hash),
    CONSTRAINT oauth2_authcode_client_id_fkey FOREIGN KEY(app_id, client_id) references oauth2_client(app_id, client_id) ON DELETE CASCADE,
    CONSTRAINT oauth2_authcode_tenant_id_fkey FOREIGN KEY(app_id, tenant_id) references tenants(app_id, tenant_id) ON DELETE CASCADE,
    CONSTRAINT oauth2_authcode_app_id_fkey FOREIGN KEY(app_id) REFERENCES apps(app_id) ON DELETE CASCADE
);

CREATE INDEX oauth2_authcode_client_id_index ON oauth2_authcode(app_id, client_id);
CREATE INDEX oauth2_authcode_session_handle_index ON oauth2_authcode(app_id, session_handle);
CREATE INDEX oauth2_authcode_expires_at_ms_index ON oauth2_authcode(expires_at_ms);

CREATE TABLE IF NOT EXISTS oauth2_token(
    app_id VARCHAR(64) DEFAULT 'public',
    tenant_id VARCHAR(64) DEFAULT 'public',
    client_id VARCHAR(128) NOT NULL,
    session_handle VARCHAR(255),
    scope TEXT NOT NULL,
    access_token_hash VARCHAR(128) NOT NULL CONSTRAINT oauth2_token_access_token_hash_key UNIQUE,
    refresh_token_hash VARCHAR(128) CONSTRAINT oauth2_token_refresh_token_hash_key UNIQUE,
    created_at_ms  BIGINT NOT NULL,
    last_updated_at_ms  BIGINT NOT NULL,
    access_token_expires_at_ms BIGINT NOT NULL,
    refresh_token_expires_at_ms BIGINT,

    CONSTRAINT oauth2_token_pkey PRIMARY KEY(app_id, tenant_id, access_token_hash),
    CONSTRAINT oauth2_token_client_id_fkey FOREIGN KEY(app_id, client_id) references oauth2_client(app_id, client_id) ON DELETE CASCADE,
    CONSTRAINT oauth2_token_tenant_id_fkey FOREIGN KEY(app_id, tenant_id) references tenants(app_id, tenant_id) ON DELETE CASCADE,
    CONSTRAINT oauth2_token_app_id_fkey FOREIGN KEY(app_id) REFERENCES apps(app_id) ON DELETE CASCADE
);

CREATE INDEX oauth2_token_client_id_index ON oauth2_token(app_id, client_id);
CREATE INDEX oauth2_token_session_handle_index ON oauth2_token(app_id, session_handle);
CREATE INDEX oauth2_token_access_token_expires_at_ms_index ON oauth2_token(access_token_expires_at_ms);
CREATE INDEX oauth2_token_refresh_token_expires_at_ms_index ON oauth2_token(refresh_token_expires_at_ms);

tip

You also have the option to rename these tables.

4) Test the connection 🤞#

To test, start SuperTokens and run the following query in your database

SELECT * FROM key_value;

If you see at least one row, it means that the connection has been successfully completed! 🥳🎉

blog

We also have a blog post writeup highlighting all the steps in more detail for different scenarios.

Auth Methods

Add ons

PostgreSQL setup

important

blog

1) Create a database 🛠️#

2) Connect SuperTokens to your database 🔌#

caution

tip

3) Create tables 👩‍💻👨‍💻#

note

tip

4) Test the connection 🤞#

blog