Redmine

turns out not to be too complicated, the SQL would look something like:

SELECT *
FROM   "issues" 
LEFT OUTER JOIN "issue_statuses" 
  ON "issue_statuses".id = "issues".status_id
LEFT OUTER JOIN "projects" 
  ON "projects".id = "issues".project_id
WHERE 
       ( 1 = 0
           OR projects.is_public = 't'
           OR projects.id IN ( 5, 6, 7 )
        )

where 5,6,7 are the projects the user has the 'view_issues' permission.

And here is my Python code which uses SQLAlchemy queries:

def apply_user_filter_to_query(user_id, q):
    rmuser = db.sess.query(RmUser).get(user_id)
    if not rmuser:
        raise ValueError('user_id %s not found' % user_id)

    # if the user is an administrative user, no filter is applied
    if not rmuser.admin:
        proj_ids = user_projects_with_view_issue_perms(user_id)
        return q.where(or_(
            text( '1 = 0'), 
            projects.c.is_public == True,
            projects.c.id.in_(proj_ids)
        ))
    return q

def user_projects_with_view_issue_perms(user_id):
    sql = """ 
        select p.id
        from projects p
        inner join members m
        on m.project_id = p.id
        inner join member_roles mr
        on mr.member_id = m.id
        inner join roles r
        on r.id = mr.role_id
        where m.user_id = :user_id and r.permissions like '%\:view_issues%'
    """ 
    result = db.sess.execute(sql, {'user_id': user_id})
    return [row['id'] for row in result]

def issues_list_users(user_id, fusers, fstatuses, is_grouped):
    issue_select = apply_user_filter_to_query(user_id, iss_select_all)
    ...
user_issues = issues_list_users(session_user.id, ...)