View Categories

Create an Odoo PostgreSQL read-only user for reporting (Looker Studio, BI)

Table of Contents

Introduction #

Sometimes you want to connect tools like Google Looker Studio, Power BI, or another reporting platform directly to your Odoo database.
For security, it’s best to use a read-only PostgreSQL user: one that can query data but not change it. We will also apply remote access to only this particular PostgreSQL user.

Follow these steps on your Odoo server.

Step 1. Connect to your server over SSH #

First, connect to your Odoo server using an SSH client such as Termius or your terminal. If you have not done this before, see our guide: How to connect to your server with SSH.

Step 2. Open the PostgreSQL shell #

Once you are connected to your server, switch into PostgreSQL:

sudo -u postgres psql

This opens the PostgreSQL shell (psql) where you can run database commands.

Step 3. Create a read-only role #

Replace reporting_user and your_password with your own:

SET password_encryption='scram-sha-256';
CREATE ROLE reporting_user WITH LOGIN PASSWORD 'your_password';

Step 4. Grant access to the Odoo database #

Replace your_odoo_db with your actual Odoo database name. You can find the database name on the dashboard of your instance in My Cloudpepper.

GRANT CONNECT ON DATABASE "your_odoo_db" TO reporting_user;
\c "your_odoo_db"
GRANT USAGE ON SCHEMA public TO reporting_user;

Step 5. Grant read-only access to all tables #

\c "your_odoo_db"
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reporting_user;

Step 6. Make future tables readable #

New tables are usually created by the Odoo database owner (in most cases, odoo). Make sure your read-only user automatically gets access to those too.

ALTER DEFAULT PRIVILEGES FOR ROLE odoo IN SCHEMA public
GRANT SELECT ON TABLES TO reporting_user;

ALTER DEFAULT PRIVILEGES FOR ROLE odoo IN SCHEMA public
GRANT SELECT ON SEQUENCES TO reporting_user;

Exit PostgreSQL shell.

\q

Step 7. Allow remote connections #

By default PostgreSQL only listens on localhost. You need to allow connections from your BI tool’s IP address.

  1. Edit postgresql.conf (replace <version> with your PostgreSQL version):
nano /etc/postgresql/<version>/main/postgresql.conf

Find the line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

This makes PostgreSQL listen on all interfaces. Access is still controlled by pg_hba.conf and your firewall (next step), so only the IPs you allow there will be able to connect.

  1. Edit pg_hba.conf to allow your BI tool’s IP. Example for one IP (replace with your BI provider’s static IP):
nano /etc/postgresql/<version>/main/pg_hba.conf

Add a line for your BI tool’s IP or CIDR, and place it near the top of the file so it matches first:

host    your_odoo_db    reporting_user    <YOUR_BI_IP_OR_CIDR>    scram-sha-256

For Looker Studio, check Google’s documentation for current outbound IPs and allow those.

If your BI provider has many changing IPs and you prefer to keep the allowlist only in the firewall, you can use a broad rule here, but keep it scoped to the database and user and require TLS:

hostssl your_odoo_db    reporting_user    0.0.0.0/0               scram-sha-256

Only use the broad rule if your firewall is strictly limiting port 5432 to the BI IP ranges.

  1. Restart PostgreSQL:
sudo systemctl restart postgresql

Step 8. Restrict the firewall to BI IPs only #

Allow port 5432 only from the BI tool’s IPs and block everyone else.

sudo ufw allow from <YOUR_BI_IPv4> to any port 5432 proto tcp
# if you use IPv6 too:
sudo ufw allow from <YOUR_BI_IPv6> to any port 5432 proto tcp

# reload to apply
sudo ufw reload

Double-check there is no wide-open rule for 5432:

sudo ufw status numbered
# if you see "5432/tcp ALLOW IN Anywhere", delete it:
sudo ufw delete <rule-number>
What are your Feelings