Postgresql:使用密码编写 psql 执行脚本

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/6523019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-10 22:59:35  来源:igfitidea点击:

Postgresql: Scripting psql execution with password

postgresqlpsql

提问by Axel Fontaine

How can I call psqlso that it doesn't prompt for a password?

如何调用psql使其不提示输入密码

This is what I have:

这就是我所拥有的:

psql -Umyuser < myscript.sql

However, I couldn't find the argument that passes the password, and so psql always prompts for it.

但是,我找不到传递密码的参数,因此 psql 总是提示输入。

回答by Reece

There are several ways to authenticate to PostgreSQL. You may wish to investigate alternatives to password authentication at https://www.postgresql.org/docs/current/static/client-authentication.html.

有几种方法可以对 PostgreSQL 进行身份验证。您可能希望在https://www.postgresql.org/docs/current/static/client-authentication.html研究密码身份验证的替代方法。

To answer your question, there are a few ways provide a password for password-based authentication. The obvious way is via the password prompt. Instead of that, you can provide the password in a pgpass file or through the PGPASSWORDenvironment variable. See these:

要回答您的问题,有几种方法可以为基于密码的身份验证提供密码。显而易见的方法是通过密码提示。取而代之的是,您可以在 pgpass 文件中或通过PGPASSWORD环境变量提供密码。看到这些:

There is no option to provide the password as a command line argument because that information is often available to all users, and therefore insecure. However, in Linux/Unix environments you can provide an environment variable for a single command like this:

没有将密码作为命令行参数提供的选项,因为该信息通常可供所有用户使用,因此不安全。但是,在 Linux/Unix 环境中,您可以为单个命令提供一个环境变量,如下所示:

PGPASSWORD=yourpass psql ...

回答by Greg

PGPASSWORD=[your password] psql -Umyuser < myscript.sql

回答by jbaylina

You can add this command line at the begining of your script:

您可以在脚本的开头添加此命令行:

set PGPASSWORD=[your password]

回答by tandy

If you intend on having multiple hosts/database connections, the ~/.pgpass fileis the way to go.

如果你打算有多个主机/数据库连接,~/.pgpass 文件是要走的路。

Steps:

脚步:

  1. Create the file using vim ~/.pgpassor similar. Input your information in the following format: hostname:port:database:username:passwordDo not add string quotes around your field values. You can also use * as a wildcard for your port/database fields.
  2. You must chmod 0600 ~/.pgpassin order for it to not be silently ignored by psql.
  3. Create an alias in your bash profile that runs your psql command for you. For example:alias postygresy='psql --host hostname database_name -U username'The values should match those that you inputted to the ~/.pgpass file.
  4. Source your bash profile with . ~/.bashrcor similar.
  5. Type your alias from the command line.
  1. 使用vim ~/.pgpass或类似方法创建文件。按以下格式输入您的信息: hostname:port:database:username:password不要在字段值周围添加字符串引号。您还可以使用 * 作为端口/数据库字段的通配符。
  2. 你必须chmod 0600 ~/.pgpass为了它不被 psql 默默地忽略。
  3. 在 bash 配置文件中创建一个别名,为您运行 psql 命令。例如:alias postygresy='psql --host hostname database_name -U username'这些值应该与您输入到 ~/.pgpass 文件中的值相匹配。
  4. 使用. ~/.bashrc或类似的方式获取您的 bash 配置文件。
  5. 从命令行键入您的别名。

Note that if you have an export PGPASSWORD='' variable set, it will take precedence over the file.

请注意,如果您有一个导出 PGPASSWORD='' 变量集,它将优先于文件。

回答by ajxs

This might be an old question, but there's an alternate method you can use that no one has mentioned. It's possible to specify the password directly in the connection URI. The documentation can be found here, alternatively here.

这可能是一个老问题,但是您可以使用一种没有人提到的替代方法。可以直接在连接 URI 中指定密码。该文件可以发现在这里,或者在这里

You can provide your username and password directly in the connection URI provided to psql:

您可以直接在提供给的连接 URI 中提供您的用户名和密码psql

# postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
psql postgresql://username:password@localhost:5432/mydb

回答by Jamie Hutber

If you're having problems on windows like me (I'm using Windows 7 64-bit) and set PGPASSWORD=[Password]did not work.

如果您在像我这样的 Windows 上遇到问题(我使用的是 64 位 Windows 7)并且set PGPASSWORD=[Password]无法正常工作。

Then, as Kavaklioglu said in one of the comments,

然后,正如 Kavaklioglu 在其中一条评论中所说,

