# Best way to perform postgresql admin related tasks with a non-admin user **URL:** \ **Category:** Ansible Project\ **Created:** [May 22, 2015, 11:30pm UTC](https://forum.ansible.com/t/best-way-to-perform-postgresql-admin-related-tasks-with-a-non-admin-user/18293 "2015-05-22T23:30:02Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Vincent\_Doba](https://avatars.discourse-cdn.com/v4/letter/v/858c86/32.png) [@Vincent\_Doba](https://forum.ansible.com/u/Vincent_Doba)\ **Post date:** [May 22, 2015, 11:30pm UTC](https://forum.ansible.com/t/best-way-to-perform-postgresql-admin-related-tasks-with-a-non-admin-user/18293/1 "2015-05-22T23:30:02Z")
Hello I’m currently stuck on a privilege issue when managing postgresql with ansible **Related modules** All postgresql modules (for instance [postgresql\_user](http://docs.ansible.com/postgresql_user_module.html)) **My problem** In my remote host I have three users : **vincent** , who is not an admin user, **root** , with admin rights, and **postgres** , who is postgresql database admin. I have a freeshly installed postgresql DBMS on which I want perform some operation (create a database user and database). **I run my ansible playbook with user vincent.** the classic way (without ansible) to perform database management task in freeshly installed postgresql DBMS is to perform the following commands : ` vincent@remote:/$ su root@remote:/# su postgres postgres@remote:/$ _perform database management commands_ ` However, as explained [here](http://docs.ansible.com/become.html) it is not possible in ansible to chain user changes in order to be postgres user, which is the only one to be admin on postgresql DBMS. Due to this limitation, when I try to run the following task with remote\_user vincent : ` - name: Create a database user become: yes become\_method: su become\_user: postgres postgresql\_user: name=db\_user password=db\_user\_password ` I naturally fail with the following errors : ` failed: [192.168.0.15] =\> {“failed”: true, “parsed”: false} su: Authentication failure Shared connection to 192.168.0.15 closed. FATAL: all hosts have already failed – aborting ` As user vincent don’t have the rights to perform a _su postgres_ commands To handle this problem, I have listed the following workaround (not yet implemented), but no one fully satifies me **Current workaround :** - Run ansible playbook using root account : I don’t want to do it as I don’t want to allow root login on remote host - Temporary grant specific rights on user vincent for postgresql-related tasks : it bothers me as if the postgresql-related tasks fail, it lets my remote host in an inconsistent state with an user having too much rights - Temporary lower rights for postgresql administration : same problem than previous solution **So I have the following questions :** - **What do you think of the listed workaround ?** - **Is there another solution I didn’t foreseen ?** - **What is the best solution in my case ?** - **To the ansible team, would it be possible to improve all postgresql modules to handle this case, which is pretty common I guess ?** Thanks for your answers ! Regards ---
**Author:** ![Alan\_Stebbens](https://avatars.discourse-cdn.com/v4/letter/a/73ab20/32.png) [@Alan\_Stebbens](https://forum.ansible.com/u/Alan_Stebbens)\ **Post date:** [May 26, 2015, 4:19am UTC](https://forum.ansible.com/t/best-way-to-perform-postgresql-admin-related-tasks-with-a-non-admin-user/18293/2 "2015-05-26T04:19:00Z")
1. Encapsulate the playbook invocation within a bash script, let’s call it “/usr/local/bin/dbupdate” 2. In the sudoers (see “man sudoers”), configure the “dbupdate” bash script as being one of the commands enabled for user “vincent”, to be run as the “postgres” user. 3. Teach “vincent” how to use “sudo dbupdate” This allows user “vincent” to run “dbupdate” as the “postgres” user, but no other commands (unless also configured in “sudoers”). Hope this helps. Alan