PostgreSQL 15授予public schema权限无效，Django迁移失败

我之前在PostgreSQL 12上运行的Django代码一切正常，但升级到PostgreSQL 15后，项目无法执行数据库迁移。尝试执行GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO django语句后仍未解决，具体情况如下：

报错信息 #

lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" smallint NOT NULL PRI...

操作及测试过程 #

sudo -u postgres psql
could not change directory to "/home/john/project/src": Permission denied
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO django;
GRANT
postgres=# 
\q

(project) john@vps-demo:~/project/src$ psql -U django my_db
Password for user django: 
psql (15.6 (Debian 15.6-0+deb12u1))
Type "help" for help.

my_db=> CREATE TABLE test_table1 (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test_table1 (id INT);
                     ^
my_db=> 

权限验证结果 #

# sudo -u postgres psql -c "\dn+ public"
could not change directory to "/home/john/project/install": Permission denied
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                  +| 
        |                   | django=UC/pg_database_owner           +| 
        |                   | john=UC/pg_database_owner          | 
(1 row)

解决方法 #

问题出在仅授权了现有表的权限，但创建新表需要public schema本身的使用和创建权限，同时要设置默认权限确保后续对象自动获取权限：

1. 授予schema级别的核心权限：

GRANT USAGE, CREATE ON SCHEMA public TO django;

2. 设置默认权限，避免后续新建表/序列/函数时重复授权：

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

3. 补全现有对象的权限（若需要操作已存在的表）：

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

执行完以上步骤后，重新测试创建表或运行Django迁移即可正常操作。

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