Configuring a datasource with PostgreSQL and JBoss/WildFly

PostgreSQL remains one of the premier open-source relational databases in enterprise architectures. Connecting modern WildFly 41 (and JBoss EAP) deployments to PostgreSQL requires configuring high-performance connection pools, verifying driver dependencies under Jakarta EE, and adapting network configurations for containerized environments such as Red Hat OpenShift.

In this tutorial, you will learn how to provision a test PostgreSQL database, install the latest JDBC driver, configure production connection pools with JBoss CLI, integrate with Hibernate 6, and deploy to OpenShift using cloud-native configuration best practices.

1. Starting a PostgreSQL Instance

For local development and testing, run the current version of PostgreSQL using Docker or Podman:

podman run --name postgres-dev -d \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=mypassword123 \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 postgres:17-alpine

Verify that PostgreSQL is ready to accept client connections:

podman exec -it postgres-dev pg_isready -U postgres

2. Installing the PostgreSQL JDBC Driver Module on WildFly 41

The official PostgreSQL JDBC driver is actively maintained on Maven Central. Download the current release (version 42.7.x or newer):

curl -O https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.4/postgresql-42.7.4.jar

Launch the JBoss CLI and connect to your running server instance:

$JBOSS_HOME/bin/jboss-cli.sh --connect

Step A: Register the JBoss Module

Add the core module under the org.postgres namespace:

module add --name=org.postgres \
  --resources=/path/to/postgresql-42.7.4.jar \
  --dependencies=jakarta.api,jakarta.transaction.api

Step B: Register the JDBC Driver in the Datasources Subsystem

Declare the driver definition referencing the newly installed module:

/subsystem=datasources/jdbc-driver=postgresql:add(\
  driver-name="postgresql",\
  driver-module-name="org.postgres",\
  driver-class-name="org.postgresql.Driver",\
  driver-xa-datasource-class-name="org.postgresql.xa.PGXADataSource")

3. Creating and Testing the Datasource

Now, define the connection pool pointing to your PostgreSQL instance. In local standalone mode, target port 5432:

data-source add \
  --name=PostgresPool \
  --jndi-name=java:jboss/datasources/PostgresDS \
  --driver-name=postgresql \
  --connection-url=jdbc:postgresql://localhost:5432/appdb \
  --user-name=postgres \
  --password=mypassword123 \
  --min-pool-size=10 \
  --max-pool-size=50 \
  --pool-prefill=true \
  --validate-on-match=true \
  --background-validation=false \
  --check-valid-connection-sql="SELECT 1"

Validating the Connection Pool

Execute an on-demand test from CLI to ensure credentials and network bindings are functional:

/subsystem=datasources/data-source=PostgresPool:test-connection-in-pool

Expected output:

{
    "outcome" => "success",
    "result" => [true]
}

Resulting standalone.xml Definition

The CLI commands above generate the following XML schema under the datasources subsystem:

<subsystem xmlns="urn:jboss:domain:datasources:7.2">
    <datasources>
        <datasource jndi-name="java:jboss/datasources/PostgresDS" pool-name="PostgresPool" enabled="true" use-java-context="true">
            <connection-url>jdbc:postgresql://localhost:5432/appdb</connection-url>
            <driver>postgresql</driver>
            <pool>
                <min-pool-size>10</min-pool-size>
                <max-pool-size>50</max-pool-size>
                <prefill>true</prefill>
            </pool>
            <security>
                <user-name>postgres</user-name>
                <password>mypassword123</password>
            </security>
            <validation>
                <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                <validate-on-match>true</validate-on-match>
            </validation>
        </datasource>
        <drivers>
            <driver name="postgresql" module="org.postgres">
                <driver-class>org.postgresql.Driver</driver-class>
                <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>

4. PostgreSQL in a Red Hat OpenShift Context

Running WildFly 41 on Red Hat OpenShift requires adapting datasource configurations for container orchestration, Kubernetes Service discovery, and Secret management.

A. Service Discovery & Pod Networking

In OpenShift, databases are rarely accessed over localhost. They run inside independent Pods exposed via a Kubernetes Service (e.g., postgresql-service). Your connection URL should target the cluster DNS name:

jdbc:postgresql://postgresql-service.my-project.svc.cluster.local:5432/appdb

B. Provisioning with Galleon Feature-Packs (Cloud-Native Build)

Rather than manually installing JARs into container images at runtime, the modern cloud-native approach builds lightweight WildFly images using the WildFly Maven Plugin and Galleon layers:

