Clean up OTRS/Znuny addresses handles by gsuite
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	jbond
	Jun 2 2021, 1:05 PM

Description

The OTRS/Znuny database contains a number of email addresses which are actually handles by gsuite. As such email will never get delivered to orts/zunny

ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: board@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: press@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: donations@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: security@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: postmaster@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: postmaster@wikipedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: privacy@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: legal-en@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: business@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: wm-cz@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: legal-tm-vio@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: ux@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: glam@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: improve@wikimedia.org
ERROR:/usr/local/bin/otrs_aliases:email is handled by gsuite: board@wikimedia.org

It would be useful to understand if theses emails should be removed from zunny or if we can ignore this.

Details

Subject	Repo	Branch	Lines +/-
Run vrts_aliases in debug mode	operations/puppet	production	+1 -1
mx: stop ignoring VRTS alias errors	operations/puppet	production	+0 -3
vrts: Stop ignoring errors from alias sync	operations/puppet	production	+0 -3
vrts: add missing comma to vrts_aliases.py	operations/puppet	production	+2 -2
Filter out addresses that cannot be removed from VRTS	operations/puppet	production	+14 -1
Revert "mx: Stop ignoring errors from alias sync"	operations/puppet	production	+3 -0
mx: Stop ignoring errors from alias sync	operations/puppet	production	+0 -3
Filter out addresses handled by gsuite that cannot be removed from VRTS	operations/puppet	production	+20 -0
P:mail::mx: update systemd timer so that it fails silently	operations/puppet	production	+8 -5

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
		Restricted Task
		Restricted Task
Resolved	LSobanski	T284145 Clean up OTRS/Znuny addresses handles by gsuite

Event Timeline

jbond triaged this task as Medium priority.Jun 2 2021, 1:05 PM

jbond created this task.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 2 2021, 1:05 PM

jbond updated the task description. (Show Details)Jun 2 2021, 1:05 PM

Change 697785 had a related patch set uploaded (by Jbond; author: John Bond):

[operations/puppet@production] P:mail::mx: update systemd timer so that it fails silently

https://gerrit.wikimedia.org/r/697785

gerritbot added a project: Patch-For-Review.Jun 2 2021, 1:07 PM

Change 697785 merged by Jbond:

[operations/puppet@production] P:mail::mx: update systemd timer so that it fails silently

https://gerrit.wikimedia.org/r/697785

jbond added a parent task: Restricted Task.Jun 2 2021, 2:02 PM

Maintenance_bot removed a project: Patch-For-Review.Jun 2 2021, 2:10 PM

@Keegan I notice you working on the orts/znuny migration and wondered if you would be able to advice on this task, or point me to someone who may know more. thanks

jbond moved this task from Active 🚁 to Blocked 🚧 on the User-jbond board.Jun 4 2021, 12:30 PM

(VRTS admin:) Except of glam@wikimedia.org, all listed mail addresses are deactivated in Znuny/OTRS, so no further actions necessary. For glam@wikimedia.org there is still a queue. @Sadads (Astinson (WMF)) is the only user with access to this queue. If the queue can be closed, we can also deactivate this mail address in Znuny. For this, please send a request to otrs-admins@lists.wikimedia.org.

@DC thanks for the update, do you know what database attribute i could use to exclude deactivated emails from my query. im curently using the following SQL statment to get all otrs emails

SELECT value0, create_time, change_time FROM system_address

@Sadads are you able to confirm if glam@wikimedia.org can be deactivated in OTRS, as far as i can tell no emails addresses to this email will be delivered to to OTRS and will instead be delivered to the google smtp servers

$ sudo exim -bt glam@wikimedia.org                                                                                                                      
glam@wikimedia.org
  router = ldap_group, transport = remote_smtp
  host aspmx.l.google.com [172.217.222.26]

Aklapper added a project: Infrastructure-Foundations.Jun 21 2021, 8:59 PM

In T284145#7134174, @jbond wrote:

@Keegan I notice you working on the orts/znuny migration and wondered if you would be able to advice on this task, or point me to someone who may know more. thanks

@jbond my utmost apologies for not replying to this earlier! These errors can be ignored, they will never go away AFAIK. These are the result of email addresses that used to be processed through OTRS/Znuny LTS, but were usurped by gsuite handling. When this occurs the old emails were invalidated and disabled from OTRS/Znuny LTS, but they cannot be deleted and so they will forever log errors unless/until the ability to delete unused emails is added as functionality.

