Announcement Announcement Module
Collapse
No announcement yet.
ERROR: invalid reference to FROM-clause entry for table "acl_object_identity" Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ERROR: invalid reference to FROM-clause entry for table "acl_object_identity"

    Hey all,

    I am trying to get acegi 1.0.3 ACL working with PostgreSQL. I have run into a problem when querying for existing ACE's. Calling the
    Code:
     public Acl readAclById(ObjectIdentity object, Sid[] sids)
    method on MutableACLService and using the BasicLookupStrategy. It seems to work fine on my hsqldb for unit tests, but running against postgres I get the following Exception and stack trace.

    Code:
    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, ACL_SID ACLI_SID, ACL_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 ACLI_SID.ID = ACL_OBJECT_IDENTITY.OWNER_SID and ACL_CLASS.ID = ACL_OBJECT_IDENTITY.OBJECT_ID_CLASS and ( (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: invalid reference to FROM-clause entry for table "acl_object_identity"
    Caused by: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "acl_object_identity"
    	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
    	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
    	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
    	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:588)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:527)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:581)
    	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:610)
    	at org.acegisecurity.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:283)
    	at org.acegisecurity.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:400)
    	at org.acegisecurity.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:112)
    	at org.acegisecurity.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:93)
    When cutting and pasting the SQL into pgAdmin III. I get the following error.
    Code:
    ERROR: invalid reference to FROM-clause entry for table "acl_object_identity"
    SQL state: 42P01
    Hint: There is an entry for table "acl_object_identity", but it cannot be referenced from this part of the query.
    Character: 503
    This refers to the following part of the SQL
    Code:
    ACL_CLASS LEFT JOIN ACL_ENTRY ON ACL_OBJECT_IDENTITY.ID = ACL_ENTRY.ACL_OBJECT_IDENTITY

  • #2
    bump? (10 characters)

    Comment


    • #3
      org.acegisecurity.acls.jdbc.BasicLookupStrategy.ja va is broken. Here's the patch I used to build my own lookup strategy:

      Code:
      --- BasicLookupStrategy.java    2007-02-18 09:38:03.000000000 -0600
      +++ FooLookupStrategy.java  2007-02-13 16:19:37.000000000 -0600
      @@ -109,7 +109,7 @@
                   + "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, ACL_SID ACLI_SID, ACL_CLASS "
      +            + "from ACL_SID ACLI_SID, ACL_CLASS, ACL_OBJECT_IDENTITY "
                   + "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 ACLI_SID.ID = ACL_OBJECT_IDENTITY.OWNER_SID "
                   + "and ACL_CLASS.ID = ACL_OBJECT_IDENTITY.OBJECT_ID_CLASS " + "and ( ";

      Comment


      • #4
        Also, here's the bug in JIRA:

        http://opensource.atlassian.com/proj...browse/SEC-422

        Vote for it!

        Comment

        Working...
        X