Atlas/Webapi Authentication setup failing

Shweta · April 2, 2019, 2:32pm

I am getting following error:

stack trace:
below is the stack tract for the error I am getting:

below is the stack tract for the error I am getting:
 
webapi    | 30-Apr-2019 20:30:20.873 INFO [main] org.apache.coyote.AbstractProtocol.start Starting ProtocolHandler ["http-nio-8080"]
webapi    | 30-Apr-2019 20:30:20.910 INFO [main] org.apache.coyote.AbstractProtocol.start Starting ProtocolHandler ["ajp-nio-8009"]
webapi    | 30-Apr-2019 20:30:20.939 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 34422 ms
webapi    | 2019-04-30 20:30:40.972 ERROR http-nio-8080-exec-1 org.ohdsi.webapi.shiro.realms.JdbcAuthRealm -  - There was a SQL error while authenticating user [ohdsi]
webapi    | org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
webapi    |           at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:56)
webapi    |           at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
webapi    |           at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2324)
webapi    |           at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1403)
webapi    |           at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1385)
webapi    |           at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)
webapi    |           at org.ohdsi.webapi.shiro.realms.JdbcAuthRealm.createPreparedStatement(JdbcAuthRealm.java:111)
webapi    |           at org.ohdsi.webapi.shiro.realms.JdbcAuthRealm.getPasswordForUser(JdbcAuthRealm.java:90)
webapi    |           at org.ohdsi.webapi.shiro.realms.JdbcAuthRealm.doGetAuthenticationInfo(JdbcAuthRealm.java:66)
webapi    |           at org.apache.shiro.realm.AuthenticatingRealm.getAuthenticationInfo(AuthenticatingRealm.java:568)
webapi    |           at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doMultiRealmAuthentication(ModularRealmAuthenticator.java:219)
webapi    |           at org.apache.shiro.authc.pam.ModularRealmAuthenticator.doAuthenticate(ModularRealmAuthenticator.java:269)
webapi    |           at org.apache.shiro.authc.AbstractAuthenticator.authenticate(AbstractAuthenticator.java:198)
webapi    |           at org.apache.shiro.mgt.AuthenticatingSecurityManager.authenticate(AuthenticatingSecurityManager.java:106)
webapi    |           at org.ohdsi.webapi.shiro.lockout.LockoutWebSecurityManager.login(LockoutWebSecurityManager.java:66)
webapi    |           at org.apache.shiro.subject.support.DelegatingSubject.login(DelegatingSubject.java:256)
webapi    |           at org.apache.shiro.web.filter.authc.AuthenticatingFilter.executeLogin(AuthenticatingFilter.java:53)
webapi    |           at org.ohdsi.webapi.shiro.filters.JdbcAuthFilter.onAccessDenied(JdbcAuthFilter.java:58)
webapi    |           at org.apache.shiro.web.filter.AccessControlFilter.onAccessDenied(AccessControlFilter.java:133)
webapi    |           at org.apache.shiro.web.filter.AccessControlFilter.onPreHandle(AccessControlFilter.java:162)
webapi    |           at org.apache.shiro.web.filter.PathMatchingFilter.isFilterChainContinued(PathMatchingFilter.java:203)
webapi    |           at org.apache.shiro.web.filter.PathMatchingFilter.preHandle(PathMatchingFilter.java:178)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:131)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
webapi    |           at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
webapi    |           at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
webapi    |           at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118)
webapi    |           at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
webapi    |           at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
webapi    |           at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
webapi    |           at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
webapi    |           at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
webapi    |           at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
webapi    |           at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
webapi    |           at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
webapi    |           at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
webapi    |           at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:105)
webapi    |           at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
webapi    |           at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
webapi    |           at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:115)
webapi    |           at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:59)
webapi    |           at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:90)
webapi    |           at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
webapi    |           at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:108)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
webapi    |           at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
webapi    |           at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
webapi    |           at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
webapi    |           at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
webapi    |           at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
webapi    |           at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
webapi    |           at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
webapi    |           at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
webapi    |           at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
webapi    |           at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
webapi    |           at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
webapi    |           at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
webapi    |           at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
webapi    |           at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
webapi    |           at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
webapi    |           at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
webapi    |           at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
webapi    |           at java.lang.Thread.run(Thread.java:748)
webapi    | 2019-04-30 20:30:41.038 INFO http-nio-8080-exec-1 com.odysseusinc.logging.LoggingService -  - Log in failed for user login = ohdsi

