auth

Paddy 2015-04-25 Parent:73e12d5a1124 Child:581c60f8dd23

164:cf1aef6eb81f Go to Latest

auth/sql/postgres_init.sql

Clean up after Client deletion, finish cleaning up after Profile deletion. 6f473576c6ae started cleaning up after Profiles when they're deleted, but didn't clean up the Clients created by that Profile. This fixes that, and also fixes a BUG note about cleaning up after a Client when it's deleted. Extend the authorizationCodeStore to have a deleteAuthorizationCodesByClientID method that will delete the AuthorizationCodes that have been granted by the Client specified by the passed ID. We also implemented this in memstore and postgres, so tests continue to pass. Extend the clientStore to have a deleteClientsByOwner method that will delete the Clients that were created by the Profile specified by the passed ID. We also implemented this in memstore and postgres, so tests continue to pass. Extend the clientStore to have a removeEndpointsByClientID method that will delete the Endpoints that belong(ed) to a the Client specified by the passed ID. We also implemented this in memstore and postgres, so tests continue to pass. Extend the tokenStore to have a revokeTokensByClientID method that will revoke all the Tokens that were granted to the Client specified by the passed ID. We also implemented this in memstore and postgres, so tests continue to pass. When listing Clients by their owner, allow setting the num argument (which controls how many to return) to 0 or lower, and using that to signal "return all Clients belonging to this owner", instead of paging. This is useful when deleting the Clients belonging to a Profile as part of the cleanup after deleting the Profile. Create a cleanUpAfterClientDeletion helper function that will delete the Endpoints and AuthorizationCodes belonging to a Client, and revoke the Tokens belonging to a Client, as part of cleaning up after a Client has been deleted. Add a check in the handler for listing Clients owned by a Profile to disallow the num argument to be lower than 1, because the API should be forced to page. Call our cleanUpAfterClientDeletion once the Client has been deleted in the appropriate handler. Fill out our Context with new methods to wrap all the new methods we're adding to our *Stores. In cleanUpAfterProfileDeletion, obtain a list of clients belonging to the owner, use our new DeleteClientsByOwner method to remove all of them, and then use the list to run our new cleanUpAfterClientDeletion function to clear away the final remnants of a Profile when it's deleted.

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@163 71 refresh_revoked BOOLEAN NOT NULL,
paddy@163 72 scopes varchar(64)[] NOT NULL
paddy@155 73 );
paddy@156 74
paddy@156 75 CREATE TABLE IF NOT EXISTS authorization_codes (
paddy@156 76 code VARCHAR(36) PRIMARY KEY,
paddy@156 77 created TIMESTAMPTZ NOT NULL,
paddy@156 78 expires_in INTEGER NOT NULL,
paddy@156 79 client_id VARCHAR(36) NOT NULL,
paddy@156 80 redirect_uri TEXT NOT NULL,
paddy@156 81 state TEXT NOT NULL,
paddy@156 82 profile_id VARCHAR(36) NOT NULL,
paddy@163 83 used BOOLEAN NOT NULL,
paddy@163 84 scopes varchar(64)[] NOT NULL
paddy@156 85 );