Change the color scheme via SQL or some other automation

[Jason Friedman]

From time-to-time we copy our PRD data down to a non-PRD instance. We then manually update the non-PRD color scheme so that we don't mistakenly work in the wrong environment.
We would like to be able to make this change via SQL or some other automation. We recognize this might be risky in a PRD environment, but we would be applying this change to non-PRD only.

This question applies to JIRA and Confluence.

[Nic Brough -Adaptavist- · Accepted solution]

Normally, I'd yell "don't touch the database", but a colour scheme modification done via SQL during the migration into a non-production system before you start the services is actually going to be fine. (Never adjust a database while an Atlassian product is running, that way lies madness, or rather screaming for backup tapes)

I don't know what the SQL is for Confluence off the top of my head, but I imagine it's similar.

For JIRA, have a look in propertyentry and propertyvalue. When you change the colour scheme away from the default, Jira adds one line to each of those. I was mucking with this the other day, and these four changes represent "change from default menu header blue to grey, then bright-kermit green". You can probably hack through the rest for yourself :-)

INSERT INTO PROPERTYENTRY VALUES(11508,'jira.properties',1,'jira.lf.top.bgcolour',5)

INSERT INTO PROPERTYSTRING VALUES(11508,'#aabbcc')

DELETE FROM PROPERTYSTRING WHERE ID=11508

INSERT INTO PROPERTYSTRING VALUES(11508,'#00ff00')

[Jason Friedman]

Any thoughts on how to disable outbound email programmatically?

[Nic Brough -Adaptavist-]

Think it's stuff in propertystring again, but I prefer to use command line switches

See the mail stuff on https://confluence.atlassian.com/display/JIRA/Restoring+Data

[Jason Friedman]

Ah, that's helpful, thank you. In the meantime I did this (required a restart):

NOW=$(python -c "import time; print(int(time.time() * 1000));")
echo "update propertynumber set propertyvalue = 1 where id = (select id from propertyentry where property_key = 'jira.mail.send.disabled');" | psql jira
echo "update propertystring set propertyvalue = 'sysadmin' where id = (select id from propertyentry where property_key = 'mailsetting.jira.mail.send.disabled.modifiedBy');" | psql jira
echo "update propertystring set propertyvalue = '$NOW' where id = (select id from propertyentry where property_key = 'mailsetting.jira.mail.send.disabled.modifiedDate');" | psql jira

[Roger Johannesen]

I'm trying to do the same thing, updating the bgcolor in the database, before starting up jira, after I have cloned a new dev env with my automated process. And this was very helpful.
Just one thing I need to know. The propertyID I assume is generated (because I see it is different on the systems I have tested this manually on) when the property is created once is is set the first time. And it will probably not be a good idea that I just create a ID value on my own. How do I know how to correctly creating this property in the database, with the correct ID for this system?

[Josep M. Cao]

Anyone knows which property is for confluence? i can't find it!!

[Azfar Masut]

You can perform this using API as well. Example as below.

##Header Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#003b48"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.bgcolour
##Header Highlight Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#708090"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.hilightcolour
##Header Separator Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://jira-staging.trifecta.com/rest/api/2/application-properties/jira.lf.top.separator.bgcolor
##Header Text Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.top.textcolour
##Menu Item Highlight Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#708090"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.menu.bgcolour
##Menu Item Highlight Text Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#ffffff"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.menu.textcolour
##Button Background Color
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "#c0c0c0"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.lf.hero.button.base.bg.colour
##Set Title
/usr/bin/curl -k -D- -u ADMINUSER:ADMINPASSWORD -X PUT --data '{"value" : "TESTING ENVIRONMENT"}' -H "Content-Type: application/json" https://TESTING.DOMAIN.COM/rest/api/2/application-properties/jira.title

For more properties, search for "jira.lf."  in the element tab in the developer tab for the page: http:/xxx/jira6411/secure/admin/LookAndFeel!default.jspa

[Jason Friedman]

Worked nicely for JIRA. Was able to reset the admin password, too:

ENCRYPTED_PASSWORD=$(python -c "if True:
    import sys
    from passlib.hash import atlassian_pbkdf2_sha1
    print(atlassian_pbkdf2_sha1.encrypt(sys.argv[1]))
" $SYSADMIN_PASSWORD)
echo "update cwd_user set credential = '$ENCRYPTED_PASSWORD' where user_name = 'sysadmin';" | psql jira