Best way to perform postgresql admin related tasks with a non-admin user

Archives Ansible Project
1

Hello

I’m currently stuck on a privilege issue when managing postgresql with ansible

Related modules

All postgresql modules (for instance postgresql_user)

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 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

2

  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