使用不带密码的psql命令运行批处理文件

I am trying to execute this psql command 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?

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

2 个解决方案

#1

Questions about login without password keep popping up. 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

-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. (...)

永远不会发出密码提示。如果服务器需要密码身份验证，并且其他方式（例如.pgpass文件）无法使用密码，则连接尝试将失败。此选项在没有用户输入密码的批处理作业和脚本中非常有用。 （......）

You probably don't need a password

Normally this is unnecessary. The default database superuser postgres usually corresponds to the system user of the same name. Running psql from this account doesn't require a password if the authentication method peer or ident are set in your pg_hba.conf file. You probably have a line like this:

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

local    all    postgres    peer

And usually also:

通常还有：

local    all    all         peer

This means, every local user can log into a all database 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连接。 localhost上的*：

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

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

Simple solution for local connections

Omit the parameter -h from the psql invocation. Quoting the manual on psql once more:

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

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

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

Windows

... doesn't have Unix-domain sockets, pg_hba.conf lines starting with local are not applicable on Windows. On Windows you connect via localhost by default, which brings us back to the start.

...没有Unix域套接字，以local开头的pg_hba.conf行不适用于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 authentication on Windows. Add this line to pg_hba.conf for "local" connections:

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

host    all    all    127.0.0.1/32     sspi

If you actually need a password

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

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

PGPASSWORD behaves the same as the password connection 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 ~/.pgpass file (see Section 32.15).

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

The manual on psql:

关于psql的手册：

A conninfo string 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 .pgpass file rather than putting passwords into script files.
Read the short chapter in the manual carefully. In particular, note that here ...

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

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

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

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

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

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

On Windows machines look for the file in:

在Windows机器上查找文件：

C:\Documents and Settings\My_Windows_User_Name\Application Data\postgresql

#2

I had kinda same problem:

我有同样的问题：

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

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

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

local   all    all                     trust

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

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

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
• 配置pg_hba.conf文件
• Authentication methods: trust, peer, ident, etc.
• 身份验证方法：信任，对等，身份等