login and register, liking on homepage

1 files changed, 30 insertions, 0 deletions

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;