auth

Paddy 2015-05-17 Parent:581c60f8dd23

172:8ecb60d29b0d Go to Latest

auth/sql/postgres_init.sql

Support email verification. The bulk of this commit is auto-modifying files to export variables (mostly our request error types and our response type) so that they can be reused in a Go client for that API. We also implement the beginnings of a Go client for that API, implementing the bare minimum we need for our immediate purposes: the ability to retrieve information about a Login. This, of course, means we need an API endpoint that will return information about a Login, which in turn required us to implement a GetLogin method in our profileStore. Which got in-memory and postgres implementations. That done, we could add the Verification field and Verified field to the Login type, to keep track of whether we've verified the user's ownership of those communication methods (if the Login is, in fact, a communication method). This required us to update sql/postgres_init.sql to account for the new fields we're tracking. It also means that when creating a Login, we had to generate a UUID to use as the Verification field. To make things complete, we needed a verifyLogin method on the profileStore to mark a Login as verified. That, in turn, required an endpoint to control this through the API. While doing so, I lumped things together in an UpdateLogin handler just so we could reuse the endpoint and logic when resending a verification email that may have never reached the user, for whatever reason (the quintessential "send again" button). Finally, we implemented an email_verification listener that will pull email_verification events off NSQ, check for the requisite data integrity, and use mailgun to email out a verification/welcome email.

History
paddy@149 1 CREATE TABLE IF NOT EXISTS profiles (
paddy@149 2 id VARCHAR(36) PRIMARY KEY,
paddy@149 3 name VARCHAR(64) NOT NULL,
paddy@149 4 passphrase VARCHAR(64) NOT NULL,
paddy@149 5 iterations INTEGER NOT NULL,
paddy@149 6 salt VARCHAR(64) NOT NULL,
paddy@149 7 passphrase_scheme INTEGER NOT NULL,
paddy@149 8 compromised BOOLEAN NOT NULL,
paddy@149 9 locked_until TIMESTAMPTZ NOT NULL,
paddy@149 10 passphrase_reset VARCHAR(64) NOT NULL,
paddy@149 11 passphrase_reset_created TIMESTAMPTZ NOT NULL,
paddy@149 12 created TIMESTAMPTZ NOT NULL,
paddy@161 13 last_seen TIMESTAMPTZ NOT NULL
paddy@149 14 );
paddy@149 15
paddy@149 16 CREATE TABLE IF NOT EXISTS logins (
paddy@149 17 type VARCHAR(16) NOT NULL,
paddy@149 18 value VARCHAR(64) PRIMARY KEY,
paddy@149 19 profile_id VARCHAR(36) NOT NULL,
paddy@149 20 created TIMESTAMPTZ NOT NULL,
paddy@172 21 last_used TIMESTAMPTZ NOT NULL,
paddy@172 22 verification VARCHAR(36) NOT NULL,
paddy@172 23 verified BOOLEAN NOT NULL
paddy@149 24 );
paddy@151 25
paddy@151 26 CREATE TABLE IF NOT EXISTS clients (
paddy@151 27 id VARCHAR(36) PRIMARY KEY,
paddy@151 28 secret VARCHAR(64) NOT NULL,
paddy@151 29 owner_id VARCHAR(36) NOT NULL,
paddy@151 30 name VARCHAR(32) NOT NULL,
paddy@151 31 logo VARCHAR(512) NOT NULL,
paddy@151 32 website VARCHAR(140) NOT NULL,
paddy@151 33 type VARCHAR(16) NOT NULL,
paddy@151 34 deleted BOOLEAN NOT NULL
paddy@151 35 );
paddy@151 36
paddy@151 37 CREATE TABLE IF NOT EXISTS endpoints (
paddy@151 38 id VARCHAR(36) PRIMARY KEY,
paddy@151 39 client_id VARCHAR(36) NOT NULL,
paddy@151 40 uri VARCHAR(512) NOT NULL,
paddy@151 41 normalized_uri VARCHAR(512) NOT NULL,
paddy@151 42 added TIMESTAMPTZ NOT NULL
paddy@151 43 );
paddy@152 44
paddy@152 45 CREATE TABLE IF NOT EXISTS scopes (
paddy@152 46 id VARCHAR(64) PRIMARY KEY,
paddy@152 47 name VARCHAR(64) NOT NULL,
paddy@152 48 description TEXT NOT NULL
paddy@152 49 );
paddy@154 50
paddy@154 51 CREATE TABLE IF NOT EXISTS sessions (
paddy@154 52 id VARCHAR(72) PRIMARY KEY,
paddy@154 53 ip VARCHAR(32) NOT NULL,
paddy@154 54 user_agent TEXT NOT NULL,
paddy@154 55 profile_id VARCHAR(36) NOT NULL,
paddy@154 56 login VARCHAR(64) NOT NULL,
paddy@154 57 created TIMESTAMPTZ NOT NULL,
paddy@154 58 expires TIMESTAMPTZ NOT NULL,
paddy@154 59 active BOOLEAN NOT NULL,
paddy@154 60 csrftoken VARCHAR(72) NOT NULL
paddy@154 61 );
paddy@155 62
paddy@155 63 CREATE TABLE IF NOT EXISTS tokens (
paddy@168 64 access_token TEXT PRIMARY KEY,
paddy@155 65 refresh_token VARCHAR(36) UNIQUE NOT NULL,
paddy@155 66 created TIMESTAMPTZ NOT NULL,
paddy@155 67 created_from VARCHAR(128) NOT NULL,
paddy@155 68 expires_in INTEGER NOT NULL,
paddy@155 69 token_type VARCHAR(64) NOT NULL,
paddy@155 70 profile_id VARCHAR(36) NOT NULL,
paddy@155 71 client_id VARCHAR(36) NOT NULL,
paddy@155 72 revoked BOOLEAN NOT NULL,
paddy@163 73 scopes varchar(64)[] NOT NULL
paddy@155 74 );
paddy@156 75
paddy@156 76 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 77 code VARCHAR(36) PRIMARY KEY,
paddy@156 78 created TIMESTAMPTZ NOT NULL,
paddy@156 79 expires_in INTEGER NOT NULL,
paddy@156 80 client_id VARCHAR(36) NOT NULL,
paddy@156 81 redirect_uri TEXT NOT NULL,
paddy@156 82 state TEXT NOT NULL,
paddy@156 83 profile_id VARCHAR(36) NOT NULL,
paddy@163 84 used BOOLEAN NOT NULL,
paddy@163 85 scopes varchar(64)[] NOT NULL
paddy@156 86 );