Connecting Apache/James to an external Postgresql databese

Question

I will try to provide as much information to avoid misunderstanding. Please bare with me.

I am trying to run two docker containers, one running Apache/James and one PostgreSQL. The Apache/James instance is supposed to use the PostgreSQL instance. I have succeeded to run the setup using linagora/james-jpa-spring image of James. I followed the instruction that is provided by Apache/James official website.

According to the documentation, I just need to download PostgreSQL JDBC Driver from here and mount it in /conf/lib folder and update the database address and credentials in /conf/james-database.properties file. I followed the instruction and it works.

However since Linagora image seems to be too old I decided that I want to switch to one of the original Apache/James:jpa* images. (based on the docker hub, I feel :jpa is the right choice but if you feel I am mistaken, please let me know)

I therefore followed the same instruction (mount JDBC Driver and update james-database.properties) and I expected to have a smooth transition to JPA image but it wasn't.

I figured out that recent JPA images require a key-pair that is supposed to save as "keystore" file and mounted in /config/ folder. The keypair must be made with code "james72laBalle". this step is made successfully.

However I am getting one error that is running me creasy. James (not the container) says that it cannot reach to my PostgreSQL container. It say: There were errors initializing your configuration: <openjpa-3.2.0-r6f721f6 fatal user error> UserException: A connection could not be obtained for driver class "org.postgresql.Driver" and URL "jdbc:postgresql://post/jamesdb". You may have specified an invalid URL.

This is the full error message:

1) [Guice/ErrorInjectingConstructor]: <openjpa-3.2.0-r6f721f6 nonfatal general error> PersistenceException: There were errors initializing your configuration: <openjpa-3.2.0-r6f721f6 fatal user error> UserException: A connection could not be obtained for driver class "org.postgresql.Driver" and URL "jdbc:postgresql://post/jamesdb".  You may have specified an invalid URL.
    at DataSourceFactory.newConnectException(DataSourceFactory.java:271)
    at DataSourceFactory.newDataSource(DataSourceFactory.java:121)
    at JDBCConfigurationImpl.createConnectionFactory(JDBCConfigurationImpl.java:925)
    at JDBCConfigurationImpl.getConnectionFactory(JDBCConfigurationImpl.java:805)
    at java.base/NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/NativeMethodAccessorImpl.invoke(Unknown Source)
    at java.base/DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.base/Method.invoke(Unknown Source)
    at ConfigurationImpl.instantiateAll(ConfigurationImpl.java:331)
    at OpenJPAConfigurationImpl.instantiateAll(OpenJPAConfigurationImpl.java:1963)
    at AbstractBrokerFactory.makeReadOnly(AbstractBrokerFactory.java:665)
    at AbstractBrokerFactory.newBroker(AbstractBrokerFactory.java:207)
    at DelegatingBrokerFactory.newBroker(DelegatingBrokerFactory.java:166)
    at EntityManagerFactoryImpl.doCreateEM(EntityManagerFactoryImpl.java:282)
    at EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:201)

This is my james-database.properties file:

database.driverClassName=org.postgresql.Driver
database.url=jdbc:postgresql://post/jamesdb
database.username=admin
database.password=password
vendorAdapter.database=POSTGRESQL

I have also tried the followings for database url: database.url=jdbc:postgresql://post:5432/jamesdb database.url=jdbc:postgresql://post/jamesdb;create=true database.url=jdbc:postgresql://post:5432/jamesdb;create=true

No success!

I have made a docker network and both containers are running on that "docker network"

I am sure that my James container can access my Postgres container (when I try to run it with its internal Derby database and do not force it to connect to external database.) cause I have Docker-exec bash of James container and pinged my Postgres container (ping post) and it responded successfully. I even installed a psql database manager inside my James container and could successfully connect to the database on my Postgres container and manipulated the data on the database.

I simply do not understand why James says that it cannot see my PostgreSQL database while it should have access.

Answer 1

The following instructions, unless otherwise stated, are executed on the Host, not in the Container.

Edit /etc/hosts

/etc/hosts

append mail.james.local into 127.0.0.1

127.0.0.1   localhost mail.james.local

Project Tree

Project directory: james_jpa_image

build directory

mkdir -p my_root/conf
mkdir -p my_root/libs

my_root
├── conf
│   ├── james-database.properties (1)
│   └── keystore (2)
└── libs
    ├── checker-qual-3.42.0.jar (3)
    └── postgresql-42.7.3.jar   (4)

james-database.properties

database.driverClassName=org.postgresql.Driver
database.url=jdbc:postgresql://my-postgres-james:5432/jamesdb
database.username=james
database.password=james123

vendorAdapter.database=POSTGRESQL

openjpa.streaming=false

Create file keystore

in james_jpa_image/my_root/conf

run command:

keytool -genkeypair \
  -alias james \
  -keyalg RSA \
  -keysize 2048 \
  -storetype PKCS12 \
  -keystore keystore \
  -validity 3650

• set password: james72laBalle
• set CN, OU, O = james

Download JDBC Jar files

Download postgresql-42.7.3.jar and checker-qual-3.42.0.jar into my_root/libs

