Developer Tip: Querying Permissions in Postgres

If you are having trouble grasping how various restrictTo annotations work, it might be helpful to play around with queries against the permissions tables to get a clearer picture of how we determine what actions a user can take on a particular object. Here's an example:

Permissions query

CODE

SELECT
    m.read_element,
    m.edit_element,
    m.delete_element
FROM
    xdat_field_mapping m
    LEFT JOIN xdat_field_mapping_set s ON m.xdat_field_mapping_set_xdat_field_mapping_set_id = s.xdat_field_mapping_set_id
    LEFT JOIN xdat_element_access a ON s.permissions_allow_set_xdat_elem_xdat_element_access_id = a.xdat_element_access_id
    LEFT JOIN xdat_usergroup g ON a.xdat_usergroup_xdat_usergroup_id = g.xdat_usergroup_id
    LEFT JOIN xdat_user_groupid i ON g.id = i.groupid
    LEFT JOIN xdat_user u ON i.groups_groupid_xdat_user_xdat_user_id = u.xdat_user_id
WHERE
    m.field = 'xnat:projectData/ID' AND
    m.field_value IN (:project, '*') AND
    u.login = :username;

Each table between the user and field mapping connects to permissions:

The user is the user, obviously
xdat_user_groupid joins the user to groups
xdat_usergroup defines the user groups, so with that join you have all of the groups to which the user belongs
xdat_element_access connects each security principal (which, in modern XNAT, means groups: xdat_element_access can also connect to users and does on CNDA and Central, but those are the effect of having been around a long time) to each secured data type. Effectively this means that you'll have as many rows in this table as you have rows in xdat_usergroup times the number of rows in xdat_element_security where secure = 1 (which is where the list of secured data types comes from).
xdat_field_mapping_set is basically nothing but a join between xdat_element_access (there should always be the same number of sets as there are element access entries) and xdat_field_mapping (there's also a method column, but I'm not quite clear what that does)
xdat_field_mapping includes an XFT field and a value for that field. This has a many-to-one relationship with xdat_field_mapping_set and xdat_element_access, but the number of entries per set is always one or two:
- If the corresponding xdat_element_access entry is xnat:projectData, there's only one field mapping entry with the field set to xnat:projectData/ID
- For all other data types, there are two field mapping entries, one for the experiment's project field, the other for the shared experiment project field, so, e.g. for xnat:mrSessionData you'd have xnat:mrSessionData/project and xnat:mrSessionData/sharing/share/project
- The field_value for each field mapping entry indicates the project to which that applies, with the exception being "*", which matches all projects

A nice illustration of how permissions resolve for group is this query:

Group access to project

CODE

SELECT
    g.id,
    m.field,
    m.read_element,
    m.edit_element,
    m.delete_element
FROM
    xdat_field_mapping m
    LEFT JOIN xdat_field_mapping_set s ON m.xdat_field_mapping_set_xdat_field_mapping_set_id = s.xdat_field_mapping_set_id
    LEFT JOIN xdat_element_access a ON s.permissions_allow_set_xdat_elem_xdat_element_access_id = a.xdat_element_access_id
    LEFT JOIN xdat_usergroup g ON a.xdat_usergroup_xdat_usergroup_id = g.xdat_usergroup_id
WHERE
    m.field_value = :project
ORDER BY
    g.id,
    m.field;

This gives results like this (I removed a bunch of secured data types so it's easier to read:

CODE

id                    field                                     read_element edit_element delete_element
XNAT_01_collaborator  xnat:mrSessionData/project                1            0            0
XNAT_01_collaborator  xnat:mrSessionData/sharing/share/project  1            0            0
XNAT_01_collaborator  xnat:petSessionData/project               1            0            0
XNAT_01_collaborator  xnat:petSessionData/sharing/share/project 1            0            0
XNAT_01_collaborator  xnat:projectData/ID                       1            0            0
XNAT_01_collaborator  xnat:subjectData/project                  1            0            0
XNAT_01_collaborator  xnat:subjectData/sharing/share/project    1            0            0
XNAT_01_member        xnat:mrSessionData/project                1            1            0
XNAT_01_member        xnat:mrSessionData/sharing/share/project  1            0            0
XNAT_01_member        xnat:petSessionData/project               1            1            0
XNAT_01_member        xnat:subjectData/project                  1            1            0
XNAT_01_member        xnat:subjectData/sharing/share/project    1            0            0
XNAT_01_owner         xnat:mrSessionData/project                1            1            1
XNAT_01_owner         xnat:mrSessionData/sharing/share/project  1            0            0
XNAT_01_owner         xnat:petSessionData/project               1            1            1
XNAT_01_owner         xnat:petSessionData/sharing/share/project 1            0            0
XNAT_01_owner         xnat:projectData/ID                       1            1            1
XNAT_01_owner         xnat:subjectData/project                  1            1            1
XNAT_01_owner         xnat:subjectData/sharing/share/project    1            0            0

And that's how project-based data-type-scoped permissions work in XNAT.