Gadget Oauth Tokens using old HTTP BaseUrl

[DarkSideMilk]

I am having a strange issue when approving gadgets between linked confluence and JIRA.

The TL:DR -
Oauth tokens at https://{baseurl}/plugins/servlet/oauth/users/access-tokens for jira and confluence are referencing old BaseURLs and cause linked gadgets to require repeated reauthorization. 

The long version

So I have Jira and Confluence on One server. They are hosted on separate NICS and IP addresses, but still the same server.

When I first set it up, it was one NIC and one IP address just different hostnames, those hostnames are now DNS aliases that redirect to the new SSL versions of the base url.

Originally I did mod_proxy through apache. But now I just run each application on tomcat through SSL utilizing authbind to allow them to run on port 443.

I updated the base urls many moons ago. The original ones were

Confluence: http://arrowdoc
JIRA: http://arrowproject

There were a few other names and port variations along the way to get to the current

Confluence: https://kb.mydomain.com
JIRA: https://arrowJira.mydomain.com

The base URL health checks all check out and all that. 

So now I'm wanting to play more with the gadgets for integrating jira and confluence together, especially for agile boards and sprint burndown reports in meeting notes.

I setup a subscription to each baseurl in the jira and confluence settings with the http link.

When I add a gadet to a dashboard or a gadget macro to a page I can look at the Urls before hitting the Login & Approve button and they are the correct URLS, so I login and approve and it does work, but it constantly requires re-approving on every refresh of the page. So I clicked the link on the login page to show current Oauth approvals. The urls to view that being

https://{baseurl}/plugins/servlet/oauth/users/access-tokens

That page on jira shows an access token referencing the baseURL on confluence of http://arrowdoc:8090 which was the original default baseurl from when I first installed it.
That page on confluence shows a token connecting to http://arrowhelpdesk:8888 which is a hostname that is an alias (via dns and a httpd/apache redirect) to go to the jira service desk, but that's on port 80, and it was never purposefully set to the Base URL. The 8888 port is a remnant of my transition to SSL when I tried to use two different ports on one ip address, but it didn't work as I wanted and I threw that idea out. So if that url ever was set to the baseURL in jira, it was for no more than maybe an hour.

I would also note that niether of those urls go anywhere anymore, both hostnames are dns aliases that will redirect to the proper base url when hit on port 80, but the ports the oauth tokens reference are not in use any more, which I figure is why the authentication doesn't stick.

I have tried deleting and recreating the application links. And I've gone through and removed any settings that contain the old urls in any way. I've also revoked all the oauth tokens and started fresh to no avail. I've also on occasion seen references to the old arrowproject base url, only its on https which the ssl cert does allow, so that url loads jira but with the wrong baseurl and therefore errors, I believe fixing that in the application manager links has fixed that, but it may be related to this issue. Any guidance would be greatly appreciated.

Thanks,

-JJ

p.s. In case it's need to be known they are running on a CentOS 7 server and all applications are the latest versions as of yesterday.
Confluence - 6.4.0
Jira - 7.5.0
Jira Software -7.5.0
Jira Service Desk - 3.8.1
Java - Oracle JDK 1.8.0.144

[DarkSideMilk · Accepted solution]

The sql queries from the workarounds that I tried, without pictures this time, thank you for forwarding them to me @Ann Worley I really didn't want to rewrite all of this
So that Bug report workaround was a lot more help and I am a lot closer to a working solution. Hadn't thought about how I had changed the Install/Site titles for both applications in their General Settings pages.
So what I did...

Confluence DB via psql commandline

Updated the name and description properties of the bandanavalue field in the bandana table in confluence. 
It's found with this select statement

SELECT * FROM bandana WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';

Updated with these commands that replace the strings within the value

#replacing the name string
UPDATE bandana SET bandanavalue = replace(bandanavalue,'<property name="name" value="Confluence"/>','<property name="name" value="NewName"/>') WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';#replacing the description
UPDATE bandana SET bandanavalue = replace(bandanavalue,'<property name="description" value="Atlassian Confluence at http://oldUrl:8090"/>','<property name="description" value="Atlassian Confluence at https://newUrl"/>') WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';

