auth

Paddy 2015-06-29 Parent:8ecb60d29b0d

174:9e3ceddf29ad Go to Latest

auth/sql/postgres_init.sql

Use an environment variable to set the JWT secret. When setting up the authd server, populate the JWT secret using a JWT_SECRET environment variable. Incidentally, we also included the subscriptions scope, for testing purposes while creating code.secondbit.org/ducky/subscriptions. We now also log the port we're listening on, listen on all interfaces (instead of just 127.0.0.1), and changed the port to 9000 instead of 8080.

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@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 );