P1000: Authentication failed against database server at `localhost`, the provided database credentials for `postgres` are not valid

huangapple
go评论118阅读模式
英文:

P1000: Authentication failed against database server at `localhost`, the provided database credentials for `postgres` are not valid

问题

尝试连接到运行在Docker中的PostgreSQL（使用pgAdmin4或尝试使用Prisma进行数据库迁移），但结果出现以下错误消息：

  1. 错误：P1000：在`localhost`上的数据库服务器上的身份验证失败，为`postgres`提供的数据库凭据无效。
  3. 请确保为`localhost`上的数据库服务器提供有效的数据库凭据。

以下是来自Docker容器pg_container的日志消息：

  1. 2023年7月10日23:28:55 2023年7月11日03:28:55.532 UTC [84]致命错误：用户“postgres”的密码验证失败
  2. 2023年7月10日23:28:55 2023年7月11日03:28:55.532 UTC [84]详细信息：角色“postgres”不存在。
  3. 2023年7月10日23:28:55 连接匹配了pg_hba.conf第100行：“host all all all scram-sha-256”

我的docker-compose.yml：

  1. services:
  2.   dev-db:
  3.     container_name: pg_container
  4.     image: postgres:latest
  5.     restart: always
  6.     ports:
  7.       - 5432:5432
  8.     environment:
  9.       - POSTGRES_USER=postgres
  10.       - POSTGRES_PASSWORD=1234
  11.       - POSTGRES_DB=dev
  12.       
  13.   pgadmin:
  14.     container_name: pgadmin4_container
  15.     image: dpage/pgadmin4
  16.     restart: always
  17.     environment:
  18.       PGADMIN_DEFAULT_EMAIL: admin@admin.com
  19.       PGADMIN_DEFAULT_PASSWORD: root
  20.     ports:
  21.       - "5050:80"

运行docker ps：

  1. CONTAINER ID   IMAGE             COMMAND                  CREATED         STATUS         PORTS                           NAMES
  2. d31b7483c8ad   postgres:latest   "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   0.0.0.0:5432->5432/tcp          pg_container      
  3. fa587cd32a04   dpage/pgadmin4    "/entrypoint.sh"         2 minutes ago   Up 2 minutes   443/tcp, 0.0.0.0:5050->80/tcp   pgadmin4_container

我尝试过的：

  1. 尝试查看是否有后台运行的本地PostgreSQL实例，但没有找到任何内容。
英文:

Attempting to connect (with pgAdmin4 or attempting a DB migrate with Prisma) to my PostgreSQL running in docker, however, results in the error message

  1. Error: P1000: Authentication failed against database server at `localhost`, the provided database credentials for `postgres` are not valid.
  3. Please make sure to provide valid database credentials for the database server at `localhost`.

Here are the log message from the docker container pg_container:

  1. 2023-07-10 23:28:55 2023-07-11 03:28:55.532 UTC [84] FATAL:  password authentication failed for user "postgres"
  2. 2023-07-10 23:28:55 2023-07-11 03:28:55.532 UTC [84] DETAIL:  Role "postgres" does not exist.
  3. 2023-07-10 23:28:55     Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

My docker-compose.yml :

  2. services:
  3.   dev-db:
  4.     container_name: pg_container
  5.     image: postgres:latest
  6.     restart: always
  7.     ports:
  8.       - 5432:5432
  9.     environment:
  10.       - POSTGRES_USER= postgres
  11.       - POSTGRES_PASSWORD= 1234
  12.       - POSTGRES_DB= dev
  13.       
  14.   pgadmin:
  15.     container_name: pgadmin4_container
  16.     image: dpage/pgadmin4
  17.     restart: always
  18.     environment:
  19.       PGADMIN_DEFAULT_EMAIL: admin@admin.com
  20.       PGADMIN_DEFAULT_PASSWORD: root
  21.     ports:
  22.       - "5050:80"

Running docker ps :

  1. CONTAINER ID   IMAGE             COMMAND                  CREATED         STATUS         PORTS                           NAMES
  2. d31b7483c8ad   postgres:latest   "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   0.0.0.0:5432->5432/tcp          pg_container      
  3. fa587cd32a04   dpage/pgadmin4    "/entrypoint.sh"         2 minutes ago   Up 2 minutes   443/tcp, 0.0.0.0:5050->80/tcp   pgadmin4_container

What I've tried:

  1. Try to see if I had a local instance of a postgres running in background, there's nothing

答案1

得分: 1

如果您使用environment:的字符串列表形式，则每个项目都应该是KEY=value，没有空白字符。如果有空白字符，则它将包含在键和/或值中。在您的示例中，用户名是带有前导空格的postgres，数据库名称也类似地带有空格的dev。

您可以尝试将带有空格的凭据输入到PGAdmin或Prisma中。

在Compose文件中，您要么需要删除这些空格

  1. environment:
  2.   - POSTGRES_USER=postgres
  3.   #              ^^ 这里没有空格

要么更改为YAML映射语法，这将更一致地使用YAML引用和转义规则

  1. environment:
  2.   POSTGRES_USER: postgres
  3.   #            ^  无前导连字符，键和值之间用冒号
英文:

If you're using the list-of-strings form of environment: then each item is exactly KEY=value, with no whitespace. If there is whitespace then it is included in the key and/or value. In your example, the username is postgres with a leading space, and the database name is similarly dev with a space.

You can try entering these credentials with the spaces into PGAdmin or Prisma.

In the Compose file, you either need to remove the spaces

  1. environment:
  2.   - POSTGRES_USER=postgres
  3.   #              ^^ no space here

or change to the YAML map syntax, which will more consistently use YAML quoting and escaping rules

  1. environment:
  2.   POSTGRES_USER: postgres
  3.   #            ^  no leading hyphen, colon between key and value

huangapple
  • 本文由 发表于 2023年7月11日 11:39:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/76658576.html
  • docker
  • pgadmin
  • postgresql
  • prisma
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定