JIRA DB via psql command line

Updated the description and consumername fields in the oauthconsumer table to be the new jira name and url and updated the oauthspconsumer table to be the new confluence name and URL.

They are viewed with these select commands

#View JIRA oauth consumer information
select * from oauthconsumer;

#View Confluence Oauth consumer information
select * from oauthspconsumer;

#I updated them like this

#update Jira Oauth consumer information
  #the consumername
UPDATE oauthconsumer SET consumername='newJiraName';
  #the description
UPDATE oauthconsumer SET description=replace(description,'http://oldJIRAurl','https://newJIRAUrl');

#update Confluence Oauth consumer information
  #the consumername
UPDATE oauthspconsumer SET consumername='newConfluenceName';
  #the description
UPDATE oauthspconsumer SET description=replace(description,'http://oldConfluenceUrl','https://newConfluenceUrl');

Application Link Reset

Delete and Recreate Application Links
Deleted the Application Link for confluence in JIRA
Deleted the link for JIRA in cofluence
Made the link to confluence from JIRA and followed the wizard to make the matching one.
The automatic method used oauth with impersonation

The Result

 So now when I make a gadget macro in confluence and view the tokens page in JIRA, it has the correct information Yay!
However when I link a confluence gadget in a dashboard and view the oauth links on the confluence side it now says that its linking to itself rather than JIRA. i.e. the description shows the confluence description when it should show the jira description.
Tried recrating application link from just confluence and also recreating space integration links in confluence and the jira service desk KB link. No change.
Tried searching for strings in the bandana confluence table that matched the jira db oauthconsumer table fields to see if it was stored somewhere in there, but this only confirmed that they were being read from the jira db. Unless the one I changed in confluence db is supposed to be for jira rather than confluence information.

I have not yet tried restarting the applications. They will restart during the nightly backup outside of production hours, so maybe it will magically fix itself overnight, but since half of it is working without restarting everything, the chances seem low, but who knows.

Can you confirm my understanding of which database entries are for which applications oauth information? i.e.
* the confluence database bandana entry should have the confluence oauth information 
* the jira oauthconsumer table has the JIRA oauth information
* the jira oauthspconsumer table has the Confluence oauth information
Since Jira has both, I feel like it would make sense for confluence to have both. Or perhaps it would make more sense if the confluence entry had the JIRA oauth information?
Also, confluence uses JIRA for authentication (JIRA mainly uses a AD LDAP directory link) in case that information is needed 

[DarkSideMilk]

Because I like to live dangerously here are the screenshots again

Working Confluence to Jira token for jira gadget in confluence

[DarkSideMilk]

Now the working screenshot repeated itself, well the broken one is still in a comment above

[AnnWorley]

I am going to try and post some images - I hope it works.

The confluence database bandana entry should have the confluence oauth information:

The jira oauthconsumer table has the JIRA oauth information:

The jira oauthspconsumer table has the Confluence oauth information:

[AnnWorley]

So...everything I typed after the last image was deleted and the last image was duplicated.

Here is what I tried to post:

I am still trying to reproduce the issue on my test instance. I do have the old base URLs in the database and in the UI when I view the tokens (I changed both base URLS, then revoked and recreated the tokens, deleted and recreated applinks and gadget feed) but my gadgets do not require me to login and approve every time so I am not sure we are on the right track.
Changes manually made to the database do not usually go into effect until a restart so I look forward to hearing whether that helps.
I hope you backed up the database before updating it, in case the applications have trouble after the changes.

[DarkSideMilk]

So I came in this morning and my sites are using self signed ssl certs all of a sudden?

I didn't do anything to that configuration, could messing with the oauth db entries have done anything to the cert configuration?

[DarkSideMilk]

Actually, no they're not, it's worse, they didn't actually start. The inseucre cert page just went to the default apache page, as if tomcat wasn't even running. That or apache stole port 443 because it got updated

Investigating further

