KBEC-00523 - Unable to delete item due to ACL integrity constraint violation in PostgreSQL

Issue

You are using a PostgreSQL database to host your CloudBees CD/RO cluster. When you try to delete an object under your project, the operation hangs and the database logs show duplicate key value violates unique constraint errors similar to the following:

ERROR:  update or delete on table "ec_acl" violates foreign key constraint "fk_acl_entry_acl" on table "ec_acl_entry"▼

Environment

CloudBees CD/RO 2025.03 and older versions.
PostgreSQL.

Context

This problem is caused by a race condition that allows adding duplicate ACL entries in highly concurrent environments. Not having a unique constraint in the table ec_acl_entry for the columns acl_id, principal_name and principal_type for an object causes that it cannot be successfully deleted.

Diagnose

To confirm if you are affected, please execute the following select query, which will return all the problematic records in the table ec_acl_entry:

/* =============================================
CDRO-499-IdentyACL_EntriesAffected.sql
Jira ID: https://cloudbees.atlassian.net/browse/CDRO-499 - Duplicates ACL entry per ACL - Principal Name - Principal Type is leading the BG Deleter to fail
Author: Javier Garcia Moreno - CloudBees, Inc.
Create date: 2024/05/27
Compatible DB: PostgreSQL
Execution Risk: Low (no data modification is performed)
Description:

    Identify the recods affected by https://cloudbees.atlassian.net/browse/CDRO-499
    This query will return all the records affected in the table ec_acl_entry.
    In case that no row is returned, you are not affected by this issue.

Script variations:
    * CDRO-499-IdentyACL_EntriesAffected_groupedByACL-Principal.sql
        Information grouped by acl_id,principal_name,principal_type
*/-- =============================================

select
	occurrences_details.*
from
	(
	select
		count(1),acl_id,principal_name,principal_type
	from
		ec_acl_entry eae
	group by
		acl_id,principal_name,principal_type
	having
		count(1) > 1
	) occurrences
left join lateral
(
	select
 		*
	from
		ec_acl_entry eae2
	where
		eae2.acl_id = occurrences.acl_id
		and eae2.principal_name = occurrences.principal_name
		and eae2.principal_type = occurrences.principal_type
) occurrences_details on true;▼

Resolution

If the query above returns more than 0 rows, please contact CloudBees Support to get help on the next steps to fix the problem.