mirror of
https://github.com/zitadel/zitadel.git
synced 2026-11-04 13:16:43 -06:00
Tim MöhlmannandSilvan
2e09effe8b
# Which Problems Are Solved
Zitadel v4.7.2 fixed a security issue by switching to the permission v2
framework for user APIs. It appears that systems that are running since
before v2.68 that were affected by a precision bug in the eventstore,
which was fixed in that version. The precision bug results in certain
events being "skipped" while being projected into the fields table, used
by the new permission system. This caused certain membership roles to be
missing, resulting in empty user lists when executed by the affected
member. The permission system basically finds no matching memberships
and therefore returns no users at all.
# How the Problems Are Solved
After research we concluded that the legacy membership projections are
projected correctly. This PR synchronizes the projected state into the
fields table. As the membership roles are not marked unique, all rows
are first deleted and then the correct membership roles are then
inserted. The operation happens in a single transaction, during which
the fields table will remain locked for modifications. This to prevent
possible concurrent modifications to membership states.
# Additional Changes
- none
# Additional Context
- Introduced in
https://github.com/zitadel/zitadel/commit/0e17d0005a98ccbf92139961ef702ef03208ffd3
- Released in
[v4.7.2](https://github.com/zitadel/zitadel/releases/tag/v4.7.2)
- Related: https://github.com/zitadel/zitadel/issues/8863
---------
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
(cherry picked from commit 58612a6ef7)
72 lines
1.6 KiB
SQL
72 lines
1.6 KiB
SQL
-- Make sure no other transaction is writing to fields table.
|
|
-- Will block transactions with events that modify the fields table.
|
|
-- SELECT is still possible during this time.
|
|
LOCK TABLE eventstore.fields IN SHARE ROW EXCLUSIVE MODE;
|
|
|
|
DELETE FROM eventstore.fields
|
|
WHERE object_type IN (
|
|
'instance_member_role',
|
|
'org_member_role',
|
|
'project_member_role'
|
|
);
|
|
|
|
INSERT INTO eventstore.fields(
|
|
instance_id,
|
|
resource_owner,
|
|
aggregate_type,
|
|
aggregate_id,
|
|
object_type,
|
|
object_id,
|
|
object_revision,
|
|
field_name,
|
|
value,
|
|
value_must_be_unique,
|
|
should_index
|
|
)
|
|
|
|
SELECT
|
|
instance_id,
|
|
resource_owner,
|
|
'instance' as aggregate_type,
|
|
id as aggregate_id,
|
|
'instance_member_role' as object_type,
|
|
user_id as object_id,
|
|
1::smallint as object_revision,
|
|
'instance_role' as field_name,
|
|
to_jsonb(unnest(roles)) as value,
|
|
false as value_must_be_unique,
|
|
true as should_index
|
|
FROM projections.instance_members4
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
instance_id,
|
|
resource_owner,
|
|
'org' as aggregate_type,
|
|
org_id as aggregate_id,
|
|
'org_member_role' as object_type,
|
|
user_id as object_id,
|
|
1::smallint as object_revision,
|
|
'org_role' as field_name,
|
|
to_jsonb(unnest(roles)) as value,
|
|
false as value_must_be_unique,
|
|
true as should_index
|
|
FROM projections.org_members4
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
instance_id,
|
|
resource_owner,
|
|
'project' as aggregate_type,
|
|
project_id as aggregate_id,
|
|
'project_member_role' as object_type,
|
|
user_id as object_id,
|
|
1::smallint as object_revision,
|
|
'project_role' as field_name,
|
|
to_jsonb(unnest(roles)) as value,
|
|
false as value_must_be_unique,
|
|
true as should_index
|
|
FROM projections.project_members4;
|