What’s “JAC of the Month Club”? Click here to find out!

Wonderful spoiler alert: This blog post will be hopefully be mostly obsolete by the time you read this. The JAC issue featured in this post (CWD-3388) was recently resolved to be release as Crowd 2.8.4. However, the release schedule of Crowd is not as aggressive as other Atlassian products like Stash, Jira or Confluence so if you need to mass update a list of user names right now or using the REST APIs does not appeal to you, keep reading. Let’s solve some Crowd administration problems today!

Steps to Rename a List of Users in Crowd

  1. Back up all databases: This whole exercise depends on you directly modifying Crowd’s backend database. You’ll notice all over AAC and Atlassian’s official documentation you should not take these direct DB manipulation operations lightly. On that note, we should back up Crowd’s and all connected apps’ databases. We’ll assume for this post you are using PostgreSQL so you will be doing something like this for each application:
    pg_dump <database name> > /path/to/backup_file.sql
    pg_dump crowddb > /tmp/crowddb.sql
    pg_dump jiradb > /tmp/jiradb.sql
    etc...
  2. Create a CSV dump of all the current Crowd users accounts: This method is a lifted from a combination of pages on Atlassian’s official docs site, AAC and other sources. In short, we are dumping all user records into a CSV file so we can further manipulate and report on the Crowd user list:
    copy cwd_user(id, user_name, lower_user_name, lower_email_address, active, lower_first_name, lower_last_name) to '/tmp/confusers.csv' delimiters',' CSV HEADER;
  3. Import the user list into Google Apps (as a spreadsheet): We highly encourage users to take the CSV file you just created and load it into Google Apps. From that point you can add an extra column for the new account name.
    Screenshot 2015-08-23 23.03.30
  4. Generate a SQL file with all the required updates. To generate the SQL statements you need to bulk change all the user names you’ll need to export out the spreadsheet back into CSV format. After you can run the CSV file through this bash script to generate the SQL you’ll need to feed into the Crowd DB:
    #!/bin/bash
    INPUT=newlist.csv
    OLDIFS=$IFS
    IFS=,
    [ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
    while read id user_name new_user_name lower_email_address active lower_first_name lower_last_name
    do
     echo "update cwd_user set user_name = '${new_user_name}', lower_user_name = '${new_user_name}' where user_name = '${user_name}';"
     echo "update cwd_membership set child_name = '${new_user_name}', lower_child_name = '${new_user_name}' where lower_child_name = '${user_name}';"
    done < $INPUT
    IFS=$OLDIFS
  5. Create the SQL update file: You need to run the command to create the SQL statements:
     bash merge.sh > output.sql

    Which will yield output like this:

    update cwd_user set user_name = 'james@asu.edu, lower_user_name = 'james@asu.edu' where user_name = 'jim@asu.edu';
     update cwd_membership set child_name = 'james@asu.edu', lower_child_name = 'james@asu.edu' where lower_child_name = 'jim@asu.edu';
  6. Make sure you are logged into all connected apps as an administrator.  You need to make sure you are logged in as an administrator in each connected apps and be ready to force a re-sync of Crowd:
    Screenshot 2015-08-23 23.32.45
  7. Make sure Crowd is shut down.  You need to make sure Crowd is not running as you execute these SQL updates.
  8. Load the SQL file into Crowd’s DB:
    psql crowddb < sql_user_updates.sql
    
  9. Restart the  Crowd service.
  10. Trigger the resync on each connected app:
    Screenshot 2015-08-23 23.32.45
  11. Test the login. You should be able to login as the new username now in each connected app.