Django连接PostgreSQL时出现MigrationSchemaMissing错误求助

问题 #

首次在Django中使用PostgreSQL（替代SQLite3），已本地安装数据库、创建用户和数据库，配置settings.py后执行migrate命令报错。

数据库配置（settings.py） #

# database config

DB_NAME = 'django_chat_db'
DB_USER = "django"
DB_PASSWORD = "password"
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': DB_NAME,
        'USER': DB_USER,
        'PASSWORD': DB_PASSWORD,
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

错误信息 #

Traceback (most recent call last):
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^


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

Traceback (most recent call last):
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\migrations\recorder.py", line 70, in ensure_schema
    editor.create_model(self.Migration)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\base\schema.py", line 447, in create_model
    self.execute(sql, params or None)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\base\schema.py", line 199, in execute    
    cursor.execute(sql, params)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 103, in execute
    return super().execute(sql, params)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\backends\utils.py", line 87, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: permission denied for schema public  
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^


During handling of the above exception, another exception occurred:    

Traceback (most recent call last):
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\src\manage.py", line 22, in <module>
    main()
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\src\manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\__init__.py", line 440, in execute   
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\base.py", line 402, in run_from_argv 
    self.execute(*args, **cmd_options)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\base.py", line 448, in execute       
    output = self.handle(*args, **options)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\base.py", line 96, in wrapped        
    res = handle_func(*args, **kwargs)
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\core\management\commands\migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\migrations\executor.py", line 107, in migrate     
    self.recorder.ensure_schema()
  File "C:\Users\rakib\OneDrive\Desktop\Real Time Messenger\env\lib\site-packages\django\db\migrations\recorder.py", line 72, in ensure_schema
    raise MigrationSchemaMissing(
django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public     
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^
)

解决方案 #

错误核心是django用户对django_chat_db数据库的public schema没有足够权限，无法创建表。按以下步骤修复：

• 以PostgreSQL超级用户身份进入数据库
  打开终端/命令提示符，执行：

  psql -U postgres
  

  （postgres是默认超级用户，若你设置了其他超级用户，替换成对应名称）

• 切换到目标数据库
  在psql交互界面中执行：

  \c django_chat_db;
  

• 给用户授权
  执行以下SQL命令，赋予django用户对public schema的完整操作权限：

  GRANT ALL PRIVILEGES ON SCHEMA public TO django;
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO django;
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO django;
  

• 重新执行migrate
  输入\q退出psql，回到Django项目目录，运行：

  python manage.py migrate
  

如果是刚创建数据库，也可以直接在创建时指定所有者为django，从根源避免权限问题：

CREATE DATABASE django_chat_db OWNER django;

内容的提问来源于stack exchange，提问作者py300