Spring Security ACL: No operator matches the given name and argument type

[vjykumar]

When trying to use Spring Security ACL, I am facing error: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying

Below is the error stack:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select acl_object_identity.object_id_identity, acl_entry.ace_order, acl_object_identity.id as acl_id, acl_object_identity.parent_object, acl_object_identity.entries_inheriting, acl_entry.id as ace_id, acl_entry.mask, acl_entry.granting, acl_entry.audit_success, acl_entry.audit_failure, acl_sid.principal as ace_principal, acl_sid.sid as ace_sid, acli_sid.principal as acl_principal, acli_sid.sid as acl_sid, acl_class.class from acl_object_identity left join acl_sid acli_sid on acli_sid.id = acl_object_identity.owner_sid left join acl_class on acl_class.id = acl_object_identity.object_id_class left join acl_entry on acl_object_identity.id = acl_entry.acl_object_identity left join acl_sid on acl_entry.sid = acl_sid.id where ( (acl_object_identity.object_id_identity = ? and acl_class.class = ?)) 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) at org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:339) at org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:130) at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:112) at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:120)

I traced the issue till this line: https://github.com/spring-projects/spring-security/blob/522bfe9e054ac4dcd747d76a2b0ad296b659548d/acl/src/main/java/org/springframework/security/acls/jdbc/BasicLookupStrategy.java#L397

I am wondering if we have decided that Spring Security ACL will always support Long SID Identifiers, why are we converting the identifier to String and setting the parameter as String. Any pointers in that direction?

[nenaraab]

Hi,

I face the same issue. As recommended in the current Spring.io documentation i've setup the acl tables in my PostgreSQL database, with column acl_object_identity.object_id_identity of type varchar(36).

IMHO the args parameter in queryForObject method call is not properly specified.

It is new Object[]{oid.getType(), oid.getIdentifier()}, but it must be new Object[]{oid.getType(), "" + oid.getIdentifier()} as shown in the example below:

public class PostgresJdbcMutableAclService extends JdbcMutableAclService {
        //copy of this JdbcMutableAclService.selectObjectIdentityPrimaryKey
        private String selectObjectIdentityPrimaryKey = "select acl_object_identity.id from acl_object_identity, acl_class "
                + "where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? "
                + "and acl_object_identity.object_id_identity = ?";

        public PostgresJdbcMutableAclService(DataSource dataSource, LookupStrategy lookupStrategy, AclCache aclCache) {
            super(dataSource, lookupStrategy, aclCache);
        }

        @Override
        protected Long retrieveObjectIdentityPrimaryKey(ObjectIdentity oid) {
            try {
                return (Long) this.jdbcTemplate.queryForObject(this.selectObjectIdentityPrimaryKey, Long.class, new Object[]{oid.getType(), "" + oid.getIdentifier()});
            } catch (DataAccessException var3) {
                return null;
            }
        }

        @Override
        public List<ObjectIdentity> findChildren(ObjectIdentity parentIdentity) {
            Object[] args = new Object[]{"" + parentIdentity.getIdentifier(), parentIdentity.getType()};
            List<ObjectIdentity> objects = this.jdbcTemplate.query(this.findChildrenSql, args, new RowMapper<ObjectIdentity>() {
                public ObjectIdentity mapRow(ResultSet rs, int rowNum) throws SQLException {
                    String javaType = rs.getString("class");
                    Serializable identifier = (Serializable) rs.getObject("obj_id");
//                    identifier = JdbcAclService.this.aclClassIdUtils.identifierFrom(identifier, rs);
                    return new ObjectIdentityImpl(javaType, identifier);
                }
            });
            return objects.size() == 0 ? null : objects;
        }
}

Further references

• Postgresql release notes: on-character data types are no longer automatically cast to TEXT

Any other ideas?

[rwinch]

There is support for non int identifiers in Spring Security now. If someone wants to setup a sample to reproduce this then we can try and go from there.

[mangei]

I took the db schema for Postgres from https://docs.spring.io/spring-security/site/docs/3.0.x/reference/appendix-schema.html and run into the same error.

As noted by @nenaraab (Thanks for that!), I changed the column type accordingly to the current documentation and it worked again.

[nenaraab]

@rwinch Where to setup a PostgreSQL sample / is there any reference for other databases?

