Spring Security Invalid type casting for BasicLookupStrategy

Expected Behavior

When we use default ACL components what Spring gives it should works fine. So when we create all required schemas what your documentation gives it should plays for Postgresql.

Current Behavior

There is type cast exception when we use default schema and BasicLookupStrategy. So we have identifier columns of bigint type but here we convert our Java identifier to string https://github.com/spring-projects/spring-security/blob/d7599ab1926a8a6cba0e4385476654962d72a07b/acl/src/main/java/org/springframework/security/acls/jdbc/BasicLookupStrategy.java#L368. So basic query leads to exception and I need to change it to the following one while using Postgresql:

basicLookupStrategy.setLookupObjectIdentitiesWhereClause("(acl_object_identity.object_id_identity = ?::bigint and acl_class.class = ?)");

Context

As I described earlier I was needed to set lookupObjectIdentitiesWhereClause with casting to bigint.

Comment From: marcusdacoregio

Hi, @DendeberiaOleksandr. Thanks for the report.

Are you able to provide a minimal, reproducible sample that we can use to test it on our side?

Comment From: DendeberiaOleksandr

Hey @marcusdacoregio Hard to say. It can be reproduces simply by adding following schema:

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 NOT NULL PRIMARY KEY,
    object_id_class BIGINT NOT NULL,
    object_id_identity BIGINT 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 NOT NULL 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)
);

And creating something like this:

@Configuration
@RequiredArgsConstructor
public class AclConfig {

    private final DataSource dataSource;

    @Bean
    public AclAuthorizationStrategy aclAuthorizationStrategy() {
        return new AclAuthorizationStrategyImpl(new SimpleGrantedAuthority("ROLE_ADMIN"));
    }

    @Bean
    public PermissionGrantingStrategy permissionGrantingStrategy() {
        return new DefaultPermissionGrantingStrategy(new ConsoleAuditLogger());
    }

    @Bean
    public AclCache aclCache() {
        return new SpringCacheBasedAclCache(
                cache(),
                permissionGrantingStrategy(),
                aclAuthorizationStrategy()
        );
    }

    @Bean
    public Cache cache() {
        return new ConcurrentMapCache("acl");
    }

    @Bean
    public LookupStrategy lookupStrategy() {
        BasicLookupStrategy basicLookupStrategy = new BasicLookupStrategy(
                dataSource,
                aclCache(),
                aclAuthorizationStrategy(),
                permissionGrantingStrategy()
        );
        // basicLookupStrategy.setLookupObjectIdentitiesWhereClause("(acl_object_identity.object_id_identity = ?::bigint and acl_class.class = ?)");
        return basicLookupStrategy;
    }

    @Bean
    public AclService aclService(DataSource dataSource) {
        JdbcMutableAclService jdbcMutableAclService = new JdbcMutableAclService(dataSource, lookupStrategy(), aclCache());
        jdbcMutableAclService.setClassIdentityQuery("SELECT currval(pg_get_serial_sequence('acl_class', 'id'))");
        jdbcMutableAclService.setSidIdentityQuery("SELECT currval(pg_get_serial_sequence('acl_sid', 'id'))");
        return jdbcMutableAclService;
    }

}

With this default configuration it will throw an BadSQLGrammar for the Postgres

Comment From: marcusdacoregio

Without a reproducible sample it might take a while for us to look into this since in-memory ACL is no longer advised, so it is unlikely to be a higher priority right now. If you have some spare time, it would be great to have a minimal, reproducible sample to analyze this issue sooner.

Comment From: DendeberiaOleksandr

@marcusdacoregio do you mean to create a minimum spring application what will throw that error ?

Comment From: marcusdacoregio

Yes, exactly what is described here https://stackoverflow.com/help/minimal-reproducible-example

Comment From: spring-projects-issues

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

Comment From: spring-projects-issues

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.