使用 psql 命令运行批处理文件，无需密码 [英] Run batch file with psql command without password

问题描述

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

I am trying to execute this psql command using a batch script:

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?

推荐答案

继续阅读，最好的选项排在最后.但让我们先澄清几件事.

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

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

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

-w
--无密码

永远不要发出密码提示.如果服务器需要密码验证并且无法通过其他方式获得密码，例如.pgpass 文件，连接尝试将失败.这个选项可以在没有用户输入密码的批处理作业和脚本中很有用.(...)

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

您可能不需要密码

通常这是不必要的.默认的数据库超级用户 postgres 通常对应同名的系统用户.如果 身份验证方法 peer 或 ident 在你的 pg_hba.conf 中设置 文件.你可能有这样的一行:

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:

local    all    postgres    peer

通常还有:

local    all    all         peer

这意味着，每个本地用户都可以以同名数据库用户身份登录all数据库，无需密码.
但是，这里有一个常见的误解.再次引用:

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:

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

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

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.0/8(环​​回)网络块中的 IPv4 地址，通常是 127.0.0.1，或 IPv6 中的 ::1.

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.

本地连接的简单解决方案

在 psql 调用中省略参数 -h.引用 关于 psql<的手册/code> 再次:

Simple solution for local connections

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

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

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.

窗口

... 没有 Unix 域套接字，以 local 开头的 pg_hba.conf 行不适用于 Windows.在 Windows 上，您默认通过 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.

如果您的安全要求不严格，您可以信任所有通过 localhost 的连接:

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

host    all    all    127.0.0.1/32     trust

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

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:

host    all    all    127.0.0.1/32     sspi

如果您确实需要密码

您可以设置一个环境变量，但这是不鼓励，尤其是对于Windows.手册:

PGPASSWORD 的行为与 密码 连接范围.不推荐使用此环境变量出于安全原因，因为某些操作系统允许非 root用户通过ps查看进程环境变量；反而考虑使用 ~/.pgpass 文件(参见 第 32.15 节).

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

手册psql:

A conninfo 字符串是指定连接参数的替代方法:

A conninfo string is an alternative to specify connection parameters:

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

或 URI，用于代替数据库名称:

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

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

密码文件

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

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

localhost 的主机名同时匹配 TCP(主机名 localhost)和 Unix 域套接字(pghost 为空或默认套接字目录)来自本地计算机的连接.

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.

具体路径取决于系统.该文件可以存储多种角色和端口组合的密码(数据库集群):

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

在 Windows 机器上查找文件:

On Windows machines look for the file in:

%APPDATA%postgresqlpgpass.conf

%APPDATA% 通常解析为:C:Documents and SettingsMy_Windows_User_NameApplication Data.

这篇关于使用 psql 命令运行批处理文件，无需密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！