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