使用无口令的psql命令运行批处理文件 [英] Run batch file with psql command without password

问题描述

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

  psql --host = localhost --dbname =< dbname> --port =<端口号> 
 --username =< dbuser> --file = C：\PSQL_Script.txt --output = C：\PSQL_Output.txt 
  

问题是每次执行批处理脚本时都要求输入密码。

解决方案

有关密码继续弹出。继续阅读， 最佳选项最后 。

只保留密码请求

如果您的问题只是密码提示，可以将其静默。我引用此处的说明：

-w

- no-password

不要发出密码提示。如果服务器需要密码认证，并且
密码不能通过其他方式（如
a .pgpass 文件），连接尝试将失败。此选项可以是
在无用户输入密码的批处理作业和脚本中有用。 （...）

您可能不需要密码

通常这是不必要的。默认数据库超级用户 postgres 通常对应于同名的系统用户。如果 psql 不需要密码.html＃AUTH-PEERrel =nofollow noreferrer>验证方法 同级 或 ident 在您的 pg_hba.conf 文件中设置。您可能有这样的行：

  local all postgres peer 
  

通常也是：

  local all all peer 
  

em>用户可以以无密码的相同名称的数据库用户身份登录全部数据库。

但，这里有一个常见的误解。 再次引用：

此方法仅支持本地连接。

大胆强调我。

您正在连接到 localhost ，这是不是本地连接，即使它有local这个词。它是到127.0.0.1的TCP / IP连接。 localhost上的维基百科：

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








本地连接的简单解决方案





省略参数 -h 从 psql 调用。引用 <$ c $的手册c> psql ：

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

Windows

。 ..没有Unix域套接字， pg_hba.conf 以 local 开头的行不适用于Windows。在Windows上，默认情况下通过 localhost 连接，这会将我们带回到开始。

如果您的安全要求lax，你可以通过 localhost 来信任所有连接：

  host all all 127.0.0.1/32 trust 
  

远程连接关闭。为了提高某些安全性，您可以使用 SSPI验证在Windows上。将此行添加到 pg_hba.conf 中为本地连接：

  host all all 127.0.0.1/32 sspi 
  

密码

您可以设置环境变量，但这是 > ，特别适用于Windows。 手册：

PGPASSWORD 的行为与密码连接
参数。出于安全考虑，不推荐使用此环境变量
，因为一些操作系统允许非root用户通过ps查看过程环境变量;而
考虑使用〜/ .pgpass 文件（请参阅 Section 32.15 ）。

psql上的手册 ：

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

  $ psqluser = myuser password = secret_pw host = localhost port = 5432 sslmode = require
  

或 URI ，代替数据库名称：

  $ psql postgresql：// myuser：secret_pw @ localhost：5432 / mydb？sslmode = require 
  

密码档案

但通常建议您设定 .pgpass 文件 比将密码输入到脚本文件中。

请阅读短篇小说手册仔细。请特别注意，此处...

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

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

  localhost：5432 ：*：myadmin：myadminPasswd 
 localhost：5434：*：myadmin：myadminPasswd 
 localhost：5437：*：myadmin：myadminPasswd 
 ... 
  
 
 
 
在 Windows 机器上查找文件：
  C：\Documents和Settings\My_Windows_User_Name\Application Data\postgresql 
  
 
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?
解决方案
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
  
  
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. (...)


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

  
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.


Simple solution for local connections

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

  
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.


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.

If your security requirements are lax, you could just trust all connections via 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:
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:

  
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).
The manual on psql:

A conninfo string is an alternative to specify connection parameters:
 $ psql "user=myuser password=secret_pw host=localhost port=5432 sslmode=require"
Or a URI, which is used instead of a database name:
 $ 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 ...

  
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 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:
C:\Documents and Settings\My_Windows_User_Name\Application Data\postgresql


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