settings.xml

<security.provider>AtlasRegularSecurity</security.provider>
<security.origin>*</security.origin>
<security.maxLoginAttempts>3</security.maxLoginAttempts>
<security.duration.initial>10</security.duration.initial>
<security.duration.increment>10</security.duration.increment>
<security.db.datasource.url>jdbc:postgresql://localhost:5432/ohdsi</security.db.datasource.url>
<security.db.datasource.driverClassName>org.postgresql.Driver</security.db.datasource.driverClassName>
<security.db.datasource.schema>ohdsi</security.db.datasource.schema>
<security.db.datasource.username>ohdsi</security.db.datasource.username>
<security.db.datasource.password>ohdsi</security.db.datasource.password>
<security.db.datasource.authenticationQuery>select password from ${security.db.datasource.schema}.demo_security where email = ?</security.db.datasource.authenticationQuery>

login database settings:

CREATE TABLE ohdsi.demo_security
(
    email character varying(255) COLLATE pg_catalog."default",
    password character varying(255) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;
 
ALTER TABLE ohdsi.demo_security
    OWNER to ohdsi;

tagging @Chris_Knoll and @pavgra have you seen this issue before? This happens as soon as Atlas is loaded it tries to access the /WebAPI/user/me adnd gives 401. when user trie to login it gives bad credentials issue.

Shweta · May 16, 2019, 1:39pm

I was able to fix this issue. For some reason the code was not getting the authentication query. As a workaround, I had to hardcode the authentication query below:

github.com

OHDSI/WebAPI/blob/master/src/main/java/org/ohdsi/webapi/shiro/realms/JdbcAuthRealm.java#L110


        String message = "There was a SQL error while authenticating user [" + username + "]";
        if (log.isErrorEnabled()) {
            log.error(message, e);
        }
        result = null;
    }
    return result;
}


private PreparedStatement createPreparedStatement(Connection conn, String username) throws SQLException {
    PreparedStatement ps = conn.prepareStatement(this.authenticationQuery);
    ps.setString(1, username);
    return ps;
}


}

Chris_Knoll · May 16, 2019, 6:45pm

This would be a good issue to raise in https://github.com/ohdsi/webapi/issues.

ambuj · May 17, 2019, 12:14pm

what is the change you have made in the file. I am trying to use basic security set up feature and getting bad credentials error on chrome console.

please help.

Thank you

Chris_Knoll · May 17, 2019, 8:51pm

I believe he put the value found in the config file:
<security.db.datasource.authenticationQuery>select password from ${security.db.datasource.schema}.demo_security where email = ?</security.db.datasource.authenticationQuery>
directly in the Java code:

   PreparedStatement ps = conn.prepareStatement("select password from ${security.db.datasource.schema}.demo_security where email = ?");

Of course, he replaced ${security.db.datasource.schema} with the correct value of his security database schema.

This is not really a great workaround, this should be tracked as an issue on github and one of the security experts can check why this doesn’t seem to be populating correctly.

Shweta · May 17, 2019, 11:56pm

I waited a month for security expert to help me. I emailed details to you and security expert. No one responded.
This is not true I had to modify WebAPI the code couple of more places, as Once we get pass this point where user gets authenticated, it keeps going in infinity loop searching for user in AD even if you don’t set it up. Once you resolve this.
The vocabulary permissions are now giving me issue. Even if you are authenticated it will give you don’t have access to this feature issue. I am still figuring it out how to resolve.

ambuj · May 20, 2019, 9:40am

I implemented the changes in Java file to hard code the verification script and it didn’t worked out. Attaching the errors I received on chrome along with the changes I made in settings.xml and JdbcAuthRealm.java

settings.xml

Screenshot%20from%202019-05-20%2015-03-171600×900 140 KB
JdbcAuthRealm.java

Screenshot%20from%202019-05-20%2015-04-571600×900 94.6 KB
Atlas Page in chrome

Screenshot%20from%202019-05-20%2015-09-251600×900 159 KB

Please let me know if anything is wrong here and why I am receiving these errors.

TIA

ambuj