Unfortuantely, the support of non-integer identifiers does not fix my Postgres issue :-(

[rwinch]

@nenaraab There isn't a sample with Postgres. The contact sample is the reference for ACL support. However, I would caution you that we don't typically recommend using ACL support because it requires doing in memory joins. If you have a million records and the user only is able to access 2, then you must process all the records in memory.

Instead, we recommend using the Spring Data support.

I see you were able to make some progress on this in #6050 Does that resolve your issue?

[mangei]

In addition to my changes above, I had to adjust two more queries, to make it work with the correct types:

    @Bean
    public JdbcMutableAclService aclService() {
        JdbcMutableAclService jdbcMutableAclService = new JdbcMutableAclService(dataSource, lookupStrategy(), aclCache());

        // from documentation
        jdbcMutableAclService.setClassIdentityQuery("select currval(pg_get_serial_sequence('acl_class', 'id'))");
        jdbcMutableAclService.setSidIdentityQuery("select currval(pg_get_serial_sequence('acl_sid', 'id'))");

        // additional adjustments
        jdbcMutableAclService.setObjectIdentityPrimaryKeyQuery("select acl_object_identity.id from acl_object_identity, acl_class where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? and acl_object_identity.object_id_identity = cast(? as varchar)");
        jdbcMutableAclService.setFindChildrenQuery("select obj.object_id_identity as obj_id, class.class as class from acl_object_identity obj, acl_object_identity parent, acl_class class where obj.parent_object = parent.id and obj.object_id_class = class.id and parent.object_id_identity = cast(? as varchar) and parent.object_id_class = (select id FROM acl_class where acl_class.class = ?)");

        return jdbcMutableAclService;
    }

[nenaraab]

@mangei - your workaround looks much nicer than mine, leveraging the new setters :-) Anyhow, this PR will hopefully make your additional adjustments obsolete.

[nenaraab]

@rwinch thanks for the sample and the hint with ACL support... for the reasons you've mentioned we don't use it for mass selects (like findChildren...) and we also don't use @PostAuthorize for paginated REST calls.

Still, for single inserts / deletions we make

[matt00000001]

@rwinch What do you mean by:

However, I would caution you that we don't typically recommend using ACL support because it requires doing in memory joins. If you have a million records and the user only is able to access 2, then you must process all the records in memory.

Instead, we recommend using the Spring Data support.

Are you saying do not use Spring ACL? I don't see a data level security called Spring Data... So I'm confused.

[rwinch]

Thanks for getting in touch, but it feels like this is a question that would be better suited to Stack Overflow. We prefer to use GitHub issues only for bugs and enhancements. Feel free to update this issue with a link to the re-posted question (so that other people can find it) or add some more details if you feel this is a genuine bug.

[mangei]

@nenaraab & @rwinch: Thanks for your time and work to fix this issue! I appreciate this a lot =)

[fprumbau]

We are using Spring-Security since acegi times, always with acl implementation. We updated to every release since. After trying 5.1.6 to 5.2.0 we reverted and tried again with 5.2.1 but have the same error. I wonder if it could be the aftermath of this change. Maybe someone have a clue?

We'are running on Oracle 12 and our error is

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.math.BigDecimal] to type [java.lang.Long]     at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321)     at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194)     at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:174)     at org.springframework.security.acls.jdbc.AclClassIdUtils.convertToLong(AclClassIdUtils.java:122)     at org.springframework.security.acls.jdbc.AclClassIdUtils.identifierFrom(AclClassIdUtils.java:71)     at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.convertCurrentResultIntoObject(BasicLookupStrategy.java:634)     at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:583)     at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:558)     at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679)     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)     at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)     at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700)     at org.springframework.security.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:381)     at org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:336)     at org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:129)     at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:111)     at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:119)     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)     at java.lang.reflect.Method.invoke(Method.java:498)     at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)     at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)     at com.sun.proxy.$Proxy172.readAclById(Unknown Source)

[jzheaux]

@fprumbau I think the one you are looking for is https://github.com/spring-projects/spring-security/issues/4814

AclClassIdUtils has a default ConversionService that it uses now. As a workaround, you might consider configuring a GenericConversionService for BasicLookupStrategy. Otherwise, I'd recommend that you log an issue in case there is a way for the framework to take care of the concern.

[fprumbau]

@jzheaux Thanx a lot, you made my day. After registering a proper implementation converting BigDecimal to long everything is good again. :-)

[urvashi01sharma]

Hi @fprumbau - I am using spring-security-acl 5.2.2-RELEASE and facing same issue while converting from Integer to Long. I have my object_identity_id column defined as int in acl_object_identity table. Could you please explain me how you added your custom converter in GenericConversionService and then injected this instance to AclClassIdUtils. As I see AclClassIdUtils is using new instance of GenericConversionService.