User Mapping from existing database

Openfire
1

Dear Community!
I have read the documentation Openfire: Custom Database Integration Guide and also followed Load users from existing database - #6 by RoiDanton however in the admin panel i still do not see my users from the postgres database showing up in the users/group page. I have added the properties to the system properties and they are also in my openfire.xml. What am i missing? Why don’t my users show up? I am running it in a Docker Container with a network with the database container and from the database view i can see that it is connected to the database.

The xml:

<?xml version="1.0" encoding="UTF-8"?>

<!--
    This file stores bootstrap properties needed by Openfire.
    Property names must be in the format: "prop.name.is.blah=value"
    That will be stored as:
        <prop>
            <name>
                <is>
                    <blah>value</blah>
                </is>
            </name>
        </prop>

    Most properties are stored in the Openfire database. A
    property viewer and editor is included in the admin console.
-->
<!-- root element, all properties must be under this element -->
<jive> 
  <adminConsole> 
    <!-- Disable either port by setting the value to -1 -->  
    <port>9090</port>  
    <securePort>9091</securePort> 
  </adminConsole>  
  <locale>en</locale>  
  <!-- Network settings. By default, Openfire will bind to all network interfaces.
      Alternatively, you can specify a specific network interfaces that the server
      will listen on. For example, 127.0.0.1. This setting is generally only useful
       on multi-homed servers. -->  
  <!--
    <network>
        <interface></interface>
    </network>
    -->  
  <!--
        One time token to gain temporary access to the admin console.
    -->  
  <!--
    <oneTimeAccessToken>secretToken</oneTimeAccessToken>
    -->  
  <connectionProvider> 
    <className>org.jivesoftware.database.DefaultConnectionProvider</className> 
  </connectionProvider>  
  <database> 
    <defaultProvider> 
      <driver>org.postgresql.Driver</driver>  
      <serverURL>jdbc:postgresql://BackendPostgres:5432/app</serverURL>  
      <username encrypted="true">1ab47a1bd3eba04e2f4049885a23cb08b8e9e8f016fd0def3196bf4ba170915a</username>  
      <password encrypted="true">f5ab33efffa3271bd7db494b302a92cfaf469a1bd11bd11d96cf0d1dd4b71cac2cd1d23cdd99071b0a686ac49d775abc</password>  
      <testSQL>select 1</testSQL>  
      <testBeforeUse>true</testBeforeUse>  
      <testAfterUse>false</testAfterUse>  
      <testTimeout>PT0.5S</testTimeout>  
      <timeBetweenEvictionRuns>PT30S</timeBetweenEvictionRuns>  
      <minIdleTime>PT15M</minIdleTime>  
      <maxWaitTime>PT0.5S</maxWaitTime>  
      <minConnections>5</minConnections>  
      <maxConnections>25</maxConnections>  
      <connectionTimeout>1.0</connectionTimeout> 
    </defaultProvider> 
  </database>  
  <setup>true</setup>  
  <fqdn>local</fqdn>  
  <provider> 
    <auth> 
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> 
    </auth>  
    <user> 
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className> 
    </user> 
  </provider>  
  <jdbcAuthProvider> 
    <passwordSQL>SELECT password FROM user_entity WHERE identifier =? OR email =? OR phoneNumber =? OR username =?</passwordSQL>  
    <passwordType>bcrypt</passwordType> 
  </jdbcAuthProvider>  
  <jdbcUserProvider> 
    <loadUserSQL>SELECT name,email,identifier FROM user_entity WHERE identifier =? OR email =? OR phoneNumber =? OR username =?</loadUserSQL>  
    <userCountSQL>SELECT COUNT(*) FROM user_entity</userCountSQL>  
    <allUsersSQL>SELECT email FROM user_entity</allUsersSQL>  
    <searchSQL>SELECT email FROM user_entity WHERE</searchSQL>  
    <usernameField>email</usernameField>  
    <nameField>name</nameField>  
    <emailField>email</emailField> 
  </jdbcUserProvider> 
</jive>
2

Can you please share your database structure? Also, please have a look at the log files that are generated by Openfire. I would be surprised if they didn’t hold important clues as to what’s going wrong.

3

The only line in the logs i could find which could matter is this one:

2024.05.06 17:13:25.060 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'provider.auth.className' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.

and

org.jivesoftware.util.XMLProperties - XML Property 'provider.user.className' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.

The structure of my userEntity is the following:

REATE TABLE public.user_entity (
    id bigint NOT NULL,
    birthday timestamp(6) with time zone,
    created_on timestamp(6) with time zone,
    description character varying(255),
    email character varying(255) NOT NULL,
    identifier character varying(255) NOT NULL,
    is_account_non_locked boolean NOT NULL,
    is_enabled boolean NOT NULL,
    name character varying(255) NOT NULL,
    password character varying(255) NOT NULL,
    phone_number character varying(255),
    profile_image_name character varying(255),
    username character varying(255) NOT NULL,
    role_id bigint NOT NULL
);


ALTER TABLE public.user_entity OWNER TO postgres;

--
-- Name: user_entity_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.user_entity_seq
    START WITH 1
    INCREMENT BY 50
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.user_entity_seq OWNER TO postgres;

Edit:
I have changed the auth and user provider in the admin panel to the JDBC auth and user provider, which excluded me from access to the db and gave me followign errors:

org.jivesoftware.openfire.user.JDBCUserProvider - Unable to load JDBC driver: null
java.lang.NullPointerException: null
	at java.lang.Class.forName0(Native Method) ~[?:?]
	at java.lang.Class.forName(Class.java:375) ~[?:?]
	at org.jivesoftware.openfire.user.JDBCUserProvider.<init>(JDBCUserProvider.java:114) [xmppserver-4.8.1.jar:4.8.1]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) [?:?]
	at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
	at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) [?:?]
	at java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) [?:?]
	at jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) [?:?]
	at java.lang.Class.newInstance(Class.java:645) [?:?]
	at org.jivesoftware.openfire.user.UserManager.initProvider(UserManager.java:531) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.user.UserManager.<init>(UserManager.java:157) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.user.UserManager.<init>(UserManager.java:146) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.user.UserManager$UserManagerContainer.<clinit>(UserManager.java:96) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.user.UserManager.getInstance(UserManager.java:134) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.getUserManager(XMPPServer.java:1565) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.spi.PresenceManagerImpl.initialize(PresenceManagerImpl.java:521) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.initModules(XMPPServer.java:833) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:673) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:220) [xmppserver-4.8.1.jar:4.8.1]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) [?:?]
	at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
	at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) [?:?]
	at java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) [?:?]
	at jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) [?:?]
	at java.lang.Class.newInstance(Class.java:645) [?:?]
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92) [startup.jar:4.8.1]
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56) [startup.jar:4.8.1]

and

org.jivesoftware.openfire.auth.JDBCAuthProvider - Unable to load JDBC driver: null
java.lang.NullPointerException: null
	at java.lang.Class.forName0(Native Method) ~[?:?]
	at java.lang.Class.forName(Class.java:375) ~[?:?]
	at org.jivesoftware.openfire.auth.JDBCAuthProvider.<init>(JDBCAuthProvider.java:145) [xmppserver-4.8.1.jar:4.8.1]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) [?:?]
	at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
	at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) [?:?]
	at java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) [?:?]
	at jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) [?:?]
	at java.lang.Class.newInstance(Class.java:645) [?:?]
	at org.jivesoftware.openfire.auth.AuthFactory.initProvider(AuthFactory.java:79) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.auth.AuthFactory.<clinit>(AuthFactory.java:71) [xmppserver-4.8.1.jar:4.8.1]
	at jdk.internal.misc.Unsafe.ensureClassInitialized0(Native Method) ~[?:?]
	at jdk.internal.misc.Unsafe.ensureClassInitialized(Unsafe.java:1155) [?:?]
	at jdk.internal.reflect.UnsafeFieldAccessorFactory.newFieldAccessor(UnsafeFieldAccessorFactory.java:42) [?:?]
	at jdk.internal.reflect.ReflectionFactory.newFieldAccessor(ReflectionFactory.java:185) [?:?]
	at java.lang.reflect.Field.acquireFieldAccessor(Field.java:1132) [?:?]
	at java.lang.reflect.Field.getFieldAccessor(Field.java:1113) [?:?]
	at java.lang.reflect.Field.get(Field.java:425) [?:?]
	at org.jivesoftware.openfire.XMPPServer.scanForSystemPropertyClasses(XMPPServer.java:732) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:701) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:220) [xmppserver-4.8.1.jar:4.8.1]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) [?:?]
	at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
	at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) [?:?]
	at java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) [?:?]
	at jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) [?:?]
	at java.lang.Class.newInstance(Class.java:645) [?:?]
	at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92) [startup.jar:4.8.1]
	at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56) [startup.jar:4.8.1]

what confuses me a lot because why should it get a null pointer exception for classes that the documentation tells me i should use?

Edit 2:
I added a path to my openfire.xml as follows which cleared the NullPointerException, however, now i get an Exception that the database uses some SCRAM authentication, how can i implement this in openfire?

<JDBCProvider> 
    <driver>org.postgresql.Driver</driver>  
    <serverURL>jdbc:postgresql://BackendPostgres:5432/app</serverURL>  
    <username encrypted="true">1ab47a1bd3eba04e2f4049885a23cb08b8e9e8f016fd0def3196bf4ba170915a</username>  
    <password encrypted="true">f5ab33efffa3271bd7db494b302a92cfaf469a1bd11bd11d96cf0d1dd4b71cac2cd1d23cdd99071b0a686ac49d775abc</password>  
    <testSQL>select 1</testSQL>  
    <testBeforeUse>true</testBeforeUse>  
    <testAfterUse>false</testAfterUse>  
    <testTimeout>PT0.5S</testTimeout>  
    <timeBetweenEvictionRuns>PT30S</timeBetweenEvictionRuns>  
    <minIdleTime>PT15M</minIdleTime>  
    <maxWaitTime>PT0.5S</maxWaitTime>  
    <minConnections>5</minConnections>  
    <maxConnections>25</maxConnections>  
    <connectionTimeout>1.0</connectionTimeout> 
  </JDBCProvider>

Exception:

org.jivesoftware.openfire.auth.JDBCAuthProvider - Exception in JDBCAuthProvider
org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but no password was provided.
	at org.postgresql.core.v3.ConnectionFactoryImpl.lambda$doAuthentication$4(ConnectionFactoryImpl.java:843) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.core.v3.AuthenticationPluginManager.withPassword(AuthenticationPluginManager.java:82) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:840) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:207) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:262) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.Driver.makeConnection(Driver.java:446) ~[postgresql-42.7.2.jar:42.7.2]
	at org.postgresql.Driver.connect(Driver.java:298) ~[postgresql-42.7.2.jar:42.7.2]
	at java.sql.DriverManager.getConnection(DriverManager.java:681) ~[java.sql:?]
	at java.sql.DriverManager.getConnection(DriverManager.java:252) ~[java.sql:?]
	at org.jivesoftware.openfire.auth.JDBCAuthProvider.getConnection(JDBCAuthProvider.java:336) ~[xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvider.java:364) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.auth.JDBCAuthProvider.authenticate(JDBCAuthProvider.java:229) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:184) [xmppserver-4.8.1.jar:4.8.1]
	at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:260) [xmppserver-4.8.1.jar:4.8.1]
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) [apache-jsp-9.0.52.jar:9.0.52]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590) [jetty-servlet-api-4.0.6.jar:?]
	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1419) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665) [jetty-servlet-10.0.18.jar:10.0.18]
	at com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:65) [sitemesh-2.4.2.jar:?]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:73) [xmppserver-4.8.1.jar:4.8.1]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:49) [xmppserver-4.8.1.jar:4.8.1]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:174) [xmppserver-4.8.1.jar:4.8.1]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:292) [xmppserver-4.8.1.jar:4.8.1]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:210) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.jivesoftware.admin.ContentSecurityPolicyFilter.doFilter(ContentSecurityPolicyFilter.java:53) [xmppserver-4.8.1.jar:4.8.1]
	at org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:202) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1635) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:598) [jetty-security-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484) [jetty-servlet-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:149) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.Server.handle(Server.java:563) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287) [jetty-server-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314) [jetty-io-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100) [jetty-io-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53) [jetty-io-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194) [jetty-util-10.0.18.jar:10.0.18]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149) [jetty-util-10.0.18.jar:10.0.18]
	at java.lang.Thread.run(Thread.java:833) [?:?]
2024.05.06 17:58:48.151 e[33mWARN e[m [Jetty-QTP-AdminConsole-32]: org.jivesoftware.admin.LoginLimitManager - Failed admin console login attempt by admin from 172.17.0.1
4

Something seems to go wrong with the database driver that should be used when trying to reach the database that holds your custom scheme: the driver might not be specified, or the specified driver might not be on the classpath (the rationale of having to provide this driver is that the ‘external’ database that you’re integrating with might be different from the database that Openfire is normally using).

The driver is specified with this property: jdbcProvider.driver (which is equivalent to a snippet like this in the XML file: <jdbcProvider><driver>blahblah</driver></jdbcProperty>)

There is a full example of this documented in the ‘database connection settings’ section of the Custom Database Integration Guide.

I hope this helps!

5

Thank you very much, i have also updated my first answer with a new exception thats coming.

6

This is a Postgres-specific error, that I’m not very familiar with. Have you provided the credentials that you want to use for connecting to your database in the connectionString property? I would think that should solve this issue.

7

Ok i think it is almost working. The SCRAM exception just vanished randomly. I wanted to add some user from the database to be an admin and i used the approach with the adminprovider. As the passwords are enrypted via bcrypt i stated this as well, however, i still cannot login. What is the rremaining problem?

<JDBCProvider> 
    <driver>org.postgresql.Driver</driver>  
    <serverURL>jdbc:postgresql://BackendPostgres:5432/app</serverURL>  
    <username encrypted="true">1ab47a1bd3eba04e2f4049885a23cb08b8e9e8f016fd0def3196bf4ba170915a</username>  
    <password encrypted="true">f5ab33efffa3271bd7db494b302a92cfaf469a1bd11bd11d96cf0d1dd4b71cac2cd1d23cdd99071b0a686ac49d775abc</password>  
    <testSQL>select 1</testSQL>  
    <testBeforeUse>true</testBeforeUse>  
    <testAfterUse>false</testAfterUse>  
    <testTimeout>PT0.5S</testTimeout>  
    <timeBetweenEvictionRuns>PT30S</timeBetweenEvictionRuns>  
    <minIdleTime>PT15M</minIdleTime>  
    <maxWaitTime>PT0.5S</maxWaitTime>  
    <minConnections>5</minConnections>  
    <maxConnections>25</maxConnections>  
    <connectionTimeout>1.0</connectionTimeout> 
  </JDBCProvider>  
  <Provider> 
    <admin> 
      <className>org.jivesoftware.openfire.admin.JDBCAdminProvider</className> 
    </admin>  
    <user> 
      <className>org.jivesoftware.openfire.user.JDBCAdminProvider</className> 
    </user> 
  </Provider>  
  <JDBCAdminProvider> 
    <getAdminsSQL>SELECT username FROM user_entity WHERE role_id=1</getAdminsSQL>  
    <passwordSQL>SELECT password FROM user_entity WHERE username=?</passwordSQL>  
    <passwordType>bcrypt</passwordType> 
  </JDBCAdminProvider>  
  <jdbcUserProvider> 
    <loadUserSQL>SELECT name,email FROM user_entity WHERE username=?</loadUserSQL>  
    <allUsersSQL>SELECT username FROM user_entity</allUsersSQL>  
    <searchSQL>SELECT username FROM user_entity WHERE</searchSQL>  
    <usernameField>username</usernameField> 
  </jdbcUserProvider>
8

I think you’re erroneously combining the ‘admin’ provider (which generates a list of usernames that are considered to have administrative permissions) and the ‘auth’ provider (which handles credentials for users).

JDBC Admin Provider doesn’t have a passwordType (but Auth provider does).

9

Ah ok makes sense. Following the documentation i have added the AuthProvider like this:

<Provider> 
    <admin> 
      <className>org.jivesoftware.openfire.admin.JDBCAdminProvider</className> 
    </admin>  
    <user> 
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className> 
    </user>  
    <auth> 
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> 
    </auth> 
  </Provider>  
  <jdbcAuthProvider> 
    <passwordSQL>SELECT password FROM user_entity WHERE username=?</passwordSQL>
<passwordType>bcrypt</passwordType> 
  </jdbcAuthProvider>

I also made sure that the Query works, which it does, however, i can still not login. Can i use bcrypt this way or do i have to state it in a different way? Whatalso confuses me is that sometimes, openfire automatically updates the xml with removing the passwordType line.

1 Like
10

I’m not seeing anything obviously wrong with that - but it has been ages since I’ve last used this. Try using ‘plain’, to see if you can isolate the problem?

11

Hm ok, in the logs, however, i get these errors again:

2024.05.06 19:28:16.857 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcAuthProvider.passwordSQL' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2024.05.06 19:28:16.857 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcAuthProvider.passwordType' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
024.05.06 19:28:15.971 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcUserProvider.loadUserSQL' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2024.05.06 19:28:15.971 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcUserProvider.allUsersSQL' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2024.05.06 19:28:15.972 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcUserProvider.searchSQL' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.
2024.05.06 19:28:15.972 e[33mWARN e[m [main]: org.jivesoftware.util.XMLProperties - XML Property 'jdbcUserProvider.usernameField' differs from what is stored in the database.  Please make property changes in the database instead of the configuration file.

So i guess, unless i state all these properties in the admin console which i cannot access now, stating them in the .xml alone will not work. So i guess openfire is not able to send the correct sql to load the user and its password and to work with its type. So i guess i have to set up the server again but now i will state all these properties directly in the admin console i hope it should work then. I will keep you updated tomorrow. Thank you a lot for your help so far.

1 Like
12

If you have access to Openfire’s database, you could try updating the values in the ofProperty tables directly. You might need to restart Openfire for changes to take effect.

13

How would i gain access? OR what would i have to mount to a volume to get access since its running in a Docker Container?

14

I can’t really advise you on that. I’d expect many docker containers to allow some kind of command line client to connect. Maybe you can use that?

15

Do you know where the database is stored in openfires files or are these properties stored in the same database i configured to search for users?

16

If you’re using org.jivesoftware.database.DefaultConnectionProvider then this table will be somewhere in the database that also is configured in openfire.xml (your first post suggests that this is the case).

If you’re using the ‘embedded’ provider, then this is a HSQL-db instance who’s files live in the embedded-db/ directory of the Openfire installation.

1 Like
17

Thank you i found it and i think it works now, at least i can pass the login screen but after this the SCRAM exception occurs but i will make another topic for this. Thank you a lot!