"Make sure the PostgreSQL users are present" task fails

[IvikGH]

I have such error

TASK [ANXS.postgresql : PostgreSQL | Make sure the PostgreSQL users are present] ***
fatal: [163.172.154.240]: FAILED! => {"failed": true, "msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chown: changing ownership of '/tmp/ansible-tmp-1489444628.62-212718584096187/': Operation not permitted\nchown: changing ownership of '/tmp/ansible-tmp-1489444628.62-212718584096187/postgresql_user.py': Operation not permitted\n). For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}
	to retry, use: --limit @/eqa/standalone/ansible_provision/install_postgre.retry

It appears only when I setup Postgres on Scaleway VPS. Error doesn't appear when I setup Postgres on DigitalOcean VPS. Any ideas ?

ansible 2.2.0.0

[maglub]

I assume you run the role in your playbook with "become: yes". In later ansible releases (>1.9), the security has been tightened, so that it is tricky to first become root, then switch to a less privileged user.

Simple workaround is to remove the "become: yes" and "become_user" in the affected tasks, as most of them can be carried out as the root user. In my case, I only had to uncomment in a couple of places, as I don't install any extensions or modules:

[email protected]:/Users/malu/dev/peaq/ansible $find roles/ANXS.postgresql/ -type f -exec grep -nE "become:|become_user:" /dev/null {} \;
roles/ANXS.postgresql//tasks/configure.yml:20:  become: yes
roles/ANXS.postgresql//tasks/configure.yml:28:  become: yes
roles/ANXS.postgresql//tasks/configure.yml:38:  become: yes
roles/ANXS.postgresql//tasks/configure.yml:39:  become_user: "{{ postgresql_service_user }}"
roles/ANXS.postgresql//tasks/configure.yml:47:  become: yes
roles/ANXS.postgresql//tasks/configure.yml:48:  become_user: "{{ postgresql_service_user }}"
roles/ANXS.postgresql//tasks/configure.yml:61:  become: yes
roles/ANXS.postgresql//tasks/configure.yml:62:  become_user: "{{ postgresql_service_user }}"
roles/ANXS.postgresql//tasks/configure.yml:96:  become: true
roles/ANXS.postgresql//tasks/configure.yml:97:  become_user: "{{ postgresql_service_user }}"
roles/ANXS.postgresql//tasks/databases.yml:19:#  become: yes
roles/ANXS.postgresql//tasks/databases.yml:20:#  become_user: "{{postgresql_admin_user}}"
roles/ANXS.postgresql//tasks/databases.yml:26:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:27:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:35:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:36:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:45:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:46:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:55:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:56:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:62:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:63:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:69:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:70:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:76:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:77:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/databases.yml:83:  become: yes
roles/ANXS.postgresql//tasks/databases.yml:84:  become_user: "{{postgresql_service_user}}"
roles/ANXS.postgresql//tasks/users.yml:16:#  become: yes
roles/ANXS.postgresql//tasks/users.yml:17:#  become_user: "{{postgresql_admin_user}}"
roles/ANXS.postgresql//tasks/users_privileges.yml:13:#  become: yes
roles/ANXS.postgresql//tasks/users_privileges.yml:14:#  become_user: "{{postgresql_admin_user}}

I would have to dig deeper into this to figure out a more beautiful solution.

[IvikGH]

Thanks, I'll try it later.

[AJamesPhillips]

@IvikGH did you solve it using this approach?

[maglub]

It works for me, after I:

• Commented out all "become: yes" and "become_user", and run the playbook/role with "become: yes"
• I added a template/pg_ident.conf.j2

#=== https://www.postgresql.org/docs/9.3/static/auth-username-maps.html

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
{% for map in postgresql_pg_ident_default %}
{% if map.comment is defined %}
# {{map.comment}}
{% endif %}
{{map.name}}  {{map.system_username}}  {{map.database_username}}
{% endfor %}

In my playbook, I added this to my variables:

    - postgresql_pg_ident_default:
      - { name: root-postgres, system_username: root, database_username: postgres, comment: 'Map root to user postgres' }
      - { name: root-postgres, system_username: postgres, database_username: postgres, comment: 'Map postgres to user postgres' }

In tasks/configure.yml

 - name: PostgreSQL | Update configuration - pt. 1.2 (pg_ident.conf)
   template:
     src: pg_ident.conf.j2
     dest: "{{postgresql_conf_directory}}/pg_ident.conf"
     owner: "{{ postgresql_service_user }}"
     group: "{{ postgresql_service_group }}"
     mode: 0640
   register: postgresql_configuration_pt1_2
...
...
...
- name: PostgreSQL | Restart PostgreSQL
  service:
    name: "{{ postgresql_service_name }}"
    state: restarted
  when: postgresql_configuration_pt1.changed or postgresql_configuration_pt1_2 or postgresql_configuration_pt2.changed or postgresql_configuration_pt3.changed or postgresql_systemd_custom_conf.changed

This way, root becomes "postgres" when logging in.

[ataki]

+1

Having the identical problem on ansible version 2.4.2.0 on a Vagrant machine with precise32. I've tried the fix that @maglub suggested, and still get the same issue:

TASK [ANXS.postgresql : PostgreSQL | Make sure the PostgreSQL users are present] *****************************************************************************
fatal: [192.168.0.42]: FAILED! => {"msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chown: changing ownership of `/tmp/ansible-tmp-1513648521.762527-131306153344239/': Operation not permitted\nchown: changing ownership of `/tmp/ansible-tmp-1513648521.762527-131306153344239/postgresql_user.py': Operation not permitted\n}). For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}

I've tried combinations of commenting out become=yes and become_user=root in my playbook as well as the role file tasks/configure.yml.

[maglub]

A colleague of mine, Ranko, had a similar issue recently, where we had a very similar scenario. We use a privileged user which is not "root", and we wanted to use "become_user: someone_else"

His solution (on Ubuntu 16) was to install the package "acl" on the server after which his playbooks worked.

Perhaps that is a solution for you as well? I will try and find time to test it asap.

[ataki]

@maglub thanks for the tip. I wound up resolving the issue by switching my vm version to the one prescribed in the Vagrantfile: bento/ubuntu-16.04.

Beyond what I noted above, there's also a hurdle with getting cert verification for www.postgresql.com on the vm precise32. The fix is to add a keyserver, but these hurdles seem to indicate limited support for precise32 and other flavors of Ubuntu not listed in the Vagrantfile. If so, you may want to note this for future users so they're not jumping through hoops.

Cheers

[neooleg]

@maglub thanks a lot for sharing solutions!

We're using "root", but still encountering the same issue. However, if I understand your correctly - we' shouldn't!?

In the master Play:

  become_user: root
  become_method: sudo

In the "wrapper" role:

- name: Install PostgreSQL from Galaxy and create user w/ CREATEDB role
  become: yes
  import_role:
    name: galaxy/ANXS.postgresql

Could you please clarify is this correct way (use root) to avoid the issue?

Thanks!

[maglub]

Hi,

I just had a look at this, and figured out how to install postgres using an unmodified ANXS.postgres by using the following construct.

I personally avoid ever logging in (ansible_user) as root, and am always using another user that is highly privileged. In my case it is called "ops", and ops can use "sudo" without password (member of the group "sudo"), and we have set sudoers as follows:

# Allow members of group sudo to execute any command
%sudo   ALL=(ALL:ALL) NOPASSWD: ALL

Ansible has been changed over time to become more and more secure, and one thing that has been restricted is the way users can see files shipped back and forth by ansible. In my case, where I use ansible_user=ops, the situation is as follows:

• Ansible logs in as the user "ops"
• Ansible becomes "root" as per playbook
• For some of the tasks (i.e "Make sure the PostgreSQL users are present"), the role wants to "become"=>"postgres"

This is where the access rights are getting in the way.

TASK [ANXS.postgresql : PostgreSQL | Make sure the PostgreSQL users are present] ****************************************************************************************
fatal: [ldap]: FAILED! => {"failed": true, "msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chown: changing ownership of '/tmp/ansible-tmp-1515586385.77-72590596798700/': Operation not permitted\nchown: changing ownership of '/tmp/ansible-tmp-1515586385.77-72590596798700/postgresql_user.py': Operation not permitted\n). For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}

To get around this, you will need to ensure (at least in Ubuntu 16.4 LTS) that you install the "acl" package before you get started with the postgres installation.

Example playbook (that works for me on Ubuntu 16.4 LTS):

---
- hosts: postgresql
  become: true
  tasks:
    - name: Install acl
      apt: pkg=acl state=present

- hosts: postgresql
  become_user: root
  become: true
  roles:
    - { role: "ANXS.postgresql" , tags: ["postgresql"] }
  vars:
# List of users to be created (optional)
    - postgresql_users:
      - name: icinga
        pass: secretpassword
        encrypted: no       # denotes if the password is already encrypted.

# List of user privileges to be applied (optional)
    - postgresql_user_privileges:
      - name: icinga
        db: postgres
        role_attr_flags: "LOGIN"

    - postgresql_log_timezone: "localtime"
    - postgresql_timezone: "localtime"
    - postgresql_admin_user: postgres
    - postgresql_listen_addresses: "*"
    - postgresql_default_auth_method: "md5"
    - postgresql_pg_hba_custom:
      - comment: "Allow connections from anywhere to any database using md5"
        type: host
        database: all
        user: all
        address: 0.0.0.0/0
        method: md5
    - postgresql_pg_hba_default:
      - { type: local, database: postgres, user: '{{ postgresql_admin_user }}', address: '', method: 'peer', comment: 'local postgres without password' }
      - { type: host, database: all, user: all, address: '::1/128',             method: '{{ postgresql_default_auth_method }}', comment: '"localhost"' }
    - postgresql_pg_ident_default:
      - { name: root-postgres, system_username: root, database_username: postgres, comment: 'Map root to user postgres' }
      - { name: root-postgres, system_username: postgres, database_username: postgres, comment: 'Map postgres to user postgres' }

One of the more important settings in this playbook, is the "local peer" config in pg_hba.conf. Without it you only get "so far", since this playbook installation does not weigh in that the default behavior does not allow the user postgres to login without a password.

If you do not have that line in pg_hba.conf, you will not be able to log in without password as the user postgres, and hence not be able to complete the installation.

root@ldap:/etc/postgresql/9.3/main# sudo -u postgres psql
psql: FATAL:  Peer authentication failed for user "postgres"

So, if I try again without further modifications, the playbook will barf out (since I would like to add a user called "icinga"):

TASK [ANXS.postgresql : PostgreSQL | Make sure the PostgreSQL users are present] ******************************************************
failed: [ldap] (item={u'encrypted': True, u'name': u'icinga', u'pass': u'XXX'}) => {"failed": true, "item": {"encrypted": true, "name": "icinga", "pass": "XXX"}, "msg": "unable to connect to database: FATAL:  Peer authentication failed for user \"postgres\"\n"}

To get this to work, you will need to think a bit on how to get this done. It is a decision that has to be made concerning security. In our environment, I am ok with having the postgres user being able to log in without using a password, when it is done on the localhost.

• You will need a line allowing the postgres user to log in without password in pg_hba.conf

local  postgres  postgres    peer

Which is what you get by setting the variable "postgresql_pg_hba_default" in your playbook or group_vars.

    - postgresql_pg_hba_default:
      - { type: local, database: postgres, user: '{{ postgresql_admin_user }}', address: '', method: 'peer', comment: 'postgres user without password on localhost' }

[maglub]

Sidenote:

• On a digital ocean droplet, the package "acl" is already installed by default

root@postgrestest:~# dpkg -l | grep acl
ii  acl                              2.2.52-3                                     amd64        Access control list utilities

[maglub]

I have just successfully installed postgres against a 512MB droplet (the smallest possible) at Digital ocean with this, unmodified, role using:

• The playbook in my comment above
• ansible_user=ops (with visudo config NOPASSWD: ALL so that the user does not need a sudoers password)

[maglub]

I have just successfully installed postgres against a 2GB X86 server with Ubuntu Xenial ast Scaleway with this, unmodified, role using:

• The playbook in my comment above
• ansible_user=ops (with visudo config NOPASSWD: ALL so that the user does not need a sudoers password)

ansible-playbook -i inventories/inventory.vagrant playbooks/postgresql.yml -l scaleway
...
PLAY RECAP ********************************************************************************************************************************************************************************
scaleway                   : ok=27   changed=11   unreachable=0    failed=0   

• on the VM

ops@pgtest:~$ sudo -u postgres psql
psql (9.6.6)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \q
ops@pgtest:~$ cat /etc/default/locale 
LANG=en_US.UTF-8

• The playbook

- hosts: postgresql
  become: true
  tasks:
    - name: Install acl
      apt: pkg=acl state=present

- hosts: postgresql
  become_user: root
  become: true
  roles:
    - { role: "ANXS.postgresql" , tags: ["postgresql"] }
  vars:
    - postgresql_version: 9.6
# List of users to be created (optional)
    - postgresql_users:
      - name: icinga
        pass: secretpassword
        encrypted: no       # denotes if the password is already encrypted.

# List of user privileges to be applied (optional)
    - postgresql_user_privileges:
      - name: icinga
        db: postgres
        role_attr_flags: "LOGIN"

    - postgresql_log_timezone: "localtime"
    - postgresql_timezone: "localtime"
    - postgresql_admin_user: postgres
    - postgresql_listen_addresses: "*"
    - postgresql_default_auth_method: "md5"
    - postgresql_pg_hba_custom:
      - comment: "Allow connections from anywhere to any database using md5"
        type: host
        database: all
        user: all
        address: 0.0.0.0/0
        method: md5
    - postgresql_pg_hba_default: 
      - { type: local, database: postgres, user: '{{ postgresql_admin_user }}', address: '', method: 'peer', comment: 'local postgres without password' }
      - { type: host, database: all, user: all, address: '::1/128',             method: '{{ postgresql_default_auth_method }}', comment: '"localhost"' }
    - postgresql_pg_ident_default:
      - { name: root-postgres, system_username: root, database_username: postgres, comment: 'Map root to user postgres' }
      - { name: root-postgres, system_username: postgres, database_username: postgres, comment: 'Map postgres to user postgres' }

I suggest that we close this issue, as it is (in my view) connected to a missing "acl" package and the use of a non "root" ansible_user.

[neooleg]

@maglub thanks a lot for detailed explanation and eventually found solution (I didn't try it yet, but read the above).

Let's me pls to summarize:

1. It's need to install "acl" package before call the role (if it's not installed yet)
2. Use config (in pg_hba.conf) for "postgres" user to login without password
3. Run playbook from a user which is configured to login without password

Is it right?

[maglub]

Hi!

Almost correct. =)

3. Run playbook with an ansible_user (in my case the user is called "ops") which does not need a password to run sudo.

I solved this by adding the user "ops" to the group "sudo" and added "NOPASSWD" to the entry in /etc/sudoers:

%sudo   ALL=(ALL:ALL) NOPASSWD: ALL

There are many ways to do this, but for me this was the easiest. My ops user still logs in by using ssh key credentials.

[aoyawale]

Is this still an issue? Acl gets installed now.

[Mehonoshin]

I just met this issue today. Installing the acl package helped. Maybe it deserves a PR, that will install it by default?

[afeld]

cc https://github.com/ansible/ansible/issues/16048

[nknganda]

Experienced same issue today on a DigitalOcean droplet running Ubuntu 18.04.2 LTS. "acl" is already installed.

[maglub]

Care to share screen output?

Sent from my iPhone

On 20 Jun 2019, at 23:29, Newton Kitonga [email protected] wrote:

Experienced same issue today on a DigitalOcean droplet running Ubuntu 18.04.2 LTS. "acl" is already installed.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[nknganda]

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? failed: [127.0.0.1] (item={'name': 'random', 'password': 'md51a1dc91c907325c69271ddf0c944bc72', 'priv': 'ALL', 'db': 'random', 'role_attr_flags': 'CREATEDB,CREATEROLE,LOGIN,NOSUPERUSER'}) => {"ansible_loop_var": "item", "changed": false, "item": {"db": "random", "name": "random", "password": "md51a1dc91c907325c69271ddf0c944bc72", "priv": "ALL", "role_attr_flags": "CREATEDB,CREATEROLE,LOGIN,NOSUPERUSER"}, "msg": "Unable to connect to database: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?\n"}

Trying to setup Postgresql 10 on DigitalOcean droplet. Strangely am able to setup a new/fresh droplet but after subsequent deploys, error arises?

Could this also be due to Postgresql 10 requiring MD5 password?

cc issue#397

Using latest version of the role with default settings in the task

become: yes become_user: "{{postgresql_admin_user}}"

Same playbook is fine in a vagrant box geerlingguy/ubuntu1804

[maglub]

I think you should create a new issue. I don't think that your error relates to the acl that this ticket is about.

That aside, it looks like you can't lig in due to that the database is not running. Check that the database processes are running and that the unix socket files mentioned in your logfile exists.

Sent from my iPhone

On 21 Jun 2019, at 16:56, Newton Kitonga [email protected] wrote:

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? failed: [127.0.0.1] (item={'name': 'random', 'password': 'md51a1dc91c907325c69271ddf0c944bc72', 'priv': 'ALL', 'db': 'random', 'role_attr_flags': 'CREATEDB,CREATEROLE,LOGIN,NOSUPERUSER'}) => {"ansible_loop_var": "item", "changed": false, "item": {"db": "random", "name": "random", "password": "md51a1dc91c907325c69271ddf0c944bc72", "priv": "ALL", "role_attr_flags": "CREATEDB,CREATEROLE,LOGIN,NOSUPERUSER"}, "msg": "Unable to connect to database: could not connect to server: No such file or directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?\n"}

Trying to setup Postgresql 10 on digitalocean droplet. Strangely am able to setup a new/fresh droplet but after subsequent deploys, error arises?

Could this also be due to Postgresql 10 requiring MD5 password?

cc issue#397

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[patsevanton]

PR for fix set permissions on the temporary files on Ubuntu 20.04 https://github.com/ANXS/postgresql/pull/529