bash postgresql：.pgpass 不起作用

postgresql: .pgpass not working

提问by Deepankar Bajpeyi

I have created a .pgpassfile in my home directory which looks like this

我.pgpass在我的主目录中创建了一个文件，看起来像这样

localhost:5432:somedb:someuser:somepass

localhost:5432:somedb:someuser:somepass

I am using a shell script which creates a directory and puts a pg_dump of somedbthere :

我正在使用一个 shell 脚本，它创建一个目录并在somedb那里放置一个 pg_dump ：

mkdir directory
pg_dump somedb > directory/somefile.dump

It still prompts for the password.

它仍然提示输入密码。

Where is the mistake here ?

这里的错误在哪里？

采纳答案by devnull

Did you try specifying the host & user?

您是否尝试指定主机和用户？

pg_dump -U someuser -h localhost somedb > directory/somefile.dump

回答by Nacho Mezzadra

Although question has already been answered and accepted, it may also happen that permissions on .pgpass file are not properly set. It has to have the world and group access disallowed:

虽然问题已经得到回答和接受，但也可能会发生 .pgpass 文件的权限设置不正确的情况。它必须禁止世界和组访问：

/bin/chmod 0600 ~/.pgpass

回答by Shrinivas

1) Create .pgpass file with content

1) 用内容创建 .pgpass 文件

host:5432:somedb:someuser:somepass

主机：5432：somedb：someuser：somepass

2) set the permissions using command

2）使用命令设置权限

sudo chmod 600 .pgpass

须藤 chmod 600 .pgpass

3) Set the file owner as the same user using which you logged in :

3) 将文件所有者设置为您登录时使用的同一用户：

sudo chown login_username:login_username .pgpass

须藤 chown login_username:login_username .pgpass

4) Set PGPASSFILE environment variable :

4) 设置 PGPASSFILE 环境变量：

export PGPASSFILE='/home/user/.pgpass'

导出 PGPASSFILE='/home/user/.pgpass'

Now check by connecting to database :

现在通过连接到数据库来检查：

psql -h host -U someuser somedb

psql -h host -U someuser somedb

It will not prompt for password and logged in to postgresql.

它不会提示输入密码并登录到 postgresql。

回答by yonran

psql(startup.c) calls PQconnectdbParams(fe-connect.c), and then passwordFromFileis called. Here's a checklist to make sure the pgpass file will be used:

psql( startup.c) 调用PQconnectdbParams( fe-connect.c)，然后passwordFromFile被调用。这是确保将使用 pgpass 文件的清单：

1. Make sure the flags --password/-Wand password=in the connection stringare unset. Otherwise, the pgpass file will not be used.
2. Make sure the environment variable PGPASSWORDis unset (echo $PGPASSWORD). Otherwise, the pgpass file will not be used.
3. Make sure the pgpass fileis in the right location ($PGPASSFILEor default ~/.pgpassor %APPDATA%\postgresql\pgpass.conf)
4. Make sure the passfile is not readable, writable, or executable by group or other (e.g. chmod 600 ~/.pgpass); otherwise psqlwill print a warning.
5. Make sure the passfile is a file (not a symlink); otherwise psqlwill print a warning.
6. Make sure the passfile is readable by the psqluser (e.g. cat ~/.pgpass); otherwise psqlwill ignore it without warning. Make sure that it is owned by the user, and that all its ancestor directories are executable by the user.
7. Make sure that the pgpass file has the correct format hostname:port:database:username:password(The Password File, passwordFromFile). Each field (other than password) can be *. The characters :and \must be escaped \:and \\(even in password). The password ends at :or the end of the line and can include any byte other than \r, \n, or \0. Any lines that aren't formatted right or don't match the host and user are ignored without warning as if they were comments.
8. Make sure the hostname, port, dbname, username of the line in the pgpass file match the server or are *. The server's “pwhost” that is checked is the hostname if non-empty, or the hostaddrip address. Otherwise, the line will be ignored without warning.

1. 确保标志--password/-W和password=中连接字符串都没有设置。否则，将不会使用 pgpass 文件。
2. 确保环境变量PGPASSWORD未设置 ( echo $PGPASSWORD)。否则，将不会使用 pgpass 文件。
3. 确保pgpass 文件位于正确的位置（$PGPASSFILE或默认~/.pgpass或%APPDATA%\postgresql\pgpass.conf）
4. 确保密码文件不可被组或其他人读取、写入或执行（例如chmod 600 ~/.pgpass）；否则psql会打印警告。
5. 确保密码文件是一个文件（不是符号链接）；否则psql会打印警告。
6. 确保psql用户可以读取密码文件（例如cat ~/.pgpass）；否则psql将在没有警告的情况下忽略它。确保它归用户所有，并且它的所有祖先目录都可由用户执行。
7. 确保 pgpass 文件具有正确的格式hostname:port:database:username:password（密码文件，passwordFromFile）。每个字段（密码除外）都可以是*. 字符:和\必须转义\:和\\（即使在密码中）。密码在行尾:或行尾结束，可以包含除\r、\n、 或之外的任何字节\0。任何格式不正确或与主机和用户不匹配的行都将被忽略而不会发出警告，就好像它们是注释一样。
8. 确保 pgpass 文件中该行的主机名、端口、数据库名、用户名与服务器匹配或为*. 被检查的服务器的“pwhost”是host名称（如果非空）或hostaddrIP地址。否则，该行将在没有警告的情况下被忽略。

Unfortunately, there is no logging within these files, so if this doesn't help, then you may need to compile psqland libpqyourself and run it in a debugger.

不幸的是，这些文件中没有日志记录，所以如果这没有帮助，那么您可能需要自己编译psql并libpq在调试器中运行它。

回答by Jonathan dos Santos

Check the owner of the .pgpass file. Just lost half an hour to find out I had created my .pgpass file with sudo. The command for my user and location was chown postgres:postgres /var/lib/pgsql/.pgpass.

检查 .pgpass 文件的所有者。花了半个小时才发现我用 sudo 创建了我的 .pgpass 文件。我的用户和位置的命令是chown postgres:postgres /var/lib/pgsql/.pgpass.