<plugin>
    <groupId>org.wildfly.plugins</groupId>
    <artifactId>wildfly-maven-plugin</artifactId>
    <version>5.0.1.Final</version>
    <configuration>
        <feature-packs>
            <feature-pack>
                <location>org.wildfly:wildfly-galleon-pack:41.0.0.Final</location>
            </feature-pack>
            <!-- Include PostgreSQL driver Galleon feature pack -->
            <feature-pack>
                <location>org.wildfly:wildfly-datasources-galleon-pack:7.0.0.Final</location>
            </feature-pack>
        </feature-packs>
        <layers>
            <layer>cloud-server</layer>
            <layer>postgresql-datasource</layer>
        </layers>
    </configuration>
</plugin>

C. Injecting Environment Variables via OpenShift Secrets

When using the postgresql-datasource Galleon layer or the official WildFly OpenShift images, datasources are configured automatically at container startup through standard environment variables:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: wildfly-app
spec:
  replicas: 2
  template:
    spec:
      containers:
        - name: wildfly
          image: image-registry.openshift-image-registry.svc:5000/my-project/wildfly-app:latest
          env:
            - name: POSTGRESQL_DATASOURCE
              value: "PostgresDS"
            - name: POSTGRESQL_DATABASE
              value: "appdb"
            - name: POSTGRESQL_SERVICE_HOST
              value: "postgresql-service"
            - name: POSTGRESQL_SERVICE_PORT
              value: "5432"
            - name: POSTGRESQL_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: database-user
            - name: POSTGRESQL_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: database-password

This approach eliminates hardcoded database passwords from deployment descriptors and simplifies secret rotation via OpenShift's native RBAC.

5. Hibernate 6 & Jakarta Persistence Configuration

In WildFly 41 (aligned with Jakarta EE 10/11), the persistence engine is powered by Hibernate ORM 6.x. The legacy version-specific dialects (such as PostgreSQL95Dialect or PostgreSQL10Dialect) are deprecated.

For modern PostgreSQL installations (versions 11 through 17), specify the unified dialect in your persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence 
                                 https://jakarta.ee/xml/ns/persistence/persistence_3_1.xsd"
             version="3.1">

    <persistence-unit name="ProductionPU" transaction-type="JTA">
        <description>Enterprise PostgreSQL Persistence Unit</description>
        <jta-data-source>java:jboss/datasources/PostgresDS</jta-data-source>

        <properties>
            <!-- Unified Hibernate 6 Dialect -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            
            <!-- Schema Generation Strategy (validate in production) -->
            <property name="jakarta.persistence.schema-generation.database.action" value="validate"/>
            
            <!-- Performance Diagnostics -->
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.jdbc.batch_size" value="30"/>
            <property name="hibernate.order_inserts" value="true"/>
            <property name="hibernate.order_updates" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

6. Troubleshooting & Production Tuning

A. SCRAM-SHA-256 vs. Ident Authentication Failures

If you encounter authentication exceptions such as:

Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

Modern PostgreSQL releases (14+) default to SCRAM-SHA-256 password encryption rather than MD5. Verify authentication settings inside your database's pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

Ensure that passwords configured via CLI or OpenShift Secrets match the encrypted SCRAM credential stored within PostgreSQL.

B. Sizing the WildFly Connection Pool vs. PostgreSQL max_connections

A frequent production issue in container clusters occurs when aggregate connection pool allocations exceed PostgreSQL's concurrency ceiling. The formula to keep in mind is:

$$\text{Total Connections} = (\text{OpenShift Pod Replicas}) \times (\text{max-pool-size}) + \text{Superuser Reserves}$$

If your WildFly deployment scales to 4 pods on OpenShift and each pod defines max-pool-size=30, you will require at least $4 \times 30 = 120$ connections. Update your postgresql.conf accordingly:

max_connections = 150
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB

Summary & Best Practices

• Use Jakarta EE Module Names: In WildFly 41, link modules against jakarta.transaction.api and jakarta.api instead of legacy javax namespaces.
• Rely on the Unified Dialect: Specify org.hibernate.dialect.PostgreSQLDialect for modern PostgreSQL (11 through 17).
• Automate with Galleon on OpenShift: Leverage wildfly-datasources-galleon-pack to package drivers at build time rather than manual post-boot scripting.
• Secure Credentials: Isolate sensitive database passwords using OpenShift Secrets or WildFly Elytron credential stores.