Django "MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in)"

Question

I'm trying to restore a production database to my local machine, similar to the workflow described for Heroku (https://devcenter.heroku.com/articles/heroku-postgres-import-export). However, I'm using Aptible, which provides a DB tunnel for this purpose.

Using pgAdmin4, I've created a 'Custom' backup. Then, slightly modifying a pgAdmin command, I've restored it using

"/Applications/pgAdmin 4.app/Contents/SharedSupport/pg_restore" --host "localhost" --port "5432" --username "postgres" --no-password --dbname "lucy_prod" --verbose "/Users/kurtpeek/lucy-prod-backup-11-june-2018" --clean

where I've added the --clean option to drop database objects before recreating them (cf. https://www.postgresql.org/docs/9.2/static/app-pgrestore.html).

The problem is that when I now try to python manage.py migrate, I get the following error:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...
                     ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 57, in ensure_schema
    editor.create_model(self.Migration)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 303, in create_model
    self.execute(sql, params or None)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...
                     ^


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 29, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 83, in handle
    executor = MigrationExecutor(connection, self.migration_progress_callback)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/executor.py", line 20, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/loader.py", line 52, in __init__
    self.build_graph()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/loader.py", line 209, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 65, in applied_migrations
    self.ensure_schema()
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 59, in ensure_schema
    raise MigrationSchemaMissing("Unable to create the django_migrations table (%s)" % exc)
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMA...
                     ^
)

From what I understand from MigrationSchemaMissing(Unable to create the django_migrations table (%s) % exc), I need to run

grant usage on schema public to username;
grant create on schema public to username;

However, what should I fill out for username? Should this be postgres?

Here are some more details on the public schema and the search_path:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py dbshell
psql (10.4)
Type "help" for help.

lucy_prod=> \dn+ public
                        List of schemas
  Name  |  Owner   | Access privileges |      Description       
--------+----------+-------------------+------------------------
 public | postgres |                   | standard public schema
(1 row)

lucy_prod=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Update

From running the select current_user; command, I determined that my current user is named lucyapp, but if I try to grant usage on schema public for this user I get a 'permission denied' error:

lucy_prod=> select current_user;
 current_user 
--------------
 lucyapp
(1 row)

lucy_prod=> grant usage on schema public to lucyapp;
ERROR:  permission denied for schema public
lucy_prod=> grant create on schema public to lucyapp;
ERROR:  permission denied for schema public

Answer 1

Just changing the database ownership worked for me.

ALTER DATABASE database OWNER TO user;

Answer 2

This is happening due to the access issue of the role to the public schema. Running this script inside the database shell will solve the issue.

GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON SCHEMA public TO public;

This is for Postgres DB. A similar script can be found for other SQL DB.