Isn't this something the API exposes as the emailable property in ApiQueryUsers?

Either way, CCing @csteipp since there's questions over whether this should really be exposed or not.

Not quite. Emailable is a composite property: email is confirmed and not disabled by the user.

Exposing user_email_authenticated shows that the editor has supplied and verified an email (as well as when) regardless of whether they have chosen to disable email from users.

I don't think users expect for the date to be visible, but neither is there an attack (that I'm aware of) that exposing the date opens up. So it's never been worth fixing in AbuseFilter, but I don't think that should be used as justification for replicating it, especially without a strong use case.

What if we changed the view to return something like '19700101000000' if the real value of user_email_authenticated is non-null?

Actually since we already expose the user_registration date, we could substitute that date and not leak any new information other than the confirmed status:

(wikiadmin@db1080) [enwiki]> select user_id, user_name, user_real_name, NULL as user_password,
    -> NULL as user_newpassword, NULL as user_email, NULL as user_options,
    -> NULL as user_touched, NULL as user_token,
    -> IF(user_email_authenticated, user_registration, NULL) as user_email_authenticated,
    -> NULL as user_email_token, NULL as user_email_token_expires,
    -> user_registration, NULL as user_newpass_time, user_editcount,
    -> NULL as user_password_expires
    -> from user where user_name='BDavis (WMF)'\G
*************************** 1. row ***************************
                 user_id: 19474624
               user_name: BDavis (WMF)
          user_real_name:
           user_password: NULL
        user_newpassword: NULL
              user_email: NULL
            user_options: NULL
            user_touched: NULL
              user_token: NULL
user_email_authenticated: 20130805161344
        user_email_token: NULL
user_email_token_expires: NULL
       user_registration: 20130805161344
       user_newpass_time: NULL
          user_editcount: 26
   user_password_expires: NULL
1 row in set (0.01 sec)

In T70876#3686314, @bd808 wrote:

Actually since we already expose the user_registration date, we could substitute that date and not leak any new information other than the confirmed status

@Bawolff, what do you think? Is exposing the state of email address validation independent of the state of the user's up_property = 'disablemail' setting an unwanted leak of new information?

Could we maybe just have a new separate view that contains user_id, user_name and user_is_emailable where user_is_emailable is a boolean field that checks that disablemail is not on and use email is authenticated?

e.g. something along the lines of [untested] SELECT user.user_name as 'user_name', user.user_id as 'user_id', IF( user.user_email_authenticated AND up_value IS NULL,1,0) as "email_enabled" from user inner join user_id = up_user and up_property = 'disablemail';

I feel like this would be the least sketchy, and also avoid confusing users who might not realize that user_email_authenticated is not really the email authenticated date.

This query would work as the source of a view, but we need to make some changes to maintain_views.py to generate it:

SELECT
  user.user_id as 'user_id',
  user.user_name as 'user_name',
  IF(user.user_email_authenticated AND user_properties.up_value IS NULL,1,0) as 'email_enabled'
FROM user LEFT OUTER JOIN user_properties ON (
  user_properties.up_user = user.user_id
  AND user_properties.up_property = 'disablemail'
);

The outer join is needed because the user_properties table only stores rows for non-default values. I this case that means it only has rows for people who have opted-out of email contact.

If we use subqueries instead we could do it without modifying maintain-views

I found this out as I was researching T195341: Post message to WLM participants who have not registered an email address . I’ll probably go for (slow) API queries, but would be interested in checking this via the WikiReplicas.

Any update on this? Being able to query the "emailable" status for an enduser is already publicly available (e.g. https://en.wikipedia.org/wiki/Special:ApiSandbox#action=query&format=json&list=users&formatversion=2&usprop=emailable&ususers=Xaosflux ) even a boolean value would be useful