Why do project leads show up as IDxxxxxx in postgreSQL project table

Richard Bone

I'm trying to get a list of all my project leads by querying my postgreSQL database directly. However, when I run either of the following queries, I only get a few projects in the response; the remaining project leads display as IDxxxxx when I select all records from the project table.

SELECT DISTINCT p.pkey,p.LEAD,u.display_name
FROM project p
INNER JOIN cwd_user u
on p.lead = u.user_name
GROUP BY p.pkey,p.LEAD,u.display_name
ORDER BY p.pkey DESC

select p.pkey as Proj_key, p.LEAD as Project_Lead, cu.email_address as Proj_Lead_Email, pc.cname as Proj_Cat
from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN cwd_user cu ON cu.user_name = p.LEAD
where na.SINK_NODE_ENTITY ='ProjectCategory'

Thanks in advance for your help!

Andy Heinzer

Hi Richard,

I see that you are using Jira Server and looking to find all the project leads using a SQL query.   The problem with this approach is in part due to to a change recently in Jira server in regards to user_key values when accounts are created and in part to using the wrong field when trying to match up these users.  I believe this change was just implemented in Jira 8.4, there is some more background about it in GDPR changes in Jira: Jira user keys .

Back in 6.0, Jira server introduced the ability to rename user accounts.  When this happened, Jira added a new table called app_user.  This has just two fields, user_key and lower_user_name.  The user_key value is expected to never change in Jira, even if the account is renamed.  Initially Jira has been just selecting the first username as the userkey.  This made sense as a means to identify the user, and so many places in Jira that contain user_names are actually the user_key that just happens to have the same value.  So your query might have worked in the past, but it only would have returned accounts accurately that have not been renamed.  The lower_user_name field in this table will always change to correspond to the current value in cwd_user with the same field name.

Starting with Jira 8.4 when an account is first created in Jira, the user_key value that is generated is no longer using the accounts first username, likely because this value is a real pain to change in Jira, and GDPR regulations could consider it a personally identifying field.  Which means if this user files some kind of GDPR request you'd be required to alter the value here, something Jira should be avoiding now.   Which is why Jira Server has started switching to use a JIRAUSER10000, JIRAUSER10001, etc when creating accounts for the first time in newer versions.

That said I think you can tweak your query here.  Instead of trying to match directly to the cwd_user table, you'll need to first match that project lead field to the app_user.user_key field.  From there we can then find the usernames and display names, like so:

SELECT DISTINCT p.pkey,p.LEAD,au.lower_user_name,cu.display_name
FROM project p
INNER JOIN app_user au on p.lead = au.user_key
JOIN cwd_user cu on cu.lower_user_name=au.lower_user_name
GROUP BY p.pkey,p.LEAD,au.lower_user_name,cu.display_name
ORDER BY p.pkey DESC

and for the second query, same idea,

select p.pkey as Proj_key, au.lower_user_name as Project_Lead, cu.email_address as Proj_Lead_Email, pc.cname as Proj_Cat
from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN app_user au ON au.user_key = p.LEAD
JOIN cwd_user cu ON cu.lower_user_name = au.lower_user_name
where na.SINK_NODE_ENTITY ='ProjectCategory'

That should still be able to give you the user account information I think you are looking for here via SQL.

Cheers,

Andy