login and register, liking on homepage

4 files changed, 151 insertions, 0 deletions

diff --git a/src/db/rest/login/_api_sign_jwt.sql b/src/db/rest/login/_api_sign_jwt.sql
new file mode 100644
index 0000000..dc8e920
--- /dev/null
+++ b/src/db/rest/login/_api_sign_jwt.sql
@@ -0,0 +1,33 @@
+CREATE FUNCTION _api.sign_jwt(
+	_role TEXT,
+	_user_id INTEGER
+)
+RETURNS sys.JWT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_jwt_secret TEXT;
+	_token sys.JWT;
+BEGIN
+	SELECT jwt_secret INTO _jwt_secret
+	FROM sys.database_info
+	WHERE name = current_database();
+
+	SELECT public.sign(
+		row_to_json(r), _jwt_secret
+	) INTO _token
+	FROM (
+		SELECT
+			_role AS role,
+			_user_id AS user_id,
+			extract(epoch FROM now())::integer + (60 * 60 * 24) AS exp
+	) r;
+
+	RETURN _token;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.sign_jwt(TEXT, INTEGER)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE sys.database_info
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/login/_api_validate_role.sql b/src/db/rest/login/_api_validate_role.sql
new file mode 100644
index 0000000..9f1e54f
--- /dev/null
+++ b/src/db/rest/login/_api_validate_role.sql
@@ -0,0 +1,30 @@
+CREATE FUNCTION _api.validate_role()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	IF NOT EXISTS (
+		SELECT TRUE
+		FROM pg_catalog.pg_roles AS r
+		WHERE r.rolname = NEW.role
+	) THEN
+		PERFORM _api.raise(
+			_err => 500
+		);
+		RETURN NULL;
+	END IF;
+
+	RETURN NEW;
+END
+$BODY$;
+
+CREATE CONSTRAINT TRIGGER api_validate_role_trgr
+	AFTER INSERT OR UPDATE
+		ON admin.user
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.validate_role();
+
+GRANT EXECUTE ON FUNCTION _api.validate_role()
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE pg_catalog.pg_roles
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/login/_api_verify_jwt.sql b/src/db/rest/login/_api_verify_jwt.sql
new file mode 100644
index 0000000..9e63cc9
--- /dev/null
+++ b/src/db/rest/login/_api_verify_jwt.sql
@@ -0,0 +1,47 @@
+CREATE FUNCTION _api.verify_jwt(
+	_token TEXT
+)
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_payload JSON;
+	_valid BOOLEAN;
+	_jwt_secret TEXT;
+	_user_id INTEGER;
+BEGIN
+	SELECT jwt_secret INTO _jwt_secret
+	FROM sys.database_info
+	WHERE name = current_database();
+
+	SELECT payload, valid
+	INTO _payload, _valid
+	FROM public.verify(
+		_token,
+		_jwt_secret
+	);
+
+	IF NOT FOUND THEN
+		RETURN NULL;
+	END IF;
+
+	IF _valid <> TRUE THEN
+		RETURN NULL;
+	END IF;
+
+	_user_id = _payload->>'user_id';
+
+	UPDATE admin.user
+	SET seen = clock_timestamp()
+	WHERE id = _user_id;
+
+	RETURN _user_id;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.verify_jwt(TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE sys.database_info
+	TO rest_anon, rest_user;
+GRANT UPDATE ON TABLE admin.user
+	TO rest_anon, rest_user;
diff --git a/src/db/rest/login/api_login.sql b/src/db/rest/login/api_login.sql
new file mode 100644
index 0000000..0cf0535
--- /dev/null
+++ b/src/db/rest/login/api_login.sql
@@ -0,0 +1,41 @@
+CREATE FUNCTION api.login(
+	username TEXT,
+	password TEXT
+)
+RETURNS sys.JWT
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_role NAME;
+	_user_id INTEGER;
+	_token sys.JWT;
+BEGIN
+	SELECT role INTO _role
+		FROM admin.user u
+		WHERE u.username = login.username
+		AND u.password = login.password;
+
+	IF _role IS NULL THEN
+		PERFORM _api.raise(
+			_msg => 'api_invalid_login'
+		);
+		RETURN NULL;
+	END IF;
+
+	SELECT id INTO _user_id
+		FROM admin.user u
+		WHERE u.username = login.username;
+
+	_token = _api.sign_jwt(
+		_role,
+		_user_id
+	);
+
+	RETURN _token;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION api.login(TEXT, TEXT)
+	TO rest_anon, rest_user;
+GRANT SELECT ON TABLE admin.user
+	TO rest_anon, rest_user;