bash 如何以非交互方式为“psql”指定密码？

How do I specify a password to 'psql' non-interactively?

提问by Alex N.

I am trying to automate database creation process with a shell script and one thing I've hit a road block with passing a password to psql. Here is a bit of code from the shell script:

我正在尝试使用 shell 脚本自动执行数据库创建过程，并且在将密码传递给psql 时遇到了一个障碍。这是shell脚本中的一些代码：

psql -U $DB_USER -h localhost -c"$DB_RECREATE_SQL"

How do I pass a password to psqlin a non-interactive way?

如何psql以非交互方式传递密码？

采纳答案by Flimzy

From the official documentation:

从官方文档：

It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 30.13for more information.

有一个 ~/.pgpass 文件也很方便，以避免经常输入密码。有关更多信息，请参阅第 30.13 节。

...

...

This file should contain lines of the following format:

此文件应包含以下格式的行：

hostname:port:database:username:password

The password field from the first line that matches the current connection parameters will be used.

将使用与当前连接参数匹配的第一行的密码字段。

回答by a_horse_with_no_name

Set the PGPASSWORD environment variable inside the script before calling psql

在调用 psql 之前在脚本中设置 PGPASSWORD 环境变量

PGPASSWORD=pass1234 psql -U MyUsername myDatabaseName

For reference, see http://www.postgresql.org/docs/current/static/libpq-envars.html

如需参考，请参阅http://www.postgresql.org/docs/current/static/libpq-envars.html

---

---

Edit

编辑

Since Postgres 9.2 there is also the option to specify a connection string or URIthat can contain the username andpassword.

从 Postgres 9.2 开始，还可以选择指定可以包含用户名和密码的连接字符串或 URI。

Using that is a security risk because the password is visible in plain text when looking at the command line of a running process e.g. using ps(Linux), ProcessExplorer (Windows) or similar tools, by other users.

使用它存在安全风险，因为当其他用户查看正在运行的进程的命令行时，例如使用ps(Linux)、ProcessExplorer (Windows) 或类似工具时，密码以纯文本形式可见。

See also this question on Database Administrators

另请参阅有关数据库管理员的此问题

回答by user4653174

• in one line:

  export PGPASSWORD='password'; psql -h 'server name' -U 'user name' -d 'base name' -c 'command'
  

  with commanda sql command such as "select * from schema.table"

• or more readable:

  export PGPASSWORD='password'
  psql -h 'server name' -U 'user name' -d 'base name' \
       -c 'command' (eg. "select * from schema.table")
  

• 在一行中：

  export PGPASSWORD='password'; psql -h 'server name' -U 'user name' -d 'base name' -c 'command'
  

  用命令一个 sql 命令，例如"select * from schema.table"

• 或更易读：

  export PGPASSWORD='password'
  psql -h 'server name' -U 'user name' -d 'base name' \
       -c 'command' (eg. "select * from schema.table")
  

回答by Jacques Gaudin

I tend to prefer passing a URL to psql:

我倾向于将 URL 传递给psql：

psql "postgresql://$DB_USER:$DB_PWD@$DB_SERVER/$DB_NAME"

This gives me the freedom to name my environment variables as I wish and avoids creating unnecessary files.

这使我可以根据需要自由地命名环境变量，并避免创建不必要的文件。

This requires libpq. The documentation can be found here.

这需要libpq. 文档可以在这里找到。

回答by JAGJ jdfoxito

On Windows:

在 Windows 上：

1. Assign value to PGPASSWORD: C:\>set PGPASSWORD=pass

2. Run command: C:\>psql -d database -U user

1. 为 PGPASSWORD 赋值： C:\>set PGPASSWORD=pass

2. 运行命令： C:\>psql -d database -U user

Ready

准备好

Or in one line,

或者在一行中，

set PGPASSWORD=pass&& psql -d database -U user

Note the lack of space before the && !

请注意 && 之前缺少空格！

回答by Srini

This can be done by creating a .pgpassfile in the home directory of the (Linux) User. .pgpassfile format:

这可以通过.pgpass在 (Linux) 用户的主目录中创建一个文件来完成。 .pgpass文件格式：

<databaseip>:<port>:<databasename>:<dbusername>:<password>

You can also use wild card *in place of details.

您还可以使用通配符*代替详细信息。

Say I wanted to run tmp.sqlwithout prompting for a password.

假设我想在tmp.sql不提示输入密码的情况下运行。

With the following code you can in *.sh file

使用以下代码，您可以在 *.sh 文件中

echo "192.168.1.1:*:*:postgres:postgrespwd" > $HOME/.pgpass
echo "` chmod 0600 $HOME/.pgpass `"

echo " ` psql -h 192.168.1.1 -p 5432  -U postgres  postgres  -f tmp.sql `        

回答by ubi

An alternative to using the PGPASSWORDenvironment variable is to use the conninfostring according to the documentation:

使用PGPASSWORD环境变量的另一种方法是conninfo根据文档使用字符串：

An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection.

指定连接参数的另一种方法是在 conninfo 字符串或 URI 中，它用于代替数据库名称。这种机制使您可以非常广泛地控制连接。

$ psql "host=<server> port=5432 dbname=<db> user=<user> password=<password>"

postgres=>

回答by Rob

Added content of pg_env.sh to my .bashrc:

将 pg_env.sh 的内容添加到我的 .bashrc 中：

cat /opt/PostgreSQL/10/pg_env.sh

#!/bin/sh
# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/10/bin:$PATH
export PGDATA=/opt/PostgreSQL/10/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5433
export PGLOCALEDIR=/opt/PostgreSQL/10/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/10/share/man

with addition of (as per user4653174 suggestion)

添加（根据 user4653174 建议）

export PGPASSWORD='password'