authorities-by-username-query syntax?

[springUser213]

Hey,
I’m using Spring Security 2.0.3 and want to access the DB using jdbc-user-service to load the user data out of the db (I’m not using the standard schema). I have just one table with users. Each user has a name (= username), a password and a usergroup (= role). How does my authorities-by-username-query and users-by-username-query have to look like? Here is what I got:

	<authentication-provider>  
		<jdbc-user-service data-source-ref="dataSource"  
			authorities-by-username-query="SELECT U.name, 'ROLE_' || U.usergroup as authority FROM siteuser U WHERE name=?" 
			users-by-username-query="SELECT U.name, U.password, 'TRUE' AS enabled FROM siteuser U where U.name=?" 
		/>	
	</authentication-provider>

I don't get any error messages but I can't login using the correct username and password. I'm using PostgreSQL by the way.

[springUser213]

I somehow feel like talking to myself here. Are these klicks real or are they generated by SpringForumKlickGeneratorImpl ?

[wwheeler]

I'm guessing nobody's responding because your config looks OK, and so there's no obvious problem. All I can think of is dumb stuff. Did you declare the security namespace?

<beans:beans xmlns="http://www.springframework.org/schema/security"
	xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
		http://www.springframework.org/schema/security
		http://www.springframework.org/schema/security/spring-security-2.0.xsd">
	...
</bean:beans>

[chudak]

Turn up logging and trace the execution of your login failure. The answer will be there.