How do you list the users and permissions for all projects?

[Dan Reid]

This article is quite handy, but the query it provides will return only repositories that have their own unique assigned users (permissions). But we have a lot of repos that inherit permissions from the project they are in, and those repos won't show in the query from the article. 

Does anyone have suggestions for a SQL query that will return all users of all projects, by project?

[Amirsina Haftlangi]

The following SQL query provides a list of projects and repositories with their respective users alongside user permission in Bitbucket. This query has been tested with Microsoft SQL Server.

WITH project_users AS (
SELECT DISTINCT p.id                                       AS "Project ID",
                p.name                                     AS "Project Name",
                r.name                                     AS "Repo Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   AS "Fullname (Individual)",
                pp.perm_id                                 AS "Permission Type"
FROM   dbo.project p
       JOIN dbo.sta_normal_project np
        ON np.project_id = p.id
       LEFT JOIN dbo.sta_project_permission pp
              ON pp.project_id = p.id
       LEFT JOIN dbo.repository r
              ON r.project_id = p.id
    LEFT JOIN dbo.sta_repo_permission srp
              ON srp.repo_id = r.id
       LEFT JOIN dbo.sta_normal_user u
              ON srp.user_id = u.user_id
       LEFT JOIN dbo.cwd_user cu
              ON cu.lower_user_name = u.name
 
WHERE  srp.group_name IS NOT NULL  OR srp.user_id IS NOT NULL
 
UNION
SELECT DISTINCT p.id                                       AS "Project ID",
                p.name                                     AS "Project Name",
                ''                                         AS "Repo Name",
                cu.id                                      AS "User ID (Individual)",
                cu.user_name                               AS "Username (Individual)",
                Concat(cu.first_name, ' ', cu.last_name)   AS "Fullname (Individual)",
                pp.perm_id                                 AS "Permission Type"
FROM   dbo.project p
       JOIN dbo.sta_normal_project np
         ON np.project_id = p.id
       LEFT JOIN dbo.sta_project_permission pp
              ON pp.project_id = p.id
       LEFT JOIN dbo.sta_normal_user u
              ON pp.user_id = u.user_id
       LEFT JOIN dbo.cwd_user cu
              ON cu.lower_user_name = u.name
WHERE  pp.group_name IS NOT NULL  OR pp.user_id IS NOT NULL
)
 
SELECT * FROM project_users
ORDER  BY [Project ID] ;

 Permission type:

PROJECT_VIEW=10
REPO_READ=0
REPO_WRITE=1
REPO_ADMIN=8
PROJECT_READ=2
PROJECT_WRITE=3
PROJECT_ADMIN=4
LICENSED_USER=9
PROJECT_CREATE=5
ADMIN=6
SYS_ADMIN=7

[Alok Dubey]

Do we have a similar query for POSTGRESQL? Thank you. 

[Vinay Srinivasan]

On your psql console you can execute this

SELECT DISTINCT p.id AS \"Project ID\",p.name AS \"Project Name\",r.name AS \"Repo Name\",cu.id AS \"User ID (Individual)\",cu.user_name AS \"Username (Individual)\",Concat(cu.first_name, ' ', cu.last_name) AS \"Fullname (Individual)\",pp.perm_id AS \"Permission Type\" FROM project p JOIN sta_normal_project np ON np.project_id = p.id LEFT JOIN sta_project_permission pp ON pp.project_id = p.id LEFT JOIN repository r ON r.project_id = p.id LEFT JOIN sta_repo_permission srp ON srp.repo_id = r.id LEFT JOIN sta_normal_user u ON srp.user_id = u.user_id LEFT JOIN cwd_user cu ON cu.lower_user_name = u.name WHERE srp.group_name IS NOT NULL OR srp.user_id IS NOT NULL UNION SELECT DISTINCT p.id AS \"Project ID\", p.name AS \"Project Name\", '' AS \"Repo Name\", cu.id AS \"User ID (Individual)\",cu.user_name AS \"Username (Individual)\",Concat(cu.first_name, ' ', cu.last_name) AS \"Fullname (Individual)\", pp.perm_id AS \"Permission Type\" FROM project p JOIN sta_normal_project np ON np.project_id = p.id LEFT JOIN sta_project_permission pp ON pp.project_id = p.id LEFT JOIN sta_normal_user u ON pp.user_id = u.user_id LEFT JOIN cwd_user cu ON cu.lower_user_name = u.name WHERE pp.group_name IS NOT NULL OR pp.user_id IS NOT NULL