JBossDeveloper

Added security-domain to ejb3 subsystem, and at least application is deployed.

Now I can't login. I have to admin I don't understand the role mapping etc, so I am sort of blind here.

My realm is the following:

               <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>
 
enabling logging I see the query is performed (actually two times, and I wonder why)
 
2017-11-29 14:02:17,030 TRACE [org.wildfly.security] (default task-30) Handling MechanismInformationCallback type='HTTP' name='FORM' host-name='localhost' protocol='https'
2017-11-29 14:02:17,030 TRACE [org.wildfly.security] (default task-30) Trying to re-authenticate session MTLvqIzVM36Ujo7TIzyfxmze2G4qGT5Ev9GeAjFn using FormAuthenticationMechanism. Request URI: [https://localhost:8181/account/login], Context path: [/]
2017-11-29 14:02:17,030 TRACE [org.wildfly.security] (default task-30) Handling CachedIdentityAuthorizeCallback: principal = null  authorizedIdentity = null
2017-11-29 14:02:17,360 TRACE [org.wildfly.security] (default task-30) Principal assigning: [alberto@myapp.com], pre-realm rewritten: [alberto@myapp.com], realm name: [wmtRealm], post-realm rewritten: [alberto@myapp.com], realm rewritten: [alberto@myapp.com]
2017-11-29 14:02:17,366 TRACE [org.wildfly.security] (default task-30) Executing principalQuery SELECT r.role, u.password FROM user u join user_role_auth r on r.email = u.email where u.email=? with value alberto@myapp.com
2017-11-29 14:02:17,372 TRACE [org.wildfly.security] (default task-30) Executing principalQuery SELECT r.role, u.password FROM user u join user_role_auth r on r.email = u.email where u.email=? with value alberto@myapp.com
2017-11-29 14:02:17,857 TRACE [org.wildfly.security] (default task-41) Created HttpServerAuthenticationMechanism [org.wildfly.security.http.util.SecurityIdentityServerMechanismFactory$1@32b8ade7] for mechanism [FORM]
 
but session remains unauthenticated.
 
 
My security domain is such
 
 
<security-domain name="wmtSD" default-realm="wmtRealm" permission-mapper="default-permission-mapper">
   <realm name="wmtRealm" role-decoder="groups-to-roles"/>
</security-domain>
 
 
and groups-to-roles is the one provided by default:
 
 
<simple-role-decoder name="groups-to-roles" attribute="groups"/>
 
 
Is there anything wrong?

Note, if you use simple digest mapper hash should be stored in DB in base64 format - not in hex format [1].

Is it your case?

Can you for sake of simplicity try clear-password-mapper, which works with clear password stored in DB.

[1] [ELY-1444] Jdbc-realm with simple digest mapper - JBoss Issue Tracker

Thanks Martin. Yes I have HEX password and clear password mapper works if I pass the pwd stored in the db. Is there any way to specify HEX in elytron?

Anyway, to solve the roles issue, I just followed this link: quickstart/servlet-security at master · wildfly/quickstart · GitHub

Now (a part from the HEX problem that I think will be easy to solve) everything seem to work. User is authenticated and authorised. Only problem is that if I turn on mysql log, I see the login query is performed on every request!

What's going on?

Actually 3 queries for every request:

2017-11-29T15:53:32.399596Z   338 Query select password from user where email = 'alberto@myapp.com'

2017-11-29T15:53:32.400879Z   338 Query select role, 'Roles' from user_role_auth where email = 'alberto@myapp.com'

2017-11-29T15:53:32.402531Z   338 Query select password from user where email = 'alberto@myapp.com'

1. FORM authentication should be associated with http session, so there definitely should not be DB query for each request.

2. Two same queries seems also strange

Could you paste a log of two subsequent requests?

Btw. you can cache jdbc-realm results with caching-realm so that DB is not hit each time.

[1] Caching Migration - Latest WildFly Documentation - Project Documentation Editor

I have enabled TRACE for org.wildfly.security logger. This is a snippet of two requests as you asked:

2017-11-30 09:31:04,049 TRACE [org.wildfly.security] (default task-124) Principal assigning: [alberto@myapp.com], pre-realm rewritten: [alberto@myapp.com], realm name: [wmtRealm], post-realm rewritten: [alberto@myapp.com], realm rewritten: [alberto@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@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@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@myapp.com

2017-11-30 09:31:04,053 TRACE [org.wildfly.security] (default task-124) Role mapping: principal [alberto@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@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@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@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@myapp.com], pre-realm rewritten: [alberto@myapp.com], realm name: [wmtRealm], post-realm rewritten: [alberto@myapp.com], realm rewritten: [alberto@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@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@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@myapp.com

2017-11-30 09:31:07,022 TRACE [org.wildfly.security] (default task-125) Role mapping: principal [alberto@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@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@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@myapp.com] -> decoded roles [Administrator] -> realm mapped roles [Administrator] -> domain mapped roles [Administrator]

These seems like problems. I have created separate issues to track them:

[ELY-1455] DB query seen for each request using FORM mechanism. - JBoss Issue Tracker

[ELY-1456] Two same SQL queries seen during one authentication attempt - JBoss Issue Tracker

Could you attach to ELY-1455:

- your standalone.xml

- full TRACE log  (starting server + first 3 requests )

Yes and no. There are few bugs around: [ELY-1455] DB query seen for each request using programatic authentication - JBoss Issue Tracker

You can find full app and Wildfly configuration attached to that bug.