[jboss-jira] [JBoss JIRA] (ELY-1455) DB query seen for each request using FORM mechanism.

[Darran Lofthouse (JIRA)]

    [ https://issues.jboss.org/browse/ELY-1455?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13502636#comment-13502636 ] 

Darran Lofthouse commented on ELY-1455:
---------------------------------------

Debugging this I can see we do actually have two bugs that are contributing to the issue you are experiencing.

# Where the JDBC realm loads an identity it should be caching the results of the queries for the duration of that HTTP request, so after the principalQuery has been executed it should retain this,  This is not happening so it is executing the query twice within a single authentication attempt.
# Although the web application is configured to use FORM authentication it appears to be mapped to /account/login rather than j_security_check so instead of using the FORM authentication mechanism it is using the programmatic authentication API on HttpServletRequest - this method has a bug in that it is caching the username and restoring it rather than the resulting SecurityIdentity.

I am going to work on both of these issues now as they are genuine issues that do require a fix but I think in light of this there are a couple of options: -

# Update the login page to submit to j_security_check this will mean the SecurityIdentity caching of the FORM authentication mechanism will kick in properly.
# Insert an in-memory cache in front of the JDBC security realm, this will cache the first interaction with the realm so for subsequent requests the realm and the database will not be hit.

The following CLI commands enable the cache: -

{noformat}
[standalone at localhost:9990 /] batch 
[standalone at localhost:9990 / #] ./subsystem=elytron/caching-realm=jdbc-cache:add(realm=wmtRealm)
[standalone at localhost:9990 / #] ./subsystem=elytron/security-domain=wmtSD:write-attribute(name=default-realm, value=jdbc-cache)
[standalone at localhost:9990 / #] ./subsystem=elytron/security-domain=wmtSD:write-attribute(name=realms, value=[{realm=jdbc-cache, role-decoder=groups-to-roles}])
[standalone at localhost:9990 / #] run-batch 
{noformat}

This explains the back and forth we had early on discussing this where the logs did not quite make sense, I understand now that the FormAuthenticationMechanism was serving up the login page but now I can see it was not being used for the subsequent authentication which is where the messages I was expecting to see in the log were going missing.





>  DB query seen for each request using FORM mechanism.
> -----------------------------------------------------
>
>                 Key: ELY-1455
>                 URL: https://issues.jboss.org/browse/ELY-1455
>             Project: WildFly Elytron
>          Issue Type: Bug
>          Components: Authentication Mechanisms
>    Affects Versions: 1.2.0.Beta10
>            Reporter: Martin Choma
>            Assignee: Darran Lofthouse
>             Fix For: 1.2.0.Beta11
>
>         Attachments: elytron-bug-src.zip, elytron-bug.zip, server.log, standalone-full-ha.xml, wmt-elytron.war
>
>
> User is complaining, that DB is accessed on each request. 
> Jdbc-realm + FORM authentication
> {noformat}
> <jdbc-realm name="myappRealm">
>                     <principal-query sql="SELECT r.role, u.password FROM user u join user_role_auth r on r.email = u.email where u.email=?" data-source="myds">
>                         <attribute-mapping>
>                             <attribute to="Roles" index="1"/>
>                         </attribute-mapping>
>                         <simple-digest-mapper password-index="2"/>
>                     </principal-query>
>                 </jdbc-realm>
> {noformat}
> {noformat}
> 2017-11-30 09:31:04,049 TRACE [org.wildfly.security] (default task-124) Principal assigning: [alberto at myapp.com], pre-realm rewritten: [alberto at myapp.com], realm name: [wmtRealm], post-realm rewritten: [alberto at myapp.com], realm rewritten: [alberto at myapp.com]
> 2017-11-30 09:31:04,049 TRACE [org.wildfly.security] (default task-124) Executing principalQuery select password from user where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:04,051 TRACE [org.wildfly.security] (default task-124) Executing principalQuery select role, 'Roles' from user_role_auth where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:04,052 TRACE [org.wildfly.security] (default task-124) Executing principalQuery select password from user where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Role mapping: principal [alberto at myapp.com] -> decoded roles [Administrator] -> realm mapped roles [Administrator] -> domain mapped roles [Administrator]
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Authorizing principal alberto at myapp.com.
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Authorizing against the following attributes: [roles] => [Administrator]
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Permission mapping: identity [alberto at myapp.com] with roles [Administrator] implies ("org.wildfly.security.auth.permission.LoginPermission" "") = true
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Authorization succeed
> 2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Role mapping: principal [alberto at myapp.com] -> decoded roles [Administrator] -> realm mapped roles [Administrator] -> domain mapped roles [Administrator]
> 2017-11-30 09:31:07,017 TRACE [org.wildfly.security] (default task-125) Principal assigning: [alberto at myapp.com], pre-realm rewritten: [alberto at myapp.com], realm name: [wmtRealm], post-realm rewritten: [alberto at myapp.com], realm rewritten: [alberto at myapp.com]
> 2017-11-30 09:31:07,018 TRACE [org.wildfly.security] (default task-125) Executing principalQuery select password from user where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:07,019 TRACE [org.wildfly.security] (default task-125) Executing principalQuery select role, 'Roles' from user_role_auth where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:07,021 TRACE [org.wildfly.security] (default task-125) Executing principalQuery select password from user where email = ? with value alberto at myapp.com
> 2017-11-30 09:31:07,022 TRACE [org.wildfly.security] (default task-125) Role mapping: principal [alberto at myapp.com] -> decoded roles [Administrator] -> realm mapped roles [Administrator] -> domain mapped roles [Administrator]
> 2017-11-30 09:31:07,022 TRACE [org.wildfly.security] (default task-125) Authorizing principal alberto at myapp.com.
> 2017-11-30 09:31:07,023 TRACE [org.wildfly.security] (default task-125) Authorizing against the following attributes: [roles] => [Administrator]
> 2017-11-30 09:31:07,023 TRACE [org.wildfly.security] (default task-125) Permission mapping: identity [alberto at myapp.com] with roles [Administrator] implies ("org.wildfly.security.auth.permission.LoginPermission" "") = true
> 2017-11-30 09:31:07,023 TRACE [org.wildfly.security] (default task-125) Authorization succeed
> 2017-11-30 09:31:07,023 TRACE [org.wildfly.security] (default task-125) Role mapping: principal [alberto at myapp.com] -> decoded roles [Administrator] -> realm mapped roles [Administrator] -> domain mapped roles [Administrator]
> {noformat}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)