postgresql 使用没有密码的 psql 命令运行批处理文件

Run batch file with psql command without password

提问by shabu 224

I am trying to execute this psqlcommand using a batch script:

我正在尝试psql使用批处理脚本执行此命令：

psql --host=localhost --dbname=<dbname> --port=<Port Number>
     --username=<dbuser> --file=C:\PSQL_Script.txt --output=C:\PSQL_Output.txt

The problem is that it's asking for the password every time I execute the batch script. How can I password argument through the batch file?

问题是每次执行批处理脚本时它都要求输入密码。如何通过批处理文件输入密码参数？

回答by Erwin Brandstetter

Keep reading, the best options come last. But let's clarify a couple of things first.

继续阅读，最好的选择放在最后。但让我们先澄清几件事。

Only silence the password request

仅静音密码请求

If your issue is only the password prompt, you can silence it. I quote the manual here:

如果您的问题只是密码提示，您可以将其静音。我在这里引用手册：

-w
--no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpassfile, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. (...)

-w
--no-password

永远不要发出密码提示。如果服务器需要密码认证，而密码无法通过其他方式（例如.pgpass文件）获得，则连接尝试将失败。此选项在没有用户输入密码的批处理作业和脚本中很有用。（……）

You probably don't need a password

您可能不需要密码

Normally this is unnecessary. The default database superuser postgresusually corresponds to the system user of the same name. Running psqlfrom this account doesn't require a password if the authentication method peeror identare set in your pg_hba.conffile. You probably have a line like this:

通常这是不必要的。默认的数据库超级用户postgres通常对应同名的系统用户。psql如果身份验证方法peer或ident在您的pg_hba.conf文件中设置，则从此帐户运行不需要密码。你可能有这样的一行：

local    all    postgres    peer

And usually also:

通常还有：

local    all    all         peer

This means, every localuser can log into a alldatabase as database user of the same name without password.
However, there is a common misconception here. Quoting again:

这意味着，每个本地用户都可以在没有密码的情况下以同名数据库用户身份登录到所有数据库。
然而，这里有一个普遍的误解。再次引用：

This method is only supported on local connections.

此方法仅在本地连接上受支持。

Bold emphasis mine.
You are connecting to localhost, which is not a "local connection", even though it has the word "local" in it. It's a TCP/IP connection to 127.0.0.1. Wikipedia on localhost:

大胆强调我的。
您正在连接到localhost，这不是“本地连接”，即使其中包含“本地”一词。它是到 127.0.0.1 的 TCP/IP 连接。本地主机上的维基百科：

On modern computer systems, localhostas a hostname translates to an IPv4 address in the 127.0.0.0/8(loopback) net block, usually 127.0.0.1, or ::1in IPv6.

在现代计算机系统上，localhost作为主机名转换为127.0.0.0/8（环回）网络块中的 IPv4 地址，通常为127.0.0.1，或::1在 IPv6 中。

Simple solution for local connections

本地连接的简单解决方案

Omit the parameter -hfrom the psqlinvocation. Quoting the manual on psqlonce more:

省略该参数-h从psql调用。再次引用手册psql：

If you omit the host name, psql will connect via a Unix-domain socketto a server on the local host, or via TCP/IP to localhoston machines that don't have Unix-domain sockets.

如果省略主机名，psql 将通过 Unix 域套接字连接 到本地主机上的服务器，或通过 TCP/IP 连接到localhost没有 Unix 域套接字的机器上。

Windows

视窗

... doesn't have Unix-domain sockets, pg_hba.conflines starting with localare not applicable on Windows. On Windows you connect via localhostby default, which brings us back to the start.

... 没有 Unix 域套接字，以pg_hba.conf开头的行local不适用于 Windows。在 Windows 上，您localhost默认通过连接进行连接，这让我们回到了起点。

If your security requirements are lax, you could just trust all connections via localhost:

如果您的安全要求不严，您可以通过localhost以下方式信任所有连接：

host    all    all    127.0.0.1/32     trust

I would only do that for debugging with remote connections off. For some more security you can use SSPI authenticationon Windows. Add this line to pg_hba.conffor "local" connections:

我只会在关闭远程连接的情况下进行调试。为了获得更高的安全性，您可以在 Windows 上使用SSPI 身份验证。将此行添加到pg_hba.conf“本地”连接：

host    all    all    127.0.0.1/32     sspi

If you actually need a password

如果您确实需要密码

You couldset an environment variable, but this is discouraged, especially for Windows. The manual:

您可以设置环境变量，但不鼓励这样做，尤其是对于 Windows。手册：

PGPASSWORDbehaves the same as the passwordconnection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpassfile (see Section 32.15).

PGPASSWORD行为与密码连接参数相同。出于安全原因，不建议使用此环境变量，因为某些操作系统允许非 root 用户通过 ps 查看进程环境变量；而是考虑使用该~/.pgpass文件（参见第 32.15 节）。

The manual on psql:

手册psql：

A conninfostring is an alternative to specify connection parameters:

甲conninfo字符串是指定连接参数的替代：

 $ psql "user=myuser password=secret_pw host=localhost port=5432 sslmode=require"

Or a URI, which is used instead of a database name:

或者使用URI代替数据库名称：

 $ psql postgresql://myuser:secret_pw@localhost:5432/mydb?sslmode=require

Password File

密码文件

But it's usually preferable to set up a .pgpassfilerather than putting passwords into script files.
Read the short chapter in the manual carefully. In particular, note that here ...

但是通常最好设置一个.pgpass文件而不是将密码放入脚本文件中。仔细
阅读手册中的简短章节。特别要注意的是，这里...

A host name of localhostmatches both TCP (host name localhost) and Unix domain socket (pghostempty or the default socket directory) connections coming from the local machine.

主机名localhost匹配来自本地机器的TCP（主机名localhost）和 Unix 域套接字（pghost空或默认套接字目录）连接。

Exact path depends on the system. This file can store passwords for multiple combinations of role and port (DB cluster):

确切的路径取决于系统。这个文件可以存储多种角色和端口组合的密码（数据库集群）：

localhost:5432:*:myadmin:myadminPasswd
localhost:5434:*:myadmin:myadminPasswd
localhost:5437:*:myadmin:myadminPasswd
...

On Windowsmachines look for the file in:

在Windows机器上查找文件：

%APPDATA%\postgresql\pgpass.conf

%APPDATA%typically resolves to: C:\Documents and Settings\My_Windows_User_Name\Application Data\.

%APPDATA%通常解析为：C:\Documents and Settings\My_Windows_User_Name\Application Data\。

回答by nyxz

I had kinda same problem:

我遇到了同样的问题：

psql -hlocalhost -d<myDB> -U<myUser>

always prompted me for password. This is as @Erwin explained because of -hlocalhostis connecting through TCP and not through the Unix-domain socket (for Unix based OS). So even if you've configured your localas trusted:

总是提示我输入密码。正如@Erwin 所解释的那样，因为-hlocalhost是通过 TCP 连接而不是通过 Unix 域套接字（对于基于 Unix 的操作系统）。因此，即使您已将您的配置local为受信任：

local   all    all                     trust

it will still prompt for password. So in order to configure the -hlocalhostto work through TCP I had to configure the hostfor localhost addresses, like so:

它仍然会提示输入密码。因此，为了配置-hlocalhost通过 TCP 工作，我必须配置host本地主机地址，如下所示：

host    all    all    127.0.0.1/32     trust
host    all    all    ::1/128          trust

But this didn't work for me. What I had to do is combine both of those as:

但这对我不起作用。我必须做的是将两者结合为：

host    all    all    localhost        trust

Some additional readings:

一些额外的阅读：

• Configuring pg_hba.conf file
• Authentication methods: trust, peer, ident, etc.

• 配置 pg_hba.conf 文件
• 认证方式：trust、peer、ident等。

回答by Darshana Patel

postgres Documentation - https://www.postgresql.org/docs/9.6/static/app-psql.html

postgres 文档 - https://www.postgresql.org/docs/9.6/static/app-psql.html

-w --no-password Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-w --no-password 从不发出密码提示。如果服务器需要密码验证并且密码不能通过其他方式（例如 .pgpass 文件）获得，则连接尝试将失败。此选项在没有用户输入密码的批处理作业和脚本中很有用。