In this article, we will explore how to securely connect Odoo to a PostgreSQL server using SSL certificates. By utilizing SSL certificates for authentication, we can enhance security beyond the traditional username and password method.

We will provide a comprehensive guide on generating the necessary SSL certificates and configuring PostgreSQL to use these certificates. This secure method of authentication is particularly beneficial when the PostgreSQL server and the Odoo server are on different networks, ensuring data integrity and protection against unauthorized access.

Generating SSL Certificates

To set up an SSL connection between Odoo and PostgreSQL, you'll need to generate the necessary SSL certificates. This includes creating a Certificate Authority (CA), server certificate, and client certificate. Here’s a step-by-step guide:

Certificate Authority Generation (CA)

First, generate a private key for your CA:

openssl genrsa -out ca.key 4096

Then, create a CA certificate using the private key:

openssl req -new -x509 -key ca.key -out ca.crt -days 3650

You’ll be prompted to enter information about your organization. Fill this out as needed.

Generate SSL certificates for Postgresql Server

Generate a private key for the PostgreSQL server:

openssl genrsa -out server.key 4096

Create a certificate signing request (CSR):

openssl req -new -key server.key -out server.csr

Sign the server certificate with your CA

openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365

Set the correct permissions for the server key:

chmod 600 server.key

Generate SSL certificates for Client 

Generate a private key for the client:

openssl genrsa -out client.key 4096

Create a CSR for the client:

openssl req -new -key client.key -out client.csr

Sign the client certificate with your CA:

openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt -days 365

Configurations

Configuring PostgreSQL for SSL

Now that we have the necessary certificates, we need to configure PostgreSQL to use SSL.

cp server.crt server.key ca.crt /var/lib/postgresql/data

Edit the postgresql.conf file to enable SSL

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'ca.crt'

Do not forget to add the listening address to "*"

listen_addresses = '*' 

Edit the pg_hba.conf file to require SSL for client connections:

hostssl     all       all      0.0.0.0/0    cert

Restart PostgreSQL to apply the chsanges:

sudo systemctl restart postgresql

Configuring Odoo to Use SSL

To configure Odoo to connect to PostgreSQL over SSL, you need to specify the SSL parameters in the Odoo configuration file (usually odoo.conf):

[options]
db_host = postgres
db_name = odoo
db_user = odoo
db_sslmode = verify-full

Odoo is not passing all the db_[VALUE] to psycopg2 so you might find on the web that you can use db_sslkey and db_sslcert but it is actually not working. 

The other option to pass information to the psycogp2 library is to pass environment variables.

export PGSSLCERT=/home/odoo/client.crt
export PGSSLKEY=/home/odoo/client.key
export PGSSLROOTCERT=/home/odoo/ca.crt 

Replace the paths with the actual paths to your client certificates and the CA certificate.

Conclusion

By following these steps, you can set up a secure SSL connection between Odoo and PostgreSQL, enhancing the security of your data, especially when the servers are on different networks. This method of authentication not only helps in protecting sensitive data but also ensures compliance with security standards.

For more detailed information, you can refer to the official PostgreSQL and Odoo documentation. If you encounter any issues, the community forums and support channels are valuable resources for troubleshooting.