Database creation failed using PostGis and Django

[Surbier Christophe]

I'm trying to create my database tables (python manage.py migrate) using Postgis. However it fails because the driver try to connect do default database "postgres" which is not my default name. I'm using django==1.8 and psycopg2==2.6.2

If i look the documentation of psycopg (​http://initd.org/psycopg/docs/module.html) The basic connection parameters are:

dbname – the database name (only in the dsn string)
database – the database name (only as keyword argument)
user – user name used to authenticate
password – password used to authenticate
host – database host address (defaults to UNIX socket if not provided)
port – connection port number (defaults to 5432 if not provided)
Here is my database connection:

DATABASES = {
 'default': {
 'ENGINE': 'django.contrib.gis.db.backends.postgis', 
 'NAME': '<mydatabase>',
 'USER': '<myuser>',
 'PASSWORD': '<mypassword>',
 'HOST':  ‘bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com'
 'PORT': '5432', 
 'URI':'postgis://myuser:mypassword@bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com:5432/bdsn15qbq8xaqoa'
}
}

I have added some trace to the connexion and as we can see, the driver uses postgres as database name instead of the one specified in the config file.

[('dbname', 'postgres'), ('user', 'myuser'), ('password', 'mypassword'), ('host', 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com'), ('port', '5432’)]
After some research, i have edited the file :

vi django/db/backends/postgresql_psycopg2/base.py

Function :

def get_connection_params(self):
        settings_dict = self.settings_dict
        # None may be used to connect to the default 'postgres' db
        if settings_dict['NAME'] == '':
            from django.core.exceptions import ImproperlyConfigured
            raise ImproperlyConfigured(
                "settings.DATABASES is improperly configured. "
                "Please supply the NAME value.")
        conn_params = {
            'database': settings_dict['NAME'] or 'postgres',
        }
        print settings_dict
        conn_params.update(settings_dict['OPTIONS'])
        conn_params.pop('isolation_level', None)
        if settings_dict['USER']:
            conn_params['user'] = settings_dict['USER']
        if settings_dict['PASSWORD']:
            conn_params['password'] = force_str(settings_dict['PASSWORD'])
        if settings_dict['HOST']:
            conn_params['host'] = settings_dict['HOST']
        if settings_dict['PORT']:
            conn_params['port'] = settings_dict['PORT']
        return conn_params

and added:
     

  if settings_dict['DBNAME']:
            conn_params['dbname'] = settings_dict['DBNAME']

Now if i relaunch the database creation : python manage.py migrate it is working fine using the good connection URI.

[('dbname', 'mydatabase'), ('user', 'myuser'), ('password', 'mypassword'), ('host', 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com'), ('port', '5432’)

Thanks a lot
Christophe

[Surbier Christophe]

Just a precision. I don't have access to the default postgres database. I'm using a PAS provider and my user only have access to my database not the "prostgres" default database. So i think it could be the origin of the bug.

Christophe

[Claude Paroz]

I guess that in your use case, you should follow the instructions and create your PostGIS database beforehand.
​https://docs.djangoproject.com/en/1.9/ref/contrib/gis/install/postgis/#creating-a-spatial-database

[Surbier Christophe]

Replying to claudep:

I guess that in your use case, you should follow the instructions and create your PostGIS database beforehand.
​https://docs.djangoproject.com/en/1.9/ref/contrib/gis/install/postgis/#creating-a-spatial-database

it doesn't help. How can i create a database and then update my models with PostGis specific fields (such as models.PointField) if i can't run migrations too ? Since the connexion fails. Why is the driver trying to connect with a default "postgres" user ? This is not what it is in my settings. I don't understand.

Thanks
Christophe

[Claude Paroz]

You can run migrations fine, you just have to run the "CREATE DATABASE"/"CREATE EXTENSION" commands first.

As you didn't provide any traceback, I cannot tell you much about the reason of the failing connection, but at some point, if Django has to talk to PostgreSQL without having to specify a database, it defaults to the postgres database (which is generally readable by all users in most setups), typically in a situation when it has to create a database, hence my recommendation to create it yourself beforehand.

[Surbier Christophe]

Replying to claudep:

You can run migrations fine, you just have to run the "CREATE DATABASE"/"CREATE EXTENSION" commands first.

As you didn't provide any traceback, I cannot tell you much about the reason of the failing connection, but at some point, if Django has to talk to PostgreSQL without having to specify a database, it defaults to the postgres database (which is generally readable by all users in most setups), typically in a situation when it has to create a database, hence my recommendation to create it yourself beforehand.

Thanks for your answer but the database had been already created by the PAAS Provider and the PostGis extension too. Now i'm just trying to create my tables but the connexion failed (even if a database name is provided as you can see in my settings file). In fact on my local machine with a PostgreSQL everything work fine. But it doesn't work when i try to launch with my provider settings. Because they don't give access to default postgres database. And if i edit the psycopg2 file base.py as i said, and add the DBNAME in the code, then it is working..
I don't know if it help but when a run the server it is working fine, when i run the migration connection fails...
Here are the logs (i have just replace my real password in the trace by the word password :) )

(envpython)serveur:citiesaround csurbier$ python manage.py runserver
Performing system checks...

System check identified no issues (0 silenced).
{'ENGINE': 'django.contrib.gis.db.backends.postgis', 'ATOMIC_REQUESTS': False, 'OPTIONS': {}, 'CONN_MAX_AGE': 0, 'URI': 'postgis://ua2phhilhulzwmqseowj:password@bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com:5432/bdsn15qbq8xaqoa', 'HOST': 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com', 'USER': 'ua2phhilhulzwmqseowj', 'PASSWORD': 'password', 'PORT': None, 'AUTOCOMMIT': True, 'NAME': 'bdsn15qbq8xaqoa', 'TIME_ZONE': 'UTC', 'DBNAME': 'bdsn15qbq8xaqoa', 'TEST': {'COLLATION': None, 'CHARSET': None, 'NAME': None, 'MIRROR': None}}
[('dbname', 'bdsn15qbq8xaqoa'), ('user', 'ua2phhilhulzwmqseowj'), ('password', 'password'), ('host', 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com')]
dbname=bdsn15qbq8xaqoa user=ua2phhilhulzwmqseowj password= password host=bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com
(0.105)

SELECT c.relname, c.relkind
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v')

AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid); args=None

(0.099) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()

You have unapplied migrations; your app may not work properly until they are applied.
Run 'python manage.py migrate' to apply them.

July 14, 2016 - 17:09:42
Django version 1.8, using settings 'citiesaround.settings'
Starting development server at ​http://127.0.0.1:8000/
Quit the server with CONTROL-C.

^{C(envpython)serveur:citiesaround csurbier$ python manage.py migrate
{'ENGINE': 'django.contrib.gis.db.backends.postgis', 'AUTOCOMMIT': True, 'ATOMIC_REQUESTS': False, 'NAME': None, 'CONN_MAX_AGE': 0, 'URI': 'postgis://ua2phhilhulzwmqseowj: password@…:5432/bdsn15qbq8xaqoa', 'PORT': None, 'HOST': 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com', 'USER': 'ua2phhilhulzwmqseowj', 'TEST': {'COLLATION': None, 'CHARSET': None, 'NAME': None, 'MIRROR': None}, 'TIME_ZONE': 'UTC', 'PASSWORD': 'tmdTEQ6yOVAOeMSRCu37', 'OPTIONS': {}, 'DBNAME': 'bdsn15qbq8xaqoa'}
[('dbname', 'postgres'), ('user', 'ua2phhilhulzwmqseowj'), ('password', 'password'), ('host', 'bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com')]
dbname=postgres user=ua2phhilhulzwmqseowj password= password host=bdsn15qbq8xaqoa-postgresql.services.clever-cloud.com
Traceback (most recent call last):}

File "manage.py", line 10, in <module>

execute_from_command_line(sys.argv)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/core/management/init.py", line 338, in execute_from_command_line

utility.execute()

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/core/management/init.py", line 330, in execute

self.fetch_command(subcommand).run_from_argv(self.argv)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/core/management/base.py", line 390, in run_from_argv

self.execute(*args, cmd_options)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/core/management/base.py", line 441, in execute

output = self.handle(*args, options)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 91, in handle

connection.prepare_database()

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/contrib/gis/db/backends/postgis/base.py", line 36, in prepare_database

if self.template_postgis is None:

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/utils/functional.py", line 60, in get

res = instance.dict[self.name] = self.func(instance)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/contrib/gis/db/backends/postgis/base.py", line 28, in template_postgis

with self._nodb_connection.cursor() as cursor:

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/base/base.py", line 162, in cursor

cursor = self.make_debug_cursor(self._cursor())

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/base/base.py", line 135, in _cursor

self.ensure_connection()

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection

self.connect()

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/utils.py", line 97, in exit

six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection

self.connect()

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/base/base.py", line 119, in connect

self.connection = self.get_new_connection(conn_params)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 175, in get_new_connection

connection = Database.connect(conn_params)

File "/Users/csurbier/Documents/workspace/citiesaround/envpython/lib/python2.7/site-packages/psycopg2/init.py", line 166, in connect

conn = _connect(dsn, connection_factory=connection_factory, async=async)

django.db.utils.OperationalError: FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.

[Claude Paroz]

Thanks for the traceback.
This code path has changed in Django 1.9 as we dropped support for PostGIS 1.5 and the query for any postgis template disappeared.
If you have to stick with 1.8, I guess patching Django is the best solution in your case.