We need to add "WHERE valid_id = 1" to the SQL query, and then sync then exemption list and OTRS active addresses (https://vrt-wiki.wikimedia.org/wiki/List_of_email_addresses).

The only addresses in the hardcoded lists, that is active on VRTS, is: otrs@wikimedia.org

So we need to add the clause to the query, and find out what the otrs@wikimedia.org address is or was used for.

Thanks @Krd that would be a great simplification, happy to craft a patch after figuring out what to do with otrs@wikimedia.org, at present it is configured to forward to otrs@ticket.wikimedia.org, but ticket.wikimedia.org has no MX records, nor is it listening on port 25, so all mail to that address is being dropped. I'm not sure what the intended use of that address is?

In T374090#10157967, @jhathaway wrote:

otrs@ticket.wikimedia.org, but ticket.wikimedia.org has no MX records, nor is it listening on port 25, so all mail to that address is being dropped. I'm not sure what the intended use of that address is?

I see this being mentioned on https://wikitech.wikimedia.org/wiki/Mail#VRT_System

The following router does the actual aliasing of the VRT address to otrs@ticket.wikimedia.org, if the queue address exists in the database..

This is followed by a "In the new VRT setup..." section.

So it seems like this was used in the past and then not anymore. afaict.

In T374090#10158007, @Dzahn wrote:

So it seems like this was used in the past and then not anymore. afaict.

Thanks for the spelunking @Dzahn, so perhaps we could mark that queue as invalid on the VRTS side and then add the WHERE valid_id = 1 clause to the script?

My knowledge is quite limited here but I would say yes, it seems like that can be marked as invalid.

I mean.. as you point out, ticket.wikimedia.org doesn't have an MX record so I don't see how that would break anything existing.

Sounds good. Can you please provide a diff of the output of the current script version vs. the new one, just to be safe? If that looks good if should be activated as soon as possible.

Change #1074200 had a related patch set uploaded (by JHathaway; author: JHathaway):

[operations/puppet@production] vrts_aliases: query database for valid addresses

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

@Krd

• patch: https://gerrit.wikimedia.org/r/1074200
• script output: P69335
• diff output: P69334

Looks good to me. Please continue.

How often does the script run?
Is there any protection just in case the database output will be empty because the format changed, or anything similar?

In T374090#10161865, @Krd wrote:

How often does the script run?

every hour

Is there any protection just in case the database output will be empty because the format changed, or anything similar?

I don't see any obvious safe guards, happy to add some, perhaps just ensuring the number of lines is greater than say 100, otherwise exit non-zero?

We had these errors in the output:

ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: donation@wikipedia.org
ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: donations@wikipedia.org
ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: otrs@wikimedia.org

for otrs I'll cut a patch to remove the postfix alias, what about the other two?

re: donations@ we should probably make sure with fr-tech what should happen (Jeff Green or Dallas Wisehaupt f,e.)

Every hour is perfect. Yes, please add the mentioned safeguard!

Regarding the two donation addresses, if you confirm that they are not routed to VRTS anyway, we can just remove the addresses there.

• donation@wikipedia.org and donations@wikipedia.org: were both part of the hardcoded list, so they have not been routed to vrts for some time, so I think they should be marked as invalid on the vrts side.
• otrs@wikimedia.org: this address seems to be no longer in use, so I think it should be marked as invalid on the vrts side and removed from postfix as well

Change #1074433 had a related patch set uploaded (by JHathaway; author: JHathaway):

[operations/puppet@production] vrts_aliases: add a basic safeguard

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

In T374090#10163252, @Krd wrote:

Every hour is perfect. Yes, please add the mentioned safeguard!

patch with safeguard, https://gerrit.wikimedia.org/r/c/operations/puppet/+/1074433

Perfect. When will it be activated?

As this is nearly complete and perhaps just needs activation, when are "we" going to do the final step?

@Krd thanks for the nudge, I would like to fix these errors before we roll it out:

ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: donation@wikipedia.org
ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: donations@wikipedia.org
ERROR:/home/jhathaway/./vrts_aliases:email is handled by postfix alias: otrs@wikimedia.org

Would you be able to mark those queues as invalid on the VRTS side?

Done.

In T374090#10216207, @Krd wrote:

Done.

Thanks @Krd, as soon as I get a review of my last patch update, I'll merge them in.

Change #1074200 merged by JHathaway:

[operations/puppet@production] vrts_aliases: query database for valid addresses

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

Change #1074433 merged by JHathaway:

[operations/puppet@production] vrts_aliases: add a basic safeguard, improve existing safeguards

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