ESE not connecting to postgres database

HiveMQ Extensions
1

I am trying to connect the hivemq ese to my postgres database and am getting the following error. The config file seems to match what you recommend. Here is the error list:

2023-07-30 23:13:49,812 INFO - Starting HiveMQ Enterprise Security Extension.
2023-07-30 23:13:49,826 INFO - Access log is written to /opt/hivemq/log/access/access.log.
2023-07-30 23:13:49,836 DEBUG - Loaded driver jar in /opt/hivemq/extensions/hivemq-enterprise-security-extension/drivers/jdbc/postgresql-42.6.0.jar
2023-07-30 23:13:49,839 DEBUG - Driver class org.postgresql.Driver found in Thread context class loader hse.aj.a@1034c6ff
2023-07-30 23:13:49,840 DEBUG - com.hivemq.extensions.ese.postgres.postgres-backend - configuration:
2023-07-30 23:13:49,841 DEBUG - allowPoolSuspension…false
2023-07-30 23:13:49,841 DEBUG - autoCommit…true
2023-07-30 23:13:49,841 DEBUG - catalog…none
2023-07-30 23:13:49,841 DEBUG - connectionInitSql…none
2023-07-30 23:13:49,841 DEBUG - connectionTestQuery…none
2023-07-30 23:13:49,841 DEBUG - connectionTimeout…9000
2023-07-30 23:13:49,842 DEBUG - dataSource…none
2023-07-30 23:13:49,842 DEBUG - dataSourceClassName…none
2023-07-30 23:13:49,842 DEBUG - dataSourceJNDI…none
2023-07-30 23:13:49,842 DEBUG - dataSourceProperties…{password=, socketTimeout=9}
2023-07-30 23:13:49,842 DEBUG - driverClassName…“org.postgresql.Driver”
2023-07-30 23:13:49,842 DEBUG - exceptionOverrideClassName…none
2023-07-30 23:13:49,842 DEBUG - healthCheckProperties…{}
2023-07-30 23:13:49,842 DEBUG - healthCheckRegistry…none
2023-07-30 23:13:49,842 DEBUG - idleTimeout…600000
2023-07-30 23:13:49,842 DEBUG - initializationFailTimeout…1
2023-07-30 23:13:49,843 DEBUG - isolateInternalQueries…false
2023-07-30 23:13:49,843 DEBUG - jdbcUrl…jdbc:postgresql://localhost:5432/hivemq
2023-07-30 23:13:49,843 DEBUG - keepaliveTime…0
2023-07-30 23:13:49,843 DEBUG - leakDetectionThreshold…0
2023-07-30 23:13:49,843 DEBUG - maxLifetime…1800000
2023-07-30 23:13:49,843 DEBUG - maximumPoolSize…10
2023-07-30 23:13:49,843 DEBUG - metricRegistry…com.codahale.metrics.MetricRegistry@1cbb3d3b
2023-07-30 23:13:49,843 DEBUG - metricsTrackerFactory…none
2023-07-30 23:13:49,843 DEBUG - minimumIdle…10
2023-07-30 23:13:49,843 DEBUG - password…
2023-07-30 23:13:49,843 DEBUG - poolName…“com.hivemq.extensions.ese.postgres.postgres-backend”
2023-07-30 23:13:49,843 DEBUG - readOnly…false
2023-07-30 23:13:49,843 DEBUG - registerMbeans…false
2023-07-30 23:13:49,843 DEBUG - scheduledExecutor…none
2023-07-30 23:13:49,843 DEBUG - schema…none
2023-07-30 23:13:49,843 DEBUG - threadFactory…internal
2023-07-30 23:13:49,843 DEBUG - transactionIsolation…default
2023-07-30 23:13:49,843 DEBUG - username…“hivemq”
2023-07-30 23:13:49,843 DEBUG - validationTimeout…5000
2023-07-30 23:13:49,844 INFO - com.hivemq.extensions.ese.postgres.postgres-backend - Starting…
2023-07-30 23:13:49,846 WARN - Registered driver with driverClassName=org.postgresql.Driver was not found, trying direct instantiation.
2023-07-30 23:13:49,846 DEBUG - Driver class org.postgresql.Driver found in Thread context class loader hse.aj.a@1034c6ff
2023-07-30 23:13:49,864 DEBUG - com.hivemq.extensions.ese.postgres.postgres-backend - Failed to create/setup connection: FATAL: Ident authentication failed for user “hivemq”
2023-07-30 23:13:49,864 DEBUG - com.hivemq.extensions.ese.postgres.postgres-backend - Cannot acquire connection from data source
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:263)
at org.postgresql.Driver.makeConnection(Driver.java:443)
at org.postgresql.Driver.connect(Driver.java:297)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(Source.java:138)
at com.zaxxer.hikari.pool.PoolBase.newConnection(Source.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(Source.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(Source.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(Source.java:561)
at com.zaxxer.hikari.pool.HikariPool.(Source.java:100)
at com.zaxxer.hikari.HikariDataSource.(Source.java:81)
at hse.aj.a.a(Source.java:135)
at hse.ah.a.h(Source.java:91)
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
at hmq.eL.f.run(Source.java:39)
at com.codahale.metrics.InstrumentedScheduledExecutorService$InstrumentedRunnable.run(Source.java:241)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Suppressed: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:267)
… 22 common frames omitted
2023-07-30 23:13:50,865 ERROR - com.hivemq.extensions.ese.postgres.postgres-backend - Exception during pool initialization.
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:263)
at org.postgresql.Driver.makeConnection(Driver.java:443)
at org.postgresql.Driver.connect(Driver.java:297)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(Source.java:138)
at com.zaxxer.hikari.pool.PoolBase.newConnection(Source.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(Source.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(Source.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(Source.java:561)
at com.zaxxer.hikari.pool.HikariPool.(Source.java:100)
at com.zaxxer.hikari.HikariDataSource.(Source.java:81)
at hse.aj.a.a(Source.java:135)
at hse.ah.a.h(Source.java:91)
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
at hmq.eL.f.run(Source.java:39)
at com.codahale.metrics.InstrumentedScheduledExecutorService$InstrumentedRunnable.run(Source.java:241)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Suppressed: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:267)
… 22 common frames omitted
2023-07-30 23:13:50,866 ERROR - Could not instantiate hikari connection jdbc:postgresql://localhost:5432/hivemq.
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: Ident authentication failed for user “hivemq”
at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(Source.java:596)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(Source.java:582)
at com.zaxxer.hikari.pool.HikariPool.(Source.java:100)
at com.zaxxer.hikari.HikariDataSource.(Source.java:81)
at hse.aj.a.a(Source.java:135)
at hse.ah.a.h(Source.java:91)
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
at hmq.eL.f.run(Source.java:39)
at com.codahale.metrics.InstrumentedScheduledExecutorService$InstrumentedRunnable.run(Source.java:241)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:263)
at org.postgresql.Driver.makeConnection(Driver.java:443)
at org.postgresql.Driver.connect(Driver.java:297)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(Source.java:138)
at com.zaxxer.hikari.pool.PoolBase.newConnection(Source.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(Source.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(Source.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(Source.java:561)
… 13 common frames omitted
Suppressed: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:267)
… 22 common frames omitted
2023-07-30 23:13:50,866 WARN - Could not create datasource for realm postgres-backend
2023-07-30 23:13:50,866 ERROR - The HiveMQ Enterprise Security Extension could not start because of a configuration error: No realm could be started.
2023-07-30 23:13:50,866 INFO - Startup of extension with id “hivemq-enterprise-security-extension” was prevented by the extension itself, reason: No realm could be started. Extension will be disabled.

2

Hi @brianstjean

Welcome to the HiveMQ Community! We are pleased to see your interest in MQTT and the HiveMQ broker. Your presence is greatly appreciated.

In your recent message, you mentioned that your config file appears to align with our recommendations. However, we were unable to locate the specific recommendations you are referring to since there was no link provided. To assist you better, could you kindly elaborate on the recommendations you followed?

Additionally, we noticed that you haven’t shared the details of your configuration. To better understand your setup and offer more accurate guidance, could you please provide more information about your current configuration?

The log you provided indicates an authentication error for the user “hivemq” while attempting to connect to your PostgreSQL database. To troubleshoot this issue effectively, we recommend verifying the following:

  1. Ensure that the specified database exists and is accessible at the configured port.
  2. Confirm the existence of the “hivemq” user and validate that you are using the correct password for authentication.

By addressing these points, we hope to resolve the authentication problem and get your setup up and running smoothly.

If you have any further questions or encounter any other issues, please don’t hesitate to reach out. Our team is here to assist you.

Best regards,

Dasha from the HiveMQ Team

3

Dasha,

Thanks for your reply, I have attached my config.xml. I have confirmed that the user and database are running and I was able to run the postgressql_create.sql and then the insert sql files to create default permissions and default roles.

Thanks,

Brian

(Attachment config.xml is missing)

4

The system won’t let me send attachements so here is the text dump of the config.xml file. I am able to connect to the database using pgAdmin4 and look at the tables created.

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

<enterprise-security-extension

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

xsi:noNamespaceSchemaLocation=“config.xsd”

version=“1”>

postgres-backend

true

POSTGRES

hivemq

localhost

5432

hivemq

PASSWORD

postgres-backend

postgres-backend

false

true

postgres-backend

postgres-backend

true

true

postgres-backend

postgres-backend

true

true

5

Hi @brianstjean ,

Sorry, your XML is in unreadable form:

image
image1442×1348 86.1 KB

I suggest you quote your XML with triple ticks ``` to make it readable :slight_smile:

Anyway, since you have pgAdmin, you can simply go to the menu, open the Query Tool (Menu → Tools → Query tool), and run the following query to check which user:

SELECT current_user;

Check if your config.xml and your pgAdmin use the same user/password or different ones.

I hope this helps.

Best regards,
Dasha from HiveMQ Team

6

Dasha,

Yes it is showing the hivemq user and the current_user.

Brian

7

Yes the current user and password are correct. Here is the error log that is shown when I remove the ‘DISABLED’ file.

2023-07-31 14:19:27,759 DEBUG - username…“hivemq”

2023-07-31 14:19:27,759 DEBUG - validationTimeout…5000

2023-07-31 14:19:27,760 INFO - com.hivemq.extensions.ese.postgres.postgres-backend - Starting…

2023-07-31 14:19:27,764 WARN - Registered driver with driverClassName=org.postgresql.Driver was not found, trying direct instantiation.

2023-07-31 14:19:27,764 DEBUG - Driver class org.postgresql.Driver found in Thread context class loader hse.aj.a@d25730e

2023-07-31 14:19:27,800 DEBUG - com.hivemq.extensions.ese.postgres.postgres-backend - Failed to create/setup connection: FATAL: Ident authentication failed for us

er “hivemq”

2023-07-31 14:19:27,800 DEBUG - com.hivemq.extensions.ese.postgres.postgres-backend - Cannot acquire connection from data source

org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user “hivemq”

at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:693)

at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:203)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)

at org.postgresql.jdbc.PgConnection.(PgConnection.java:263)

at org.postgresql.Driver.makeConnection(Driver.java:443)

at org.postgresql.Driver.connect(Driver.java:297)

at com.zaxxer.hikari.util.DriverDataSource.getConnection(Source.java:138)

at com.zaxxer.hikari.pool.PoolBase.newConnection(Source.java:359)

at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(Source.java:201)

at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(Source.java:470)

at com.zaxxer.hikari.pool.HikariPool.checkFailFast(Source.java:561)

8

Hey @brianstjean ,

  1. Is HiveMQ broker running in a Docker container or directly on a Linux machine?
  2. Are there any special characters in the PASSWORD?
  3. Verify that your connection to the db succeeds when using the same username and password as in the config.xml. You can use psql command (you need to test it on the same machine / container as the HiveMQ broker):
PGPASSWORD="PASSWORD" 
psql --host localhost -U hivemq -d hivemq -p 5432

Best,
Dasha from HiveMQ Team

9

Thanks Dasha,

I discovered it was an issues with the pg_hba.conf file. I have it working now.

Brian

10

Hi @brianstjean,

Well done! If you need any further assistance, feel free to reach out.

Thank you and have a great day!

Best regards,
Dasha from HiveMQ Team