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

faheemkodi · Accepted Answer · 2023-05-05 07:20:40Z

33

Just changing the database ownership worked for me.

ALTER DATABASE database OWNER TO user;

answered May 5, 2023 at 7:20

faheemkodi

1,09410 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Phil Dukhov · Accepted Answer · 2023-06-13 04:10:01Z

13

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.

edited Jun 13, 2023 at 4:10

Phil Dukhov

91.1k22 gold badges276 silver badges302 bronze badges

answered Jul 29, 2020 at 15:53

Kundan Kumar

4191 gold badge4 silver badges13 bronze badges

3 Comments

Yabesh Over a year ago

still got same issue

wobbily_col Over a year ago

should the second one not be" TO you_user" rather than "TO public"?

Life5ign Over a year ago

usually an explanation of the solution/code is helpful; you defined the problem, and then gave a solution (purportedly), but didn't explain or justify it. Especially granting privileges on public to public.

Sandeepa Kariyawasam · Accepted Answer · 2023-05-07 10:14:34Z

7

Check the user created earlier after db creation and the one set in DATABASES configurations in settings.py is matching. Lets call it as myuser and the db created as mydb

Apply below permissions to the user -

GRANT ALL ON DATABASE mydb TO myuser; 
ALTER DATABASE mydb OWNER TO myuser;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO myuser;

edited May 7, 2023 at 10:14

Sandeepa Kariyawasam

5951 gold badge9 silver badges31 bronze badges

answered May 2, 2023 at 20:04

Vishesh Jha

711 silver badge1 bronze badge

Comments

Илим Калманбетов · Accepted Answer · 2024-06-02 06:26:07Z

1

My project can't work even after that command: ALTER DATABASE database OWNER TO user; GRANT ALL ON DATABASE mydb TO myuser; ALTER DATABASE mydb OWNER TO myuser; GRANT USAGE, CREATE ON SCHEMA PUBLIC TO myuser;

And I gave directly privileges on all table where it was error GRANT ALL PRIVILEGES ON TABLE django_migrations TO user; GRANT ALL PRIVILEGES ON TABLE django_content_type TO user; GRANT ALL PRIVILEGES ON TABLE auth_permission TO core;

answered Jun 2, 2024 at 6:26

Илим Калманбетов

112 bronze badges

Comments

Kurt Peek · Accepted Answer · 2018-06-14 22:40:34Z

0

I was able to GRANT USAGE and GRANT CREATE when I simply entered psql using the psql command instead of python manage.py dbshell. I subsequently ran into errors relating to the owner of the objects in the database (relations, etc.) not being the one in the DATABASES['USER'] settings, namely, 'lucyapp'.

I finally resolved the issues using pg_restore with the --no-owner and --role=lucyapp options, following Postgresql - backup database and restore on different owner?.

answered Jun 14, 2018 at 22:40

Kurt Peek

58.5k104 gold badges354 silver badges573 bronze badges

Comments

Shahid Khan · Accepted Answer · 2025-01-23 00:39:38Z

In PostgreSQL 14 and in prior versions, by default anybody can create a table. The new table will simply end up in the PUBLIC schema. The problem with this approach is that the public schema can quickly turn into a trash can containing all kinds of used and unused tables - which is neither desirable nor recommended. An even bigger problem is security

The public schema is the perfect vector for such a privilege escalation attack. Therefore PostgreSQL has made a major leap forward and changed this behavior. In version 15, only the database owner can create objects in the public schema.

How to grant permissions on the PUBLIC schema:

GRANT ALL ON SCHEMA public TO demo;

where demo is the username

HERE IS THE COMPLETE BLOG

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related