49 lines
2.1 KiB
SQL
49 lines
2.1 KiB
SQL
-- Run as the Synapse database owner after creating the LOGIN role
|
|
-- botsu_presence_membership. Presence receives EXECUTE only; it must not
|
|
-- receive SELECT on Synapse event tables.
|
|
|
|
CREATE OR REPLACE FUNCTION public.botsu_can_access_drawing(
|
|
requested_room_id text,
|
|
requested_user_id text,
|
|
requested_resource_id text
|
|
) RETURNS boolean
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
SET search_path = pg_catalog, public
|
|
AS $function$
|
|
SELECT EXISTS (
|
|
SELECT 1
|
|
FROM current_state_events AS membership_state
|
|
JOIN current_state_events AS drawing_state
|
|
ON drawing_state.room_id = membership_state.room_id
|
|
AND drawing_state.type = 'net.botsu.drawing'
|
|
AND drawing_state.state_key = ''
|
|
JOIN event_json AS drawing_event
|
|
ON drawing_event.event_id = drawing_state.event_id
|
|
AND drawing_event.room_id = drawing_state.room_id
|
|
WHERE membership_state.room_id = requested_room_id
|
|
AND membership_state.type = 'm.room.member'
|
|
AND membership_state.state_key = requested_user_id
|
|
AND membership_state.membership = 'join'
|
|
AND drawing_event.json::jsonb #>> '{content,version}' = '1'
|
|
AND drawing_event.json::jsonb #>> '{content,resourceId}' = requested_resource_id
|
|
AND 1 = (
|
|
SELECT count(*)
|
|
FROM current_state_events AS unique_drawing_state
|
|
JOIN event_json AS unique_drawing_event
|
|
ON unique_drawing_event.event_id = unique_drawing_state.event_id
|
|
AND unique_drawing_event.room_id = unique_drawing_state.room_id
|
|
WHERE unique_drawing_state.type = 'net.botsu.drawing'
|
|
AND unique_drawing_state.state_key = ''
|
|
AND unique_drawing_event.json::jsonb #>> '{content,version}' = '1'
|
|
AND unique_drawing_event.json::jsonb #>> '{content,resourceId}' = requested_resource_id
|
|
)
|
|
)
|
|
$function$;
|
|
|
|
REVOKE ALL ON FUNCTION public.botsu_can_access_drawing(text, text, text) FROM PUBLIC;
|
|
GRANT EXECUTE ON FUNCTION public.botsu_can_access_drawing(text, text, text)
|
|
TO botsu_presence_membership;
|
|
REVOKE SELECT ON TABLE current_state_events, event_json FROM botsu_presence_membership;
|