New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Describe how to use "postgresql_user" properly with ansible >= 2.1.0.0 #16048

Open
abguy opened this Issue May 30, 2016 · 20 comments

Comments

Projects
None yet
@abguy
Copy link

abguy commented May 30, 2016

ISSUE TYPE

Bug Report

COMPONENT NAME

core

ANSIBLE VERSION
2.1.0.0
CONFIGURATION
OS / ENVIRONMENT

Ubuntu 14.04

SUMMARY

After upgrading to Ansible 2.1 I can't add PostgreSQL user because of this error

FAILED! => {"failed": true, "msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user. For information on working around this, see https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user"}
STEPS TO REPRODUCE

I have no direct ssh access enabled for root user. I use the default ubuntu user to login to my server. It's quite common configuration for Ubuntu.
That's why I have this book:

- hosts: dbservers
  remote_user: ubuntu
  become: true
  become_user: postgres
  roles:
    - postgresql

and the postgresql role with:

- name: Create user for the app
  postgresql_user: user={{ db_user }} password={{ db_pass }}
  tags: postgresql
EXPECTED RESULTS

Please describe in the documentation how to add PG users properly.

ACTUAL RESULTS

I didn't find how to add PG users properly. The only option that works for me is allow_world_readable_tmpfiles = True in the ansible.cfg.

@WiNloSt

This comment has been minimized.

Copy link

WiNloSt commented May 31, 2016

Can you tell me the exact Ansible version before upgrading to 2.1 that didn't have this error. Because I'm getting the same error right now.

@abguy

This comment has been minimized.

Copy link
Author

abguy commented May 31, 2016

For instance, the previous one works fine, ansible 2.0.2.0.
On Ubuntu you can install it by

sudo apt-get install ansible=2.0.2.0-1ppa~trusty

flibbertigibbet added a commit to flibbertigibbet/vagrant-cartodb that referenced this issue Jun 6, 2016

@michaelem

This comment has been minimized.

Copy link

michaelem commented Jun 7, 2016

I did run into this as well and tried all the solutions on the proposed documentation site (https://docs.ansible.com/ansible/become.html#becoming-an-unprivileged-user).
None of them worked, had to fall back to allow_world_readable_tmpfiles = True and living with warnings for now.

@abguy

This comment has been minimized.

Copy link
Author

abguy commented Jun 8, 2016

Yes, that's it! I don't see any other ways too. It looks like this is a product issue, but not the documentation one.

@aselshamy

This comment has been minimized.

Copy link

aselshamy commented Jun 9, 2016

I'm having the same issue, with 2.1.0.0-1ppa~trusty. please fix.

  • name: postgresql - create user
    become: yes
    become_user: postgres
    postgresql_user: user={{ db_username }}
    password={{ db_password }}
    role_attr_flags=CREATEDB,NOSUPERUSER
@sgsabbage

This comment has been minimized.

Copy link

sgsabbage commented Jun 28, 2016

I managed to get this temporarily working with pipelining per task and becoming postgres user:

- name: Create database user
  become: yes
  become_user: postgres
  vars:
    ansible_ssh_pipelining: true
  postgresql_user:
    name: user

Hopefully this helps anyone in the same boat.

@richardsmd

This comment has been minimized.

Copy link

richardsmd commented Jul 8, 2016

I'm curious if there is a way to check for superuser privileges on remote_user.

If your remote_user is ubuntu and you have this line in /etc/sudoers

ubuntu ALL=(ALL:ALL) NOPASSWD:ALL

then shouldn't ansible be able to change permissions in the same way as if you had connected via root?

@jklaiho

This comment has been minimized.

Copy link

jklaiho commented Aug 9, 2016

To make things slightly worse, #15297 means that I can't even use @sgsabbage's pipelining workaround, and have no choice but to rely on allow_world_readable_tmpfiles.

@geerlingguy

This comment has been minimized.

Copy link
Contributor

geerlingguy commented Sep 30, 2016

@jklaiho - @sgsabbage's hack is currently working for me under Ansible 2.1.1.0 (haven't tested 2.1.2.0 or devel yet).

@theromis

This comment has been minimized.

Copy link

theromis commented Oct 3, 2016

✗ ansible --version
ansible 2.1.2.0
  config file =
  configured module search path = Default w/o overrides

not works for me

- name: Postgres db
  become: true
  become_user: postgres
  postgresql_db: name={{db.name}}
  vars:
    ansible_ssh_pipelining: true
@audiolion

This comment has been minimized.

Copy link

audiolion commented Oct 23, 2016

Getting same issue 2.1.2.0, I just went back to 1.9 until issue is resolved

dsigurds added a commit to privacylabs/oasis that referenced this issue Nov 4, 2016

@ansibot ansibot added the bug_report label Dec 13, 2016

