start database (user and post), and initial barebones home page

21 files changed, 709 insertions, 0 deletions

diff --git a/db/rest/login/_api_sign_jwt.sql b/db/rest/login/_api_sign_jwt.sql
new file mode 100644
index 0000000..dc8e920
--- /dev/null
+++ b/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/db/rest/login/_api_validate_role.sql b/db/rest/login/_api_validate_role.sql
new file mode 100644
index 0000000..9f1e54f
--- /dev/null
+++ b/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/db/rest/login/_api_verify_jwt.sql b/db/rest/login/_api_verify_jwt.sql
new file mode 100644
index 0000000..f5a6daf
--- /dev/null
+++ b/db/rest/login/_api_verify_jwt.sql
@@ -0,0 +1,38 @@
+CREATE FUNCTION _api.verify_jwt(
+	_token TEXT
+)
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_payload JSON;
+	_valid BOOLEAN;
+	_jwt_secret TEXT;
+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;
+
+	RETURN _payload->>'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;
diff --git a/db/rest/login/api_login.sql b/db/rest/login/api_login.sql
new file mode 100644
index 0000000..0cf0535
--- /dev/null
+++ b/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/db/rest/post/api_post.sql b/db/rest/post/api_post.sql
new file mode 100644
index 0000000..a91d9d2
--- /dev/null
+++ b/db/rest/post/api_post.sql
@@ -0,0 +1,13 @@
+CREATE VIEW api.post AS
+	SELECT
+		p.id,
+		p.user_id,
+		p.content,
+		p.date
+	FROM
+		admin.post p;
+
+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/db/rest/post/api_post_delete.sql b/db/rest/post/api_post_delete.sql
new file mode 100644
index 0000000..e3dec55
--- /dev/null
+++ b/db/rest/post/api_post_delete.sql
@@ -0,0 +1,31 @@
+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;
+
+	DELETE FROM admin.post
+	WHERE user_id = _user_id
+	AND 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 DELETE 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/db/rest/post/api_post_insert.sql b/db/rest/post/api_post_insert.sql
new file mode 100644
index 0000000..9eb200c
--- /dev/null
+++ b/db/rest/post/api_post_insert.sql
@@ -0,0 +1,40 @@
+CREATE FUNCTION _api.post_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_user_id INTEGER;
+BEGIN
+	_user_id = _api.get_user_id();
+
+	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
+	);
+
+	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;
+
+CREATE TRIGGER api_post_insert_trgr
+	INSTEAD OF INSERT
+			ON api.post
+			FOR EACH ROW
+				EXECUTE PROCEDURE _api.post_insert();
diff --git a/db/rest/post/api_post_update.sql b/db/rest/post/api_post_update.sql
new file mode 100644
index 0000000..915d0cd
--- /dev/null
+++ b/db/rest/post/api_post_update.sql
@@ -0,0 +1,18 @@
+CREATE FUNCTION _api.post_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.post_update() 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/db/rest/rest.sql b/db/rest/rest.sql
new file mode 100644
index 0000000..a286f9d
--- /dev/null
+++ b/db/rest/rest.sql
@@ -0,0 +1,43 @@
+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_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;
+
+-- 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/db/rest/user/api_avatar.sql b/db/rest/user/api_avatar.sql
new file mode 100644
index 0000000..981409f
--- /dev/null
+++ b/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/db/rest/user/api_user.sql b/db/rest/user/api_user.sql
new file mode 100644
index 0000000..e45768a
--- /dev/null
+++ b/db/rest/user/api_user.sql
@@ -0,0 +1,23 @@
+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.join_date,
+		u.birth_date,
+		u.profile_avatar,
+		u.profile_banner,
+		u.profile_bio
+	FROM
+		admin.user u;
+
+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/db/rest/user/api_user_delete.sql b/db/rest/user/api_user_delete.sql
new file mode 100644
index 0000000..8d7d52f
--- /dev/null
+++ b/db/rest/user/api_user_delete.sql
@@ -0,0 +1,30 @@
+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;
+
+	DELETE FROM admin.user
+	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 DELETE 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/db/rest/user/api_user_insert.sql b/db/rest/user/api_user_insert.sql
new file mode 100644
index 0000000..da3ae2d
--- /dev/null
+++ b/db/rest/user/api_user_insert.sql
@@ -0,0 +1,121 @@
+CREATE FUNCTION _api.user_insert()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+	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',
+		_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_avatar,
+		profile_banner,
+		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_avatar,
+		NEW.profile_banner,
+		NEW.profile_bio
+	);
+
+	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/db/rest/user/api_user_update.sql b/db/rest/user/api_user_update.sql
new file mode 100644
index 0000000..c6e7f4f
--- /dev/null
+++ b/db/rest/user/api_user_update.sql
@@ -0,0 +1,21 @@
+CREATE FUNCTION _api.user_update()
+RETURNS TRIGGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_length INTEGER;
+BEGIN
+	RETURN NEW;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.user_update()
+	TO rest_user;
+GRANT DELETE ON TABLE api.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/db/rest/util/_api_get_user_id.sql b/db/rest/util/_api_get_user_id.sql
new file mode 100644
index 0000000..23eb160
--- /dev/null
+++ b/db/rest/util/_api_get_user_id.sql
@@ -0,0 +1,11 @@
+CREATE FUNCTION _api.get_user_id()
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+BEGIN
+	RETURN CURRENT_SETTING(
+		'request.jwt.claims',
+		TRUE
+	)::JSON->>'user_id';
+END
+$BODY$;
diff --git a/db/rest/util/_api_raise.sql b/db/rest/util/_api_raise.sql
new file mode 100644
index 0000000..5c740c6
--- /dev/null
+++ b/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/db/rest/util/_api_raise_deny.sql b/db/rest/util/_api_raise_deny.sql
new file mode 100644
index 0000000..17406b7
--- /dev/null
+++ b/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/db/rest/util/_api_raise_null.sql b/db/rest/util/_api_raise_null.sql
new file mode 100644
index 0000000..be6ee29
--- /dev/null
+++ b/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/db/rest/util/_api_raise_unique.sql b/db/rest/util/_api_raise_unique.sql
new file mode 100644
index 0000000..a18d960
--- /dev/null
+++ b/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/db/rest/util/_api_serve_media.sql b/db/rest/util/_api_serve_media.sql
new file mode 100644
index 0000000..8b0f0b8
--- /dev/null
+++ b/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/db/rest/util/_api_validate_text.sql b/db/rest/util/_api_validate_text.sql
new file mode 100644
index 0000000..2a3764c
--- /dev/null
+++ b/db/rest/util/_api_validate_text.sql
@@ -0,0 +1,51 @@
+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_text_min',
+			_detail => _column,
+			_hint => _min || ''
+		);
+	END IF;
+
+	IF _max IS NOT NULL AND _length > _max THEN
+		PERFORM _api.raise(
+			_msg => 'api_text_max',
+			_detail => _column,
+			_hint => _max || ''
+		);
+	END IF;
+
+	RETURN TRUE;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION _api.validate_text(TEXT, TEXT, INTEGER, INTEGER, BOOLEAN)
+	TO rest_anon, rest_user;