Connect ofbiz to postgres using ssl

[Sumesh Acharya]

hello community,



I am getting this error :- `org.postgresql.util.PSQLException: FATAL: 
connection requires a valid client certificate` when i started the ofbiz 
application.



Also i tried accessing  the postgres server using psql tool by providing the 
required client.crt and client key and it seems to be working fine. 


I tried configuring the ssl by the following steps:-



```

1. Generate the Keystore with Client Certificate 

---

keytool -genkeypair -alias ofbiz-client -keyalg RSA -keysize 2048 -keystore 
/path/to/ofbiz.keystore -validity 365

---

2. Export the Client Certificate

---

keytool -export -alias ofbiz-client -file /path/to/ofbiz.crt -keystore 
/path/to/ofbiz.keystore

---

3. Obtain the PostgreSQL Server Certificate



Created postgres server certificate using openssl tool



4. Create the Truststore and Import the postgres Server Certificate

---

keytool -import -alias postgres-server -file /path/to/server.crt -keystore 
/path/to/ofbiz.truststore -storepass your-truststore-password

---

5. Configure PostgreSQL for SSL



Edit the postgresql.conf file to enable SSL:

---

ssl = on

ssl_cert_file = '/path/to/server.crt'

ssl_key_file = '/path/to/server.key'

ssl_ca_file = '/path/to/ca.crt'  # If using a CA

---



Edit the pg_hba.conf file to require SSL:

---

hostssl all all 0.0.0.0/0 cert

---



Restart PostgreSQL to apply the changes



6. Configure OFBiz to Use the Keystore and Truststore



Edit the framework/entity/configentityengine.xml file,

and add the following datasource configuration:

---

<datasource>

<inline-jdbc

                jdbc-driver="org.postgresql.Driver"

                
jdbc-uri="jdbc:postgresql://localhost:5432/ofbiz?ssl=true&amp;sslmode=require"

                isolation-level="ReadCommitted"

                pool-minsize="2"

                pool-maxsize="250"

                jdbc-username="ofbiz"

                jdbc-password="ofbiz"

                time-between-eviction-runs-millis="600000"/>

       

        <property name="javax.net.ssl.keyStore" 
value="/path/to/ofbiz.keystore"/>

        <property name="javax.net.ssl.keyStorePassword" 
value="your-keystore-password"/>

        <property name="javax.net.ssl.trustStore" 
value="/path/to/ofbiz.truststore"/>

        <property name="javax.net.ssl.trustStorePassword" 
value="your-truststore-password"/>

</datasource>

---



7. Restart OFBiz

Restart OFBiz to apply the new configuration.



```


Regards,

Sumesh