OHDSI Home | Forums | Wiki | Github

Atlas/Webapi Authentication setup failing

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.

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:

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

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

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.

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.

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

  1. settings.xml
  2. JdbcAuthRealm.java
  3. Atlas Page in chrome

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

TIA

  • ambuj

I have added this issue in git:

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

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.

@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

yes i was able to implement basic security.

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.

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

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.

@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?

@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.

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?


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.

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.

t