auth
auth/sql/postgres_init.sql
Make client use our auth(n/z) scheme. Our auth(n/z) scheme can be loosely defined as "encrypted tokens that nginx transforms into headers" and "scopes for bypassing ACL". Our Go client, which is what we'll be using to have services communicate with each other, follows this paradigm now by auto-injecting the headers we'll need to identify ourselves. This will work behind our firewall, but will be useless for the rest of the world, which will need to go through the nginx bastion that can strip the headers and replace them with the headers appropriate to the token attached to the request. This did involve setting a static client ID as the client for our email_verification listener. Ideally, this would cause Client registration (using that ID) when the listener starts up, ignoring ErrClientAlreadyExists. I don't want to have to write the code that will allow us to bypass the Client ACL properly right now, though, so we're just going to have to remember to manually create that Client. Or not. I don't think it will do any harm (outside the OAuth flow) to be using a Client ID that doesn't actually point to a Client. I just think it'd be good for record-keeping purposes.
| 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@172 | 21 last_used TIMESTAMPTZ NOT NULL, |
| paddy@172 | 22 verification VARCHAR(36) NOT NULL, |
| paddy@172 | 23 verified BOOLEAN NOT NULL |
| paddy@149 | 24 ); |
| paddy@151 | 25 |
| paddy@151 | 26 CREATE TABLE IF NOT EXISTS clients ( |
| paddy@151 | 27 id VARCHAR(36) PRIMARY KEY, |
| paddy@151 | 28 secret VARCHAR(64) NOT NULL, |
| paddy@151 | 29 owner_id VARCHAR(36) NOT NULL, |
| paddy@151 | 30 name VARCHAR(32) NOT NULL, |
| paddy@151 | 31 logo VARCHAR(512) NOT NULL, |
| paddy@151 | 32 website VARCHAR(140) NOT NULL, |
| paddy@151 | 33 type VARCHAR(16) NOT NULL, |
| paddy@151 | 34 deleted BOOLEAN NOT NULL |
| paddy@151 | 35 ); |
| paddy@151 | 36 |
| paddy@151 | 37 CREATE TABLE IF NOT EXISTS endpoints ( |
| paddy@151 | 38 id VARCHAR(36) PRIMARY KEY, |
| paddy@151 | 39 client_id VARCHAR(36) NOT NULL, |
| paddy@151 | 40 uri VARCHAR(512) NOT NULL, |
| paddy@151 | 41 normalized_uri VARCHAR(512) NOT NULL, |
| paddy@151 | 42 added TIMESTAMPTZ NOT NULL |
| paddy@151 | 43 ); |
| paddy@152 | 44 |
| paddy@152 | 45 CREATE TABLE IF NOT EXISTS scopes ( |
| paddy@152 | 46 id VARCHAR(64) PRIMARY KEY, |
| paddy@152 | 47 name VARCHAR(64) NOT NULL, |
| paddy@152 | 48 description TEXT NOT NULL |
| paddy@152 | 49 ); |
| paddy@154 | 50 |
| paddy@154 | 51 CREATE TABLE IF NOT EXISTS sessions ( |
| paddy@154 | 52 id VARCHAR(72) PRIMARY KEY, |
| paddy@154 | 53 ip VARCHAR(32) NOT NULL, |
| paddy@154 | 54 user_agent TEXT NOT NULL, |
| paddy@154 | 55 profile_id VARCHAR(36) NOT NULL, |
| paddy@154 | 56 login VARCHAR(64) NOT NULL, |
| paddy@154 | 57 created TIMESTAMPTZ NOT NULL, |
| paddy@154 | 58 expires TIMESTAMPTZ NOT NULL, |
| paddy@154 | 59 active BOOLEAN NOT NULL, |
| paddy@154 | 60 csrftoken VARCHAR(72) NOT NULL |
| paddy@154 | 61 ); |
| paddy@155 | 62 |
| paddy@155 | 63 CREATE TABLE IF NOT EXISTS tokens ( |
| paddy@168 | 64 access_token TEXT PRIMARY KEY, |
| paddy@155 | 65 refresh_token VARCHAR(36) UNIQUE NOT NULL, |
| paddy@155 | 66 created TIMESTAMPTZ NOT NULL, |
| paddy@155 | 67 created_from VARCHAR(128) NOT NULL, |
| paddy@155 | 68 expires_in INTEGER NOT NULL, |
| paddy@155 | 69 token_type VARCHAR(64) NOT NULL, |
| paddy@155 | 70 profile_id VARCHAR(36) NOT NULL, |
| paddy@155 | 71 client_id VARCHAR(36) NOT NULL, |
| paddy@155 | 72 revoked BOOLEAN NOT NULL, |
| paddy@163 | 73 scopes varchar(64)[] NOT NULL |
| paddy@155 | 74 ); |
| paddy@156 | 75 |
| paddy@156 | 76 CREATE TABLE IF NOT EXISTS authorization_codes ( |
| paddy@156 | 77 code VARCHAR(36) PRIMARY KEY, |
| paddy@156 | 78 created TIMESTAMPTZ NOT NULL, |
| paddy@156 | 79 expires_in INTEGER NOT NULL, |
| paddy@156 | 80 client_id VARCHAR(36) NOT NULL, |
| paddy@156 | 81 redirect_uri TEXT NOT NULL, |
| paddy@156 | 82 state TEXT NOT NULL, |
| paddy@156 | 83 profile_id VARCHAR(36) NOT NULL, |
| paddy@163 | 84 used BOOLEAN NOT NULL, |
| paddy@163 | 85 scopes varchar(64)[] NOT NULL |
| paddy@156 | 86 ); |