#13279

pending completion

Build # #13279

Build Type

push

circleci

Committed by

web-flow

Commit Message

MNTOR-1452 - use UPDATE FOR to lock table and prevent race condition when adding email (#2982)

Run Details

282 of 1679 branches covered (16.8%)

Branch coverage included in aggregate %.

3 of 3 new or added lines in 1 file covered. (100.0%)

959 of 4548 relevant lines covered (21.09%)

1.76 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/src/db/tables/emailAddresses.js

/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

import { v4 as uuidv4 } from 'uuid'
import Knex from 'knex'
import knexConfig from '../knexfile.js'
import mozlog from '../../utils/log.js'
import { fluentError } from '../../utils/fluent.js'
import { subscribeHash } from '../../utils/hibp.js'
import { getSha1 } from '../../utils/fxa.js'
import { getSubscriberByEmail, updateFxAData } from './subscribers.js'
const knex = Knex(knexConfig)
const log = mozlog('DB.email_addresses')

async function getEmailByToken (token) {
  const res = await knex('email_addresses')
    .where('verification_token', '=', token)

  return res[0]
}

async function getEmailById (emailAddressId) {
  const res = await knex('email_addresses')
    .where('id', '=', emailAddressId)

  return res[0]
}

async function getEmailAddressRecordByEmail (email) {
  const emailAddresses = await knex('email_addresses').where({
    email, verified: true
  })
  if (!emailAddresses) {
    return null
  }
  if (emailAddresses.length > 1) {
    // TODO: handle multiple emails in separate(?) subscriber accounts?
    log.warn('getEmailAddressRecordByEmail', { msg: 'found the same email multiple times' })
  }
  return emailAddresses[0]
}

async function addSubscriberUnverifiedEmailHash (user, email) {
  const res = await knex.transaction(trx => {
    return trx('email_addresses')
      .forUpdate()
      .select({
        subscriber_id: user.id
      })
      .insert({
        subscriber_id: user.id,
        email,
        sha1: getSha1(email),
        verification_token: uuidv4(),
        verified: false
      }).returning('*')
  })
  return await res[0]
}

async function resetUnverifiedEmailAddress (emailAddressId) {
  const newVerificationToken = uuidv4()

  // Time in ms to require between verification reset.
  const verificationWait = 5 * 60 * 1000 // 5 minutes

  const verificationRecentlyUpdated = await knex('email_addresses')
    .select('id')
    .whereRaw('"updated_at" > NOW() - INTERVAL \'1 MILLISECOND\' * ?', verificationWait)
    .andWhere('id', emailAddressId)
    .first()

  if (verificationRecentlyUpdated?.id === parseInt(emailAddressId)) {
    throw fluentError('error-email-validation-pending')
  }

  const res = await knex('email_addresses')
    .update({
      verification_token: newVerificationToken,
      updated_at: knex.fn.now()
    })
    .where('id', emailAddressId)
    .returning('*')
  return res[0]
}

async function verifyEmailHash (token) {
  const unverifiedEmail = await getEmailByToken(token)
  if (!unverifiedEmail) {
    throw fluentError('Error message for this verification email timed out or something went wrong.')
  }
  const verifiedEmail = await _verifyNewEmail(unverifiedEmail)
  return verifiedEmail[0]
}

// TODO: refactor into an upsert? https://jaketrent.com/post/upsert-knexjs/
// Used internally, ideally should not be called by consumers.
async function _getSha1EntryAndDo (sha1, aFoundCallback, aNotFoundCallback) {
  const existingEntries = await knex('subscribers')
    .where('primary_sha1', sha1)

  if (existingEntries.length && aFoundCallback) {
    return await aFoundCallback(existingEntries[0])
  }

  if (!existingEntries.length && aNotFoundCallback) {
    return await aNotFoundCallback()
  }
}

// Used internally.
async function _addEmailHash (sha1, email, signupLanguage, verified = false) {
  log.debug('_addEmailHash', { sha1, email, signupLanguage, verified })
  try {
    return await _getSha1EntryAndDo(sha1, async aEntry => {
      // Entry existed, patch the email value if supplied.
      if (email) {
        const res = await knex('subscribers')
          .update({
            primary_email: email,
            primary_sha1: getSha1(email.toLowerCase()),
            primary_verified: verified,
            updated_at: knex.fn.now()
          })
          .where('id', '=', aEntry.id)
          .returning('*')
        return res[0]
      }

      return aEntry
    }, async () => {
      // Always add a verification_token value
      const verificationToken = uuidv4()
      const res = await knex('subscribers')
        .insert({ primary_sha1: getSha1(email.toLowerCase()), primary_email: email, signup_language: signupLanguage, primary_verification_token: verificationToken, primary_verified: verified })
        .returning('*')
      return res[0]
    })
  } catch (e) {
    log.error(e)
    throw fluentError('error-could-not-add-email')
  }
}

/**
 * Add a subscriber:
 * 1. Add a record to subscribers
 * 2. Immediately call _verifySubscriber
 * 3. For FxA subscriber, add refresh token and profile data
 *
 * @param {string} email to add
 * @param {string} signupLanguage from Accept-Language
 * @param {string} fxaAccessToken from Firefox Account Oauth
 * @param {string} fxaRefreshToken from Firefox Account Oauth
 * @param {string} fxaProfileData from Firefox Account
 * @returns {object} subscriber knex object added to DB
 */
async function addSubscriber (email, signupLanguage, fxaAccessToken = null, fxaRefreshToken = null, fxaProfileData = null) {
  console.log({ email })
  console.log({ signupLanguage })
  const emailHash = await _addEmailHash(getSha1(email), email, signupLanguage, true)
  const verified = await _verifySubscriber(emailHash)
  const verifiedSubscriber = Array.isArray(verified) ? verified[0] : null
  if (fxaRefreshToken || fxaProfileData) {
    return updateFxAData(verifiedSubscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData)
  }
  return verifiedSubscriber
}

/**
 * When an email is verified, convert it into a subscriber:
 * 1. Subscribe the hash to HIBP
 * 2. Update our subscribers record to verified
 * 3. (if opted in) Subscribe the email to Fx newsletter
 *
 * @param {object} emailHash knex object in DB
 * @returns {object} verified subscriber knex object in DB
 */
async function _verifySubscriber (emailHash) {
  await subscribeHash(emailHash.primary_sha1)

  const verifiedSubscriber = await knex('subscribers')
    .where('primary_email', '=', emailHash.primary_email)
    .update({
      primary_verified: true,
      updated_at: knex.fn.now()
    })
    .returning('*')

  return verifiedSubscriber
}

// Verifies new emails added by existing users
async function _verifyNewEmail (emailHash) {
  await subscribeHash(emailHash.sha1)

  const verifiedEmail = await knex('email_addresses')
    .where('id', '=', emailHash.id)
    .update({
      verified: true
    })
    .returning('*')

  return verifiedEmail
}

/**
 * @typedef {object} EmailRow Email data, as returned from the database table `email_addresses`
 * @property {string} email
 * @property {string} sha1
 * @property {boolean} verified
 */

/**
 * @param {number} userId
 * @returns {Promise<EmailRow[]>}
 */
async function getUserEmails (userId) {
  const userEmails = await knex('email_addresses')
    .where('subscriber_id', '=', userId)
    .returning('*')

  return userEmails
}

// This is used by SES callbacks to remove email addresses when recipients
// perma-bounce or mark our emails as spam
// Removes from either subscribers or email_addresses as necessary
async function removeEmail (email) {
  const subscriber = await getSubscriberByEmail(email)
  if (!subscriber) {
    const emailAddress = await getEmailAddressRecordByEmail(email)
    if (!emailAddress) {
      log.warn('removed-subscriber-not-found')
      return
    }
    await knex('email_addresses')
      .where({
        email,
        verified: true
      })
      .del()
    return
  }
  // This can fail if a subscriber has more email_addresses and marks
  // a primary email as spam, but we should let it fail so we can see it
  // in the logs
  await knex('subscribers')
    .where({
      primary_verification_token: subscriber.primary_verification_token,
      primary_sha1: subscriber.primary_sha1
    })
    .del()
}

async function removeOneSecondaryEmail (emailId) {
  await knex('email_addresses')
    .where({
      id: emailId
    })
    .del()
}

async function getEmailAddressesByHashes (hashes) {
  return await knex('email_addresses')
    .join('subscribers', 'email_addresses.subscriber_id', '=', 'subscribers.id')
    .whereIn('email_addresses.sha1', hashes)
    .andWhere('email_addresses.verified', '=', true)
}

async function deleteEmailAddressesByUid (uid) {
  await knex('email_addresses').where({ subscriber_id: uid }).del()
}

export {
  getEmailByToken,
  getEmailById,
  getEmailAddressRecordByEmail,
  addSubscriberUnverifiedEmailHash,
  resetUnverifiedEmailAddress,
  verifyEmailHash,
  addSubscriber,
  getUserEmails,
  removeEmail,
  removeOneSecondaryEmail,
  getEmailAddressesByHashes,
  deleteEmailAddressesByUid
}

1	/* This Source Code Form is subject to the terms of the Mozilla Public
2	* License, v. 2.0. If a copy of the MPL was not distributed with this
3	* file, You can obtain one at http://mozilla.org/MPL/2.0/. */
4
5	import { v4 as uuidv4 } from 'uuid'
6	import Knex from 'knex'
7	import knexConfig from '../knexfile.js'
8	import mozlog from '../../utils/log.js'
9	import { fluentError } from '../../utils/fluent.js'
10	import { subscribeHash } from '../../utils/hibp.js'
11	import { getSha1 } from '../../utils/fxa.js'
12	import { getSubscriberByEmail, updateFxAData } from './subscribers.js'
13	const knex = Knex(knexConfig)	×
14	const log = mozlog('DB.email_addresses')	×
15
16	async function getEmailByToken (token) {
17	const res = await knex('email_addresses')	×
18	.where('verification_token', '=', token)
19
20	return res[0]	×
21	}
22
23	async function getEmailById (emailAddressId) {
24	const res = await knex('email_addresses')	×
25	.where('id', '=', emailAddressId)
26
27	return res[0]	×
28	}
29
30	async function getEmailAddressRecordByEmail (email) {
31	const emailAddresses = await knex('email_addresses').where({	×
32	email, verified: true
33	})
34	if (!emailAddresses) {	×
35	return null	×
36	}
37	if (emailAddresses.length > 1) {	×
38	// TODO: handle multiple emails in separate(?) subscriber accounts?
39	log.warn('getEmailAddressRecordByEmail', { msg: 'found the same email multiple times' })	×
40	}
41	return emailAddresses[0]	×
42	}
43
44	async function addSubscriberUnverifiedEmailHash (user, email) {
45	const res = await knex.transaction(trx => {	×
46	return trx('email_addresses')	×
47	.forUpdate()
48	.select({
49	subscriber_id: user.id
50	})
51	.insert({
52	subscriber_id: user.id,
53	email,
54	sha1: getSha1(email),
55	verification_token: uuidv4(),
56	verified: false
57	}).returning('*')
58	})
59	return await res[0]	×
60	}
61
62	async function resetUnverifiedEmailAddress (emailAddressId) {
63	const newVerificationToken = uuidv4()	×
64
65	// Time in ms to require between verification reset.
66	const verificationWait = 5 * 60 * 1000 // 5 minutes	×
67
68	const verificationRecentlyUpdated = await knex('email_addresses')	×
69	.select('id')
70	.whereRaw('"updated_at" > NOW() - INTERVAL \'1 MILLISECOND\' * ?', verificationWait)
71	.andWhere('id', emailAddressId)
72	.first()
73
74	if (verificationRecentlyUpdated?.id === parseInt(emailAddressId)) {	×
75	throw fluentError('error-email-validation-pending')	×
76	}
77
78	const res = await knex('email_addresses')	×
79	.update({
80	verification_token: newVerificationToken,
81	updated_at: knex.fn.now()
82	})
83	.where('id', emailAddressId)
84	.returning('*')
85	return res[0]	×
86	}
87
88	async function verifyEmailHash (token) {
89	const unverifiedEmail = await getEmailByToken(token)	×
90	if (!unverifiedEmail) {	×
91	throw fluentError('Error message for this verification email timed out or something went wrong.')	×
92	}
93	const verifiedEmail = await _verifyNewEmail(unverifiedEmail)	×
94	return verifiedEmail[0]	×
95	}
96
97	// TODO: refactor into an upsert? https://jaketrent.com/post/upsert-knexjs/
98	// Used internally, ideally should not be called by consumers.
99	async function _getSha1EntryAndDo (sha1, aFoundCallback, aNotFoundCallback) {
100	const existingEntries = await knex('subscribers')	×
101	.where('primary_sha1', sha1)
102
103	if (existingEntries.length && aFoundCallback) {	×
104	return await aFoundCallback(existingEntries[0])	×
105	}
106
107	if (!existingEntries.length && aNotFoundCallback) {	×
108	return await aNotFoundCallback()	×
109	}
110	}
111
112	// Used internally.
113	async function _addEmailHash (sha1, email, signupLanguage, verified = false) {	×
114	log.debug('_addEmailHash', { sha1, email, signupLanguage, verified })	×
115	try {	×
116	return await _getSha1EntryAndDo(sha1, async aEntry => {	×
117	// Entry existed, patch the email value if supplied.
118	if (email) {	×
119	const res = await knex('subscribers')	×
120	.update({
121	primary_email: email,
122	primary_sha1: getSha1(email.toLowerCase()),
123	primary_verified: verified,
124	updated_at: knex.fn.now()
125	})
126	.where('id', '=', aEntry.id)
127	.returning('*')
128	return res[0]	×
129	}
130
131	return aEntry	×
132	}, async () => {
133	// Always add a verification_token value
134	const verificationToken = uuidv4()	×
135	const res = await knex('subscribers')	×
136	.insert({ primary_sha1: getSha1(email.toLowerCase()), primary_email: email, signup_language: signupLanguage, primary_verification_token: verificationToken, primary_verified: verified })
137	.returning('*')
138	return res[0]	×
139	})
140	} catch (e) {
141	log.error(e)	×
142	throw fluentError('error-could-not-add-email')	×
143	}
144	}
145
146	/**
147	* Add a subscriber:
148	* 1. Add a record to subscribers
149	* 2. Immediately call _verifySubscriber
150	* 3. For FxA subscriber, add refresh token and profile data
151	*
152	* @param {string} email to add
153	* @param {string} signupLanguage from Accept-Language
154	* @param {string} fxaAccessToken from Firefox Account Oauth
155	* @param {string} fxaRefreshToken from Firefox Account Oauth
156	* @param {string} fxaProfileData from Firefox Account
157	* @returns {object} subscriber knex object added to DB
158	*/
159	async function addSubscriber (email, signupLanguage, fxaAccessToken = null, fxaRefreshToken = null, fxaProfileData = null) {	×
160	console.log({ email })	×
161	console.log({ signupLanguage })	×
162	const emailHash = await _addEmailHash(getSha1(email), email, signupLanguage, true)	×
163	const verified = await _verifySubscriber(emailHash)	×
164	const verifiedSubscriber = Array.isArray(verified) ? verified[0] : null	×
165	if (fxaRefreshToken \|\| fxaProfileData) {	×
166	return updateFxAData(verifiedSubscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData)	×
167	}
168	return verifiedSubscriber	×
169	}
170
171	/**
172	* When an email is verified, convert it into a subscriber:
173	* 1. Subscribe the hash to HIBP
174	* 2. Update our subscribers record to verified
175	* 3. (if opted in) Subscribe the email to Fx newsletter
176	*
177	* @param {object} emailHash knex object in DB
178	* @returns {object} verified subscriber knex object in DB
179	*/
180	async function _verifySubscriber (emailHash) {
181	await subscribeHash(emailHash.primary_sha1)	×
182
183	const verifiedSubscriber = await knex('subscribers')	×
184	.where('primary_email', '=', emailHash.primary_email)
185	.update({
186	primary_verified: true,
187	updated_at: knex.fn.now()
188	})
189	.returning('*')
190
191	return verifiedSubscriber	×
192	}
193
194	// Verifies new emails added by existing users
195	async function _verifyNewEmail (emailHash) {
196	await subscribeHash(emailHash.sha1)	×
197
198	const verifiedEmail = await knex('email_addresses')	×
199	.where('id', '=', emailHash.id)
200	.update({
201	verified: true
202	})
203	.returning('*')
204
205	return verifiedEmail	×
206	}
207
208	/**
209	* @typedef {object} EmailRow Email data, as returned from the database table `email_addresses`
210	* @property {string} email
211	* @property {string} sha1
212	* @property {boolean} verified
213	*/
214
215	/**
216	* @param {number} userId
217	* @returns {Promise<EmailRow[]>}
218	*/
219	async function getUserEmails (userId) {
220	const userEmails = await knex('email_addresses')	×
221	.where('subscriber_id', '=', userId)
222	.returning('*')
223
224	return userEmails	×
225	}
226
227	// This is used by SES callbacks to remove email addresses when recipients
228	// perma-bounce or mark our emails as spam
229	// Removes from either subscribers or email_addresses as necessary
230	async function removeEmail (email) {
231	const subscriber = await getSubscriberByEmail(email)	×
232	if (!subscriber) {	×
233	const emailAddress = await getEmailAddressRecordByEmail(email)	×
234	if (!emailAddress) {	×
235	log.warn('removed-subscriber-not-found')	×
236	return	×
237	}
238	await knex('email_addresses')	×
239	.where({
240	email,
241	verified: true
242	})
243	.del()
244	return	×
245	}
246	// This can fail if a subscriber has more email_addresses and marks
247	// a primary email as spam, but we should let it fail so we can see it
248	// in the logs
249	await knex('subscribers')	×
250	.where({
251	primary_verification_token: subscriber.primary_verification_token,
252	primary_sha1: subscriber.primary_sha1
253	})
254	.del()
255	}
256
257	async function removeOneSecondaryEmail (emailId) {
258	await knex('email_addresses')	×
259	.where({
260	id: emailId
261	})
262	.del()
263	}
264
265	async function getEmailAddressesByHashes (hashes) {
266	return await knex('email_addresses')	×
267	.join('subscribers', 'email_addresses.subscriber_id', '=', 'subscribers.id')
268	.whereIn('email_addresses.sha1', hashes)
269	.andWhere('email_addresses.verified', '=', true)
270	}
271
272	async function deleteEmailAddressesByUid (uid) {
273	await knex('email_addresses').where({ subscriber_id: uid }).del()	×
274	}
275
276	export {
277	getEmailByToken,
278	getEmailById,
279	getEmailAddressRecordByEmail,
280	addSubscriberUnverifiedEmailHash,
281	resetUnverifiedEmailAddress,
282	verifyEmailHash,
283	addSubscriber,
284	getUserEmails,
285	removeEmail,
286	removeOneSecondaryEmail,
287	getEmailAddressesByHashes,
288	deleteEmailAddressesByUid
289	}

mozilla / blurts-server / #13279

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous