Prisma无法连接AWS RDS Postgres实例的问题求助

问题概述 #

团队正在将数据库迁移至AWS RDS，但Prisma无法建立数据库连接——执行任何Prisma命令都会抛出P1010: User 'backuser' was denied access on the database 'api-dev.public'错误。但使用完全相同的凭证，通过psql可以正常连接该RDS Postgres实例，且能执行任意数据库操作。

曾怀疑是Prisma影子数据库的问题，已为数据库用户授予CREATEDB权限，并在实例中显式配置了独立的影子数据库，但问题仍未解决。

prisma migrate status报错详情 #

root@8k3k2k29s:/code# prisma migrate status
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "api-dev", schema "public" at "api-dev.xxxxxx.us-east-9.rds.amazonaws.com"
Error: P1010: User `backuser` was denied access on the database `api-dev.public`

psql成功连接示例 #

root@398fba4348c9:/code# psql -h api-dev.xxxxxx.us-east-9.rds.amazonaws.com -U backuser -d api-dev
Password for user backuser:
psql (15.6 (Debian 15.6-0+deb12u1), server 16.1)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

api-dev=>

相关配置信息 #

Docker Compose配置 #

version: '3'

services:
  api:
    platform: linux/x86_64
    container_name: api
    image: api
    build:
      context: .
      dockerfile: Dockerfile
    volumes:
      - .:/code
    ports:
      - "8081:8000"
    environment:
      - APP_ENV=dev
      - PORT=8000
      - DATABASE_URL=postgresql://backuser:supersecretpassword@api-dev.xxxxxx.us-east-9.rds.amazonaws.com/api-dev?sslmode=disable&schema=public
      - SHADOW_DATABASE_URL=postgresql://backuser:supersecretpassword@api-shadow.xxxxxx.us-east-9.rds.amazonaws.com/api?sslmode=disable&schema=public

Prisma Schema配置 #

generator client {
  provider        = "prisma-client-js"
  output          = "../src/generated/client"
  previewFeatures = ["postgresqlExtensions", "fullTextSearch"]
  binaryTargets   = ["native", "debian-openssl-3.0.x"]
}

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  extensions        = [pgcrypto()]
}

环境与版本信息 #

• OS: Debian Bookworm
• 数据库: Postgres 16.1
• Node.js版本: v21.7.1

Prisma版本详情

prisma                  : 5.11.0
@prisma/client          : 5.10.2
Computed binaryTarget   : debian-openssl-3.0.x
Operating System        : linux
Architecture            : x64
Node.js                 : v21.7.1
Query Engine (Node-API) : libquery-engine efd2449663b3d73d637ea1fd226bafbcf45b3102 (at ../../../usr/local/lib/node_modules/prisma/node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli efd2449663b3d73d637ea1fd226bafbcf45b3102 (at ../../../usr/local/lib/node_modules/prisma/node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.11.0-15.efd2449663b3d73d637ea1fd226bafbcf45b3102
Default Engines Hash    : efd2449663b3d73d637ea1fd226bafbcf45b3102
Studio                  : 0.499.0
Preview Features        : fullTextSearch, postgresqlExtensions

已尝试的排查操作 #

• 为backuser用户授予全量权限：

GRANT CONNECT ON DATABASE "api-dev" TO backuser;
GRANT USAGE ON SCHEMA public TO backuser;
GRANT ALL ON DATABASE "api-dev" TO backuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO backuser;
ALTER USER backuser CREATEDB;

• 创建RDS参数组，将rds.forcessl设置为0并应用到目标RDS实例
• 配置独立的影子数据库，并在环境变量中指定SHADOW_DATABASE_URL

复现步骤 #

1. 在Docker环境中搭建Node/Express API服务
2. 配置Prisma连接至AWS RDS上的Postgres实例
3. 创建RDS参数组，修改rds.forcessl参数为0并应用到实例
4. 执行prisma migrate status命令

预期结果 #

Prisma能够正常连接RDS Postgres实例，成功输出prisma migrate status的执行结果

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