使用Ansible Playbook安装PostgreSQL 12并创建用户及数据库时遇到权限错误的解决方法咨询

我尝试用下面的Playbook在Ubuntu 22和23版本的机器上安装PostgreSQL 12，同时给postgres用户设置默认密码，之后创建新的用户和数据库：

- hosts: local
  become: yes
  become_method: sudo
  vars:
    postgresql_version: 12
    db_name: your_database
    db_user: your_user
    db_password: your_password
    postgres_password: your_postgres_password
  tasks:
    - name: Update apt cache
      apt: update_cache=yes

    - name: Add the PostgreSQL APT key
      apt_key:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        state: present

    - name: Add the PostgreSQL APT repositories
      apt_repository:
        repo: deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main
        state: present

    - name: Update apt cache
      apt: update_cache=yes

    - name: Install PostgreSQL
      apt:
        name: postgresql-{{ postgresql_version }}
        state: present

    - name: Set password for the default PostgreSQL user
      become: yes
      become_user: postgres
      shell: 'psql -c "ALTER USER postgres WITH PASSWORD ''{{ postgres_password }}'';"'

    - name: Create a new user in PostgreSQL
      become: yes
      become_user: postgres
      shell: 'psql -c "CREATE USER {{ db_user }} WITH PASSWORD ''{{ db_password }}'' SUPERUSER;"'

    - name: Create a new database in PostgreSQL
      become: yes
      become_user: postgres
      shell: 'psql -c "CREATE DATABASE {{ db_name }} WITH OWNER {{ db_user }};"'

    - name: Create a new database in PostgreSQL
      become: yes
      become_user: postgres
      command: 'psql -c "CREATE DATABASE {{ db_name }} WITH OWNER {{ db_user }};"'

但运行时遇到了下面的错误：

TASK [Set password for the default PostgreSQL user]
******************************************************************** fatal: [localhost]: FAILED! => {"msg": "Failed to set permissions on
the temporary files Ansible needs to create when becoming an
unprivileged user (rc: 1, err: chmod: invalid mode:
‘A+user:postgres:rx:allow’\nTry 'chmod --help' for more
information.\n}). For information on working around this, see
https://docs.ansible.com/ansible-core/2.14/user_guide/become.html#risks-of-becoming-an-unprivileged-user"}

PLAY RECAP
************************************************************************************************************* localhost : ok=6 changed=2 unreachable=0
failed=1 skipped=0 rescued=0 ignored=0

我的inventory文件内容是：

[local]
localhost ansible_connection=local

请问该怎么修复这个问题？

嘿，这个权限错误我之前也碰到过，主要是Ansible在切换到非特权用户（比如postgres）时，需要用ACL来设置临时文件的权限，但你的系统可能没装ACL工具，或者用shell模块切换用户的方式容易踩坑。给你两个靠谱的解决办法：

方法一：先安装ACL工具 #

在Playbook的开头，添加一个安装acl包的任务，这样Ansible就能正确处理临时文件的权限了：

- name: Install ACL package
  apt:
    name: acl
    state: present

把这个任务放在第一个Update apt cache任务之后就行，安装完ACL再继续后面的步骤，应该就能解决权限问题。

方法二：改用Ansible官方的PostgreSQL模块（更推荐） #

用shell/command模块直接执行psql命令虽然直观，但容易遇到权限问题，而且不符合Ansible的最佳实践。Ansible有专门的postgresql_user和postgresql_db模块，这些模块会自动处理用户切换和权限问题，更稳定。

修改后的Playbook任务部分如下：

# 先确保安装postgresql模块依赖
- name: Install python3-psycopg2 (PostgreSQL module dependency)
  apt:
    name: python3-psycopg2
    state: present

- name: Set password for default postgres user
  postgresql_user:
    name: postgres
    password: "{{ postgres_password }}"
    login_unix_socket: /var/run/postgresql
  become: yes
  become_user: postgres

- name: Create new PostgreSQL user
  postgresql_user:
    name: "{{ db_user }}"
    password: "{{ db_password }}"
    role_attr_flags: SUPERUSER
    login_unix_socket: /var/run/postgresql
  become: yes
  become_user: postgres

- name: Create new PostgreSQL database
  postgresql_db:
    name: "{{ db_name }}"
    owner: "{{ db_user }}"
    login_unix_socket: /var/run/postgresql
  become: yes
  become_user: postgres

注意要先安装python3-psycopg2，这是PostgreSQL模块的依赖包。用这些模块的话，不仅能避免权限错误，还能让Playbook更具幂等性（重复运行不会报错）。

另外，我看到你Playbook里有两个重复的创建数据库任务，记得删掉其中一个哦～

备注：内容来源于stack exchange，提问作者user2331760