auth
auth/sql/postgres_init.sql
Implement postgres version of the tokenStore. Create a postgres implementation for the tokenStore. Note that because pq doesn't support Postgres' array types (see https://github.com/lib/pq/issues/49), we couldn't just store the token.Scopes field as a Postgres array of varchars, which would have been the ideal. Instead, we need a many-to-many table that maps tokens to scopes. This meant we needed a special tokenScope type for our database mapping, and we needed to complicate the token storage/retrieval functions a little bit. It's kind of ugly, I'm not a huge fan of it, and I'd much rather be using the Postgres array types, but... well, here we are. We also added the postgres tokenStore to our slice of tokenStores to test when the correct environment variables are present. We wrote initialization SQL for the tables required by the postgres tokenStore. Also, added a helper script for emptying the test database, because I got tired of doing it by hand. We should be doing that in an automated fashion in the tests themselves, but that would mean extending the *Store interfaces.
| 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 ); |