Fedora本地PostgreSQL迁移Prisma schema遇P1010权限拒绝错误

错误场景 #

首次在Fedora本地PostgreSQL环境执行Prisma schema迁移时，触发权限拒绝错误：

Error: P1010: User blog_user was denied access on the database blog_db.public

现有配置信息 #

.env 数据库连接串 #

DATABASE_URL=postgresql://blog_user:blog_pswd@localhost:5432/blog_db

数据库权限状态（\l 命令输出） #

Name    |   Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |    Access privileges    
-----------+-----------+----------+-------------+-------------+------------+-----------------+-------------------------
 blog_db   | blog_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/blog_user          +
           |           |          |             |             |            |                 | blog_user=CTc/blog_user

pg_hba.conf 认证配置 #

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# host    all             all             192.168.1.1/24            md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

已尝试的权限授予操作 #

GRANT CONNECT ON DATABASE blog_db TO blog_user;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO blog_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blog_user;
GRANT ALL PRIVILEGES ON DATABASE blog_db TO blog_user;
GRANT pg_write_all_data TO blog_user;
GRANT pg_read_all_data TO blog_user;

排查建议 #

1. 补充Schema的USAGE权限
   仅拥有表权限不足以访问schema内对象，需先授予schema的USAGE权限：

   GRANT USAGE ON SCHEMA public TO blog_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO blog_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON FUNCTIONS TO blog_user;
   

2. 修正IPv6连接认证方式
   当前pg_hba.conf中IPv6本地连接使用ident认证，与密码连接方式冲突，修改为md5：

   host    all             all             ::1/128                 md5
   

   修改后重启PostgreSQL服务：

   sudo systemctl restart postgresql
   

3. 直接验证用户数据库操作权限
   绕过Prisma，直接用blog_user连接数据库测试基础操作：

   psql -U blog_user -d blog_db -h localhost
   

   进入后执行：

   CREATE TABLE test (id SERIAL PRIMARY KEY);
   DROP TABLE test;
   

   如果操作失败，说明数据库权限仍存在问题；如果成功，再排查Prisma配置。

4. 确认Prisma Schema的指定
   检查schema.prisma文件中是否指定了正确的schema，确保为public：

   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
     schema   = "public" # 需与授权的schema一致
   }
   

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