SQL: Deletion of Policy impossible when a Contract Definition exists #1403

[DominikPinsel]

Bug Report

Describe the Bug

Affects all connectors that are using the PostgreSQL Policy Extension.

When there is any contract definition defined, it is impossible to delete policies.

Expected Behavior

• deletion of policies possible as long as its legal (not part of any definition etc.)

Observed Behavior

• deletion of all policies impossible when contract definition exists

Steps to Reproduce

Steps to reproduce the behavior:

1. Create a Contract Definition
2. Create a new Policy
3. Delete the new Policy -> It's not possible to delete this new Policy

Context Information

Before deleting the Policy the PolicyService will check, whether a negotation exists for this asset. The SELECT does not filter.

 @Override
    public @NotNull ServiceResult<Policy> deleteById(String policyId) {

        var contractFilter = format("contractPolicyId = %s ", policyId);
        var accessFilter = format("accessPolicyId = %s ", policyId);

        return transactionContext.execute(() -> {

            if (policyStore.findById(policyId) == null) {
                return ServiceResult.notFound(format("Policy %s does not exist", policyId));
            }

            var queryContractPolicyFilter = QuerySpec.Builder.newInstance().filter(contractFilter).build();
            var contractDefinitionOnPolicy = contractDefinitionStore.findAll(queryContractPolicyFilter);
            if (contractDefinitionOnPolicy.findAny().isPresent()) {
                return ServiceResult.conflict(format("Policy %s cannot be deleted as it is referenced by at least one contract policy", policyId));
            }

            // more code

            return ServiceResult.success(deleted);
        });
    }

@Override
    public @NotNull Stream<ContractDefinition> findAll(QuerySpec spec) {
        return transactionContext.execute(() -> {
            Objects.requireNonNull(spec);

            var query = statements.getSelectAllTemplate();
            try (var connection = getConnection()) {
                var definitions = executeQuery(connection, this::mapResultSet, String.format(query, statements.getContractDefinitionTable()), spec.getLimit(), spec.getOffset());
                return definitions.stream();
            } catch (Exception exception) {
                throw new EdcPersistenceException(exception);
            }
        });
    }

    @Override
    public String getSelectAllTemplate() {
        return String.format("SELECT * from %s LIMIT ? OFFSET ?",
                getContractDefinitionTable());
    }

Detailed Description

Possible Implementation

_{Dominik Pinsel [email protected], Mercedes-Benz Tech Innovation GmbH, legal info/Impressum}

[paullatzelsperger]

Dynamic filtering is NOT supported yet on the SQL implementations. In order to enable dynamically composing WHERE/AND statements in a safe way (avoiding SQL injection) we could re-use the CosmosDB implementation. That's another PR though.

I suggest adding a method isReferenced(String policyId) that checks whether a particular policy is referenced as either contract- or accesspolicy on any definition.