Fail to insert into acl_entry when build acl twice with reset between the two execution

Describe the bug Hi. First of all, a little disclaimer. Is the first time I open an issue in Github, so please be patient is I do something wrong and, indeed, I'm open to any suggestion on how to do this better.

Back to us: I've tried to integrate ACLs into my project. When I first execute the init of the data (with sample data to test the APIs) everything goes well. The data are insered and secured correcly. But, if I execute a DB reset (also resetting the ACLs tables) and then I execute the data init again I get the following error (Note: without ant stack trace, I don't kwon why) PreparedStatementCallback; SQL [insert into acl_entry (acl_object_identity, ace_order, sid, mask, granting, audit_success, audit_failure)values (?, ?, ?, ?, ?, ?, ?)]; Batch entry 0 insert into acl_entry (acl_object_identity, ace_order, sid, mask, granting, audit_success, audit_failure)values (('1'::int8), ('0'::int4), ('1'::int8), ('16'::int4), ('TRUE'), ('FALSE'), ('FALSE')) was aborted: ERROR: insert or update on table "acl_entry" violates foreign key constraint "foreign_fk_4" Details: Key (acl_object_identity)=(1) is not present in table "acl_object_identity". Call getNextException to see other errors in the batch.

To Reproduce This is the ACLs tables schema: `drop table if exists acl_entry; drop table if exists acl_object_identity; drop table if exists acl_class; drop table if exists acl_sid;

create table IF NOT EXISTS acl_sid( id bigserial not null primary key, principal boolean not null, sid varchar(100) not null, constraint unique_uk_1 unique(sid,principal) );

create table IF NOT EXISTS acl_class( id bigserial not null primary key, class varchar(100) not null, constraint unique_uk_2 unique(class) );

create table IF NOT EXISTS acl_object_identity( id bigserial primary key, object_id_class bigint not null, object_id_identity varchar(36) not null, parent_object bigint, owner_sid bigint, entries_inheriting boolean not null, constraint unique_uk_3 unique(object_id_class,object_id_identity), constraint foreign_fk_1 foreign key(parent_object)references acl_object_identity(id), constraint foreign_fk_2 foreign key(object_id_class)references acl_class(id), constraint foreign_fk_3 foreign key(owner_sid)references acl_sid(id) );

create table IF NOT EXISTS acl_entry( id bigserial primary key, acl_object_identity bigint not null, ace_order int not null, sid bigint not null, mask integer not null, granting boolean not null, audit_success boolean not null, audit_failure boolean not null, constraint unique_uk_4 unique(acl_object_identity,ace_order), constraint foreign_fk_4 foreign key(acl_object_identity) references acl_object_identity(id), constraint foreign_fk_5 foreign key(sid) references acl_sid(id) );`

The AclManagerService.java file (without imports) `@Component public class AclManagerService {

@Autowired private JdbcMutableAclService aclService; public void addUserPermission(User user, String principal) { ObjectIdentity oi = new ObjectIdentityImpl(User.class, user.getId()); log.info(user.getId().toString()); updateAcl(oi, principal, BasePermission.ADMINISTRATION, true);

}

private void updateAcl(ObjectIdentity oi, String principal, Permission p, boolean grantAccess) { Sid sid = new PrincipalSid(principal);

// Create or read the relevant ACL
MutableAcl acl = null;
try {
  acl = (MutableAcl) aclService.readAclById(oi, List.of(sid));
} catch (NotFoundException nfe) {
  acl = aclService.createAcl(oi);
}

for (int i = acl.getEntries().size() - 1; i > 0; i--) {
  acl.deleteAce(i);
}

if (grantAccess) {
  // Now grant some permissions via an access control entry (ACE)
  acl.insertAce(0, p, sid, true);
}
try {
  aclService.updateAcl(acl);
} catch (Exception ex) {
  System.out.println(ex.getMessage());
}

}

The AclContext.java class `@Configuration @EnableWebSecurity public class AclContext {

@Autowired private DataSource dataSource; /**

Acl default expression handler definition. */ @Bean public DefaultMethodSecurityExpressionHandler defaultMethodSecurityExpressionHandler() { DefaultMethodSecurityExpressionHandler expressionHandler = new DefaultMethodSecurityExpressionHandler(); expressionHandler.setPermissionEvaluator(new AclPermissionEvaluator(aclService())); expressionHandler.setPermissionCacheOptimizer(new AclPermissionCacheOptimizer(aclService())); return expressionHandler; }

/**
Acl service jdbc to update entries. */ @Bean public JdbcMutableAclService aclService() { JdbcMutableAclService aclService = new JdbcMutableAclService(// dataSource, // lookupStrategy(), // aclCache()); String sidQuery = "select currval(pg_get_serial_sequence('acl_sid', 'id'))"; String classQuery = "select currval(pg_get_serial_sequence('acl_class', 'id'))"; aclService.setSidIdentityQuery(sidQuery); aclService.setClassIdentityQuery(classQuery); return aclService; }

/**
Definition of base acl authorization strategy. */ @Bean public AclAuthorizationStrategy aclAuthorizationStrategy() { return new AclAuthorizationStrategyImpl(// new SimpleGrantedAuthority("ROLE_" + KeycloakService.ADMIN_ROLE)); }

/**
Definition of permission granting strategy. */ @Bean public PermissionGrantingStrategy permissionGrantingStrategy() { return new DefaultPermissionGrantingStrategy(// new ConsoleAuditLogger()); }

/**
Definition of acl cache to improve performance. */ @Bean public SpringCacheBasedAclCache aclCache() { final ConcurrentMapCache aclCache = new ConcurrentMapCache("acl_cache"); return new SpringCacheBasedAclCache(// aclCache, // permissionGrantingStrategy(), // aclAuthorizationStrategy()); }

/**
Definition of lookup strategy. */ @Bean public LookupStrategy lookupStrategy() { return new MyLookupStrategy(// dataSource, // aclCache(), // aclAuthorizationStrategy(), // new ConsoleAuditLogger()); } }` Expected behavior The insert can be done whenever I want

Thank you

spring-projects / spring-security

Fail to insert into acl_entry when build acl twice with reset between the two execution #14934