PostgreSQL Read-Only User

Create a read-only database user for reporting, BI tools, and analytics without risking data modification.

Why Use a Read-Only User?

Create Read-Only User

# Connect to the database container
docker exec -it YOUR_ENV_ID_db psql -U odoo YOUR_DATABASE_NAME

-- Create read-only user
CREATE USER readonly_user WITH PASSWORD 'secure_password_here';
 
-- Grant connect permission
GRANT CONNECT ON DATABASE your_database_name TO readonly_user;
 
-- Grant schema usage
GRANT USAGE ON SCHEMA public TO readonly_user;
 
-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
 
-- Grant SELECT on all future tables (important!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;
 
-- Grant SELECT on sequences (for auto-increment columns)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON SEQUENCES TO readonly_user;

-- List user permissions
\du readonly_user
 
-- Test as readonly user (exit and reconnect)
\q

psql -U readonly_user -d your_database_name -h localhost

-- This should work
SELECT COUNT(*) FROM res_partner;
 
-- This should fail with "permission denied"
DELETE FROM res_partner WHERE id = 1;

Connection Details for BI Tools

Connection String

postgresql://readonly_user:your_password@server-ip:5432/database_name

Common BI Tool Settings

Using PgBouncer (Recommended)

If PgBouncer is enabled:

SSH Tunnel (Recommended for Security)

Instead of exposing PostgreSQL to the internet, use an SSH tunnel:

Create Tunnel

# On your local machine or BI server
ssh -L 5432:localhost:5432 user@your-oecsh-server -N

Connect via Tunnel

Your BI tool connects to:

• Host: localhost
• Port: 5432
• (The tunnel forwards to the remote PostgreSQL)

Persistent Tunnel with autossh

# Install autossh
apt install autossh
 
# Create persistent tunnel
autossh -M 0 -f -N -L 5432:localhost:5432 user@your-oecsh-server

Connecting Popular BI Tools

Metabase

1. Go to Admin → Databases → Add database
2. Select PostgreSQL
3. Enter connection details:
   • Display name: Odoo Production (Read-Only)
   • Host: Your server IP
   • Port: 5432
   • Database: Your database name
   • Username: readonly_user
   • Password: Your password
4. Click Save

Power BI

1. Get Data → PostgreSQL database
2. Server: your-server-ip:5432
3. Database: Your database name
4. Data Connectivity: Import
5. Enter readonly_user credentials

Tableau

1. Connect → PostgreSQL
2. Server: Your server IP
3. Port: 5432
4. Database: Your database name
5. Username: readonly_user
6. Authentication: Username and Password
7. Require SSL: Recommended for production

Grafana

1. Configuration → Data Sources → Add data source
2. Select PostgreSQL
3. Host: your-server-ip:5432
4. Database: Your database name
5. User: readonly_user
6. Password: Your password
7. SSL Mode: require (recommended)
8. Click Save & Test

Restrict Access to Specific Tables

For additional security, you can limit which tables the user can access:

-- Revoke access to all tables first
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM readonly_user;
 
-- Grant access only to specific tables
GRANT SELECT ON TABLE res_partner TO readonly_user;
GRANT SELECT ON TABLE sale_order TO readonly_user;
GRANT SELECT ON TABLE sale_order_line TO readonly_user;
GRANT SELECT ON TABLE account_move TO readonly_user;
GRANT SELECT ON TABLE account_move_line TO readonly_user;
GRANT SELECT ON TABLE product_product TO readonly_user;
GRANT SELECT ON TABLE product_template TO readonly_user;

Row-Level Security (Advanced)

For multi-company setups, restrict access to specific company data:

-- Enable row-level security on a table
ALTER TABLE sale_order ENABLE ROW LEVEL SECURITY;
 
-- Create policy for specific company
CREATE POLICY company_isolation ON sale_order
  FOR SELECT
  TO readonly_user
  USING (company_id = 1);
 
-- Force the policy even for table owner
ALTER TABLE sale_order FORCE ROW LEVEL SECURITY;

Common Tables for Reporting

Sales & CRM

Accounting

Inventory

HR

Security Best Practices

1. Use strong passwords - At least 16 characters, random
2. Rotate credentials - Change passwords periodically
3. Use SSL - Always require SSL for remote connections
4. Limit IP access - Restrict in firewall to BI server IPs only
5. Audit access - Enable PostgreSQL logging for the user
6. Use read replica - Connect BI tools to replica, not primary (Odoo 18+)
7. SSH tunnel - Prefer SSH tunnel over exposing port 5432

Troubleshooting

"Permission denied for table"

Cause: User doesn't have SELECT permission on the table.

Solution:

GRANT SELECT ON TABLE table_name TO readonly_user;

"Connection refused"

Cause: PostgreSQL not accepting external connections.

Solution:

1. Check postgresql.conf: listen_addresses = '*'
2. Check pg_hba.conf for the user's IP
3. Check firewall allows port 5432

"Password authentication failed"

Cause: Wrong password or user doesn't exist.

Solution:

-- Verify user exists
SELECT * FROM pg_user WHERE usename = 'readonly_user';
 
-- Reset password if needed
ALTER USER readonly_user WITH PASSWORD 'new_password';

Query Performance Issues

Cause: Large queries affecting production database.

Solutions:

1. Use read replica instead of primary (Odoo 18+)
2. Add indexes for frequently queried columns
3. Limit query time: SET statement_timeout = '30s';
4. Schedule reports during off-peak hours

Revoking Access

To remove the read-only user:

-- Revoke all permissions
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly_user;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readonly_user;
REVOKE CONNECT ON DATABASE your_database FROM readonly_user;
 
-- Drop the user
DROP USER readonly_user;

Need Help?

For database access configuration:

• Email: support@oec.sh
• Include: Odoo version, BI tool name, connection errors