Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

API to set Portfolio Plan Permissions Edited

Yello,

 

JIRA Software, 8.5.3
JIRA Data Center, 8.5.3
PostgreSQL 9.6.15
Java 11 (Oracle)
Advanced Roadmaps (Portfolio), 3.24

 

My goal is to write a program/code/job that would automate Plan Permissions (Access Control).

The reason is that by default, new Plans are created and shared with all "Portfolio Users", as far as I'm aware this cannot be disabled. So I'm trying to do some analysis here and workaround a few things.

 

Long story short Portfolio API is horribly documented or outdated, last Javadoc I can find is for Portfolio 2.13 (latest, yeah sure), and the REST API is limited and does not contain any interfaces that would be handy here at all.

So I came to the conclusion I will need to include the DB to fetch the data I need, as well as probably use it to insert the data I need.

 

Short story shorter, I'm able to find all plans, and get their permission settings, so say I am currently processing a sample Plan "test", which through Portfolio API returns a PlanDTO object (https://docs.atlassian.com/portfolio-for-jira-server/javadoc/2.13.0/com/atlassian/rm/portfolio/publicapi/interfaces/plan/PlanDTO.html)

 

From this, all I can get is the Plan's ID, which I am using to read permissions from the db with:

def sqlGetPermissionsOfPlan = """
SELECT \"HOLDER_KEY\", \"HOLDER_TYPE\", \"PERMISSION\", \"PLAN_ID\"
FROM \"AO_D9132D_PERMISSIONS\"
WHERE \"PLAN_ID\" = ${planId}
"""

 

This will return me some strings which I format to needed classes to work with, etc., now I get to the 2 main use cases I'm trying to do:

 

Remove permissions:

DELETE FROM "AO_D9132D_PERMISSIONS" WHERE "PLAN_ID" = $planId AND "HOLDER_KEY" = 'jira-users'

 

This will remove all occurrences (either View or Edit) of the group 'jira-users' for the Portfolio Plan's permissions, everything seems to work like a charm, if it's the only group, portfolio gets set to public as expected, if it holds any other permission shares, those stay. I don't need to reset caches, refreshing the portfolio page in browser shows correct results, everything works.

 

Adding permissions:

INSERT INTO "AO_D9132D_PERMISSIONS"("HOLDER_KEY", "HOLDER_TYPE", "ID", "PERMISSION", "PLAN_ID") VALUES ($username, '0', (SELECT MAX("ID")+1 FROM "AO_D9132D_PERMISSIONS"), '0', $planId);

INSERT INTO "AO_D9132D_PERMISSIONS"("HOLDER_KEY", "HOLDER_TYPE", "ID", "PERMISSION", "PLAN_ID") VALUES ($groupName, '1', (SELECT MAX("ID")+1 FROM "AO_D9132D_PERMISSIONS"), '0', $planId);

 

Suffice to say, this also seems to work pretty well. I am able to run these without any apparent issues. I was trying to do sql logging to see if I can find what SQL portfolio will use to create new entries, but I couldn't find anything that I could copy from, so this 'MAX("ID")' is really just an attempt at trying to make this work.

 

Finally, to my question. I'm not a DBA nor exactly a seasoned developer (more of a personal projects type of things) and I must say that the entire API of Portfolio is crooked and uncomfortable to work with. Has anybody tried anything similar and do you have any hints which SQL queries I could use to make sure nothing breaks down the line?

 

I'm a little suspicious of one thing:

 - if I add a new permission for someone in a plan, the ID in the database will get to e.g. 850

 - if I remove it, and re-add it, it will increment and add it with ID 851

 - if I do manual INSERT then it will take the max+1 so it will add 850 first, then I remove it, and INSERT manually in db again, it will use 850 again, it will not use 851

 

I am not seeing any impact or problems yet from this, but I do not know if I might be missing something.

Additionally, I'm worried a bit whether I should take into account any differences between Server and Data Center (this code probably will only ever run on a server, but, keeping it as a possible candidate depending on requirements).

 

Can you think of any better way other than "MAX" function in PostgreSQL that I should use there?

I kind of thought of decompiling the plugin to see if I can find some better API in it and re-use it, or at minimum it could give me a hint which tables to aim for. Portfolio has plenty of tables so going and checking in each of them one by one to see what possible could correlate is on the list of things to do, but since it would take hours, figured I'd try asking here.

 

Looking forward to any hints,

 

Thanks,

Radek

 

1 answer

https://jira.atlassian.com/browse/JPOSERVER-286 this is what I'm trying to workaround, but considering the SQL, getting doubts..

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Advanced Roadmaps

Introducing Advanced Roadmap’s new dependency report

To see this feature in action check out our recent dependencies demo here: https://www.youtube.com/watch?v=eQu5VsUqyZA Keeping on top of your dependencies is a key part of ensuring project success....

119 views 2 6
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you