Users and groups deletion leave behind role assignment records

[askfongjojo]

While evaluating SCIM user and group de-provisioning use cases, I noticed that currently role assignments for deleted users and groups are not purged from the database table. Here are such orphan records on rack2:

root@[fd00:1122:3344:105::3]:32221/omicron> select * from role_assignment
where identity_type = 'silo_user'
and identity_id not in (select id from silo_user where time_deleted is NULL);

  resource_type |  role_name   |             resource_id              |             identity_id              | identity_type
----------------+--------------+--------------------------------------+--------------------------------------+----------------
  silo          | admin        | 8fe2e59e-2c8c-40fb-a8bc-9f5f53b8195b | 7885f92e-3b37-44c4-8fd5-b2924c9b0eb2 | silo_user
  silo          | collaborator | 8fe2e59e-2c8c-40fb-a8bc-9f5f53b8195b | fa649b5c-7e62-4c6d-aa25-506fb724a1c7 | silo_user
(2 rows)

root@[fd00:1122:3344:105::3]:32221/omicron> select * from role_assignment
where identity_type = 'silo_group'
and identity_id not in (select id from silo_group where time_deleted is NULL);

  resource_type | role_name |             resource_id              |             identity_id              | identity_type
----------------+-----------+--------------------------------------+--------------------------------------+----------------
  silo          | admin     | 0b8fd0c6-26ec-4bdb-a0dc-2bda04da64cf | 65bccd52-3d1a-4fd6-a04d-247eb6909a93 | silo_group
  silo          | admin     | 22233ef5-89a1-486a-bc16-dfe258f92f52 | 5cda7def-433f-41c3-90a0-4815a5bc52d0 | silo_group
  silo          | admin     | 2a4f7cfe-c4dc-4524-884d-9af6a3fe2331 | 74273055-b224-429e-81f2-7b5043ff8262 | silo_group
  silo          | admin     | 2c74fe3d-c9ad-4594-bcbe-df16e8b79d40 | 96ffdd3c-5079-4412-b5dc-3d4e9a502a4b | silo_group
--SNIP--
  silo          | admin     | f350ab53-4b45-4f65-bbeb-5e1b078e1024 | 5bfe9d88-0bd9-4830-9f10-f20ce09593df | silo_group
  silo          | admin     | f4ff5366-bc07-4820-b994-c90bd5f27308 | 02cbabd2-2624-4a7c-8b44-e0a0b6112a69 | silo_group
  silo          | admin     | f998acc6-ddea-4153-b5a1-19d0ac82f555 | 5e2334f6-cf78-4430-bac6-7570b245f93f | silo_group
(46 rows)

It doesn't appear that the records are kept intentionally as audit trail because: 1) The treatment is different from the silo_group_membership table which does not maintain any deleted users and groups; 2) Without the related entries in the group membership table, these left-behind records in role_assignment do not provide the complete user permission audit history.

To be clear, these records do not cause any functional or security issues because the uuids won't be re-used even if the same user or group is provisioned again via external API or IDP integration. This issue just serves as documentation for why we may leave this behavior unchanged when implementing SCIM user and group deletion.

[papertigers]

I think this will be fixed by commit 3d1a4d5 as a part of #9276