JAAS login configuration with PostgreSQL

[baradon]

Hi,

I'm currently developing a JSF2 web application which uses a j_security_check based login, running on JBoss AS 5.1.  Authentication works perfect, if I store the account data in a MySQL database.  Unfortunately, the web application has to use a PostgreSQL database.  Access to this database is no problem in general, but the login does not work.

This is the respective section from login-config.xml:

  <!-- MySQL based -->
  <application-policy name="myappAccess_A">
      <authentication>
          <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">
              <module-option name="dsJndiName">java:/MySQL_DB</module-option>
              <module-option name="principalsQuery">select user_password from tk_user where user_code=?</module-option>
              <module-option name="rolesQuery">select 'user', 'Roles' from tk_user where user_code=?</module-option>
          </login-module>
      </authentication>
  </application-policy>
  <!-- PostgreSQL based -->
  <application-policy name="myappAccess_B">
    <authentication>
      <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">
        <module-option name="dsJndiName">java:/PostgreSQL_DB</module-option>
        <module-option name="principalsQuery">select user_password from tk_user where user_code=?</module-option>
        <module-option name="rolesQuery">select 'user', 'Roles' from tk_user where user_code=?</module-option>
      </login-module>
    </authentication>
  </application-policy>

As you can see, there is not much difference between the two sections.  Even if I use a hardcoded password here

<module-option name="principalsQuery">select 'mysecret' from tk_user where user_code=?</module-option>

the MySQL version is working, the PostgreSQL version is not.  Unforunately, I don't get any error messages. I'm always redirected to the error page, which usually comes in case of wong username or password.

Does anybody have a hint what's goin wrong here?  I noticed, that the MySQL configuration is also not working when using the most recent version of the MySQL JDBC driver.  Maybe I could use a different PostgreSQL JDBC driver?  But which?

Any help welcome!

Heiner

[baradon]

Uups, just noticed, that the code is gone. Don't know, what I did here.

Okay, this is the configuration from login-config.xml

  <application-policy name="mysqlAccess">

      <authentication>

          <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">

              <module-option name="dsJndiName">java:/MySQL_DB</module-option>

              <module-option name="principalsQuery">select user_password from tk_user where user_code=?</module-option>

              <module-option name="rolesQuery">select user_role, 'Roles' from tk_user where user_code=?</module-option>

          </login-module>

      </authentication>

  </application-policy>

  <application-policy name="postgresAccess">

    <authentication>

      <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag="required">

        <module-option name="dsJndiName">java:/Postgres_DS</module-option>

        <module-option name="principalsQuery">select user_password from tk_user where user_code=?</module-option>

        <module-option name="rolesQuery">select user_role, 'Roles' from tk_user where user_code=?</module-option>

      </login-module>

    </authentication>

  </application-policy>

Is there any documentation available for the possible module-options?

Has anybody successfully used PostgreSQL with JAAS on JBoss?

All the best,

Heiner