API to set Portfolio Plan Permissions

[Radek Dostál]

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

[Radek Dostál]

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