@bcoca bcoca removed the triage label Dec 16, 2016

@sayap

This comment has been minimized.

Copy link
Contributor

sayap commented Jan 18, 2017

We got the same problem, and in our case it was because the acl package is not installed by default on GCE ubuntu trusty image. The set_user_facl method calls setfacl -m u:postgres:rx /tmp/xxx, and the acl package provides /usr/bin/setfacl.

@jasimmonsv

This comment has been minimized.

Copy link

jasimmonsv commented Feb 16, 2017

+1 Same problem

@jasimmonsv

This comment has been minimized.

Copy link

jasimmonsv commented Feb 17, 2017

@sgsabbage solution finally worked for me after running the playbook with -vvvv
I had to add edit my sudoers file to include:
%sudo ALL=(ALL) NOPASSWD: ALL

so that all my logged in user in the sudo group would not be prompted for a password for any command.
This work for me to allow:

- name: configure DB
  become: yes
  become_user: postgres
  vars:
    ansible_ssh_pipelining: true
  postgresql_db:
    name: '{{ db_name }}'
    encoding: UTF-8
    lc_ctype: en_US.UTF-8
    lc_collate: en_US.UTF-8
    template: template0
    state: present
@sgsabbage

This comment has been minimized.

Copy link

sgsabbage commented Mar 3, 2017

Coming back to this as I ended up having some problems with it, so I eventually just gave up and let the root user login as the postgres user with the following file changes:

pg_ident.conf:

# MAPNAME    SYSTEM-USERNAME       PG-USERNAME
root         root                  postgres
# We also need to allow the postgres user to login as itself
root         postgres              postgres 

pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer map=root
local   all             all                                     peer
...

Please bear in mind, this isn't my complete pg_hba.conf file, just an example of the main change. Then you can just use the postgres tasks as:

- name: Create database
  become: yes
  postgresql_db:
    name: database_name
@ur5us

This comment has been minimized.

Copy link

ur5us commented Apr 22, 2017

@sayap Thanks for your tip. In my case I'm using a box on https://www.scaleway.com/.

Installing the acl package fixes the problem, no extra privilege escalation settings required other than become_user: postgres.

ansible --version
ansible 2.3.0.0
  config file =
  configured module search path = Default w/o overrides
  python version = 2.7.13 (default, Dec 18 2016, 07:03:39) [GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.42.1)]
@Taytay

This comment has been minimized.

Copy link
Contributor

Taytay commented Jul 13, 2017

Installing the acl package as a first task worked for me to solve this problem.

@idanshimon

This comment has been minimized.

Copy link

idanshimon commented Aug 7, 2017

@Taytay can you explain ??

I am also trying for sometime to deploy postgresql using a simple play and it gets an error about peer authentication failure for user postgres (9.6)

---
- hosts: postgresqls
  become: True

  tasks:
    - name: Install PostgreSQL
      apt: name={{ item }} state=latest update_cache=yes
      with_items:
        - postgresql
        - python-psycopg2
        - postgresql-contrib
        - libpq-dev

    - name: Create database user
      become: yes
      become_user: postgres
      vars:
        ansible_ssh_pipelining: true
      postgresql_user:
        name: testuser

Result Ansible-playbook:
"msg": "unable to connect to database: FATAL: Peer authentication failed for user "postgres"\n"

@abguy

This comment has been minimized.

Copy link
Author

abguy commented Sep 22, 2017

Any updates on this? Yesterday I've noticed that my workaround stopped working with Ansible 2.4.0
... and I didn't find a way to fix it quickly

@abguy abguy changed the title Describe how to use "postgresql_user" properly with ansible 2.1.0.0 Describe how to use "postgresql_user" properly with ansible >= 2.1.0.0 Sep 22, 2017

pypt added a commit to berkmancenter/mediacloud that referenced this issue Sep 28, 2017

@alikins

This comment has been minimized.

Copy link
Contributor

alikins commented Sep 29, 2017

Related to #31022

@calfonso calfonso added this to the 2.5.0 milestone Sep 29, 2017

@calfonso calfonso removed this from the 2.5.0 milestone Sep 29, 2017

aepyornis added a commit to aepyornis/nyc-db that referenced this issue Jan 13, 2018

@ansibot ansibot added docs bug and removed docs_report labels Mar 1, 2018

aepyornis added a commit to aepyornis/nyc-db that referenced this issue Jun 15, 2018

ansell added a commit to AtlasOfLivingAustralia/ala-install that referenced this issue Jul 20, 2018

Fix pg_instance using ansible_ssh_pipelining
Was having issues with the dropped permissions to postgres seeing the ansible tmp file, so switching on the tasks that it was failing on to ssh pipelining

ansible/ansible#16048

Signed-off-by: Peter Ansell <p_ansell@yahoo.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment