WildFly Postgres DataSource remote connection-url ignored

user_2736851 0
Viewed 4739

I am running into some configuration troubles in setting up a Keycloak server in standalone clustered mode. Despite configuring the datasource to use a postgres database on {REMOTE_IP}, it is failing to start the server complaining that it cannot connect to localhost:5432.

I've been searching all over but I'm befuddled why the DataSource would try to connect to localhost when the connection-url is set to a remote host.

Is there any mistake in my configuration? How can I figure out why PG is trying to connect to localhost instead of {REMOTE_IP}

My setup is 1 Postgres database server and 2 Keycloak servers

I followed the installation instructions for using a relational database and have added the JDBC drivers v9.4.1212 for Postgres.

My DataSource configuration is as follows:

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:postgresql://{REMOTE_IP}:5432/keycloak</connection-url>
    <driver>postgresql</driver>
    <pool>
        <max-pool-size>20</max-pool-size>
    </pool>
    <security>
        <user-name>keycloak</user-name>
        <password>{PASSWORD}</password>
    </security>
</datasource>

The error message from the logs reads:

...
Caused by: org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:262)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:52)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:216)
    at org.postgresql.Driver.makeConnection(Driver.java:404)
    at org.postgresql.Driver.connect(Driver.java:272)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:86)
    at org.postgresql.ds.PGPoolingDataSource.getConnection(PGPoolingDataSource.java:309)
    at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:312)
    ... 43 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at org.postgresql.core.PGStream.<init>(PGStream.java:61)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)
    ... 52 more

I've verified that I can connect to postgres using psql

# psql -h {REMOTE_IP} keycloak keycloak
psql (9.5.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)
Type "help" for help.

keycloak=>
wildfly keycloak postgresql
2 Answers

a) Below is the snapshot of standalone.xml of wildfly 18.0.1.Final :

 <datasources>
                    <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                        <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
                        <driver>h2</driver>
                        <security>
                            <user-name>sa</user-name>
                            <password>sa</password>
                        </security>
                    </datasource>
                    <datasource jndi-name="java:jboss/jdbc/myapp-ds" pool-name="myapp-ds" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                        <connection-url>jdbc:postgresql://mydatabase.mycompany.com:5445/customerdb</connection-url>
                        <connection-property name="url">
                            jdbc:postgresql://mydatabase.mycompany.com:5445/customerdb
                        </connection-property>
                        <driver>postgresql</driver>
                        <pool>
                            <max-pool-size>20</max-pool-size>
                        </pool>
                        <security>
                            <user-name>postgres</user-name>
                            <password>password123$</password>
                        </security>
                    </datasource>
                    <drivers>
                        <driver name="h2" module="com.h2database.h2">
                            <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                        </driver>
                        <driver name="postgresql" module="org.postgresql">
                            <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
                            <datasource-class>org.postgresql.ds.PGPoolingDataSource</datasource-class>
                        </driver>
                    </drivers>
                </datasources>

b) Also make sure that following files are added in the path wildfly-18.0.1.Final\modules\org\postgresql\main (create if the folder does not exist):

  1. module.xml:

  2. postgresql-42.2.22.jar (download the jar file from internet)

Module.xml:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.postgresql">
 <resources>
 <resource-root path="postgresql-42.2.22.jar"/>
 </resources>
 <dependencies>
 <module name="javax.api"/>
 <module name="javax.transaction.api"/>
 </dependencies>
</module>
user_17399490
Related