Vireo 3 to 4 migration script to map person.institutionalidentifier to user.netid

[wwelling]

Here is a query to get all persons without UIN in Vireo 3 DB.

SELECT currentemailaddress as email, firstname, lastname, netid as identifier FROM person WHERE institutionalidentifier IS NULL

[wwelling]

Here is a node.js script to update Vireo 4 weaver_users netid to be institutionalidentifier (UIN) from Vireo 3. If UIN not available, it will use netid (unique hash) or email.

// npm install pg
// mkdir logs
// node index.js > logs/index.log
const { Client } = require('pg');

const vireo4db = {
  user: process.env.VIREO4_DB_USER || 'vireo',
  host: process.env.VIREO4_DB_HOST || 'localhost',
  database: process.env.VIREO4_DB_NAME || 'vireo',
  password: process.env.VIREO4_DB_PASSWORD || 'vireo',
  port: process.env.VIREO4_DB_PORT || 5432,
};

const vireo3db = {
  user: process.env.VIREO3_DB_USER || 'etduser',
  host: process.env.VIREO3_DB_HOST || 'localhost',
  database: process.env.VIREO3_DB_NAME || 'vireo',
  password: process.env.VIREO3_DB_PASSWORD || 'etduser',
  port: process.env.VIREO3_DB_PORT || 5433,
};

async function queryDatabase(config, query) {
  const client = new Client(config);

  try {
    await client.connect();
    const res = await client.query(query);
    return res.rows;
  } catch (err) {
    console.log('Error querying database:', err);
  } finally {
    await client.end();
  }
}

async function updateDatabase(config, query, values) {
  const client = new Client(config);

  try {
    await client.connect();
    const res = await client.query(query, values);
    return res.rowCount;
  } catch (err) {
    console.log('Error updating database:', err);
  } finally {
    await client.end();
  }
}

async function main() {
  const selectQuery = 'SELECT coalesce(institutionalidentifier, netid, email) AS primary, coalesce(netid, email) AS secondary FROM person';
  const updateQuery = 'UPDATE weaver_users SET netid = $1 WHERE netid = $2 OR email = $2';

  try {
    for (const person of await queryDatabase(vireo3db, selectQuery)) {
      const rowsUpdated = await updateDatabase(vireo4db, updateQuery, [person.primary, person.secondary]);
      if (rowsUpdated) {
        console.log(`SUCCCESS: UPDATE weaver_users SET netid = ${person.primary} WHERE netid = ${person.secondary} OR email = ${person.secondary}`);
      } else {
        console.log(`FAILURE: UPDATE weaver_users SET netid = ${person.primary} WHERE netid = ${person.secondary} OR email = ${person.secondary}`);
      }
    }
  } catch (err) {
    console.log('Error during main execution:', err);
  }
}

main();

[wwelling]

A request ticket has been sent to the ID office to see if we can collect missing UIN for persons Vireo 3 that do not have one. The ticket number is INC2267838.

[wwelling]

Here is a node.js script to request individual record from directory search ID office provides to retrieve UIN to update netid. However, we will have to get elevated permissions to get UIN in the response. If elevated permissions do not provide UIN in the responses for lookup by UID then not sure there is anything left to do for this issue.

https://docs.security.tamu.edu/docs/identity-security/attribute-services/api/directory_search/

// provide client identifier and shared secret
// npm install pg axios crypto
// mkdir logs
// node uin.js > logs/uin.log
const { Client } = require('pg');
const axios = require('axios');
const crypto = require('crypto');

const vireo4db = {
  user: process.env.VIREO4_DB_USER || 'vireo',
  host: process.env.VIREO4_DB_HOST || 'localhost',
  database: process.env.VIREO4_DB_NAME || 'vireo',
  password: process.env.VIREO4_DB_PASSWORD || 'vireo',
  port: process.env.VIREO4_DB_PORT || 5432,
};

const directoryClient = {
  identifier: process.env.CLIENT_IDENTIFIER || '',
  sharedSecret: process.env.CLIENT_SHARED_SECRET || '',
  throttleEvery: process.env.CLIENT_THROTTLE_EVERY || 10,
  throttleInMilliseconds: process.env.CLIENT_THROTTLE_IN_MILLISECONDS || 2000
}

async function queryDatabase(config, query) {
  const client = new Client(config);

  try {
    await client.connect();
    const res = await client.query(query);
    return res.rows;
  } catch (err) {
    console.log('Error querying database:', err);
  } finally {
    await client.end();
  }
}

async function updateDatabase(config, query, values) {
  const client = new Client(config);

  try {
    await client.connect(); 
    const res = await client.query(query, values);
    return res.rowCount;
  } catch (err) {
    console.log('Error updating database:', err);
  } finally {
    await client.end();
  }
}

async function getPerson(netid) {
  const api = netid.length === 32 ? 'uid' : 'netid';
  const requestUri = `/rest/directory/${api}/${netid}/json/`;
  const baseUrl = 'https://mqs.tamu.edu';
  const url = baseUrl + requestUri;
  const date = new Date().toUTCString();

  const authenticationString = `${requestUri}\n${date}\n${directoryClient.identifier}`;

  const signature = crypto
    .createHmac('sha256', directoryClient.sharedSecret)
    .update(authenticationString)
    .digest('base64');

  const authorizationHeader = `TAM ${directoryClient.identifier}:${signature}`;

  try {
    console.log(url);
    const response = await axios.get(url, {
      headers: {
        'Authorization': authorizationHeader,
        'Date': date
      }
    });
    return response.data;
  } catch (error) {
    console.log('Error making request:', error);
  }
}

async function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

async function main() {
  const selectQuery = 'SELECT netid FROM weaver_users WHERE length(netid) != 9 AND netid !~ \'^\\d+$\'';
  const updateQuery = 'UPDATE weaver_users SET netid = $1 WHERE netid = $2';

  try {
    let i = 0;
    for (const user of await queryDatabase(vireo4db, selectQuery)) {
      console.log(user);
      if (user.netid.indexOf('@') >= 0) {
        continue;
      }
      const person = await getPerson(user.netid);
      console.log(person);
      if (person.tamuEduPersonUIN && person.tamuEduPersonUIN.length > 0) {
        const uin = person.tamuEduPersonUIN[0]
        const rowsUpdated = await updateDatabase(vireo4db, updateQuery, [uin, user.netid]);
        if (rowsUpdated) {
          console.log(`SUCCCESS: UPDATE weaver_users SET netid = ${uin} WHERE netid = ${user.netid}`);
        } else {
          console.log(`FAILURE: UPDATE weaver_users SET netid = ${uin} WHERE netid = ${user.netid}`);
        }
      }
      i++;
      if (i % directoryClient.throttleEvery == 0) {
        await sleep(directoryClient.throttleInMilliseconds);
      }
    }
  } catch (err) {
    console.log('Error during main execution:', err);
  }
}

main();