使用没有密码的 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
这意味着,每个本地用户都可以在没有密码的情况下以同名数据库用户身份登录所有数据库.
然而,这里有一个常见的误解.再次引用:
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 the127.0.0.0/8
(loopback) net block, usually127.0.0.1
, or::1
in IPv6.
本地连接的简单解决方案
省略 psql
调用中的参数 -h
.引用 psql
再次:
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).
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 namelocalhost
) 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屋!