[DarkSideMilk]

Nevermind, false alarm, apache just had a yum update so the default ssl conf file came back and apache tried to steal port 443 from tomcat. It's a downside to using authbind to make tomcat run on 443 for jira and confluence and then still using apache on port 80 for making easy shortcut urls without having to restart tomcat.

Going to test the connections again.

[DarkSideMilk]

Ok so the behavior is a bit different now. I am still getting the wrong url in the token description for links from jira to confluence, but there are some good things, let me explain...

JIRA gadgets in Conflunece

This I believe is working as expected.

Whenever I add a gadget I haven't added to any other page I get the Login & Approve button. 

If I add a JIRA gadget that I have previously added to any confluence page I can configure it to the already linked jira without any additional steps.

If I go look at the token descriptions, they all have the proper urls.

Confluence gadgets on JIRA Dashboards

So perhaps these are supposed to work differently, but based on the way the previous linking works, when I add a confluence gadget to a JIRA dashboard that I have previously added to any other JIRA dashboard, it shouldn't ask me for a login.

Granted, I did just test whether or not reauthentication was prompted for viewing the same instance of a gadget and that has been fixed. As in, I set up a confluence page gadget on a dashboard and then went to a different computer and viewed that dashboard and I was able to view it without it asking me to login again. 

However, if I try to add another confluence page gadget on the same or a different dashboard, it prompts me to login & approve the gadget again. i.e. If I already approved the quickNav gadget on a dashboard once, I get prompted for approval again if add another instance of the quick nav gadget to the same or a different dashboard. And then duplicate tokens are on the confluence authorized applications page for that gadget.

If I understand the way the gadget/token authentication should be working correctly, then there should be one oauth token per gadget. When I add confluence gadgets in JIRA its asking for a new token for every instance of any gadget. 

TL:DR

JIRA gadgets in confluence pages work exactly as they should now, yay! Thank you!  

Confluence gadgets in JIRA dashboards are sort of working, it no longer re-requests access every time its viewed once a gadget instance has been allowed. There are however still 2 minor issues occurring. 

- The token descriptions for confluence gadgets linked to JIRA dashboards have the icon, name, and url of confluence instead of JIRA.

- When linking confluence gadgets on a JIRA dashboard it asks for a new token/approval for every instance of a gadget rather than the expected token per type of gadget. 

[DarkSideMilk]

So is this as solved as I am gonna get it? Or is there perhaps more that can be done to make the confluence side tokens appear correctly?

[DarkSideMilk]

I accepted my outline of the commands I got from the referenced workarounds as the solution for now. Though I still hope for further help on the confluence side tokens when linking confluence gadgets on jira dashboards

[AnnWorley]

Thanks for following up!

Sorry for the delayed response. I am getting help from some colleagues and we will update you when we have more troubleshooting steps.

[AnnWorley]

@DarkSideMilk You will be getting a notification from our portal shortly, as well as a request for logs.

I wanted to let you know that issue with the forum posting duplicate images and dropping parts of posts has been fixed, so we shouldn't experience that trouble again.

[DarkSideMilk]

Shoot now my beautiful comment with everything I did disappeared, is there a way to revert comments on the admin end?

[AnnWorley]

I am not sure we can recover your comment. I just emailed you the notice I got when you posted the SQL queries earlier, in the hope that you will post them again.

I am very sorry about the duplicated images and posts disappearing - I have reported the issues to the team responsible for the forum. It has been happening to me as well, so I understand the frustration.

[AnnWorley]

Thanks for all the details, it's good to know you already checked the basics like the application links and gadget feeds, so we won't repeat the same steps.

It looks like you are running into this bug: OAuth Consumer description does not change based on Base URL

I added your post to the bug in a private comment, and re-opened it because it had been closed as pertaining to an old version of Confluence. If you are interested in the fix, just comment or vote on the bug report to be added to notifications.

There is a workaround on the bug report. Updating the URL in the JIRA and Confluence databases should resolve the gadget issue. I tested the select statements on the workaround to make sure the description field still contains the URL you would want to update.

