acl - Unable to obtain the class id type - column name class_id_type was not found

Describe the bug

I'm trying to use Spring Security in combination with ACLs. I use PostgreSQL as my database and have used the schema from the Spring Security docs.

create table 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 acl_class(
        id bigserial not null primary key,
        class varchar(100) not null,
        constraint unique_uk_2 unique(class)
);

create table 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 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)
);

I'm trying to create a new ACL.

var contact = new Contact();
contact.setName("John Doe");
contact.setId(1L);
var objectIdentity = new ObjectIdentityImpl(contact);

tt.execute(
    status -> {
      var acl = mutableAclService.createAcl(objectIdentity);
      var sid = new PrincipalSid("john");
      acl.insertAce(acl.getEntries().size(), BasePermission.READ, sid, true);
      mutableAclService.updateAcl(acl);
      return null;
    });

At first I thought it works but then I enabled logging for Spring Security.

logging.level.org.springframework.security=TRACE

Afterwards I started seeing the following error message.

2024-12-07T08:54:52.074+01:00 DEBUG 53186 --- [demo] [           main] o.s.security.acls.jdbc.AclClassIdUtils   : Unable to obtain the class id type

org.postgresql.util.PSQLException: The column name class_id_type was not found in this ResultSet.
        at org.postgresql.jdbc.PgResultSet.findColumn(PgResultSet.java:3094) ~[postgresql-42.7.4.jar:42.7.4]
        at org.postgresql.jdbc.PgResultSet.getString(PgResultSet.java:2930) ~[postgresql-42.7.4.jar:42.7.4]
        at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java) ~[HikariCP-5.1.0.jar:na]
        at org.springframework.security.acls.jdbc.AclClassIdUtils.classIdTypeFrom(AclClassIdUtils.java:90) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.AclClassIdUtils.hasValidClassIdType(AclClassIdUtils.java:81) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.AclClassIdUtils.identifierFrom(AclClassIdUtils.java:71) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.convertCurrentResultIntoObject(BasicLookupStrategy.java:580) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:532) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:504) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:733) ~[spring-jdbc-6.2.0.jar:6.2.0]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.2.0.jar:6.2.0]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723) ~[spring-jdbc-6.2.0.jar:6.2.0]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754) ~[spring-jdbc-6.2.0.jar:6.2.0]
        at org.springframework.security.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:335) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:300) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:136) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:117) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:125) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at org.springframework.security.acls.jdbc.JdbcMutableAclService.updateAcl(JdbcMutableAclService.java:357) ~[spring-security-acl-6.4.1.jar:6.4.1]
        at com.example.demo.DataSourcePopulator.lambda$afterPropertiesSet$0(DataSourcePopulator.java:106) ~[main/:na]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-6.2.0.jar:6.2.0]
        at com.example.demo.DataSourcePopulator.afterPropertiesSet(DataSourcePopulator.java:101) ~[main/:na]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1849) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1798) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:601) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:523) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:336) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:288) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:334) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.instantiateSingleton(DefaultListableBeanFactory.java:1122) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingleton(DefaultListableBeanFactory.java:1093) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:1030) ~[spring-beans-6.2.0.jar:6.2.0]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:987) ~[spring-context-6.2.0.jar:6.2.0]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:627) ~[spring-context-6.2.0.jar:6.2.0]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:752) ~[spring-boot-3.4.0.jar:3.4.0]
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439) ~[spring-boot-3.4.0.jar:3.4.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:318) ~[spring-boot-3.4.0.jar:3.4.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1361) ~[spring-boot-3.4.0.jar:3.4.0]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1350) ~[spring-boot-3.4.0.jar:3.4.0]
        at com.example.demo.DemoApplication.main(DemoApplication.java:10) ~[main/:na]

I was confused because I use a Long identifier for my Contact entity and tried everything on my side but in the end never achieved to get rid of the error message.

To Reproduce

I spent quite some time to find a solution and I believe this is related to the following GitHub issues and Stack Overflow questions

• https://stackoverflow.com/questions/41706241/how-to-use-spring-security-acl-when-my-domain-identifiers-are-of-string-type
• https://github.com/spring-projects/spring-security/issues/7598
• https://github.com/spring-projects/spring-security/issues/7978
• https://github.com/spring-projects/spring-security/issues/7621

Expected behavior

We should not see any error messages in the logs.

Sample

Here is a minimal, reproducible sample https://github.com/zemirco/spring-security-acl.

Thank you! You're awesome and we love Spring Boot 🥳

Start with AclClassIdUtils.classIdTypeFrom at line 90 and follow its use from BasicLookupStrategy. Compare the PostgreSQL schema in the issue and the linked Spring Security database-schema documentation with the ACL query result set. Reproduce the reported log message using the linked sample, then verify that the expected behavior produces no erroneous error messages.