ambuj · May 20, 2019, 12:07pm

I have added this issue in git:

github.com/OHDSI/WebAPI

WebAPI Basic Security - wiki updates required

opened 12:05PM - 20 May 19 UTC

closed 05:07PM - 18 Dec 20 UTC

ambuj369

bug

Hello Team, I am implementing the basic security feature in Atlas by followin…g the contents mentioned in following path - https://github.com/OHDSI/WebAPI/wiki/Basic-Security-Configuration https://github.com/OHDSI/WebAPI/wiki/Atlas-Security Whenever. I try to access WebAPI/source/sources , I get the following error on webpage : ![WebAPIError](https://user-images.githubusercontent.com/47858365/58020119-dc812100-7b24-11e9-9a08-adaa97e9a063.jpg) As per my understanding, if we don't see the source/source_daimon details through above link, something is not correct. However, I moved ahead with Atlas and got the errors as shown below: ![AtlasERROR](https://user-images.githubusercontent.com/47858365/58020173-08040b80-7b25-11e9-8582-041d8e2c1ea0.png) P.S: If I keep security.provider as DisabledSecurity and rest of the settings as per Basic Security, I am able to open WebAPI/source/sources page, but Atlas throws another error 403. I believe similar issue is being faced by multiple users. Please look into this issue as there is not any workaround or so and update if anything is being missed or not implemented as per guidelines. Thank you - Ambuj

Chris_Knoll · May 20, 2019, 1:47pm

Thanks @ambuj, I’m sure someone will look into the issue in git and respond there. Thanks for your patience!

Shweta · May 21, 2019, 1:55pm

Yes, this is also happened to me. when I looked into WebAPI logs it keep on going in loop, saying user authentication failed for LDAP even if you don’t configure LDAP. I remember I had to make more changes in code.
@ambuj did you do hard refresh or clear the cookies that WebAPI creates before login in again? the WebAPI needs to be recompile the code and re-deployed. I remember whenever I made changes I had to delete all the cookies.

ambuj · May 21, 2019, 2:03pm

@Shweta
Yes, I recompiled WebAPI after adding changes to settings.xml and deleted cookies too. Nothing worked out so far. I guess this issue will be taken care in next WebAPI release or may be we are entering some details wrong which either we are unable to identify or code is not able to read it.
Fingers crossed.
Were you able to implement basic security for OHDSI tools?

Thanks
-ambuj

Shweta · May 21, 2019, 2:07pm

yes i was able to implement basic security.

Shweta · May 21, 2019, 2:09pm

like i said the query is not the issue there is also issue in AtlasRegularSecurity code and couple more spots… I have implemented WebAPI and Atlas in Docker.
Do you have the SQL logs? you can check what query the WebAPI is executing at the backend. The sql query also helped me to rule out the issue.

ambuj · May 21, 2019, 2:10pm

Ok, so you made changes to the existing Java code to achieve it?

Shweta · May 21, 2019, 2:11pm

yes as the issue is in WebAPI. When I was tracing the Atlas frontend. It was sending all the right information to WebAPI. But the WebAPI was failing to execute it.

Shweta · May 21, 2019, 2:14pm

@ambuj Are you using SQL or Postgres? As I see your setting.xml has both specified. For security it says postgres.
does the ohdsi has permissions to read from webapi.demo_security?

ambuj · May 21, 2019, 2:16pm

@Shweta
I am using postgres. webapi-sql is showing up as it is the part of new settings.xml available in settings.xml.

So while building .war file i am passing the profile id as webapi-postgresql.

ambuj · May 21, 2019, 2:24pm

Thats a good catch, it was nowhere mentioned in the documentation to have a new user role to be created for demo_security.
Do we have to create a new user/login role named ohdsi and assign it to demo_security for read/write?

Shweta · May 21, 2019, 2:40pm

you mentioned the security database username and passoword. I used my datasource(CDM data) username (i.e admin) and password here and not the new user. I have given the admin user permission to read and write the security database table.
did you add following in the demo_security table:

when the login is successful new entry is created in sec_role, and you can start giving permissions from there.

ambuj · May 21, 2019, 2:49pm

Got the point, I am trying that now. And yes, I have added the same details for username and password in demo_security table.
Also, in settings.xml, I am using the username and password for ohdsi_admin_user.