auth

Paddy 2015-04-11 Parent:849f3820b164 Child:73e12d5a1124

162:6f473576c6ae Go to Latest

auth/sql/postgres_init.sql

Clean up sessions and tokens after Profile is deleted. Add a terminateSessionsByProfile method to our sessionStore to mark Sessions associated with a Profile as inactive. Implement memstore and postgres implementations of the terminateSessionsByProfile method. Add a TerminateSessionsByProfile wrapper method to Context. Add a revokeTokensByProfileID method to our tokenStore to mark Tokens associated with a Profile as revoked. Implement memstore and postgres implementation of the revokeTokensByProfileID method. Add a RevokeTokensByProfileID wrapper method to Context. Call our RevokeTokensByProfileID and TerminateSessionsByProfile methods after a Profile is deleted, to clean up the Tokens and Sessions associated with it.

Download raw file
View source Diff to previous Annotate
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@149 21 last_used TIMESTAMPTZ NOT NULL
paddy@149 22 );
paddy@151 23
paddy@151 24 CREATE TABLE IF NOT EXISTS clients (
paddy@151 25 id VARCHAR(36) PRIMARY KEY,
paddy@151 26 secret VARCHAR(64) NOT NULL,
paddy@151 27 owner_id VARCHAR(36) NOT NULL,
paddy@151 28 name VARCHAR(32) NOT NULL,
paddy@151 29 logo VARCHAR(512) NOT NULL,
paddy@151 30 website VARCHAR(140) NOT NULL,
paddy@151 31 type VARCHAR(16) NOT NULL,
paddy@151 32 deleted BOOLEAN NOT NULL
paddy@151 33 );
paddy@151 34
paddy@151 35 CREATE TABLE IF NOT EXISTS endpoints (
paddy@151 36 id VARCHAR(36) PRIMARY KEY,
paddy@151 37 client_id VARCHAR(36) NOT NULL,
paddy@151 38 uri VARCHAR(512) NOT NULL,
paddy@151 39 normalized_uri VARCHAR(512) NOT NULL,
paddy@151 40 added TIMESTAMPTZ NOT NULL
paddy@151 41 );
paddy@152 42
paddy@152 43 CREATE TABLE IF NOT EXISTS scopes (
paddy@152 44 id VARCHAR(64) PRIMARY KEY,
paddy@152 45 name VARCHAR(64) NOT NULL,
paddy@152 46 description TEXT NOT NULL
paddy@152 47 );
paddy@154 48
paddy@154 49 CREATE TABLE IF NOT EXISTS sessions (
paddy@154 50 id VARCHAR(72) PRIMARY KEY,
paddy@154 51 ip VARCHAR(32) NOT NULL,
paddy@154 52 user_agent TEXT NOT NULL,
paddy@154 53 profile_id VARCHAR(36) NOT NULL,
paddy@154 54 login VARCHAR(64) NOT NULL,
paddy@154 55 created TIMESTAMPTZ NOT NULL,
paddy@154 56 expires TIMESTAMPTZ NOT NULL,
paddy@154 57 active BOOLEAN NOT NULL,
paddy@154 58 csrftoken VARCHAR(72) NOT NULL
paddy@154 59 );
paddy@155 60
paddy@155 61 CREATE TABLE IF NOT EXISTS tokens (
paddy@155 62 access_token VARCHAR(36) PRIMARY KEY,
paddy@155 63 refresh_token VARCHAR(36) UNIQUE NOT NULL,
paddy@155 64 created TIMESTAMPTZ NOT NULL,
paddy@155 65 created_from VARCHAR(128) NOT NULL,
paddy@155 66 expires_in INTEGER NOT NULL,
paddy@155 67 token_type VARCHAR(64) NOT NULL,
paddy@155 68 profile_id VARCHAR(36) NOT NULL,
paddy@155 69 client_id VARCHAR(36) NOT NULL,
paddy@155 70 revoked BOOLEAN NOT NULL,
paddy@155 71 refresh_revoked BOOLEAN NOT NULL
paddy@155 72 );
paddy@155 73
paddy@155 74 CREATE TABLE IF NOT EXISTS scopes_tokens (
paddy@155 75 token VARCHAR(36) NOT NULL,
paddy@155 76 scope VARCHAR(64) NOT NULL,
paddy@155 77 PRIMARY KEY(token, scope)
paddy@155 78 );
paddy@156 79
paddy@156 80 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 81 code VARCHAR(36) PRIMARY KEY,
paddy@156 82 created TIMESTAMPTZ NOT NULL,
paddy@156 83 expires_in INTEGER NOT NULL,
paddy@156 84 client_id VARCHAR(36) NOT NULL,
paddy@156 85 redirect_uri TEXT NOT NULL,
paddy@156 86 state TEXT NOT NULL,
paddy@156 87 profile_id VARCHAR(36) NOT NULL,
paddy@156 88 used BOOLEAN NOT NULL
paddy@156 89 );
paddy@156 90
paddy@156 91 CREATE TABLE IF NOT EXISTS authorization_codes_scopes (
paddy@156 92 code VARCHAR(36) NOT NULL,
paddy@156 93 scope VARCHAR(64) NOT NULL,
paddy@156 94 PRIMARY KEY(code, scope)
paddy@156 95 );