let's goHEADmain

1 files changed, 120 insertions, 0 deletions

diff --git a/src/db/auth.sql b/src/db/auth.sql
new file mode 100644
index 0000000..b3e4c50
--- /dev/null
+++ b/src/db/auth.sql
@@ -0,0 +1,120 @@
+-- https://github.com/jeremyevans/rodauth
+GRANT CREATE ON SCHEMA :schema TO :password_role;
+
+CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);
+
+CREATE TABLE account_statuses(
+    account_status_id smallint,
+    name text NOT NULL,
+
+    UNIQUE (name),
+    CONSTRAINT account_statuses_pk PRIMARY KEY (account_status_id)
+);
+INSERT INTO account_statuses (account_status_id, name) VALUES (1, 'Unverified');
+INSERT INTO account_statuses (account_status_id, name) VALUES (2, 'Verified');
+INSERT INTO account_statuses (account_status_id, name) VALUES (3, 'Closed');
+
+CREATE TABLE accounts(
+    account_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    status_id smallint NOT NULL DEFAULT 1,
+    -- email citext UNIQUE,
+    email text UNIQUE,
+    name text,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    UNIQUE (email),
+    CONSTRAINT accounts_pk PRIMARY KEY (account_id),
+    CONSTRAINT accounts_account_statuses_fk FOREIGN KEY (status_id) REFERENCES account_statuses(account_status_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+
+    CONSTRAINT accounts_email_ck CHECK (char_length(email) <= 255)
+);
+
+CREATE INDEX accounts_email_uniq ON accounts (email) WHERE status_id IN (1, 2);
+
+CREATE TABLE account_active_session_keys(
+    session_id text NOT NULL,
+    account_id bigint NOT NULL,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    last_use timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT account_active_session_keys_pk PRIMARY KEY (session_id, account_id),
+    CONSTRAINT account_active_session_keys_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+
+);
+
+CREATE OR REPLACE FUNCTION create_account(_email text, _password text)
+RETURNS int
+AS $$
+    INSERT INTO accounts (email) VALUES (lower(_email)) RETURNING account_id;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION create_session(account_id bigint)
+RETURNS text
+AS $$
+    INSERT INTO account_active_session_keys (session_id, account_id) VALUES 
+    (pgsodium.crypto_auth_hmacsha256_keygen(), account_id) RETURNING session_id;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION get_account_by_session_id(_session_id text)
+RETURNS SETOF accounts
+AS $$
+SELECT accounts.* FROM account_active_session_keys JOIN accounts USING(account_id) WHERE session_id = _session_id;
+$$
+LANGUAGE SQL;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON account_statuses TO :password_role;
+GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO :password_role;
+
+SET ROLE teamdraft_password;
+\c :database_name
+
+DROP TABLE IF EXISTS account_password_hashes;
+CREATE TABLE account_password_hashes(
+    account_id bigint NOT NULL,
+    password_hash int NOT NULL,
+
+    CONSTRAINT account_password_hashes_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION get_salt(_account_id int8) RETURNS text AS $$
+DECLARE salt text;
+BEGIN
+SELECT
+CASE
+    WHEN password_hash ~ '^\\$argon2id'
+      THEN substring(password_hash from '\\$argon2id\\$v=\\d+\\$m=\\d+,t=\\d+,p=\\d+\\$.+\\$')
+    ELSE substr(password_hash, 0, 30)
+  END INTO salt
+END
+FROM account_password_hashes
+WHERE _account_id = account_id;
+RETURN salt;
+END;
+$$ LANGUAGE plpgsql
+SECURITY DEFINER
+SET search_path = :schema, public;
+
+CREATE OR REPLACE FUNCTION valid_password_hash(_account_id int8, hash text) RETURNS boolean AS $$
+DECLARE valid boolean;
+BEGIN
+SELECT password_hash = hash INTO valid
+FROM account_password_hashes
+WHERE _account_id = account_id;
+RETURN valid;
+END;
+$$ LANGUAGE plpgsql
+SECURITY DEFINER
+SET search_path = :schema, public;
+
+REVOKE ALL ON account_password_hashes FROM :schema;
+REVOKE ALL ON FUNCTION get_salt(int8) FROM :schema;
+REVOKE ALL ON FUNCTION valid_password_hash(int8, text) FROM :schema;
+GRANT INSERT, UPDATE, DELETE ON account_password_hashes TO :password_role;
+GRANT SELECT(account_id) ON account_password_hashes TO :password_role;
+GRANT EXECUTE ON FUNCTION get_salt(int8) TO :password_role;
+GRANT EXECUTE ON FUNCTION valid_password_hash(int8, text) TO :password_role;
+
+SET ROLE teamdraft;