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

[jtowell]

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

 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.

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.

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

ACL_CLASS LEFT JOIN ACL_ENTRY ON ACL_OBJECT_IDENTITY.ID = ACL_ENTRY.ACL_OBJECT_IDENTITY

[jtowell]

bump? (10 characters)

[tduffey]

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

--- 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 ( ";

[tduffey]

Also, here's the bug in JIRA:

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

Vote for it!