How to connect to Odoo DB using pgAdmin

Connect to Odoo database in minutes

So you have your Odoo up and running now and would like to connect to the DB itself? Not a problem. This tutorial will show you how to connect to your Odoo database using pgAdmin.

Why is this useful? Well, this feature is not just for fun. Odoo is MVC app so database(Model), front-end(View) and Controller are separated. That means you can allow the database specialist to work on the data without even knowing Odoo, HTML, xml and Python. Same with programmers - they can create a code that controls some functions without a knowledge of Postgresql or front-end. It is especially useful when it comes to data migration from Odoo-Odoo or Other Software to Odoo.

Remember that it is your responsibility to make a backup before any edits, also giving access to the database for unauthorised users is a threat from the security point of view as well as it might break the law.

Download pgAdmin

It is available for most of the platforms and can be download for free from:

https://www.pgadmin.org/download/

Remember to download the latest release.

Odoo text and image block
Odoo image and text block

Set your Postgresql.conf

Freshly installed Postgresql is configured the way that it can not receive any connections from the remote server. It is a good security feature. IN order to connect to the Postgres you will have to change the config file located in(Ubuntu 16,04, Postgres 9.5):

/etc/postgresql/9.5/main/postgresql.config

Find the line:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

and chenge it to:

listen_addresses = '*'         # what IP address(es) to listen on;
* means it will allow to listen for msgs from all IPs. You can change it if you like.
Removed # means it is no longer a comment(for those who are not familiar with).

Limit access to DB to your IP in pg_hba.conf

It might be a good idea to allow access to the specific IP addresses. From my personal experience, I see that you must check pg_hba if you are working on Odoo deployed by other company/developer.

In my case, Odoo's "Gold Partner" left a backdoor in the pg_hba allowing some IP's from Greece and India to have access to the Odoo DB that I had to work on. The only thing that secured me from them accessing the DB and using a "drop" was the fact that Amazon AWS offers another layer of security(security rules). If you experience that kind of situation inform your company about it so they can decide if they want to go to the police/court.

Start with the customer – find out what they want and give it to them.

To check it out simply edit the pg_hba file(same folder as the previous one):

nano pg_hba.conf
Add the following line to IPV4 connections:
host    all             all             your.public.ip/32         md5


Odoo text and image block
Odoo image and text block

Set a safe password for Odoo user

Some people are doing this, others not. I just wanted to guide you through this point so there is no error during the connection process.

Firstly log in to Postgres as a superuser:

sudo -u postgres psql

Now you can set you secret password:

ALTER USER odoo WITH ENCRYPTED PASSWORD 'secret';
\q

To apply the changes restart the PostgreSQL:

service postgresql restart

Add Server in pgAdmin

Now run pgAdmin. In the main window click "Add New Server". You will get the popup window "Create - Server". Fill in the name field, in the "Host" paste the server IP, the username is odoo and password is the one you set in the previous step.

Click save and it is done - you have now access to your Odoo DB from your pgAdmin client.

Odoo text and image block

Good to know...

References and more

Before changing any settings and password on your server please always make a backup or image. Always test new features on the test server/DB. I am not an expert, knowledge from the following links was used:

Connecting to PostgreSQL on Linux for the first time

How to change PostgreSQL user password?