• postgresql-42.7.3.jar(https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.3/postgresql-42.7.3.jar)
• checker-qual-3.42.0.jar(https://repo1.maven.org/maven2/org/checkerframework/checker-qual/3.42.0/checker-qual-3.42.0.jar)

Create Docker Network

docker network create james-net

Create Docker Postgresql

docker run \
  --name my-postgres-james \
  --network james-net \
  -e POSTGRES_USER=james \
  -e POSTGRES_PASSWORD=james123 \
  -e POSTGRES_DB=jamesdb \
  -p 5432:5432 \
  -d \
  postgres

Create Docker James JPA

NOTE:

In Project directory: james_jpa_image

Run command:

docker run \
  --rm \
  --network james-net \
  -it \
  -v `pwd`/my_root/libs/postgresql-42.7.3.jar:/root/libs/postgresql-42.7.3.jar \
  -v `pwd`/my_root/libs/checker-qual-3.42.0.jar:/root/libs/checker-qual-3.42.0.jar \
  -v `pwd`/my_root/conf/james-database.properties:/root/conf/james-database.properties \
  -v `pwd`/my_root/conf/keystore:/root/conf/keystore \
  -p 143:143 \
  -p 25:25 \
  -p 4000:4000 \
  -p 465:465 \
  -p 587:587 \
  -p 80:80 \
  -p 8000:8000 \
  -p 993:993 \
  apache/james:jpa-3.8.1

Create James Domain and Users

Reference: https://james.apache.org/server/manage-webadmin.html

Create Domain

• james.local

curl -XPUT http://mail.james.local:8000/domains/james.local

Create Users

• user01@james.local : Passw0rd!
• user02@james.local : Passw0rd!
• user02@james.local : Passw0rd!

curl -XPUT http://mail.james.local:8000/users/user01@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"
  
curl -XPUT http://mail.james.local:8000/users/user02@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"
  
curl -XPUT http://mail.james.local:8000/users/user03@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"  

Check in Postgresql

Docker James Output

Mail Client Thunder Bird

Docker James Output

Everything is OK.

In James Server Terminal, Crtl + C , Stop James Container.

Delete postgres

docker stop my-postgres-james
docker container rm -f -v my-postgres-james

Redo again

Create Docker Postgresql

docker run \
  --name my-postgres-james \
  --network james-net \
  -e POSTGRES_USER=james \
  -e POSTGRES_PASSWORD=james123 \
  -e POSTGRES_DB=jamesdb \
  -p 5432:5432 \
  -d \
  postgres

Create Docker James JPA

NOTE:

In Project directory: james_jpa_image

Run command:

• remove --rm
• remove -it
• add -d
• add --name my-james-server

docker run \
  -d \
  --name my-james-server \
  --network james-net \
  -v `pwd`/my_root/libs/postgresql-42.7.3.jar:/root/libs/postgresql-42.7.3.jar \
  -v `pwd`/my_root/libs/checker-qual-3.42.0.jar:/root/libs/checker-qual-3.42.0.jar \
  -v `pwd`/my_root/conf/james-database.properties:/root/conf/james-database.properties \
  -v `pwd`/my_root/conf/keystore:/root/conf/keystore \
  -p 143:143 \
  -p 25:25 \
  -p 4000:4000 \
  -p 465:465 \
  -p 587:587 \
  -p 80:80 \
  -p 8000:8000 \
  -p 993:993 \
  apache/james:jpa-3.8.1

Create James Domain and Users

It has been mentioned above, so I won’t repeat it here.

• Step: Create Domain
• Step: Create Users

Use docker-compose

You can create your own docker-compose.yml. Start two containers using docker-compose.

docker-compose.yml

version: "3.9"

services:
  james:
    image: apache/james:jpa-3.8.1
    container_name: my-james-server
    networks:
      - james-net
    volumes:
      - ./my_root/libs/postgresql-42.7.3.jar:/root/libs/postgresql-42.7.3.jar
      - ./my_root/libs/checker-qual-3.42.0.jar:/root/libs/checker-qual-3.42.0.jar
      - ./my_root/conf/james-database.properties:/root/conf/james-database.properties
      - ./my_root/conf/keystore:/root/conf/keystore
    ports:
      - "143:143"
      - "25:25"
      - "4000:4000"
      - "465:465"
      - "587:587"
      - "80:80"
      - "8000:8000"
      - "993:993"
    restart: unless-stopped

  postgres:
    image: postgres
    container_name: my-postgres-james
    networks:
      - james-net
    environment:
      - POSTGRES_USER=james
      - POSTGRES_PASSWORD=james123
      - POSTGRES_DB=jamesdb
    ports:
      - "5432:5432"
    restart: unless-stopped

networks:
  james-net:
    driver: bridge

Run

docker-compose up -d

Create Domain and Users

curl -XPUT http://mail.james.local:8000/domains/james.local

curl -XPUT http://mail.james.local:8000/users/user01@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"
  
curl -XPUT http://mail.james.local:8000/users/user02@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"
  
curl -XPUT http://mail.james.local:8000/users/user03@james.local \
  -d '{"password":"Passw0rd!"}' \
  -H "Content-Type: application/json"

View Logs

docker logs -f my-james-server