Email Masking Script for Dry Run SMTP Testing

This document provides SQL scripts to mask patron email addresses in dry run environments to enable workflow testing with SMTP enabled.

Problem: SMTP is typically disabled in dry run environments, preventing testing of email-based workflows like password reset.

Solution: Enable SMTP for selected tenants but replace patron email addresses with a fake address (fse.hosting@ebsco.com) to prevent emails from being sent to real patrons.

IMPORTANT: These scripts must be executed separately on each tenant that requires SMTP testing. Replace ${tenant_schema} with the appropriate tenant schema name before each execution.

User type	Action	Reason

User type	Action	Reason
patron	UPDATE	Real patrons - must mask email
dcb	UPDATE	Distributed Catalog Browser users - not staff
NULL(no type)	UPDATE	Likely patrons without type populated
staff	SKIP	Staff users need real email for testing
shadow	SKIP	Created from staff users - maintain consistency
system	SKIP	Service/automated accounts - not real users

SQL cripts

USERS table

Verification Query (Run First):

  -- Preview users that will have their email updated
  -- Excludes: staff, shadow, system
  -- Includes: patron, dcb, NULL (no type), or any other value
  SELECT
      jsonb->>'id' AS user_id,
      jsonb->>'username' AS username,
      jsonb->>'type' AS user_type,
      jsonb->'personal'->>'firstName' AS first_name,
      jsonb->'personal'->>'lastName' AS last_name,
      jsonb->'personal'->>'email' AS current_email
  FROM ${tenant_schema}.users
  WHERE
      jsonb->'personal'->>'email' IS NOT NULL
      AND jsonb->'personal'->>'email' != ''
      AND (
          jsonb->>'type' IS NULL
          OR jsonb->>'type' NOT IN ('staff', 'shadow', 'system')
      )
  ORDER BY jsonb->>'type', jsonb->>'username';

Update Query:

  -- Update email addresses for patron-type users
  -- Excludes: staff, shadow, system
  -- Includes: patron, dcb, NULL (no type), or any other value
  UPDATE ${tenant_schema}.users
  SET jsonb = jsonb_set(
      jsonb,
      '{personal,email}',
      '"fse.hosting@ebsco.com"'
  )
  WHERE
      jsonb->'personal'->>'email' IS NOT NULL
      AND jsonb->'personal'->>'email' != ''
      AND (
          jsonb->>'type' IS NULL
          OR jsonb->>'type' NOT IN ('staff', 'shadow', 'system')
      );

STAGING_USERS table

Verification Query (Run First):

  -- Preview staging users that will have their email updated
  -- All staging users are patrons in self-registration process
  SELECT
      jsonb->>'id' AS staging_user_id,
      jsonb->>'externalSystemId' AS external_system_id,
      jsonb->>'status' AS status,
      jsonb->'generalInfo'->>'firstName' AS first_name,
      jsonb->'generalInfo'->>'lastName' AS last_name,
      jsonb->'contactInfo'->>'email' AS current_email
  FROM ${tenant_schema}.staging_users
  WHERE
      jsonb->'contactInfo'->>'email' IS NOT NULL
      AND jsonb->'contactInfo'->>'email' != ''
  ORDER BY jsonb->>'status';

Update Query:

  -- Update email addresses for all staging users
  -- All staging users are patrons in self-registration process
  UPDATE ${tenant_schema}.staging_users
  SET jsonb = jsonb_set(
      jsonb,
      '{contactInfo,email}',
      '"fse.hosting@ebsco.com"'
  )
  WHERE
      jsonb->'contactInfo'->>'email' IS NOT NULL
      AND jsonb->'contactInfo'->>'email' != '';