ESE not connecting to postgres database

brianstjean · July 30, 2023, 11:16pm

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.

Daria_H · July 31, 2023, 9:31am

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:

Ensure that the specified database exists and is accessible at the configured port.
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

brianstjean · July 31, 2023, 1:19pm

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)

brianstjean · July 31, 2023, 1:30pm

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

false

true

postgres-backend

true

postgres-backend

true

Daria_H · July 31, 2023, 1:45pm

Hi @brianstjean ,

Sorry, your XML is in unreadable form:

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

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

brianstjean · July 31, 2023, 2:18pm

Dasha,

Yes it is showing the hivemq user and the current_user.

Brian

brianstjean · July 31, 2023, 2:26pm

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)

Daria_H · July 31, 2023, 3:45pm

Hey @brianstjean ,

Is HiveMQ broker running in a Docker container or directly on a Linux machine?
Are there any special characters in the PASSWORD?
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

brianstjean · July 31, 2023, 4:48pm

Thanks Dasha,

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

Brian

Daria_H · August 1, 2023, 7:18am

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

Topic		Replies	Views
Enterprise Security Extension SQL Auth HiveMQ Extensions	1	448	June 12, 2023
Cannot remove DISABLED file from ESE HiveMQ Extensions	5	451	August 20, 2023
How can I force reload hivemq ESE HiveMQ Extensions	9	496	September 26, 2023
ESE Start Up Guide - expired links	1	305	September 26, 2022
I have 2 issues HiveMQ Commercial Offerings	7	763	September 16, 2022

ESE not connecting to postgres database

Related topics