Spring acl BasicLookupStrategy fails when object_id_identity is of type long

[charybr]

Summary

Hi,

I am upgrading spring acl from 3.x to 5.x.
In my schema, object_id_identity column in table acl_object_identity is of type long (bigint in postgresql). BasicLookupStrategy.lookupObjectIdentities query fails when object_id_identity is of type long.

Actual Behavior

I am aware of below changes, where object_id_identity is converted to varchar:
6decf1c

My intention is to retain object_id_identity as long. In this case BasicLookupStrategy.lookupObjectIdentities fails as it is not able to type cast string to long. Error:

 = ?)) order by acl_object_identity.object_id_identity asc, acl_entry.ace_order asc]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 781
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
        at org.springframework.security.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:384)

Expected Behavior

In order to support backward compatibility, query should take long. Looking at the code it is converting long to string. That results in error.

		Set<Long> parentsToLookup = jdbcTemplate.query(sql,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps) throws SQLException {
						int i = 0;
						for (ObjectIdentity oid : objectIdentities) {
							// Determine prepared statement values for this iteration
							String type = oid.getType();

							// No need to check for nulls, as guaranteed non-null by
							// ObjectIdentity.getIdentifier() interface contract
							String identifier = oid.getIdentifier().toString();

							// Inject values
							ps.setString((2 * i) + 1, identifier);
							ps.setString((2 * i) + 2, type);
							i++;
						}
					}
				}, new ProcessResultSet(acls, sids));

Workaround is to override setLookupObjectIdentitiesWhereClause like below.Then it works.

	@Bean
	public LookupStrategy lookupStrategy() {
		BasicLookupStrategy basicLookupStrategy = new BasicLookupStrategy(aclDataSource(), aclCache(),
				aclAuthorizationStrategy(), new ConsoleAuditLogger());
		String lookupObjectIdentitiesWhereClause = "(acl_object_identity.object_id_identity::varchar(36) = ? and acl_class.class = ?)";
		basicLookupStrategy.setLookupObjectIdentitiesWhereClause(lookupObjectIdentitiesWhereClause);
		return basicLookupStrategy;
	}

Configuration

Version

Spring security 5.0.5

Sample

[m410]

Same issue with spring-security-acl:5.1.1.RELEASE

[rwinch]

@charybr Thanks for the report

@m410 Thanks for the bump

This is a duplicate of #5508 so I'm closing this as a duplicate. If you disagree please comment on this or reopen