Another approach would be to abandon OAuth tokens and use OAuth with impersonation as described in OAuth security for application links. Impersonating authentication makes requests on behalf of the user who is currently logged in, so it's a good solution if people use the same account in both Confluence and Jira.

I look forward to hearing how you decide to proceed.

[DarkSideMilk]

Thank you for the speedy reply.

I'm going to check out the work arounds and give them a try and report back. 

I do currently have Oauth with impersonation on my application links if that makes any difference.

[DarkSideMilk]

I gave it a try but changing the descriptions in the database didn't fix the issue.

Also this bug only covers the baseurl for confluence being wrong in the token. I wasn't able to see the old references to the jira baseurl in the confluence or jira database. Perhaps they're in different tables?

[AnnWorley]

It looks like Confluence reads the Jira URL from the Jira database. This bug report has a query to change the Jira URL in the Jira database: JIRA OAuth Consumer Description can't be edited

It refers to Stash but applies to any Atlassian application links.

The strategy is to change the Jira URL in the Jira database, then recreate the Application link, which should cause Confluence to pick up the new URL from Jira.

I look forward to hearing whether this procedure works for you.

[DarkSideMilk]

I did not try changing the name (realized I did change the name of the installation technically) in the key nor recreating the application link, just tried to recreate the token link by relinking a new gadget.

I will check the oauth related tables in jira again for one that has the old jira url in it.

[DarkSideMilk]

So that Bug report workaround was a lot more help and I am a lot closer to a working solution. Hadn't thought about how I had changed the Install/Site titles for both applications in their General Settings pages.

So what I did...

Confluence DB via psql commandline

Updated the name and description properties of the bandanavalue field in the bandana table in confluence. 

It's found with this select statement

SELECT * FROM bandana WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';

Updated with these commands that replace the strings within the value

#replacing the name string
UPDATE bandana SET bandanavalue = replace(bandanavalue,'<property name="name" value="Confluence"/>','<property name="name" value="NewName"/>') WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';

#replacing the description
UPDATE bandana SET bandanavalue = replace(bandanavalue,'<property name="description" value="Atlassian Confluence at http://oldUrl:8090"/>','<property name="description" value="Atlassian Confluence at https://newUrl"/>') WHERE BANDANAKEY LIKE 'com.atlassian.oauth.consumer.ConsumerService:host.__HOST_SERVICE__';

JIRA DB via psql command line

Updated the description and consumername fields in the oauthconsumer table to be the new jira name and url and updated the oauthspconsumer table to be the new confluence name and URL.

They are viewed with these select commands

#View JIRA oauth consumer information
select * from oauthconsumer;

#View Confluence Oauth consumer information
select * from oauthspconsumer;

I updated them like this

#update Jira Oauth consumer information
  #the consumername
UPDATE oauthconsumer SET consumername='newJiraName';
  #the description
UPDATE oauthconsumer SET description=replace(description,'http://oldJIRAurl','https://newJIRAUrl');

#update Confluence Oauth consumer information
  #the consumername
UPDATE oauthspconsumer SET consumername='newConfluenceName';
  #the description
UPDATE oauthspconsumer SET description=replace(description,'http://oldConfluenceUrl','https://newConfluenceUrl');

Delete and Recreate Application Links

Deleted the Application Link for confluence in JIRA

Deleted the link for JIRA in cofluence

Made the link to confluence from JIRA and followed the wizard to make the matching one.

The automatic method used oauth with impersonation

The Result

 So now when I make a gadget macro in confluence and view the tokens page in JIRA, it has the correct information Yay!

However when I link a confluence gadget in a dashboard and view the oauth links on the confluence side it now says that its linking to itself rather than JIRA. i.e. the description shows the confluence description when it should show the jira description.

[DarkSideMilk]

The bottom part of my last comment is getting cut off. Trying to post it again, this is what should come after the picture that is repeating itself...

[DarkSideMilk]

The bottom part of my last comment is getting cut off. Trying to post it again, this is what should come after the picture that is repeating itself...