vikunja/pkg/models/project_access.go

// Vikunja is a to-do list application to facilitate your life.
// Copyright 2018-present Vikunja and contributors. All rights reserved.
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package models

import (
	"sort"
	"strconv"

	"code.vikunja.io/api/pkg/db"
	"code.vikunja.io/api/pkg/user"
	"code.vikunja.io/api/pkg/web"

	"xorm.io/builder"
	"xorm.io/xorm"
)

// projectAccess is every project a user can reach, with the effective permission.
// Absent id = no access.
type projectAccess struct {
	userID         int64
	includeTrashed bool
	permissions    map[int64]Permission
	sortedIDs      []int64
}

func (pa *projectAccess) permission(projectID int64) (Permission, bool) {
	p, has := pa.permissions[projectID]
	return p, has
}

// Binds the user id three times.
const projectAccessCTE = `
WITH grants (project_id, permission) AS (
    SELECT project_id, MAX(permission)
    FROM (
        SELECT id AS project_id, 2 AS permission FROM projects WHERE owner_id = ?
        UNION ALL
        SELECT project_id, permission FROM users_projects WHERE user_id = ?
        UNION ALL
        SELECT tp.project_id, tp.permission
        FROM team_projects tp
        INNER JOIN team_members tm ON tm.team_id = tp.team_id
        WHERE tm.user_id = ?
    ) direct_grants
    GROUP BY project_id
)`

// Trashed projects never have live descendants, so dropping trashed targets is enough.
const liveProjectTargetJoin = `
    INNER JOIN projects target ON target.id = pa.project_id AND target.deleted_at IS NULL`

// Inherited admin is sticky (it could unshare the parent anyway); below that the nearest grant wins.
func projectAccessSelect(includeTrashed bool) string {
	targetJoin := liveProjectTargetJoin
	if includeTrashed {
		targetJoin = ""
	}
	return `
SELECT id,
    CASE WHEN MAX(permission) = 2 THEN 2
         ELSE MAX(CASE WHEN nearest = 1 THEN permission END)
    END AS permission
FROM (
    SELECT pa.project_id AS id, g.permission,
        ROW_NUMBER() OVER (PARTITION BY pa.project_id ORDER BY pa.depth) AS nearest
    FROM grants g
    INNER JOIN project_ancestors pa ON pa.ancestor_id = g.project_id` + targetJoin + `
) ranked
GROUP BY id`
}

func projectAccessQuery(includeTrashed bool) string {
	return projectAccessCTE + projectAccessSelect(includeTrashed)
}

// Binds the user id three times, then the permission bounds.
func projectAccessMinPermissionIDsQuery(includeTrashed bool) string {
	return projectAccessCTE + `
SELECT id FROM (` + projectAccessSelect(includeTrashed) + `) effective
WHERE permission BETWEEN ? AND ?`
}

type projectAccessRow struct {
	ID         int64 `xorm:"id"`
	Permission int64 `xorm:"permission"`
}

// Resolves the whole reachable tree in one query, memoized per session. Trashed projects are left out.
func getProjectAccessForUser(s *xorm.Session, userID int64) (*projectAccess, error) {
	return getProjectAccess(s, userID, false)
}

// Only for trash operations, which act on projects nobody can otherwise reach.
func getProjectAccessIncludingTrash(s *xorm.Session, userID int64) (*projectAccess, error) {
	return getProjectAccess(s, userID, true)
}

func getProjectAccess(s *xorm.Session, userID int64, includeTrashed bool) (*projectAccess, error) {
	key := "project-access-" + strconv.FormatInt(userID, 10)
	if includeTrashed {
		key = "project-access-with-trash-" + strconv.FormatInt(userID, 10)
	}
	return db.Remember(s, key, func() (*projectAccess, error) {
		rows := []*projectAccessRow{}
		err := s.SQL(projectAccessQuery(includeTrashed), userID, userID, userID).Find(&rows)
		if err != nil {
			return nil, err
		}

		pa := &projectAccess{
			userID:         userID,
			includeTrashed: includeTrashed,
			permissions:    make(map[int64]Permission, len(rows)),
			sortedIDs:      make([]int64, 0, len(rows)),
		}
		for _, r := range rows {
			permission := Permission(r.Permission)
			// A grant outside the enum (manual db edit, restored dump) is no grant at all.
			if permission.isValid() != nil {
				continue
			}
			pa.permissions[r.ID] = permission
			pa.sortedIDs = append(pa.sortedIDs, r.ID)
		}
		sort.Slice(pa.sortedIDs, func(i, j int) bool { return pa.sortedIDs[i] < pa.sortedIDs[j] })
		return pa, nil
	})
}

// Past this many ids the inlined list defeats plan caching; the subquery keeps binds fixed.
const maxInListSize = 1000

func (pa *projectAccess) cond(column string, minPermission Permission) builder.Cond {
	// The empty slice matters: an argument-less builder.In is dropped by Where and
	// matches every row, while In with an empty slice renders as 0=1.
	ids := []int64{}
	for _, id := range pa.sortedIDs {
		if pa.permissions[id] >= minPermission {
			ids = append(ids, id)
		}
	}
	if len(ids) > maxInListSize {
		return pa.condViaSubquery(column, minPermission)
	}
	return builder.In(column, ids)
}

// The upper bound drops out-of-enum grants, like getProjectAccessForUser does.
func (pa *projectAccess) condViaSubquery(column string, minPermission Permission) builder.Cond {
	return builder.In(column, builder.Expr(projectAccessMinPermissionIDsQuery(pa.includeTrashed), pa.userID, pa.userID, pa.userID, int(minPermission), int(PermissionAdmin)))
}

// Includes projects inherited through a shared parent.
func accessibleProjectIDsCond(s *xorm.Session, a web.Auth, column string) (builder.Cond, error) {
	return projectIDsWithPermissionCond(s, a, column, PermissionRead)
}

func projectIDsWithPermissionCond(s *xorm.Session, a web.Auth, column string, minPermission Permission) (builder.Cond, error) {
	if share, ok := a.(*LinkSharing); ok {
		if share.Permission < minPermission {
			return builder.In(column, []int64{}), nil
		}
		return builder.Eq{column: share.ProjectID}, nil
	}

	u, err := user.GetFromAuth(a)
	if err != nil {
		return nil, err
	}

	access, err := getProjectAccessForUser(s, u.ID)
	if err != nil {
		return nil, err
	}
	return access.cond(column, minPermission), nil
}

// GetAllParentProjects returns the project itself and every ancestor, keyed by id.
func GetAllParentProjects(s *xorm.Session, projectID int64) (map[int64]*Project, error) {
	chain, err := db.Remember(s, "parent-projects-"+strconv.FormatInt(projectID, 10), func() (map[int64]*Project, error) {
		loaded := make(map[int64]*Project)
		err := s.
			Table("projects").
			Select("projects.*").
			Join("INNER", "project_ancestors", "project_ancestors.ancestor_id = projects.id").
			Where(builder.Eq{"project_ancestors.project_id": projectID}).
			Find(&loaded)
		if err != nil {
			return nil, err
		}
		return loaded, nil
	})
	if err != nil {
		return nil, err
	}

	out := make(map[int64]*Project, len(chain))
	for id, p := range chain {
		out[id] = p.memoCopy()
	}
	return out, nil
}