export PGPASSWORD=[password]

You will need to save this at the top of the file, or before any usage so its set before being called.

您需要将其保存在文件的顶部,或在任何使用之前,以便在调用之前进行设置。

Certainly does work on windows :)

当然可以在 Windows 上工作:)

回答by Femi

You have to create a password file: see http://www.postgresql.org/docs/9.0/interactive/libpq-pgpass.htmlfor more info.

您必须创建一个密码文件:有关更多信息,请参阅http://www.postgresql.org/docs/9.0/interactive/libpq-pgpass.html

回答by mightybyte

Given the security concerns about using the PGPASSWORD environment variable, I think the best overall solution is as follows:

鉴于使用 PGPASSWORD 环境变量的安全问题,我认为最好的整体解决方案如下:

  1. Write your own temporary pgpass file with the password you want to use.
  2. Use the PGPASSFILE environment variable to tell psql to use that file.
  3. Remove the temporary pgpass file
  1. 使用您要使用的密码编写您自己的临时 pgpass 文件。
  2. 使用 PGPASSFILE 环境变量告诉 psql 使用该文件。
  3. 删除临时 pgpass 文件

There are a couple points of note here. Step 1 is there to avoid mucking with the user's ~/.pgpass file that might exist. You also must make sure that the file has permissions 0600 or less.

这里有几点需要注意。第 1 步是为了避免处理可能存在的用户 ~/.pgpass 文件。您还必须确保该文件具有 0600 或更少的权限。

Some have suggested leveraging bash to shortcut this as follows:

有些人建议利用 bash 来简化此操作,如下所示:

PGPASSFILE=<(echo myserver:5432:mydb:jdoe:password) psql -h myserver -U jdoe -p 5432 mydb

This uses the <() syntax to avoid needing to write the data to an actual file. But it doesn't work because psql checks what file is being used and will throw an error like this:

这使用 <() 语法来避免需要将数据写入实际文件。但它不起作用,因为 psql 检查正在使用的文件,并会抛出这样的错误:

WARNING: password file "/dev/fd/63" is not a plain file

回答by pyAddict

It can be done simply using PGPASSWORD. I am using psql 9.5.10. In your case the solution would be

只需使用 PGPASSWORD 即可完成。我正在使用 psql 9.5.10。在您的情况下,解决方案是

PGPASSWORD=password psql -U myuser < myscript.sql

PGPASSWORD=password psql -U myuser < myscript.sql

回答by Eliyahu Skoczylas

Building on mightybyte's answerfor those who aren't comfortable with *nixshell scripting, here's a working script:

对于那些不熟悉*nixshell 脚本的人,基于mightybyte 的回答,这是一个工作脚本:

#!/bin/sh
PGPASSFILE=/tmp/pgpasswd$$
touch $PGPASSFILE
chmod 600 $PGPASSFILE
echo "myserver:5432:mydb:jdoe:password" > $PGPASSFILE
export PGPASSFILE
psql mydb
rm $PGPASSFILE

The double dollar sign ($$) in /tmp/pgpasswd$$at line 2 appends the process ID number to the file name, so that this script can be run more than once, even simultaneously, without side effects.

第2 行中的双美元符号 ( $$)/tmp/pgpasswd$$将进程 ID 号附加到文件名,因此该脚本可以多次运行,甚至可以同时运行,而不会产生副作用。

Note the use of the chmodcommand at line 4 - just like the "not a plain file" error that mightybytedescribed, there's also a "permissions" error if this is not done.

请注意第chmod4 行命令的使用- 就像mayybyte描述的“不是普通文件”错误一样,如果不这样做,也会出现“权限”错误。

At line 7, you won't have to use the -hmyserver, the -pmyport, or -Ujdoeflag if you use the defaults (localhost: 5432) and only have one database user. For multiple users, (but the default connection) change that line to

在第 7 行,如果您使用默认值 ( localhost: 5432) 并且只有一个数据库用户,则不必使用-hmyserver-pmyport-Ujdoe标志。对于多个用户,(但默认连接)将该行更改为

psql mydb jdoe

Don't forget to make the script executablewith

不要忘了使脚本可执行

chmod +x runpsql(or whatever you called the script file)

chmod +x runpsql或任何你称之为脚本文件的东西

UPDATE:

更新:

I took RichVel's advice and made the file unreadable beforeputting the password into it. That closes a slight security hole. Thanks!

我接受了RichVel的建议,并将密码放入文件之前使文件不可读。这关闭了一个轻微的安全漏洞。谢谢!