Summary

Team Calendar e-mail notification reminders are not respecting Space permissions

Description

Issue Summary

When a user is removed from Space Permissions, the reminders he has on events associated/defined in a Calendar in the Space still trigger e-mail notifications, even though the user no longer has access to either the Space or the Calendar events.

Steps to Reproduce

1. Install Confluence 9.2
2. Create Space "CalendarTesting" with an admin user
3. Configure a Mail Server in the Confluence site (example How do I configure Confluence to use Gmail as the mail server? )
4. Have 2 users a admin and a test user accessing the space
5. Both users have access to a "CalendarTesting" Space in my Confluence 9.2 instance
6. Create a Test Calendar tied to the space "Testing Calendar" with the admin user
7. Create an event in the calendar to invite the test user not to far away in the future and set it to send a reminder 5 minutes before the event
8. Whilst connected with the test user, subscribe/regiter to email notification reminders on the calendar
9. Then whilst connected with my admin user, remove the confluence-users group from the Space permissions
   • Confirm that whilst logged in with the test user you can't access see neither the space nor the corresponding calendar and events anymore
10. Waiting a few minutes for the reminder to happen

Expected Results

The email notification reminder should not be sent because the user does not have access to the Space and the Calendar anymore

Actual Results

The email notification reminder is still being sent out

Workaround

You can run the SQL query (validated for PostgreSQL syntax) below to find users and their Calendar reminders in a Specific Space.

-- List users having reminders defined in calendars in a specific Space
SELECT DISTINCT(um.username),
        cal2."SPACE_KEY" AS space_key,
        um.user_key
FROM "AO_950DC3_TC_REMINDER_USERS" reminder
LEFT JOIN user_mapping um
    ON um.user_key = reminder."USER_KEY"
LEFT JOIN "AO_950DC3_TC_SUBCALS" cal
    ON cal."ID" = reminder."SUB_CALENDAR_ID"
LEFT JOIN "AO_950DC3_TC_SUBCALS" cal2
    ON cal2."ID"= cal."PARENT_ID"
WHERE cal2."SPACE_KEY" = 'CAL';

• Just replace the last line SPACE_KEY CAL with your own Space Key (case sensitive)
• We have created the SQL query based on this Knowledge-Based article Team Calendars Data Model in Confluence Data Center

From this first SQL Query, you can get the Users that have reminders defined in the specific space.
Using the SpaceKey and the user_key associated with the user you can then remove the specific user reminders with the SQL query below

-- Remove all Calendar reminders for a specific user in a specific Space
DELETE
FROM "AO_950DC3_TC_REMINDER_USERS"
WHERE "SUB_CALENDAR_ID" IN 
	(SELECT "ID"
	FROM "AO_950DC3_TC_SUBCALS"
	WHERE ("PARENT_ID" is NOT null
	        AND "PARENT_ID" IN 
		(SELECT "ID"
		FROM "AO_950DC3_TC_SUBCALS"
		WHERE "SPACE_KEY"='SpaceKeyHere'))
		        OR "SPACE_KEY"='SpaceKeyHere')
	        AND "USER_KEY"='UserKeyHere';

• You have 2 placeholders to replace SpaceKeyHere with your Space Key
• You have 1 placeholder to replace UserKeyHere with your specific user key you wish to remove all calendar reminders
• if you have to remove reminders for multiple users you can replace the last line with a list of user key e.g. "USER_KEY" in ('UserKey1','UserKey2','UserKey3');

Please always make sure to proceed with a change request: stop Confluence, take a database backup before proceeding with running the DELETE SQL Statement, which is an irreversible operation. Restart Confluence, and the reminders will be cleaned

Notes

N/A