OpenERP client fails to connect with a restricted pg_hba.conf

[Ian Beardslee]

Using OpenERP 6.0.3 the client fails with "Could not connect to server" if the server's pg_hba.conf restricts access to 'template1'.

This bug may only affect people in a shared environment with multiple apps with their own databases hosted on the same instance of postgres. In a managed environment, it'd be expected that database access would be controlled via pg_hba.conf

Initially we had /etc/postgresql/8.4/main/pg_hba.conf configured to allow the 'openerp' user access to only the 'openerpdb' database.

 #/etc/postgresql/8.4/main/pg_hba.conf
 host openerpdb openerp 192.168.94.21/32 md5

The openerp-server.log file showed ..

[2011-09-15 13:58:04,013][postgres] ERROR:db.connection_pool:Connection to the database failed
Traceback (most recent call last):
  File "/usr/share/pyshared/openerp-server/sql_db.py", line 303, in borrow
    result = psycopg2.connect(dsn=dsn, connection_factory=PsycoConnection)
OperationalError: FATAL: no pg_hba.conf entry for host "192.168.94.21", user "openerp", database "template1", SSL on
FATAL: no pg_hba.conf entry for host "192.168.94.21", user "openerp", database "template1", SSL off

It seems that as part of connecting to the database the applications connect to a database before enumerating the databases owned by the 'openerp' postgres user.

The immediate fix is to adjust the /etc/postgresql/8.4/main/pg_hba.conf to allow access to template1

 #/etc/postgresql/8.4/main/pg_hba.conf
 host openerpdb openerp 192.168.94.21/32 md5
 host template1 openerp 192.168.94.21/32 md5

[Ian Beardslee (ibeardslee)]

And now for some further complications .. the above fix is than ideal. Because 'template1' are used as a template for new databases, it has been recommended to us that connecting to the 'postgres' database would be preferred to connecting to the 'template1' (or 'template0') databases. Once again this is likely to only be an issue in a shared/managed environment.

A change to '/usr/share/pyshared/openerp-server/service/web_services.py' fixes that (patch to come).

 sed -i s/db_connect\(\'template1\'\)/db_connect\(\'postgres\'\)/ /usr/share/pyshared/openerp-server/service/web_services.py

But now to make that more complicated, creating a new database requires access to the template0 .. and of course the new database would need to be enabled in the pg_hba.conf once it is created. But remember that this is bug is probably isolated to a shared environment where creating databases is a controlled process where the DBA would be working with the OpenERP user to create required databases.

[Ian Beardslee (ibeardslee)]

And now rather than using 'template1' or 'postgres', a more ideal solution would be to connect to an existing database owned by the openerp user. But a new setup probably doesn't have an existing openerp owned database, so should the fall back to the postgres database.

/usr/share/pyshared/openerp-server/service/web_services.py should be able to check for a configured default database (eg in /etc/openerp-server.conf) and connect to that before enumerating any further databases owned by the 'openerp' user.

[Denis Seleznyov (xy2)]

Agreed with last comment. Many database administrators prefer to restrict access as much as possible and create production databases by hands rather than allow other people to do that.

[Ian Beardslee (ibeardslee)]

That looks like it might do the job. Although when I go through and check that it does work in my test environment I'll change the

   def exp_initial_dbname(self):
       dbname = tools.config['db_name']
       if not dbname:
           return 'template1'
       return dbname

to ..

   def exp_initial_dbname(self):
       dbname = tools.config['db_name']
       if not dbname:
           return 'postgres'
       return dbname

I do notice that there is still a

         db = sql_db.db_connect('template1')

Just after that first block of changes. Considering that the rest of them seem to be removed, is that connection to template1 still planned?

[Denis Seleznyov (xy2)]

1. postgres is a "DBA's database" and template1 is more common to initial connect
2. template1 should be used in case of new database creation (if user is allowed to create database). You still see db_connect('template1') in function _create_empty_database()

[Olivier Dony (Odoo) (odo-openerp)]

Hi,

Thanks for analyzing this issue and providing possible solutions. We'll probably come up with a solution along these lines!

Notes to dev:
- config['db_name'] could contain a comma-separated list of database names
- all exp_*() methods are meant to be exposed in RPC, which might not be desired here.
- to be discussed with odo/vmt

[Antony Lesuisse (OpenERP) (al-openerp)]

I think this will be fixed by:

https://code.launchpad.net/~openerp-dev/openobject-server/trunk-template-postgres-al/+merge/92376

We will merge it after the 6.1 release to mitigate regression risks

[Marleny Lopez (marlenylopez)]

la solución a este problema es que en el archivo pg_hba.conf de postgresql se debe cambiar la linea:

local all all trust

 se debe quedar de esta manera:

local all all ident

y asunto solucionado.......