Quiddity subscribed.Dec 6 2021, 11:22 PM

In T284145#7218511, @Keegan wrote:

@jbond my utmost apologies for not replying to this earlier! These errors can be ignored, they will never go away AFAIK. These are the result of email addresses that used to be processed through OTRS/Znuny LTS, but were usurped by gsuite handling. When this occurs the old emails were invalidated and disabled from OTRS/Znuny LTS, but they cannot be deleted and so they will forever log errors unless/until the ability to delete unused emails is added as functionality.

@Keegan I suppose this is still the case as of today? If it's just this small list of legacy addressed then we'd simply add them to the export script and filter them out as known false positives.

Let's review if the new Znuny version enabled removal of unused emails and remove them if possible
If not, then let's filter the emails in the export script (in profile::mail::vrts)

cc @Quiddity in case there are any comments about the above

@MoritzMuehlenhoff I cannot say for sure as I have not worked in this area for several years, but I cannot imagine that the situation has changed.

LSobanski added a project: collaboration-services.May 7 2024, 10:58 AM

LSobanski merged a task: T341302: VRTS aliases - errors due to addresses being handled by Google.May 7 2024, 3:25 PM

LSobanski added subscribers: Dzahn, Arnoldokoth.

In T284145#9743789, @LSobanski wrote:

Let's review if the new Znuny version enabled removal of unused emails and remove them if possible

If not, then let's filter the emails in the export script (in profile::mail::vrts)

cc @Quiddity in case there are any comments about the above

I don't know if this feature was added (perhaps in a more obscure location, or in a CLI-only way?), but I cannot see any relevant UI features in the admin panel for "Email Addresses" - there's only an "Add System Address" button at https://ticket.wikimedia.org/otrs/index.pl?Action=AdminSystemAddress ; and within the pages for individual addresses linked therein, there are not any removal-related features.
Sounds reasonable.

Change #1031432 had a related patch set uploaded (by LSobanski; author: LSobanski):

[operations/puppet@production] Filter out addresses handled by gsuite that cannot be removed from VRTS

https://gerrit.wikimedia.org/r/1031432

gerritbot added a project: Patch-For-Review.May 14 2024, 12:43 PM

LSobanski claimed this task.May 14 2024, 1:38 PM

LSobanski lowered the priority of this task from Medium to Low.

LSobanski moved this task from Incoming to Consultation on the collaboration-services board.

LSobanski moved this task from Consultation to Work in Progress on the collaboration-services board.

Change #1031432 merged by EoghanGaffney:

[operations/puppet@production] Filter out addresses handled by gsuite that cannot be removed from VRTS

https://gerrit.wikimedia.org/r/1031432

Maintenance_bot removed a project: Patch-For-Review.May 14 2024, 4:32 PM

Resolving as the change is now in place.

Reopening as the alert silencing (https://gerrit.wikimedia.org/r/c/operations/puppet/+/697785) still needs to be reverted.

Change #1031760 had a related patch set uploaded (by Muehlenhoff; author: Muehlenhoff):

[operations/puppet@production] mx: Stop ignoring errors from alias sync

https://gerrit.wikimedia.org/r/1031760

Change #1031761 had a related patch set uploaded (by Muehlenhoff; author: Muehlenhoff):

[operations/puppet@production] vrts: Stop ignoring errors from alias sync

https://gerrit.wikimedia.org/r/1031761

Change #1031760 merged by Muehlenhoff:

[operations/puppet@production] mx: Stop ignoring errors from alias sync

https://gerrit.wikimedia.org/r/1031760

Change #1031942 had a related patch set uploaded (by Muehlenhoff; author: Muehlenhoff):

[operations/puppet@production] Revert "mx: Stop ignoring errors from alias sync"

https://gerrit.wikimedia.org/r/1031942

Change #1031942 merged by Muehlenhoff:

[operations/puppet@production] Revert "mx: Stop ignoring errors from alias sync"

https://gerrit.wikimedia.org/r/1031942

The filter list isn't complete yet, when enabling it on the MX hosts (reverted for now) it logged the following errors:

May 15 13:34:56 mx2001 systemd[1]: Finished Generate VRTS aliases file for Exim.
May 15 13:34:56 mx2001 systemd[1]: generate_vrts_aliases.service: Consumed 1.185s CPU time.
May 15 14:22:08 mx2001 systemd[1]: Starting Generate VRTS aliases file for Exim...
May 15 14:22:08 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: otrs@wikimedia.org
May 15 14:22:08 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: noc@wikimedia.org
May 15 14:22:09 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: webmaster@wikimedia.org
May 15 14:22:09 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: webmaster@wikipedia.org
May 15 14:22:14 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by gsuite: wikimania@wikimedia.org
May 15 14:22:14 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by gsuite: donation@wikimedia.org
May 15 14:22:14 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: donation@wikipedia.org
May 15 14:22:14 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: donations@wikipedia.org
May 15 14:22:27 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: security@wikipedia.org
May 15 14:22:34 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: jobs@wikipedia.org
May 15 14:23:14 mx2001 vrts_aliases[947648]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: privacy@wikidata.org
May 15 14:23:17 mx2001 systemd[1]: generate_vrts_aliases.service: Main process exited, code=exited, status=1/FAILURE
May 15 14:23:17 mx2001 systemd[1]: generate_vrts_aliases.service: Failed with result 'exit-code'.
May 15 14:23:17 mx2001 systemd[1]: Failed to start Generate VRTS aliases file for Exim.
May 15 14:23:17 mx2001 systemd[1]: generate_vrts_aliases.service: Consumed 1.224s CPU time.

Change #1034046 had a related patch set uploaded (by LSobanski; author: LSobanski):

[operations/puppet@production] Filter out additional addresses handled by gsuite and postfix that cannot be removed from VRTS

https://gerrit.wikimedia.org/r/1034046

Change #1034046 merged by AOkoth:

[operations/puppet@production] Filter out addresses that cannot be removed from VRTS

https://gerrit.wikimedia.org/r/1034046

Last run:

May 28 18:18:41 mx1001 systemd[1]: Starting Generate VRTS aliases file for Exim...
May 28 18:18:43 mx1001 vrts_aliases[1605430]: ERROR:/usr/local/bin/vrts_aliases:email is handled by postfix alias: donations@wikipedia.org
May 28 18:18:57 mx1001 vrts_aliases[1605430]: ERROR:/usr/local/bin/vrts_aliases:email is handled by gsuite: wm-cz@wikimedia.org
May 28 18:19:24 mx1001 systemd[1]: generate_vrts_aliases.service: Succeeded.
May 28 18:19:24 mx1001 systemd[1]: Finished Generate VRTS aliases file for Exim.
May 28 18:19:24 mx1001 systemd[1]: generate_vrts_aliases.service: Consumed 1.094s CPU time.

Do we still have the 2 remaining addresses here because line 83 in the script doesn't end with a comma and both appear after that, maybe?

Run with comma added manually. Thanks @Dzahn

May 28 19:44:49 mx1001 systemd[1]: Starting Generate VRTS aliases file for Exim...
May 28 19:45:31 mx1001 systemd[1]: generate_vrts_aliases.service: Succeeded.
May 28 19:45:31 mx1001 systemd[1]: Finished Generate VRTS aliases file for Exim.
May 28 19:45:31 mx1001 systemd[1]: generate_vrts_aliases.service: Consumed 1.301s CPU time.

Change #1036760 had a related patch set uploaded (by Dzahn; author: Dzahn):

[operations/puppet@production] vrts: add missing comma to vrts_aliases.py

https://gerrit.wikimedia.org/r/1036760

Change #1036760 merged by Dzahn:

[operations/puppet@production] vrts: add missing comma to vrts_aliases.py

https://gerrit.wikimedia.org/r/1036760

on mx1001:

before: Process: 1319423 ExecStart=/usr/local/bin/vrts_aliases (code=exited, status=1/FAILURE)

now: Process: 1399506 ExecStart=/usr/local/bin/vrts_aliases (code=exited, status=0/SUCCESS)

Change #1037117 had a related patch set uploaded (by Dzahn; author: Dzahn):

[operations/puppet@production] mx: stop ignoring VRTS alias errors, email on error

https://gerrit.wikimedia.org/r/1037117