Ошибка оператор не существует character varying bigint

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?

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.

@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

@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?

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;
    }

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

@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

rwinch

changed the title
Error with postgres

Spring Security ACL Error with postgres

Nov 29, 2018

rwinch

changed the title
Spring Security ACL Error with postgres

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

Nov 29, 2018

This comment has been minimized.

This comment has been minimized.

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

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)

@fprumbau I think the one you are looking for is #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.

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

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.