在 Windows 中远程连接时如何正确地为 PostgreSQL 提供密码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4472054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to correctly provide password to PostgreSQL when connecting remotely in Windows?
提问by Scott Klarenbach
DB: PostgreSQL 9.0 Client: Windows 7 Server Windows 2008, 64bit
数据库：PostgreSQL 9.0 客户端：Windows 7 Server Windows 2008，64 位
I'm trying to connect remotely to a PostgreSQL instance for purposes of performing a pg_dump to my local machine.
我正在尝试远程连接到 PostgreSQL 实例，以便对我的本地机器执行 pg_dump。
Everything works from my client machine, except that I need to provide a password at the password prompt, and I'd ultimately like to batch this with a script.
一切都在我的客户端机器上工作，除了我需要在密码提示下提供密码，我最终想用脚本来批处理它。
I've followed the instructions here:
我已按照此处的说明进行操作：
http://www.postgresql.org/docs/current/static/libpq-pgpass.html
http://www.postgresql.org/docs/current/static/libpq-pgpass.html
But it's not working.
但它不起作用。
To recap, I've created a file on the server: C:/Users/postgres/AppData/postgresql/pgpass.conf, where PostgreSQL is the db user.
回顾一下，我在服务器上创建了一个文件：C:/Users/postgres/AppData/postgresql/pgpass.conf，其中 PostgreSQL 是 db 用户。
The file has one line with the following data:
该文件有一行包含以下数据：
\*:5432:\*postgres:[mypassword]
I've also tried replacing each *with [localhost|myip]and [mydatabasename]respectively.
我也试过分别*用[localhost|myip]和替换每个[mydatabasename]。
From my client machine, I connect using:
在我的客户端机器上，我使用以下方法进行连接：
pg_dump -h <myip> -U postgres -w [mydbname] > [mylocaldumpfile]
I'm presuming that I need to provide the -wswitch in order to ignore password prompt, at which point it should look in the AppData directory on the server.
我假设我需要提供-w开关以忽略密码提示，此时它应该在服务器上的 AppData 目录中查找。
It just comes back with:
它只是回来了：
connection to database failed: fe_sendauth: no password supplied.
与数据库的连接失败：fe_sendauth：未提供密码。
As a hack workaround, if there was a way I could tell the Windows batch file on my client machine to inject the password at the PostgreSQL prompt, that would work as well.
作为一种 hack 解决方法，如果有一种方法可以告诉我客户端计算机上的 Windows 批处理文件在 PostgreSQL 提示符下注入密码，那也可以。
回答by anakkin
It works for me:
这个对我有用：
Use command line
使用命令行
cd %appdata%
mkdir postgresql
cd postgresql
notepad pgpass.conf
inside pgpass.conf paste your connection string (*:5432:*postgres:[mypassword]) and save the file.
To connect to postgres use:
在 pgpass.conf 中粘贴您的连接字符串 ( *:5432:*postgres:[mypassword]) 并保存文件。要连接到 postgres，请使用：
psql/pg_dump -U <username> -h <host> -w <other params you want to use>
回答by leonidv
I have solved similar problem (only in Linux) to use ip address in pgpass and psql.
我已经解决了类似的问题（仅在 Linux 中）以在 pgpass 和 psql 中使用 ip 地址。
.pgpass
.pgpass
127.0.0.1:5432:db:dbuser:123
psql params
psql 参数
psql -d db -U dbuser -h 127.0.0.1 -w
pg_hba conf with default settings:
具有默认设置的 pg_hba conf：
# IPv4 local connections:
84 host all all 127.0.0.1/32 md5
回答by Jayden
I've had a similar problem which I didn't manage to resolve - I couldn't get the script to recognise the pgpass.conf file. I however used a work-around of setting the PGPASSWORD environment variable in the batch file I was using (PostgreSQL 9.6).
我有一个类似的问题，但我没能解决——我无法让脚本识别 pgpass.conf 文件。然而，我使用了一种在我使用的批处理文件（PostgreSQL 9.6）中设置 PGPASSWORD 环境变量的解决方法。
Inside a batch file:
在批处理文件中：
SET PGPASSWORD=<<password>>
pg_dump.exe -h <<host>> -p <<port>> -U <<user>> -Fc -b -v -f <<output file path>> <<database>>
SET PGPASSWORD=<<password>>
pg_dump.exe -h <<host>> -p <<port>> -U <<user>> -Fc -b -v -f <<output file path>> <<database>>
回答by Casey
I have gotten it to work with the following:
我已经得到它与以下工作：
pgpass.conf:
pgpass.conf：
127.0.0.1:5432:*:username:password
However, I have it stored here:
但是，我将它存储在这里：
C:\Users\<user>\AppData\Roaming\postgresql
For some reason, on a previous iteration of Postgres, the database had generated the pgpassfile and stored it there. I was running into the same issue you were having, moved it to that directory and it worked. I'm not sure why though.
出于某种原因，在 Postgres 的先前迭代中，数据库生成了pgpass文件并将其存储在那里。我遇到了您遇到的相同问题，将其移动到该目录并且它起作用了。我不知道为什么。
Then, all you'll need to do is:
然后，您需要做的就是：
pg_dump -h myip mydb > mylocaldumpfile
...ensuring that myipand the ip in pgpass.confare identical. If they are not, it will prompt you for a password.
...确保myip和 ip inpgpass.conf是相同的。如果不是，它会提示您输入密码。
回答by kickinchicken
Make sure you are logged in as the user corresponding with the folder where the pgpass.conf file lives.
确保您以与 pgpass.conf 文件所在文件夹对应的用户身份登录。
回答by Manohar Reddy Poreddy
Create pgpass.conf file
创建 pgpass.conf 文件
Windows > Start > Run
type %APPDATA%
press OK
Create a folder: postgresql
Create a file : pgpass.conf (under postgresql folder)
Open pgpass.conf file
打开 pgpass.conf 文件
Now you should have below file ready, open it via below (making sure it exists):
现在你应该已经准备好了下面的文件，通过下面打开它（确保它存在）：
Windows > Start > Run
type %APPDATA%\postgresql\pgpass.conf
press OK
Paste pgpass.conf file contents
粘贴 pgpass.conf 文件内容
Paste the below
粘贴下面
# serverDomainOrIP:PORT:databaseName:userName:password
# 127.0.0.1:5432:myDbName:postgres:myPassword
# *:5432:*:*:myPassword
You can do one of the below:
- Remove # for the 3rd line, and give your password in the place of "myPassword"
OR
- Remove # for the 2nd line, and give ip (or, yourDomain.com), dbname, username & password
您可以执行以下操作之一：
- 删除第 3 行的 #，并在“myPassword”的位置提供您的密码
或
- 删除第 2 行的 #，并提供 ip（或 yourDomain.com）、dbname、用户名＆ 密码
Hope that helps.
希望有帮助。
回答by svandragt
You could use pgAdmin III to store the password (in the server's properties). That operation automatically creates a correct pgpass.conf file. You can then schedule a task to run a simple batch file that would read:
您可以使用 pgAdmin III 来存储密码（在服务器的属性中）。该操作会自动创建一个正确的 pgpass.conf 文件。然后，您可以安排一个任务来运行一个简单的批处理文件，该文件将读取：
"C:\path\to\pg_dump.exe" -U <user> -w <database> > C:\path\to\database.backup
"C:\path\to\pg_dump.exe" -U <user> -w <database> > C:\path\to\database.backup
