Configure one Postgresql DB in a domain setup !?

Configuring the server
1

I am trying to have a single Postgresql DB for a Master -> Slave setup.
I start the master without problems and the postgresql runs without any problems (on master@192.168.5.11).
From the second VM (the slave) I can psql -h 192.168.5.11 -U keycloak -W without any problems (the connection is there)
My datasource configuration:

 <datasource jndi-name="java:jboss/datasources/PostgresDS" pool-name="PostgresDS" enabled="true" use-java-context="true" use-ccm="true" statistics-enabled="true">
                    <connection-url>jdbc:postgresql://192.168.5.11/keycloak</connection-url>
                    <connection-property name="url">jdbc:postgresql://192.168.5.11/keycloak</connection-property>
                    <driver>postgresql</driver>
                    <driver-class>org.postgresql.Driver</driver-class>
                    <pool>
                        <max-pool-size>20</max-pool-size>
                    </pool>
                    <security>
                        <user-name>keycloak</user-name>
                        <password>keycloak</password>
                    </security>
                    <validation>
                            <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                            <background-validation>true</background-validation>
                            <background-validation-millis>60000</background-validation-millis>
                    </validation>
                </datasource>
                <drivers>
                    <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>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                </drivers>

I know you will say that the config has too much information, but I started from the config in the documentation that didn’t work for me (all on KeycloakDS without connection-property, driver-class, datasourc-class, validation) to all combinations from stackoverflow and other blogs.

The error:
[Server:server-two] Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "keycloak" [Server:server-two] at org.postgresql@42.2.14//org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:525) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:146) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:197) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:217) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.Driver.makeConnection(Driver.java:458) [Server:server-two] at org.postgresql@42.2.14//org.postgresql.Driver.connect(Driver.java:260) [Server:server-two] at org.jboss.ironjacamar.jdbcadapters@1.4.20.Final//org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:321) [Server:server-two] ... 57 more [Server:server-two] [Server:server-two] 22:00:50,207 FATAL [org.keycloak.services] (ServerService Thread Pool -- 63) java.lang.RuntimeException: Failed to connect to database

Remember the psql direct connection from slave-vm works (with password and user). Only from datasource I get that the password auth failed.
I’ve tried to encode base64 also the password, but didn’t help me in any way.
Is there something that I’m missing?

2

I’ve changed the pg_hba.conf settings on slave-vm to:

#"local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident (previously password)
# IPv6 local connections:
host    all             all             ::1/128                 ident (previously password)
host   all            keycloak        192.168.5.11  255.0.0.0   password

and now I get this error:
Caused by: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "keycloak"
which makes me think that the driver is trying to login to the postgres db on the slave and not the one from the master.
The question is why? because in the settings I have the ip of the master VM:
<connection-url>jdbc:postgresql://192.168.5.11/keycloak</connection-url> <connection-property name="url">jdbc:postgresql://192.168.5.11/keycloak</connection-property>.
What do I miss?

Thanks

3

Ok, I found the problem.
In the domain.xml on the master the connection-url settings were done for localhost and the slave actually uses the domain.xml from master not from slave VM.

That’s that and I hope this lesson will help others too.

1 Like
4

Hey edwint, I’m trying to do a similar configuration, could you please show how did your

<connection-url> tag ended up like?

5

It was exactly like in the example:

<connection-url>jdbc:postgresql://192.168.5.11/keycloak</connection-url>

instead of jdbc:postgresql://localhost/keycloak

1 Like