Data integrity issue on `protected_environment_deploy_access_levels` table
Problem
Since we tweaked the validation on protected environments, we're having a serious data integrity issue that the both access_level
and group_id
or user_id
exist in the same row. The presence of the authorization-type should be mutually exclusive, meaning when access_level
exists, group_id
and user_id
must be NULL
.
Because of this reason, the expected authorization schema can't be identified from the database rows, but it's dependent on the application side, which is quite fragile and could accidentally be changed. We should correct the data integrity by running additional database migration.
Proposal
- Remove
DEFAULT 40
onaccess_level
column fromprotected_environment_deploy_access_levels
table. - Perform a database migration to nullify
access_level
if eithergroup_id
oruser_id
exists. - Add a new constraint that the one of
user_id
,group_id
oraccess_level
must exist. For example:CONSTRAINT chk_rails_bed75249bc CHECK ((((access_level IS NOT NULL) AND (group_id IS NULL) AND (user_id IS NULL)) OR ((user_id IS NOT NULL) AND (access_level IS NULL) AND (group_id IS NULL)) OR ((group_id IS NOT NULL) AND (user_id IS NULL) AND (access_level IS NULL))))
- Remove the patch.
Auto-generated
The following discussion from !38188 (merged) should be addressed:
-
@10io started a discussion: (+2 comments) Just to confirm: the
if: role?
part has been dropped. Is that intended?