PostgreSQL 与 Ansible 的对等身份验证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25751085/
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
PostgreSQL failing peer authentication with Ansible
提问by oakservice
I am running PostgreSQL 9.3 on FreeBSD. FreeBSD uses pgsqlas the default system user for PostgreSQL. My /usr/local/pgsql/data/pg_hba.conflooks like this:
我在 FreeBSD 上运行 PostgreSQL 9.3。FreeBSDpgsql用作 PostgreSQL 的默认系统用户。我的/usr/local/pgsql/data/pg_hba.conf看起来像这样：
# TYPE DATABASE USER ADDRESS METHOD
local all pgsql peer
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
With this configuration I can connect to the database as pgsqlwithout a password.
通过这种配置，我可以在pgsql没有密码的情况下连接到数据库。
$ su pgsql
$ psql template1
template1=# \l
List of databases
...
That works as intended.
这按预期工作。
On a remote machine, I have an Ansible task to create a database on the FreeBSD server.
在远程机器上，我有一个 Ansible 任务来在 FreeBSD 服务器上创建一个数据库。
- name: Create the postgresql database
postgresql_db: name=mydatabase login_user=pgsql
Executing this task fails with the error Peer authentication failed for user "pgsql".
执行此任务失败并显示错误Peer authentication failed for user "pgsql"。
PLAY [web] ********************************************************************
GATHERING FACTS ***************************************************************
ok: [host.example.org]
TASK: [database | Create the postgresql database] *****************************
failed: [host.example.org] => {"failed": true}
msg: unable to connect to database: FATAL: Peer authentication failed for user "pgsql"
FATAL: all hosts have already failed -- aborting
Why does this fail when peer authentication for the user pgsqlis clearly working?
当用户的对等身份验证pgsql显然有效时，为什么会失败？
回答by gitaarik
This worked for me:
这对我有用：
- name: Create postgres database
become: true
become_user: postgres
postgresql_db:
name: <database-name>
In your specific case the user might be pgsql, but I think usually the user is postgres.
在您的特定情况下，用户可能是pgsql，但我认为用户通常是postgres.
回答by Most Wanted
Or with slightly different syntax (from Ansible 1.9) and for user creation (might be helpful for someone)
或者使用稍微不同的语法（来自 Ansible 1.9）和用户创建（可能对某人有帮助）
- name: Create postgres user
postgresql_user: name={{ pg_user }} password={{ pg_password }}
become: true
become_user: postgres
回答by Dylan Pierce
For those running into "Failed to set permissions on the temporary files Ansible needs to create..." in order to switch to the postgresuser with become_useryou can leverage pipelining on Ubuntu hosts.
对于那些遇到“无法对 Ansible 需要创建的临时文件设置权限...”以便切换到postgres用户的问题，become_user您可以利用 Ubuntu 主机上的流水线。
Create a ansible.cfgin your playbook directory and add the following lines:
ansible.cfg在您的剧本目录中创建一个并添加以下行：
[ssh_connection]
pipelining=True
Update:according to @lolcode Ansible 2.9.0 has updated to ansible_pipelining
更新：根据@lolcode Ansible 2.9.0 已更新为ansible_pipelining
[ssh_connection]
ansible_piplining = true
Update 4/30/2020:for those who still have issues, try installing aclwhich will cause Ansible to use this acl filesystem to mount module that need to be accessible by the 2nd user instead of making them readable by everyone. Thanks @Andreas Florath
2020 年 4 月 30 日更新：对于那些仍然有问题的人，请尝试安装acl这将导致 Ansible 使用此 acl 文件系统来挂载需要由第二个用户访问的模块，而不是让每个人都可以读取它们。谢谢@Andreas Florath
- name: install setfacl support
become: yes
apt: pkg=acl
回答by René Pijl
I had the same problem. In my case I overlooked that I configured my Ansible-playbook to run as another Linux user than the one with peer access (pgsql in your case). Solution is either run the Ansible play as pgsql:
我有同样的问题。就我而言，我忽略了我将 Ansible-playbook 配置为作为另一个 Linux 用户运行而不是具有对等访问权限的用户（在您的情况下为 pgsql）。解决方案是作为 pgsql 运行 Ansible play：
- name: Create the postgresql database
remote_user: pgsql
postgresql_db: name=mydatabase login_user=pgsql
...
Or run it as root, and su to pgsql for the command:
或者以 root 身份运行它，然后 su 到 pgsql 的命令：
- name: Create the postgresql database
remote_user: root
become: yes
become_user: pgsql
postgresql_db: name=mydatabase login_user=pgsql
...
... depending on your access rights via ssh.
... 取决于您通过 ssh 的访问权限。
This is using Ansible 2.0.
这是使用 Ansible 2.0。
回答by mdh
Another workaround is to connect via host(localhost) rather than the default localpeer authentication method:
另一种解决方法是通过host(localhost) 而不是默认的local对等身份验证方法进行连接：
- name: Create the postgresql database
postgresql_db:
name: mydatabase
login_user: postgres
login_host: 127.0.0.1
Depending on the settings in pg_hba.conf, you may also need to provide login_password. You can circumvent this by setting
根据 中的设置pg_hba.conf，您可能还需要提供login_password. 您可以通过设置来规避这一点
host all postgres 127.0.0.1/32 md5
to
到
host all postgres 127.0.0.1/32 trust
回答by bugged
I notice your Postgres version is really out of date (9.3). I had this issue recently when working on an Ubuntu 14 server with Postgres 9.3.
我注意到你的 Postgres 版本真的过时了（9.3）。我最近在使用 Postgres 9.3 的 Ubuntu 14 服务器上工作时遇到了这个问题。
I tried a dozen different things, and finally what worked was installing the aclpackage via apt. Ansible uses it for navigating some of it's permissions issues. The package is installed by default on newer distros, hence why I've only seen this problem crop up on an old server.
我尝试了十几种不同的方法，最后acl通过 apt安装软件包。Ansible 使用它来解决它的一些权限问题。该软件包默认安装在较新的发行版上，因此我只看到这个问题出现在旧服务器上。
回答by Rooie3000
Thanks to this threat I made a variant of mdh's post. When I set up a database I generate a password for the postgres user and I store it in a file under the root directory.
由于这个威胁，我制作了 mdh 帖子的变体。当我设置数据库时，我为 postgres 用户生成一个密码，并将其存储在根目录下的文件中。
I thought why not store it also (or instead) in a .pgpass file for root. So I created a template like this (only last line is important):
我想为什么不把它也（或代替）存储在一个 .pgpass 文件中供 root 使用。所以我创建了一个这样的模板（只有最后一行很重要）：
#### password file for posgres connection ###
#### *:*:*:*
#### works like
#### * : * : * : *
#### <ip addr> : <port nr> : <db name> : <password>
127.0.0.1:*:*:postgres:{{ new_postgres_pass }}
Store the .pgpass file in the home directory of root. Now you can use the module as root without switching user of having to change the pg_hba.conf:
将 .pgpass 文件存储在 root 的主目录中。现在您可以以 root 用户身份使用该模块，而无需切换用户，而不必更改 pg_hba.conf：
- name: Ensure postgresql mydatabase
postgresql_db:
name: mydatabase
login_user: postgres
login_host: 127.0.0.1
回答by Laurent B
So if I understood well you are on a remote machine, and maybe you should change /usr/local/pgsql/data/pg_hba.conf to allow remote connections 'host all all 0.0.0.0/0 md5' or another specific network address.
因此，如果我理解您在远程机器上，也许您应该更改 /usr/local/pgsql/data/pg_hba.conf 以允许远程连接'host all all 0.0.0.0/0 md5' 或其他特定网络地址。
