auth

Paddy 2015-04-11 Parent:2809016184f6 Child:849f3820b164

160:48200d8c4036 Go to Latest

auth/sql/postgres_init.sql

Start to support deleting profiles through the API. Create a removeLoginsByProfile method on the profileStore, to allow an easy way to bulk-delete logins associated with a Profile after the Profile has been deleted. Create postgres and memstore implementations of the removeLoginsByProfile method. Create a cleanUpAfterProfileDeletion helper method that will clean up the child objects of a Profile (its Sessions, Tokens, Clients, etc.). The intended usage is to call this in a goroutine after a Profile has been deleted, to try and get things back in order. Detect when the UpdateProfileHandler API is used to set the Deleted flag of a Profile to true, and clean up after the Profile when that's the case. Add a DeleteProfileHandler API endpoint that is a shortcut to setting the Deleted flag of a Profile to true and cleaning up after the Profile. The problem with our approach thus far is that some of it is reversible and some is not. If a Profile is maliciously/accidentally deleted, it's simple enough to use the API as a superuser to restore the Profile. But doing that will not (and cannot) restore the Logins associated with that Profile, for example. While it would be nice to add a Deleted flag to our Logins that we could simply toggle, that would wreak havoc with our database constraints and ensuring uniqueness of Login values. I still don't have a solution for this, outside the superuser manually restoring a Login for the Profile, after which the user can authenticate themselves and add more Logins as desired. But there has to be a better way. I suppose since the passphrase is being stored with the Profile and not the Login, we could offer an endpoint that would automate this, but... well, that would be tricky. It would require the user remembering their Profile ID, and let's be honest, nobody's going to remember a UUID. Maybe such an endpoint would help from a customer service standpoint: we identify their Profile manually, then send them to /profiles/ID/restorelogin or something, and that lets them add a Login back to the Profile. I'll figure it out later. For now, we know we at least have enough information to identify a user is who they say they are and resolve the situation manually.

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