mirror of
https://github.com/zitadel/zitadel.git
synced 2025-07-11 13:38:33 +00:00
658ca3606b
# Which Problems Are Solved Add the possibility to filter project resources based on project member roles. # How the Problems Are Solved Extend and refactor existing Pl/PgSQL functions to implement the following: - Solve O(n) complexity in returned resources IDs by returning a boolean filter for instance level permissions. - Individually permitted orgs are returned only if there was no instance permission - Individually permitted projects are returned only if there was no instance permission - Because of the multiple filter terms, use `INNER JOIN`s instead of `WHERE` clauses. # Additional Changes - system permission function no longer query the organization view and therefore can be `immutable`, giving big performance benefits for frequently reused system users. (like our hosted login in Zitadel cloud) - The permitted org and project functions are now defined as `stable` because the don't modify on-disk data. This might give a small performance gain - The Pl/PgSQL functions are now tested using Go unit tests. # Additional Context - Depends on https://github.com/zitadel/zitadel/pull/9677 - Part of https://github.com/zitadel/zitadel/issues/9188 - Closes https://github.com/zitadel/zitadel/issues/9190
79 lines
3.3 KiB
Go
79 lines
3.3 KiB
Go
package query
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
|
|
sq "github.com/Masterminds/squirrel"
|
|
|
|
"github.com/zitadel/zitadel/internal/api/authz"
|
|
"github.com/zitadel/zitadel/internal/domain"
|
|
)
|
|
|
|
// ExamplePermissionClause_org shows how to use the PermissionClause function to filter
|
|
// permitted records based on the resource owner and the user's instance or organization membership.
|
|
func ExamplePermissionClause_org() {
|
|
// These variables are typically set in the middleware of Zitadel.
|
|
// They do not influence the generation of the clause, just what
|
|
// the function does in Postgres.
|
|
ctx := authz.WithInstanceID(context.Background(), "instanceID")
|
|
ctx = authz.SetCtxData(ctx, authz.CtxData{
|
|
UserID: "userID",
|
|
})
|
|
|
|
join, args := PermissionClause(
|
|
ctx,
|
|
UserResourceOwnerCol, // match the resource owner column
|
|
domain.PermissionUserRead,
|
|
SingleOrgPermissionOption([]SearchQuery{
|
|
mustSearchQuery(NewUserDisplayNameSearchQuery("zitadel", TextContains)),
|
|
mustSearchQuery(NewUserResourceOwnerSearchQuery("orgID", TextEquals)),
|
|
}), // If the request had an orgID filter, it can be used to optimize the SQL function.
|
|
OwnedRowsPermissionOption(UserIDCol), // allow user to find themselves.
|
|
)
|
|
|
|
sql, _, _ := sq.Select("*").
|
|
From(userTable.identifier()).
|
|
JoinClause(join, args...).
|
|
Where(sq.Eq{
|
|
UserInstanceIDCol.identifier(): authz.GetInstance(ctx).InstanceID(),
|
|
}).ToSql()
|
|
fmt.Println(sql)
|
|
// Output:
|
|
// SELECT * FROM projections.users14 INNER JOIN eventstore.permitted_orgs(?, ?, ?, ?, ?) permissions ON (permissions.instance_permitted OR projections.users14.resource_owner = ANY(permissions.org_ids) OR projections.users14.id = ?) WHERE projections.users14.instance_id = ?
|
|
}
|
|
|
|
// ExamplePermissionClause_project shows how to use the PermissionClause function to filter
|
|
// permitted records based on the resource owner and the user's instance or organization membership.
|
|
// Additionally, it allows returning records based on the project ID and project membership.
|
|
func ExamplePermissionClause_project() {
|
|
// These variables are typically set in the middleware of Zitadel.
|
|
// They do not influence the generation of the clause, just what
|
|
// the function does in Postgres.
|
|
ctx := authz.WithInstanceID(context.Background(), "instanceID")
|
|
ctx = authz.SetCtxData(ctx, authz.CtxData{
|
|
UserID: "userID",
|
|
})
|
|
|
|
join, args := PermissionClause(
|
|
ctx,
|
|
ProjectColumnResourceOwner, // match the resource owner column
|
|
"project.read",
|
|
WithProjectsPermissionOption(ProjectColumnID),
|
|
SingleOrgPermissionOption([]SearchQuery{
|
|
mustSearchQuery(NewUserDisplayNameSearchQuery("zitadel", TextContains)),
|
|
mustSearchQuery(NewUserResourceOwnerSearchQuery("orgID", TextEquals)),
|
|
}), // If the request had an orgID filter, it can be used to optimize the SQL function.
|
|
)
|
|
|
|
sql, _, _ := sq.Select("*").
|
|
From(projectsTable.identifier()).
|
|
JoinClause(join, args...).
|
|
Where(sq.Eq{
|
|
ProjectColumnInstanceID.identifier(): authz.GetInstance(ctx).InstanceID(),
|
|
}).ToSql()
|
|
fmt.Println(sql)
|
|
// Output:
|
|
// SELECT * FROM projections.projects4 INNER JOIN eventstore.permitted_projects(?, ?, ?, ?, ?) permissions ON (permissions.instance_permitted OR projections.projects4.resource_owner = ANY(permissions.org_ids) OR projections.projects4.id = ANY(permissions.project_ids)) WHERE projections.projects4.instance_id = ?
|
|
}
|