src/db/rest/login/_api_validate_role.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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 xssbook.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;