Connection to postgis database fails if PGSERVICE or PGPASS config files are used.

[Anafi]

The connection to the postgis database assumes that there is always a host, port, database name, password and username provided e.g. 'host=localhost port=5432 username=postgres password=password dbname=mydb' . However, in the cases where:

• a PGSERVICE file is configured, the connection string should only include the service name
  • e.g. 'service=service_name'
• a PGPASS file is configured, the connection string should not include any password
  • e.g. 'host=localhost port=5432 username=postgres dbname=mydb'

The functions below deal with these issues

1. function to get the available databases setting in QGIS. It returns a dictionary of that kind:

   {‘db': {'username': ‘postgres', 'host': ‘111.111.111.1', 'password': ‘xxxxxxx', 'port': '5432'},
   ‘db_w_pgpass': {'username': 'postgres', 'host': 'localhost', 'port': '5432'},
   ‘db_w_pg_service': {'service': 'service_name’} }

import itertools, operator
from PyQt4.QtCore import QSettings 
def getQGISDbs():
        settings = QSettings()
        settings.beginGroup('/PostgreSQL/connections')
        named_dbs = settings.childGroups()
        all_info = [i.split("/") + [unicode(settings.value(i))] for i in settings.allKeys() if settings.value(i) != NULL and settings.value(i)!= '']
        all_info = [i for i in all_info if i[0] in named_dbs and i[2] != NULL and i[1] in ['name', 'host', 'service', 'password', 'username', 'port']]
        available_dbs = dict([k, dict([i[1:] for i in list(g)])] for k, g in itertools.groupby(sorted(all_info), operator.itemgetter(0)))
        settings.endGroup()
        return available_dbs

2. function to create connection string

   def get_connstring(available_dbs, db_name):
       db_info = available_dbs[db_name]
       connstring = ''
       try: # replace username key with user 
           db_info['user'] = db_info['username']
           del db_info['username']
       except KeyError:
           pass
       for k, v in db_info.items():
           connstring += str(k) + '=' + str(v) + ' '
       if 'service' in db_info.keys():
           pass
       else:
           connstring += 'dbname=' + str(selected_db)
       return