subzero auth

swizzard · Jun 21, 2020 · 4ca6cf3 · 4ca6cf3
1 parent 58e1064
commit 4ca6cf3
Show file tree

Hide file tree

Showing 9 changed files with 522 additions and 78 deletions.
diff --git a/app/dbConfig.js b/app/dbConfig.js
@@ -0,0 +1,9 @@
+const config = {
+  database: process.env.POSTGRES_DB,
+  user: process.env.POSTGRES_USER,
+  password: process.env.POSTGRES_PASSWORD,
+  host: process.env.POSTGRES_HOST,
+  port: parseInt(process.env.POSTGRES_PORT)
+};
+
+export default config;
diff --git a/app/migrate.js b/app/migrate.js
@@ -1,17 +1,10 @@
 const { createDb, migrate } = require('postgres-migrations');
-
-const config = {
-  database: process.env.POSTGRES_DB,
-  user: process.env.POSTGRES_USER,
-  password: process.env.POSTGRES_PASSWORD,
-  host: process.env.POSTGRES_HOST,
-  port: parseInt(process.env.POSTGRES_PORT),
-};
+const config = require('./dbConfig');
 
 async function doMigration() {
   await createDb(process.env.POSTGRES_DB, {
     ...config,
-    defaultDatabase: process.env.POSTGRES_DB,
+    defaultDatabase: process.env.POSTGRES_DB
   });
   await migrate(config, '/app/migrations');
 }

diff --git a/app/migrations/0001-subzero.sql b/app/migrations/0001-subzero.sql
@@ -0,0 +1,170 @@
+-- adapted from https://github.com/subzerocloud/subzero-starter-kit
+
+
+drop schema if exists request cascade;
+create schema request;
+grant usage on schema request to public;
+
+create or replace function request.env_var(v text) returns text as $$
+    select current_setting(v, true);
+$$ stable language sql;
+
+create or replace function request.jwt_claim(c text) returns text as $$
+    select request.env_var('request.jwt.claim.' || c);
+$$ stable language sql;
+
+create or replace function request.cookie(c text) returns text as $$
+    select request.env_var('request.cookie.' || c);
+$$ stable language sql;
+
+create or replace function request.header(h text) returns text as $$
+    select request.env_var('request.header.' || h);
+$$ stable language sql;
+
+create or replace function request.user_id() returns int as $$
+    select 
+    case coalesce(request.jwt_claim('user_id'),'')
+    when '' then 0
+    else request.jwt_claim('user_id')::int
+	end
+$$ stable language sql;
+
+create or replace function request.user_role() returns text as $$
+    select request.jwt_claim('role')::text;
+$$ stable language sql;
+
+drop schema if exists response cascade;
+create schema response;
+grant usage on schema response to public;
+
+
+create or replace function response.get_cookie_string(name text, value text, expires_after int, path text) returns text as $$
+    with vars as (
+        select
+            case
+                when expires_after > 0 
+                then current_timestamp + (expires_after::text||' seconds')::interval
+                else timestamp 'epoch'
+            end as expires_on
+    )
+    select 
+        name ||'=' || value || '; ' ||
+        'Expires=' || to_char(expires_on, 'Dy, DD Mon YYYY HH24:MI:SS GMT') || '; ' ||
+        'Max-Age=' || expires_after::text || '; ' ||
+        'Path=' ||path|| '; HttpOnly'
+    from vars;
+$$ stable language sql;
+
+create or replace function response.set_header(name text, value text) returns void as $$
+    select set_config(
+        'response.headers', 
+        jsonb_insert(
+            (case coalesce(current_setting('response.headers',true),'')
+            when '' then '[]'
+            else current_setting('response.headers')
+            end)::jsonb,
+            '{0}'::text[], 
+            jsonb_build_object(name, value))::text, 
+        true
+    );
+$$ stable language sql;
+
+create or replace function response.set_cookie(name text, value text, expires_after int, path text) returns void as $$
+    select response.set_header('Set-Cookie', response.get_cookie_string(name, value, expires_after, path));
+$$ stable language sql;
+
+create or replace function response.delete_cookie(name text) returns void as $$
+    select response.set_header('Set-Cookie', response.get_cookie_string(name, 'deleted', 0 ,'/'));
+$$ stable language sql;
+
+-- addapted from https://github.com/michelp/pgjwt
+-- license follows
+
+-- The MIT License (MIT)
+
+-- Copyright (c) 2016 Michel Pelletier
+
+-- Permission is hereby granted, free of charge, to any person obtaining a copy
+-- of this software and associated documentation files (the "Software"), to deal
+-- in the Software without restriction, including without limitation the rights
+-- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+-- copies of the Software, and to permit persons to whom the Software is
+-- furnished to do so, subject to the following conditions:
+
+-- The above copyright notice and this permission notice shall be included in all
+-- copies or substantial portions of the Software.
+
+-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+-- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+-- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+-- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+-- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+-- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
+-- SOFTWARE.
+
+
+create extension if not exists pgcrypto;
+drop schema if exists pgjwt cascade;	
+create schema pgjwt;
+set search_path to pgjwt, public;
+
+CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE sql AS $$
+    SELECT translate(encode(data, 'base64'), E'+/=\n', '-_');
+$$;
+
+
+CREATE OR REPLACE FUNCTION url_decode(data text) RETURNS bytea LANGUAGE sql AS $$
+WITH t AS (SELECT translate(data, '-_', '+/')),
+     rem AS (SELECT length((SELECT * FROM t)) % 4) -- compute padding size
+    SELECT decode(
+        (SELECT * FROM t) ||
+        CASE WHEN (SELECT * FROM rem) > 0
+           THEN repeat('=', (4 - (SELECT * FROM rem)))
+           ELSE '' END,
+    'base64');
+$$;
+
+
+CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
+RETURNS text LANGUAGE sql AS $$
+WITH
+  alg AS (
+    SELECT CASE
+      WHEN algorithm = 'HS256' THEN 'sha256'
+      WHEN algorithm = 'HS384' THEN 'sha384'
+      WHEN algorithm = 'HS512' THEN 'sha512'
+      ELSE '' END)  -- hmac throws error
+SELECT pgjwt.url_encode(public.hmac(signables, secret, (select * FROM alg)));
+$$;
+
+
+CREATE OR REPLACE FUNCTION sign(payload json, secret text, algorithm text DEFAULT 'HS256')
+RETURNS text LANGUAGE sql AS $$
+WITH
+  header AS (
+    SELECT pgjwt.url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8'))
+    ),
+  payload AS (
+    SELECT pgjwt.url_encode(convert_to(payload::text, 'utf8'))
+    ),
+  signables AS (
+    SELECT (SELECT * FROM header) || '.' || (SELECT * FROM payload)
+    )
+SELECT
+    (SELECT * FROM signables)
+    || '.' ||
+    pgjwt.algorithm_sign((SELECT * FROM signables), secret, algorithm);
+$$;
+
+
+CREATE OR REPLACE FUNCTION verify(token text, secret text, algorithm text DEFAULT 'HS256')
+RETURNS table(header json, payload json, valid boolean) LANGUAGE sql AS $$
+  SELECT
+    convert_from(pgjwt.url_decode(r[1]), 'utf8')::json AS header,
+    convert_from(pgjwt.url_decode(r[2]), 'utf8')::json AS payload,
+    r[3] = pgjwt.algorithm_sign(r[1] || '.' || r[2], secret, algorithm) AS valid
+  FROM regexp_split_to_array(token, '\.') r;
+$$;
+
+
+SET search_path TO public;
diff --git a/app/migrations/0001-tables.sql b/app/migrations/0001-tables.sql
diff --git a/app/migrations/0002-functions.sql b/app/migrations/0002-functions.sql
@@ -1,10 +1,10 @@
-CREATE OR REPLACE FUNCTION ip_banned(u_ipaddr inet) RETURNS boolean AS $$
+CREATE OR REPLACE FUNCTION api.ip_banned(u_ipaddr inet) RETURNS boolean AS $$
   SELECT EXISTS(SELECT 1 FROM player p WHERE p.ipaddr = u_ipaddr AND p.banned IS true);
 $$ LANGUAGE SQL STABLE;
 
-CREATE OR REPLACE FUNCTION new_player(
+CREATE OR REPLACE FUNCTION api.new_player(
   u_email VARCHAR(255), u_password TEXT, u_ipaddr inet, u_display_name VARCHAR(32)
-) RETURNS player_result AS $$
+) RETURNS api.player_result AS $$
 DECLARE
   is_banned boolean;
   banned_user player_result = (-1, '', '');
@@ -29,7 +29,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql VOLATILE;
 
