diff options
Diffstat (limited to 'src/db/rest')
30 files changed, 1306 insertions, 0 deletions
diff --git a/src/db/rest/comment/api_comment.sql b/src/db/rest/comment/api_comment.sql new file mode 100644 index 0000000..c8a0e19 --- /dev/null +++ b/src/db/rest/comment/api_comment.sql @@ -0,0 +1,29 @@ +CREATE VIEW api.comment AS + SELECT + c.id, + c.user_id, + c.post_id, + c.content, + c.created, + c.modified + FROM + admin.comment c + LEFT JOIN + admin.post p + ON + p.id = c.post_id + LEFT JOIN + admin.user u + ON + u.id = c.user_id + WHERE + c.deleted <> TRUE AND + p.deleted <> TRUE AND + u.deleted <> TRUE + ORDER BY + id ASC; + +GRANT SELECT ON TABLE api.comment + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.comment + TO rest_anon, rest_user; diff --git a/src/db/rest/comment/api_comment_delete.sql b/src/db/rest/comment/api_comment_delete.sql new file mode 100644 index 0000000..262b2ed --- /dev/null +++ b/src/db/rest/comment/api_comment_delete.sql @@ -0,0 +1,32 @@ +CREATE FUNCTION _api.comment_delete() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + UPDATE admin.comment SET + deleted = TRUE, + modified = clock_timestamp() + WHERE id = OLD.id; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.comment_delete() + TO rest_user; +GRANT DELETE ON TABLE api.comment + TO rest_user; +GRANT UPDATE ON TABLE admin.comment + TO rest_user; + +CREATE TRIGGER api_comment_delete_trgr + INSTEAD OF DELETE + ON api.comment + FOR EACH ROW + EXECUTE PROCEDURE _api.comment_delete(); diff --git a/src/db/rest/comment/api_comment_insert.sql b/src/db/rest/comment/api_comment_insert.sql new file mode 100644 index 0000000..990beef --- /dev/null +++ b/src/db/rest/comment/api_comment_insert.sql @@ -0,0 +1,58 @@ +CREATE FUNCTION _api.comment_insert() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + NEW.content := _api.trim(NEW.content); + PERFORM _api.validate_text( + _text => NEW.content, + _column => 'content', + _min => 1, + _max => 1024 + ); + + PERFORM TRUE + FROM admin.post + WHERE id = NEW.post_id; + + IF NOT FOUND THEN + PERFORM _api.raise( + _msg => 'api_null_post', + _err => 400 + ); + END IF; + + INSERT INTO admin.comment ( + user_id, + post_id, + content + ) VALUES ( + _user_id, + NEW.post_id, + NEW.content + ) + RETURNING id + INTO NEW.id; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.comment_insert() + TO rest_user; +GRANT INSERT ON TABLE api.comment + TO rest_user; +GRANT INSERT ON TABLE admin.comment + TO rest_user; +GRANT UPDATE ON TABLE sys.comment_id_seq + TO rest_user; + +CREATE TRIGGER api_comment_insert_trgr + INSTEAD OF INSERT + ON api.comment + FOR EACH ROW + EXECUTE PROCEDURE _api.comment_insert(); diff --git a/src/db/rest/comment/api_comment_update.sql b/src/db/rest/comment/api_comment_update.sql new file mode 100644 index 0000000..b8fc16d --- /dev/null +++ b/src/db/rest/comment/api_comment_update.sql @@ -0,0 +1,51 @@ +CREATE FUNCTION _api.comment_update() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; + _changed BOOLEAN; +BEGIN + _user_id = _api.get_user_id(); + _changed = FALSE; + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + NEW.content = COALESCE(NEW.content, OLD.content); + NEW.content := _api.trim(NEW.content); + PERFORM _api.validate_text( + _text => NEW.content, + _column => 'content', + _min => 1, + _max => 1024 + ); + + IF NEW.content IS DISTINCT FROM OLD.content THEN + _changed = TRUE; + END IF; + + IF _changed THEN + UPDATE admin.comment SET + content = NEW.content, + modified = clock_timestamp() + WHERE id = OLD.id; + END IF; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.comment_update() + TO rest_user; +GRANT UPDATE ON TABLE api.comment + TO rest_user; +GRANT UPDATE ON TABLE admin.comment + TO rest_user; + +CREATE TRIGGER api_comment_update_trgr + INSTEAD OF UPDATE + ON api.comment + FOR EACH ROW + EXECUTE PROCEDURE _api.comment_update(); diff --git a/src/db/rest/like/api_like.sql b/src/db/rest/like/api_like.sql new file mode 100644 index 0000000..6588b43 --- /dev/null +++ b/src/db/rest/like/api_like.sql @@ -0,0 +1,16 @@ +CREATE VIEW api.like AS + SELECT + l.id, + l.user_id, + l.post_id, + l.comment_id, + l.value, + l.created, + l.modified + FROM + admin.like l; + +GRANT SELECT ON TABLE api.like + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.like + TO rest_anon, rest_user; diff --git a/src/db/rest/like/api_like_delete.sql b/src/db/rest/like/api_like_delete.sql new file mode 100644 index 0000000..7209a40 --- /dev/null +++ b/src/db/rest/like/api_like_delete.sql @@ -0,0 +1,32 @@ +CREATE FUNCTION _api.like_delete() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + UPDATE admin.like SET + value = FALSE, + modified = clock_timestamp() + WHERE id = OLD.id; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.like_delete() + TO rest_user; +GRANT DELETE ON TABLE api.like + TO rest_user; +GRANT UPDATE ON TABLE admin.like + TO rest_user; + +CREATE TRIGGER api_like_delete_trgr + INSTEAD OF DELETE + ON api.like + FOR EACH ROW + EXECUTE PROCEDURE _api.like_delete(); diff --git a/src/db/rest/like/api_like_insert.sql b/src/db/rest/like/api_like_insert.sql new file mode 100644 index 0000000..a02ad4e --- /dev/null +++ b/src/db/rest/like/api_like_insert.sql @@ -0,0 +1,51 @@ +CREATE FUNCTION _api.like_insert() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + IF + NEW.post_id IS NULL AND + NEW.comment_id IS NULL + THEN + -- for now + PERFORM _api.raise_deny(); + END IF; + + NEW.value := COALESCE(NEW.value, TRUE); + + INSERT INTO admin.like ( + user_id, + post_id, + comment_id, + value + ) VALUES ( + _user_id, + NEW.post_id, + NEW.comment_id, + NEW.value + ) + RETURNING id + INTO NEW.id; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.like_insert() + TO rest_user; +GRANT INSERT ON TABLE api.like + TO rest_user; +GRANT INSERT ON TABLE admin.like + TO rest_user; +GRANT UPDATE ON TABLE sys.like_id_seq + TO rest_user; + +CREATE TRIGGER api_like_insert_trgr + INSTEAD OF INSERT + ON api.like + FOR EACH ROW + EXECUTE PROCEDURE _api.like_insert(); diff --git a/src/db/rest/like/api_like_update.sql b/src/db/rest/like/api_like_update.sql new file mode 100644 index 0000000..76db73a --- /dev/null +++ b/src/db/rest/like/api_like_update.sql @@ -0,0 +1,44 @@ +CREATE FUNCTION _api.like_update() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; + _changed BOOLEAN; +BEGIN + _user_id = _api.get_user_id(); + _changed = FALSE; + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + NEW.value = COALESCE(NEW.value, OLD.value); + + IF NEW.value IS DISTINCT FROM OLD.value THEN + _changed = TRUE; + END IF; + + IF _changed THEN + UPDATE admin.like SET + value = NEW.value, + modified = clock_timestamp() + WHERE id = OLD.id; + END IF; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.like_update() + TO rest_user; +GRANT UPDATE ON TABLE api.like + TO rest_user; +GRANT UPDATE ON TABLE admin.like + TO rest_user; + +CREATE TRIGGER api_like_update_trgr + INSTEAD OF UPDATE + ON api.like + FOR EACH ROW + EXECUTE PROCEDURE _api.like_update(); 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; diff --git a/src/db/rest/post/api_post.sql b/src/db/rest/post/api_post.sql new file mode 100644 index 0000000..0d60473 --- /dev/null +++ b/src/db/rest/post/api_post.sql @@ -0,0 +1,37 @@ +CREATE VIEW api.post AS + SELECT + p.id, + p.user_id, + p.content, + p.created, + p.modified, + COALESCE(c.cc, 0) + AS comment_count + FROM + admin.post p + LEFT JOIN ( + SELECT + COUNT(c.id) as cc, + c.post_id + FROM + admin.comment c + GROUP BY + c.post_id + ) c + ON + p.id = c.post_id + LEFT JOIN + admin.user u + ON + u.id = p.user_id + WHERE + p.deleted <> TRUE + AND + u.deleted <> TRUE + ORDER BY + p.id DESC; + +GRANT SELECT ON TABLE api.post + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.post + TO rest_anon, rest_user; diff --git a/src/db/rest/post/api_post_delete.sql b/src/db/rest/post/api_post_delete.sql new file mode 100644 index 0000000..8f26b40 --- /dev/null +++ b/src/db/rest/post/api_post_delete.sql @@ -0,0 +1,32 @@ +CREATE FUNCTION _api.post_delete() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + UPDATE admin.post SET + deleted = TRUE, + modified = clock_timestamp() + WHERE id = OLD.id; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.post_delete() + TO rest_user; +GRANT DELETE ON TABLE api.post + TO rest_user; +GRANT UPDATE ON TABLE admin.post + TO rest_user; + +CREATE TRIGGER api_post_delete_trgr + INSTEAD OF DELETE + ON api.post + FOR EACH ROW + EXECUTE PROCEDURE _api.post_delete(); diff --git a/src/db/rest/post/api_post_insert.sql b/src/db/rest/post/api_post_insert.sql new file mode 100644 index 0000000..e0594dc --- /dev/null +++ b/src/db/rest/post/api_post_insert.sql @@ -0,0 +1,46 @@ +CREATE FUNCTION _api.post_insert() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + NEW.content := _api.trim(NEW.content); + + PERFORM _api.validate_text( + _text => NEW.content, + _column => 'content', + _min => 1, + _max => 4096 + ); + + INSERT INTO admin.post ( + user_id, + content + ) VALUES ( + _user_id, + NEW.content + ) + RETURNING id + INTO NEW.id; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.post_insert() + TO rest_user; +GRANT INSERT ON TABLE api.post + TO rest_user; +GRANT INSERT ON TABLE admin.post + TO rest_user; +GRANT UPDATE ON TABLE sys.post_id_seq + TO rest_user; + +CREATE TRIGGER api_post_insert_trgr + INSTEAD OF INSERT + ON api.post + FOR EACH ROW + EXECUTE PROCEDURE _api.post_insert(); diff --git a/src/db/rest/post/api_post_update.sql b/src/db/rest/post/api_post_update.sql new file mode 100644 index 0000000..7b4360d --- /dev/null +++ b/src/db/rest/post/api_post_update.sql @@ -0,0 +1,51 @@ +CREATE FUNCTION _api.post_update() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; + _changed BOOLEAN; +BEGIN + _user_id = _api.get_user_id(); + _changed = FALSE; + + IF OLD.user_id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + NEW.content = COALESCE(NEW.content, OLD.content); + NEW.content := _api.trim(NEW.content); + PERFORM _api.validate_text( + _text => NEW.content, + _column => 'content', + _min => 1, + _max => 4096 + ); + + IF NEW.content IS DISTINCT FROM OLD.content THEN + _changed = TRUE; + END IF; + + IF _changed THEN + UPDATE admin.post SET + content = NEW.content, + modified = clock_timestamp() + WHERE id = OLD.id; + END IF; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.post_update() + TO rest_user; +GRANT UPDATE ON TABLE api.post + TO rest_user; +GRANT UPDATE ON TABLE admin.post + TO rest_user; + +CREATE TRIGGER api_post_update_trgr + INSTEAD OF UPDATE + ON api.post + FOR EACH ROW + EXECUTE PROCEDURE _api.post_update(); diff --git a/src/db/rest/rest.sql b/src/db/rest/rest.sql new file mode 100644 index 0000000..3e6737c --- /dev/null +++ b/src/db/rest/rest.sql @@ -0,0 +1,56 @@ +BEGIN TRANSACTION; +SET search_path = public; + +DROP SCHEMA IF EXISTS api CASCADE; +CREATE SCHEMA api; + +DROP SCHEMA IF EXISTS _api CASCADE; +CREATE SCHEMA _api; + +GRANT USAGE ON SCHEMA admin TO rest_anon, rest_user; +GRANT USAGE ON SCHEMA sys TO rest_anon, rest_user; + +GRANT USAGE ON SCHEMA api TO rest_anon, rest_user; +GRANT USAGE ON SCHEMA _api TO rest_anon, rest_user; + +-- util +\i /db/rest/util/_api_trim.sql; +\i /db/rest/util/_api_serve_media.sql; +\i /db/rest/util/_api_raise.sql; +\i /db/rest/util/_api_raise_null.sql; +\i /db/rest/util/_api_raise_unique.sql; +\i /db/rest/util/_api_validate_text.sql; +\i /db/rest/util/_api_get_user_id.sql; + +-- user +\i /db/rest/user/api_user.sql; +\i /db/rest/user/api_user_insert.sql; +\i /db/rest/user/api_user_update.sql; +\i /db/rest/user/api_user_delete.sql; +\i /db/rest/user/api_avatar.sql; + +-- post +\i /db/rest/post/api_post.sql; +\i /db/rest/post/api_post_insert.sql; +\i /db/rest/post/api_post_update.sql; +\i /db/rest/post/api_post_delete.sql; + +-- comment +\i /db/rest/comment/api_comment.sql; +\i /db/rest/comment/api_comment_insert.sql; +\i /db/rest/comment/api_comment_update.sql; +\i /db/rest/comment/api_comment_delete.sql; + +-- like +\i /db/rest/like/api_like.sql; +\i /db/rest/like/api_like_insert.sql; +\i /db/rest/like/api_like_update.sql; +\i /db/rest/like/api_like_delete.sql; + +-- login +\i /db/rest/login/_api_sign_jwt.sql; +\i /db/rest/login/_api_verify_jwt.sql; +\i /db/rest/login/_api_validate_role.sql; +\i /db/rest/login/api_login.sql; + +COMMIT TRANSACTION; diff --git a/src/db/rest/user/api_avatar.sql b/src/db/rest/user/api_avatar.sql new file mode 100644 index 0000000..981409f --- /dev/null +++ b/src/db/rest/user/api_avatar.sql @@ -0,0 +1,22 @@ +CREATE FUNCTION api.avatar( + user_id INTEGER DEFAULT 0 +) +RETURNS sys."*/*" +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _mod INTEGER; + _name TEXT; +BEGIN + _mod = MOD(user_id, 24); + _name = 'default_avatar_' || _mod || '.png'; + RETURN _api.serve_media(_name); +END +$BODY$; + +GRANT EXECUTE ON FUNCTION api.avatar(INTEGER) + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.user + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.media + TO rest_anon, rest_user; diff --git a/src/db/rest/user/api_user.sql b/src/db/rest/user/api_user.sql new file mode 100644 index 0000000..6735775 --- /dev/null +++ b/src/db/rest/user/api_user.sql @@ -0,0 +1,25 @@ +CREATE VIEW api.user AS + SELECT + u.id, + u.username, + NULL AS password, + u.role, + u.first_name, + u.last_name, + u.middle_name, + u.email, + u.gender, + u.birth_date, + u.profile_bio, + u.created, + u.modified, + u.seen + FROM + admin.user u + WHERE + u.deleted <> TRUE; + +GRANT SELECT ON TABLE api.user + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.user + TO rest_anon, rest_user; diff --git a/src/db/rest/user/api_user_delete.sql b/src/db/rest/user/api_user_delete.sql new file mode 100644 index 0000000..4389fa0 --- /dev/null +++ b/src/db/rest/user/api_user_delete.sql @@ -0,0 +1,32 @@ +CREATE FUNCTION _api.user_delete() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = _api.get_user_id(); + + IF OLD.id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + UPDATE admin.user SET + deleted = TRUE, + modified = clock_timestamp() + WHERE id = _user_id; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.user_delete() + TO rest_user; +GRANT DELETE ON TABLE api.user + TO rest_user; +GRANT UPDATE ON TABLE admin.user + TO rest_user; + +CREATE TRIGGER api_user_delete_trgr + INSTEAD OF DELETE + ON api.user + FOR EACH ROW + EXECUTE PROCEDURE _api.user_delete(); diff --git a/src/db/rest/user/api_user_insert.sql b/src/db/rest/user/api_user_insert.sql new file mode 100644 index 0000000..1a6ef7c --- /dev/null +++ b/src/db/rest/user/api_user_insert.sql @@ -0,0 +1,130 @@ +CREATE FUNCTION _api.user_insert() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _length INTEGER; +BEGIN + + NEW.username := _api.trim(NEW.username); + NEW.password := _api.trim(NEW.password); + NEW.first_name := _api.trim(NEW.first_name); + NEW.last_name := _api.trim(NEW.last_name); + NEW.middle_name := _api.trim(NEW.middle_name); + NEW.email := _api.trim(NEW.email); + NEW.gender := _api.trim(NEW.gender); + NEW.profile_bio := _api.trim(NEW.profile_bio); + + PERFORM _api.validate_text( + _text => NEW.username, + _column => 'username', + _min => 1, + _max => 24 + ); + + PERFORM TRUE FROM admin.user + WHERE username = NEW.username; + + IF FOUND THEN + PERFORM _api.raise_unique('username'); + END IF; + + PERFORM _api.validate_text( + _text => NEW.password, + _column => 'password', + _min => 1, + _max => 256 + ); + + PERFORM _api.validate_text( + _text => NEW.first_name, + _nullable => TRUE, + _column => 'first_name', + _max => 256 + ); + NEW.first_name = COALESCE(NEW.first_name, ''::text); + + PERFORM _api.validate_text( + _text => NEW.last_name, + _nullable => TRUE, + _column => 'last_name', + _max => 256 + ); + NEW.last_name = COALESCE(NEW.last_name, ''::text); + + PERFORM _api.validate_text( + _text => NEW.middle_name, + _nullable => TRUE, + _column => 'middle_name', + _max => 256 + ); + NEW.middle_name = COALESCE(NEW.middle_name, ''::text); + + PERFORM _api.validate_text( + _text => NEW.email, + _column => 'email', + _max => 256 + ); + + PERFORM _api.validate_text( + _text => NEW.gender, + _column => 'gender', + _max => 256 + ); + + IF NEW.birth_date IS NULL THEN + PERFORM _api.raise_null('birth_date'); + END IF; + + PERFORM _api.validate_text( + _text => NEW.profile_bio, + _nullable => TRUE, + _column => 'profile_bio', + _max => 2048 + ); + NEW.profile_bio = COALESCE(NEW.profile_bio, ''::text); + + INSERT INTO admin.user ( + username, + password, + first_name, + last_name, + middle_name, + email, + gender, + birth_date, + profile_bio + ) VALUES ( + NEW.username, + NEW.password, + NEW.first_name, + NEW.last_name, + NEW.middle_name, + NEW.email, + NEW.gender, + NEW.birth_date, + NEW.profile_bio + ) + RETURNING id + INTO NEW.id; + + NEW.password := NULL; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.user_insert() + TO rest_anon, rest_user; +GRANT INSERT ON TABLE api.user + TO rest_anon, rest_user; +GRANT INSERT ON TABLE admin.user + TO rest_anon, rest_user; +GRANT UPDATE ON TABLE sys.user_id_seq + TO rest_anon, rest_user; + +CREATE TRIGGER api_user_insert_trgr + INSTEAD OF INSERT + ON api.user + FOR EACH ROW + EXECUTE PROCEDURE _api.user_insert(); diff --git a/src/db/rest/user/api_user_update.sql b/src/db/rest/user/api_user_update.sql new file mode 100644 index 0000000..2e7cd50 --- /dev/null +++ b/src/db/rest/user/api_user_update.sql @@ -0,0 +1,168 @@ +CREATE FUNCTION _api.user_update() +RETURNS TRIGGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; + _changed BOOLEAN; +BEGIN + _user_id = _api.get_user_id(); + _changed = FALSE; + + IF OLD.id <> _user_id THEN + PERFORM _api.raise_deny(); + END IF; + + -- username + NEW.username = COALESCE(NEW.username, OLD.username); + NEW.username := _api.trim(NEW.username); + PERFORM _api.validate_text( + _text => NEW.username, + _column => 'username', + _min => 1, + _max => 24 + ); + + IF NEW.username IS DISTINCT FROM OLD.username THEN + PERFORM TRUE FROM admin.user + WHERE username = NEW.username; + IF FOUND THEN + PERFORM _api.raise_unique('username'); + END IF; + _changed = TRUE; + END IF; + + -- password + SELECT password + INTO OLD.password + FROM admin.user + WHERE id = OLD.id; + + NEW.password = COALESCE(NEW.password, OLD.password); + NEW.password := _api.trim(NEW.password); + PERFORM _api.validate_text( + _text => NEW.password, + _column => 'password', + _min => 1, + _max => 256 + ); + + IF NEW.password IS DISTINCT FROM OLD.password THEN + _changed = TRUE; + END IF; + + -- first name + NEW.first_name = COALESCE(NEW.first_name, OLD.first_name); + NEW.first_name := _api.trim(NEW.first_name); + PERFORM _api.validate_text( + _text => NEW.first_name, + _column => 'first_name', + _max => 256 + ); + + IF NEW.first_name IS DISTINCT FROM OLD.first_name THEN + _changed = TRUE; + END IF; + + -- last name + NEW.last_name = COALESCE(NEW.last_name, OLD.last_name); + NEW.last_name := _api.trim(NEW.last_name); + PERFORM _api.validate_text( + _text => NEW.last_name, + _column => 'last_name', + _max => 256 + ); + + IF NEW.last_name IS DISTINCT FROM OLD.last_name THEN + _changed = TRUE; + END IF; + + -- middle name + NEW.middle_name = COALESCE(NEW.middle_name, OLD.middle_name); + NEW.middle_name := _api.trim(NEW.middle_name); + PERFORM _api.validate_text( + _text => NEW.middle_name, + _column => 'middle_name', + _max => 256 + ); + + IF NEW.middle_name IS DISTINCT FROM OLD.middle_name THEN + _changed = TRUE; + END IF; + + -- email + NEW.email = COALESCE(NEW.email, OLD.email); + NEW.email := _api.trim(NEW.email); + PERFORM _api.validate_text( + _text => NEW.email, + _column => 'email', + _max => 256 + ); + + IF NEW.email IS DISTINCT FROM OLD.email THEN + _changed = TRUE; + END IF; + + -- gender + NEW.gender = COALESCE(NEW.gender, OLD.gender); + NEW.gender := _api.trim(NEW.gender); + PERFORM _api.validate_text( + _text => NEW.gender, + _column => 'gender', + _max => 256 + ); + + IF NEW.gender IS DISTINCT FROM OLD.gender THEN + _changed = TRUE; + END IF; + + -- birth date + NEW.birth_date = COALESCE(NEW.birth_date, OLD.birth_date); + IF NEW.birth_date IS DISTINCT FROM OLD.birth_date THEN + _changed = TRUE; + END IF; + + -- profile bio + NEW.profile_bio = COALESCE(NEW.profile_bio, OLD.profile_bio); + NEW.profile_bio := _api.trim(NEW.profile_bio); + PERFORM _api.validate_text( + _text => NEW.profile_bio, + _column => 'profile_bio', + _max => 2048 + ); + + IF NEW.profile_bio IS DISTINCT FROM OLD.profile_bio THEN + _changed = TRUE; + END IF; + + IF _changed THEN + UPDATE admin.user SET + username = NEW.username, + password = NEW.password, + first_name = NEW.first_name, + last_name = NEW.last_name, + middle_name = NEW.middle_name, + email = NEW.email, + gender = NEW.gender, + birth_date = NEW.birth_date, + profile_bio = NEW.profile_bio, + modified = clock_timestamp() + WHERE id = OLD.id; + END IF; + + RETURN NEW; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.user_update() + TO rest_user; +GRANT UPDATE ON TABLE api.user + TO rest_user; +GRANT UPDATE ON TABLE admin.user + TO rest_user; + +CREATE TRIGGER api_user_update_trgr + INSTEAD OF UPDATE + ON api.user + FOR EACH ROW + EXECUTE PROCEDURE _api.user_update(); diff --git a/src/db/rest/util/_api_get_user_id.sql b/src/db/rest/util/_api_get_user_id.sql new file mode 100644 index 0000000..e86afc3 --- /dev/null +++ b/src/db/rest/util/_api_get_user_id.sql @@ -0,0 +1,22 @@ +CREATE FUNCTION _api.get_user_id() +RETURNS INTEGER +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _user_id INTEGER; +BEGIN + _user_id = CURRENT_SETTING( + 'request.jwt.claims', + TRUE + )::JSON->>'user_id'; + + UPDATE admin.user + SET seen = clock_timestamp() + WHERE id = _user_id; + + RETURN _user_id; +END +$BODY$; + +GRANT UPDATE ON TABLE admin.user + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_raise.sql b/src/db/rest/util/_api_raise.sql new file mode 100644 index 0000000..5c740c6 --- /dev/null +++ b/src/db/rest/util/_api_raise.sql @@ -0,0 +1,50 @@ +CREATE TABLE _api.err_map ( + err INTEGER, + pg_err TEXT +); + +ALTER TABLE _api.err_map OWNER TO xssbook; + +ALTER TABLE ONLY _api.err_map + ADD CONSTRAINT err_map_pkey PRIMARY KEY (err); + +INSERT INTO _api.err_map (err, pg_err) +VALUES + (400, 'P0001'), + (401, '42501'), + (403, '42501'), + (404, '42883'), + (409, '23505'), + (500, 'XX001'); + +CREATE FUNCTION _api.raise( + _msg TEXT DEFAULT '', + _detail TEXT DEFAULT '', + _hint TEXT DEFAULT '', + _err INTEGER DEFAULT 400 +) +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _pg_err TEXT; +BEGIN + + SELECT pg_err INTO _pg_err + FROM _api.err_map + WHERE err = _err; + + RAISE EXCEPTION USING + MESSAGE := _msg, + DETAIL := _detail, + HINT := _hint, + ERRCODE := _pg_err; + + RETURN FALSE; +END +$BODY$; + +GRANT SELECT ON TABLE _api.err_map + TO rest_anon, rest_user; +GRANT EXECUTE ON FUNCTION _api.raise(TEXT, TEXT, TEXT, INTEGER) + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_raise_deny.sql b/src/db/rest/util/_api_raise_deny.sql new file mode 100644 index 0000000..17406b7 --- /dev/null +++ b/src/db/rest/util/_api_raise_deny.sql @@ -0,0 +1,16 @@ +CREATE FUNCTION _api.raise_deny() +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +BEGIN + PERFORM _api.raise( + _msg => 'api_denied', + _err => 403 + ); + + RETURN TRUE; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT) + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_raise_null.sql b/src/db/rest/util/_api_raise_null.sql new file mode 100644 index 0000000..be6ee29 --- /dev/null +++ b/src/db/rest/util/_api_raise_null.sql @@ -0,0 +1,18 @@ +CREATE FUNCTION _api.raise_null( + _column TEXT DEFAULT '' +) +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +BEGIN + PERFORM _api.raise( + _msg => 'api_null_value', + _detail => _column + ); + + RETURN TRUE; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.raise_null(TEXT) + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_raise_unique.sql b/src/db/rest/util/_api_raise_unique.sql new file mode 100644 index 0000000..a18d960 --- /dev/null +++ b/src/db/rest/util/_api_raise_unique.sql @@ -0,0 +1,18 @@ +CREATE FUNCTION _api.raise_unique( + _column TEXT DEFAULT '' +) +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +BEGIN + PERFORM _api.raise( + _msg => 'api_unique_value', + _detail => _column + ); + + RETURN TRUE; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.raise_unique(TEXT) + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_serve_media.sql b/src/db/rest/util/_api_serve_media.sql new file mode 100644 index 0000000..8b0f0b8 --- /dev/null +++ b/src/db/rest/util/_api_serve_media.sql @@ -0,0 +1,41 @@ +CREATE FUNCTION _api.serve_media( + _name TEXT +) +RETURNS sys."*/*" +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _headers TEXT; + _data BYTEA; +BEGIN + + SELECT FORMAT( + '[{"Content-Type": "%s"},' + '{"Content-Disposition": "inline; filename=\"%s\""},' + '{"Cache-Control": "max-age=259200"}]' + , m.type, m.name) + FROM admin.media m + WHERE m.name = _name INTO _headers; + + PERFORM SET_CONFIG('response.headers', _headers, true); + + SELECT m.content + FROM admin.media m + WHERE m.name = _name + INTO _data; + + IF FOUND THEN + RETURN(_data); + ELSE + PERFORM _api.raise( + _msg => 'api_not_found', + _err => 404 + ); + END IF; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.serve_media(TEXT) + TO rest_anon, rest_user; +GRANT SELECT ON TABLE admin.media + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_trim.sql b/src/db/rest/util/_api_trim.sql new file mode 100644 index 0000000..c972282 --- /dev/null +++ b/src/db/rest/util/_api_trim.sql @@ -0,0 +1,25 @@ +CREATE FUNCTION _api.trim( + _text TEXT +) +RETURNS TEXT +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _new TEXT; +BEGIN + + IF _text IS NULL THEN + RETURN NULL; + END IF; + + _new = _text; + _new = TRIM(_new); + _new = REGEXP_REPLACE(_new, '^(?: |\r|\n)*', ''); + _new = REGEXP_REPLACE(_new, '(?: |\r|\n)*$', ''); + + RETURN _new; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.trim(TEXT) + TO rest_anon, rest_user; diff --git a/src/db/rest/util/_api_validate_text.sql b/src/db/rest/util/_api_validate_text.sql new file mode 100644 index 0000000..ff3a227 --- /dev/null +++ b/src/db/rest/util/_api_validate_text.sql @@ -0,0 +1,53 @@ +CREATE FUNCTION _api.validate_text( + _column TEXT DEFAULT '', + _text TEXT DEFAULT NULL, + _min INTEGER DEFAULT NULL, + _max INTEGER DEFAULT NULL, + _nullable BOOLEAN DEFAULT FALSE +) +RETURNS BOOLEAN +LANGUAGE plpgsql VOLATILE +AS $BODY$ +DECLARE + _length INTEGER; +BEGIN + + -- make sure that text can only be null + -- when we allow it + IF _text IS NULL AND NOT _nullable THEN + PERFORM _api.raise( + _msg => 'api_null_value', + _detail => _column + ); + END IF; + + IF _text IS NULL THEN + RETURN TRUE; + END IF; + + _length = LENGTH(_text); + + IF _min IS NOT NULL AND _length < _min THEN + PERFORM _api.raise( + _msg => 'api_min_value', + _detail => _column, + _hint => _min || '' + ); + RETURN FALSE; + END IF; + + IF _max IS NOT NULL AND _length > _max THEN + PERFORM _api.raise( + _msg => 'api_max_value', + _detail => _column, + _hint => _max || '' + ); + RETURN FALSE; + END IF; + + RETURN TRUE; +END +$BODY$; + +GRANT EXECUTE ON FUNCTION _api.validate_text(TEXT, TEXT, INTEGER, INTEGER, BOOLEAN) + TO rest_anon, rest_user; |