User Authentication

This is the user authentication system

Reference: https://www.webtoolkit.eu/wt/doc/tutorial/auth.html

User authentication is handled by three internal Wt tables with an additional application-supplied User table.

To manually verify an email login, execute the following;

livesite# update "sysAuthInfo" set
            email='user@email.addy',
            unverified_email='',
            email_token='',
            email_token_expires=null,
            email_token_role=1
            where id={id-of-item};

Auth Identity Type

livesite# \d "sysAuthIdentityType";
                                     Table "public.sysAuthIdentityType"
         Column     |          Type          |                             Modifiers
    ----------------+------------------------+--------------------------------------------------------------------
     id             | bigint                 | not null default nextval('"sysAuthIdentityType_id_seq"'::regclass)
     version        | integer                | not null
     sysAuthInfo_id | bigint                 |
     provider       | character varying(64)  | not null
     identity       | character varying(512) | not null
    Indexes:
        "sysAuthIdentityType_pkey" PRIMARY KEY, btree (id)
    Foreign-key constraints:
        "fk_sysAuthIdentityType_sysAuthInfo" FOREIGN KEY ("sysAuthInfo_id") REFERENCES "sysAuthInfo"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

Auth Token Type

livesite# \d "sysAuthTokenType";
                                     Table "public.sysAuthTokenType"
         Column     |            Type             |                            Modifiers
    ----------------+-----------------------------+-----------------------------------------------------------------
     id             | bigint                      | not null default nextval('"sysAuthTokenType_id_seq"'::regclass)
     version        | integer                     | not null
     sysAuthInfo_id | bigint                      |
     value          | character varying(64)       | not null
     expires        | timestamp without time zone |
    Indexes:
        "sysAuthTokenType_pkey" PRIMARY KEY, btree (id)
    Foreign-key constraints:
        "fk_sysAuthTokenType_sysAuthInfo" FOREIGN KEY ("sysAuthInfo_id") REFERENCES "sysAuthInfo"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

Auth Info Table

livesite# \d "sysAuthInfo"
                                             Table "public.sysAuthInfo"
         Column         |            Type             |                         Modifiers
 -----------------------+-----------------------------+------------------------------------------------------------
  id                    | bigint                      | not null default nextval('"sysAuthInfo_id_seq"'::regclass)
  version               | integer                     | not null
  user_id               | bigint                      |
  password_hash         | character varying(100)      | not null
  password_method       | character varying(20)       | not null
  password_salt         | character varying(20)       | not null
  status                | integer                     | not null
  failed_login_attempts | integer                     | not null
  last_login_attempt    | timestamp without time zone |
  email                 | character varying(256)      | not null
  unverified_email      | character varying(256)      | not null
  email_token           | character varying(64)       | not null
  email_token_expires   | timestamp without time zone |
  email_token_role      | integer                     | not null
 Indexes:
     "sysAuthInfo_pkey" PRIMARY KEY, btree (id)
 Foreign-key constraints:
     "fk_sysAuthInfo_user" FOREIGN KEY (user_id) REFERENCES "sysAuthUser"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 Referenced by:
     TABLE ""sysAuthIdentityType"" CONSTRAINT "fk_sysAuthIdentityType_sysAuthInfo" FOREIGN KEY ("sysAuthInfo_id") REFERENCES "sysAuthInfo"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
     TABLE ""sysAuthTokenType"" CONSTRAINT "fk_sysAuthTokenType_sysAuthInfo" FOREIGN KEY ("sysAuthInfo_id") REFERENCES "sysAuthInfo"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

Auth User Table

                                   Table "public.sysAuthUser"
       Column        |            Type             |                         Modifiers
---------------------+-----------------------------+------------------------------------------------------------
 id                  | bigint                      | not null default nextval('"sysAuthUser_id_seq"'::regclass)
 version             | integer                     | not null
 isActive            | boolean                     | not null
 isVisible           | boolean                     | not null
 isDeleted           | boolean                     | not null
 keyField            | character varying(250)      | not null
 cfyField            | character varying(80)       | not null
 varField            | text                        | not null
 createdOn           | timestamp without time zone |
 changedOn           | timestamp without time zone |
 firstName           | text                        | not null
 lastName            | text                        | not null
 favouritePet        | text                        | not null
 userRole_id         | bigint                      |
 primary_sysGroup_id | bigint                      |
Indexes:
 "sysAuthUser_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
 "fk_sysAuthUser_primary_sysGroup" FOREIGN KEY ("primary_sysGroup_id") REFERENCES "sysGroup"(id)    DEFERRABLE INITIALLY DEFERRED
 "fk_sysAuthUser_userRole"         FOREIGN KEY ("userRole_id")         REFERENCES "sysUserRole"(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
 TABLE ""crmActivity""     CONSTRAINT "fk_crmActivity_changedBy_sysUser" FOREIGN KEY ("changedBy_sysUser_id") REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmActivity""     CONSTRAINT "fk_crmActivity_createdBy_sysUser" FOREIGN KEY ("createdBy_sysUser_id") REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmActivity""     CONSTRAINT "fk_crmActivity_p_sysUser"         FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmAddress""      CONSTRAINT "fk_crmAddress_p_sysUser"          FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmCompany""      CONSTRAINT "fk_crmCompany_p_sysUser"          FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmContact""      CONSTRAINT "fk_crmContact_p_sysUser"          FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""crmPerson""       CONSTRAINT "fk_crmPerson_p_sysUser"           FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsAccount""      CONSTRAINT "fk_fmsAccount_p_sysUser"          FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsBatch""        CONSTRAINT "fk_fmsBatch_p_sysUser"            FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsInvoice""      CONSTRAINT "fk_fmsInvoice_p_sysUser"          FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsInvoice""      CONSTRAINT "fk_fmsInvoice_rep_sysUser"        FOREIGN KEY ("rep_sysUser_id") REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsPayDevice""    CONSTRAINT "fk_fmsPayDevice_p_sysUser"        FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsSubscription"" CONSTRAINT "fk_fmsSubscription_p_sysUser"     FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""fmsTransaction""  CONSTRAINT "fk_fmsTransaction_p_sysUser"      FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""mktCampaign""     CONSTRAINT "fk_mktCampaign_p_sysUser"         FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""mktReferrer""     CONSTRAINT "fk_mktReferrer_p_sysUser"         FOREIGN KEY ("p_sysUser_id")   REFERENCES "sysAuthUser"(id) DEFERRABLE INITIALLY DEFERRED
 TABLE ""sysAuthInfo""     CONSTRAINT "fk_sysAuthInfo_user"              FOREIGN KEY (user_id)          REFERENCES "sysAuthUser"(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED