执行Django migrate命令报错：无public schema权限创建django_migrations表

问题描述 #

在Django项目的settings.py中配置PostgreSQL连接如下：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': '<name>',
        'USER': '<username>',
        'PASSWORD': 'admin',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

执行python3 manage.py migrate时触发报错，提示无法创建django_migrations表，核心原因是对public schema无权限，报错详情：

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/p.gauthamprasad/Downloads/pb/pbapp/manage.py", line 22, in <module>
    main()
  File "/Users/p.gauthamprasad/Downloads/pb/pbapp/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/site-packages/django/db/migrations/executor.py", line 107, in migrate
    self.recorder.ensure_schema()
  File "/Users/p.gauthamprasad/Downloads/pb/pbvenv/lib/python3.10/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...
                     ^
)

已尝试在psql中执行grant命令但无效，寻求解决方案。

解决步骤 #

1. 以PostgreSQL超级用户身份登录
   打开终端执行：

   psql -U postgres
   

   输入postgres用户密码进入交互环境。

2. 切换到目标数据库
   使用\c命令切换到settings.py中配置的数据库：

   \c <name>
   

   替换<name>为实际数据库名称。

3. 授予完整权限
   依次执行以下SQL命令，替换<username>为settings.py中配置的数据库用户名：

   -- 授予public schema的使用和创建权限
   GRANT USAGE, CREATE ON SCHEMA public TO <username>;
   -- 授予public schema下所有表的全部操作权限
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <username>;
   -- 授予public schema下所有序列的全部权限（Django迁移依赖序列）
   GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO <username>;
   -- 确保未来创建的表/序列自动赋予该用户权限
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO <username>;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO <username>;
   

4. 重新执行迁移
   输入\q退出psql，回到项目目录执行：

   python3 manage.py migrate
   

常见排查点 #

• 确认执行grant命令时已切换到目标数据库，否则权限不会生效到对应库。
• 检查<username>和<name>与settings.py配置完全一致，PostgreSQL对名称大小写敏感。
• 若为新创建的数据库，需先授予用户连接权限：GRANT CONNECT ON DATABASE <name> TO <username>;

内容的提问来源于stack exchange，提问作者P.Gautham Prasad