We have an application that uses the database's authentication and then uses that for the connection. Meaning, when they log into the application, the application tries to make a connection with that username/password to the database and uses that connection for the session. So if Fred logs into the application with username "fred", then there should be a user on the database named "fred" and Fred will use that connection when he uses the application.
We already have a datasource that handles this for us and it works find in Glassfish if we turn off the database connection pooling. Now we want to migrate to Wildfly but can't seem to get this to work.
When the application starts up, it runs an "initialization" query using the username/password in the database descriptor in Wildfly. That runs correctly. When we try to log in, the connection belongs so the user from the initialization query.
I've tried setting min-pool-size and max-pool-size to 1, and that causes our JPA implementation to throw a "can't get managed connection" exception.
The following config gets the application started but can't log in because the login user doesn't match the username on the connection:
<pool>
<min-pool-size>1</min-pool-size>
<max-pool-size>2</max-pool-size>
<use-strict-min>true</use-strict-min>
<allow-multiple-users>true</allow-multiple-users>
<prefill>false</prefill>
</pool>
It seems that connection pooling is messing us up. Does anyone have any ideas how to shut off pooling? Or does anyone have any ideas on how they would tackle this?
I figured out what I did wrong. . .the original datasource code never called getConnection(username,password). Once I changed that, things seems to work well.