authorization always fails but authentication works

[boardsonair]

I'm getting a authorization failure for all valid users. The log confirms that I'm definitely authenticating and even successfully querying the database but my failure occurs in the jboss realm authorization. I've tried both jboss 3.0.2 and jboss 3.0.4 with the same results. I've been stuck on this problem for quite some time so anything you think might help would be useful. I've check the rolegroup sql query's and I'm at a loss
Thanks

---log files---
2002-11-21 19:59:04,571 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] JBossUserPrincipal: tfw
2002-11-21 19:59:04,584 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] created JBossUserRealm::JBossUserPrincipal: tfw
2002-11-21 19:59:04,585 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] authenticating: Name:tfw Password:****
2002-11-21 19:59:04,914 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] authenticated: tfw
2002-11-21 19:59:04,915 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] setting JAAS subjectAttributeName(j_subject) : Subject:
Principal: tfw
Principal: CallerPrincipal

2002-11-21 19:59:04,932 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] authenticating: Name:tfw Password:****
2002-11-21 19:59:04,933 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] authenticated: tfw
2002-11-21 19:59:04,934 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] JBossUserPrincipal: tfw is NOT in Role: user
2002-11-21 19:59:04,935 DEBUG [org.jboss.jetty.security.JBossUserRealm#PostgresDbRealm] JBossUserPrincipal: tfw is NOT in Role: admin

--Web.xml--
<web-app>
<security-constraint >
<web-resource-collection>
<web-resource-name> Entire Application
</web-resource-name>
<url-pattern> /MainMenu.jsp </url-pattern>
<http-method> GET </http-method>
<http-method> POST </http-method>
<http-method> PUT </http-method>
</web-resource-collection>
<auth-constraint>
<role-name>admin</role-name>
</auth-constraint>
<user-data-constraint>
web security
<transport-guarantee>NONE</transport-guarantee>
</user-data-constraint>
</security-constraint>
<login-config>
<auth-method>FORM</auth-method>
<realm-name>PostgresDbRealm</realm-name>
<form-login-config > <form-login-page>/security/home.jsp</form-login-page> <form-error-page>/security/error.jsp</form-error-page>
</form-login-config></login-config>
</web-app>

--jboss-web.xml--
<jboss-web>
<security-domain>java:/jaas/WebPortalRealm</security-domain>
</jboss-web>

--Login-config.xml--
<application-policy name = "WebPortalRealm">

<login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
<module-option name = "dsJndiName">java:/PostgresDS</module-option>
<module-option name = "principal">postgres</module-option>
<module-option name = "principalsQuery">select password from userejb where id=?</module-option>
<module-option name = "rolesQuery"> select role, 'CallerPrincipal' from rolesejb where id=?</module-option>
<module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=PostgresDS</module-option>
<module-option name="unauthenticatedIdentity">nobody</module-option>
</login-module>

</application-policy>

--Database sql query returns the following--
dogmang=# select role, 'CallerPrincipal' from rolesejb where id='tfw';
role | ?column?
-------+-----------------
admin | CallerPrincipal

I'm at a loss...

[jwkaltz]

Sorry I can't tell what you're doing wrong as I've only used JBoss 2.4.X so far.
What I can tell is that when I got stuck as to what exactly was going on in the authorization I looked at the source code: it's actually quite easy and doesn't take much time.
I guess you would start with the source code for
org.jboss.security.auth.spi.DatabaseServerLoginModule
maybe add some more debug messages or something.

Don't be afraid to look at the source code, it's not a waste of time and it's a huge advantage to be able to do that.

[jmoring]

You are missing the "Roles" role group. You have hard coded "CallerPrincipal" in your query as the only role group returned. The CallerPrincipal group is used to return a your principal in calls to getCallerPrincipal. The Roles group is used to hold the roles to which a given principal belongs.

HTH

[boardsonair]

Sure enough the correct query should have been

select role, 'Roles' from rolesejb where id=?

Thanks a lot