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 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.
It is available for most of the platforms and can be download for free from:
Remember to download the latest release.
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):
Find the line:
#listen_addresses = 'localhost' # what IP address(es) to listen on;
and change it to:
listen_addresses = '*' # what IP address(es) to listen on;
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):
host all all your.public.ip/32 md5
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 login to Postgres as a superuser:
sudo -u postgres psql
Now you can set you the secret password:
ALTER USER odoo WITH ENCRYPTED PASSWORD 'secret';
To apply the changes restart the PostgreSQL:
service postgresql restart
Add Server to 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 Oodoo and password is the one you set in the previous step.
Click save and it is done - you now have access to your Odoo DB from your pgAdmin client.
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: