4 Replies Latest reply on May 31, 2018 8:00 AM by Achilleas Mantzios

    Per-user data-source connection pools (via security-domain) in jboss-6.0.0.Final vs wildfly-10.1.0.Final

    Achilleas Mantzios Newbie

      Back with Jboss-6.0.0.Final we had the following data-source/pool definition :

      <datasources> 
      <xa-datasource>
      <jndi-name>pgsql</jndi-name>
      <track-connection-by-tx>true</track-connection-by-tx>
      <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
      <xa-datasource-property
      name="ServerName">localhost</xa-datasource-property>
      <xa-datasource-property name="PortNumber">5432</xa-datasource-property>
      <xa-datasource-property name="DatabaseName">somedb</xa-datasource-property>
      <security-domain>postgresqluser</security-domain>
      <min-pool-size>0</min-pool-size>
      <max-pool-size>5</max-pool-size>
      </xa-datasource>

      </datasources>

      The definition for security-domain postgresqluser was:

      <application-policy name="postgresqluser"> 
      <authentication>
      <login-module code="org.jboss.resource.security.CallerIdentityLoginModule" flag="required">
      <module-option name = "managedConnectionFactoryName">jboss.jca:service=XATxCM,name=pgsql</module-option>
      </login-module>
      </authentication>
      </application-policy>

      So this gave us one pool per user with max size 5 (5 is the most that app requires + 1). If one user abused the system, and try to get more than 5 connections (e.g by pressing F5 quickly) the rest of users were unaffected since all he/she could do was to block waiting for more connections.

      Now we migrated the above configuration to wildfly-10.1.0.Final as follows :

      <xa-datasource jndi-name="java:/pgsql" pool-name="smaDS" enabled="true" use-ccm="true" mcp="org.jboss.jca.core.connectionmanager.pool.mcp.LeakDumperManagedConnectionPool" statistics-enabled="true">
      <xa-datasource-class>
      org.postgresql.xa.PGXADataSource</xa-datasource-class>
      <xa-datasource-property name="PortNumber">5432</xa-datasource-property>
      <xa-datasource-property name="DatabaseName">somedb</xa-datasource-property>
      <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
      <xa-datasource-class>
      org.postgresql.xa.PGXADataSource</xa-datasource-class>
      <driver>postgresql-9.4.1212.jar</driver>
      <xa-pool>
      <min-pool-size>0</min-pool-size>
      <initial-pool-size>0</initial-pool-size>
      <max-pool-size>5</max-pool-size>
      <allow-multiple-users>true</allow-multiple-users>
      <wrap-xa-resource>true</wrap-xa-resource>
      </xa-pool>
      <security>
      <security-domain>postgresqluser</security-domain>
      </security>

      </xa-datasource>

      <security-domain name="postgresqluser">
      <authentication>
      <login-module code="org.picketbox.datasource.security.CallerIdentityLoginModule" flag="required">
      <module-option name="password-stacking" value="useFirstPass"/>
      </login-module>

      </authentication>
      </security-domain>

      The behavior we have witnessed is different, it seems that wildfly gives the first user his 5 connections, who is able to work as long as no other user logs into the system. When the second users logs in, it seems to give to him his 5 connections (by looking into postgresql activity and logs) but it doesn't seem to be able to do any work with all 10 connections at the same time, then the second user gets blocked or some connection attempts of the first user get blocked as well. By specifying max-pool-size=10 in wildfly seems to be able to handle the first two users successfully. I know (by looking at the logs) the default pool in wildfly uses strategy : org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubjectAndCri . So I guess now in wildfly subpools' semantics have been changed to meaning partitioning of the same single pool, instead of multiple pool instances as used to be the case in jboss-6.0.0.Final.

      Is my understanding correct? And if yes, which means max-pool-size applies to the whole pool, then is there a way to limit connections per user? (I know we could limit the users at the database layer, but we would like to be able to replicate the old behavior, before we move on to new changes).