-CREATE OR REPLACE FUNCTION delete_user(email VARCHAR(255), u_ipaddr inet) RETURNS boolean AS $$
+CREATE OR REPLACE FUNCTION api.delete_user(email VARCHAR(255), u_ipaddr inet) RETURNS boolean AS $$
 DECLARE u record;
 BEGIN
   IF ip_banned(u_ipaddr) THEN
@@ -46,7 +46,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql VOLATILE;
 
-CREATE OR REPLACE FUNCTION draft_game(data json) RETURNS integer AS $$
+CREATE OR REPLACE FUNCTION api.draft_game(data json) RETURNS integer AS $$
 DECLARE
   questions json;
   q_id integer;
@@ -78,7 +78,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION user_answers(data json) RETURNS boolean AS $$
+CREATE OR REPLACE FUNCTION api.user_answers(data json) RETURNS boolean AS $$
 DECLARE
   p_id integer;
   key text;
@@ -92,7 +92,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql VOLATILE;
 
-CREATE OR REPLACE FUNCTION host_game(data json) RETURNS uuid AS $$
+CREATE OR REPLACE FUNCTION api.host_game(data json) RETURNS uuid AS $$
 DECLARE
   user_id integer;
   game_id integer;
@@ -109,7 +109,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql VOLATILE;
 
-CREATE OR REPLACE FUNCTION update_game(data json) RETURNS boolean AS $$
+CREATE OR REPLACE FUNCTION api.update_game(data json) RETURNS boolean AS $$
 DECLARE
   questions json;
   creator_id integer;

diff --git a/app/migrations/0002-tables.sql b/app/migrations/0002-tables.sql
@@ -0,0 +1,67 @@
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+
+DROP SCHEMA IF EXISTS api;
+
+CREATE TABLE IF NOT EXISTS api.player (
+  id SERIAL PRIMARY KEY,
+  email VARCHAR(255) UNIQUE NOT NULL,
+  ipaddr inet NOT NULL,
+  password TEXT NOT NULL,
+  display_name VARCHAR(32) NOT NULL,
+  banned BOOLEAN NOT NULL DEFAULT false
+);
+
+CREATE TABLE IF NOT EXISTS api.quiz (
+  id SERIAL PRIMARY KEY,
+  creator INTEGER REFERENCES api.player(id) ON DELETE CASCADE,
+  name VARCHAR(255) NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS api.quiz_round (
+  id SERIAL PRIMARY KEY,
+  quiz_id INTEGER REFERENCES api.quiz(id) ON DELETE CASCADE,
+  round_no SMALLINT NOT NULL DEFAULT 1,
+  UNIQUE(quiz_id, round_no)
+);
+
+CREATE TABLE IF NOT EXISTS api.question (
+  id SERIAL PRIMARY KEY,
+  round_id INTEGER REFERENCES api.quiz_round(id) ON DELETE CASCADE,
+  question TEXT,
+  question_no SMALLINT NOT NULL DEFAULT 1,
+  UNIQUE(round_id, question_no)
+);
+
+CREATE TABLE IF NOT EXISTS api.answer (
+  id SERIAL PRIMARY KEY,
+  question_id INTEGER REFERENCES api.question(id) ON DELETE CASCADE,
+  answer TEXT,
+  points SMALLINT
+);
+
+CREATE TABLE IF NOT EXISTS api.game (
+  id SERIAL PRIMARY KEY,
+  quiz_id INTEGER REFERENCES api.quiz(id) ON DELETE CASCADE,
+  code uuid NOT NULL DEFAULT gen_random_uuid(),
+  completed BOOLEAN NOT NULL DEFAULT false
+);
+
+CREATE TABLE IF NOT EXISTS api.game_participant (
+  id SERIAL PRIMARY KEY,
+  game_id INTEGER REFERENCES api.game(id) ON DELETE CASCADE,
+  player_id INTEGER REFERENCES api.player(id) ON DELETE CASCADE,
+  score INTEGER NOT NULL DEFAULT 0
+);
+
+CREATE TABLE IF NOT EXISTS participant_response (
+  id SERIAL PRIMARY KEY,
+  answer_id INTEGER REFERENCES api.answer(id) ON DELETE CASCADE,
+  participant_id INTEGER REFERENCES api.game_participant(id) ON DELETE CASCADE,
+  response TEXT
+);
+
+CREATE TYPE api.player_result AS (id integer, email VARCHAR(255), display_name VARCHAR(32));
+
+CREATE TABLE IF NOT EXISTS public.jwt_secret (
+  secret TEXT
+);