Ansible create postgresql user with access to all tables?

Question

This should be very simple. I want to make an Ansible statement to create a Postgres user that has connection privileges to a specific database and select/insert/update/delete privileges to all tables within that specific database. I tried the following:

  - name: Create postgres user for my app
    become: yes
    become_user: postgres
    postgresql_user:
      db: "mydatabase"
      name: "myappuser"
      password: "supersecretpassword"
      priv: CONNECT/ALL:SELECT,INSERT,UPDATE,DELETE

I get relation \"ALL\" does not exist

If I remove ALL:, I get Invalid privs specified for database: INSERT UPDATE SELECT DELETE

Inti · Accepted Answer · 2017-11-17 13:12:06Z

27

What I had to do was first create the user and then grant the privileges separately. It's working like a charm.

 - name: Create postgres user for my app
    become: yes
    become_user: postgres
    postgresql_user:
      name: "myappuser"
      password: "supersecretpassword"

  - name: Ensure we have access from the new user
    become: yes
    become_user: postgres
    postgresql_privs:
      db: mydatabase
      role: myappuser
      objs: ALL_IN_SCHEMA
      privs: SELECT,INSERT,UPDATE,DELETE

answered Nov 17, 2017 at 13:12

Inti

3,8111 gold badge36 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Dec 11, 2024 at 17:57

There are "hidden" defaults at work. The default schema is "public". The default type is "table".

radtek · Accepted Answer · 2019-01-29 22:53:07Z

Here is the playbook I use, using debian and setting up user and db, as well as giving user access to all databases:

- hosts: all
  become: yes

  vars:
    ansible_ssh_pipelining: true

  tasks:
    - name: install postgresql server
      apt:
        pkg: postgresql
        state: present

    - name: change postgres network binding
      lineinfile:
        path: /etc/postgresql/9.6/main/postgresql.conf
        regexp: '# listen_addresses'
        line: "listen_addresses = '*'"

    - name: change postgres pg hba access
      lineinfile:
        path: /etc/postgresql/9.6/main/pg_hba.conf
        regexp: 'host  all  all 0.0.0.0/0 md5'
        line: 'host  all  all 0.0.0.0/0 md5'

    - name: start postgresql server
      service:
        enabled: yes
        name: postgresql
        state: restarted

    # psycopg2 needed for user, db creation
    - pip:
        name: psycopg2-binary

    - name: create postgresql user
      postgresql_user:
        user: "root"
        password: "root"
        role_attr_flags: "CREATEDB,NOSUPERUSER"
      become: true
      become_user: postgres

    - name: create postgresql db
      postgresql_db:
        name: "your-db-name"
        state: present
      become: true
      become_user: postgres

Your paths may vary so adjust accordingly.

And for bonus here is my Vagrantfile, using virtualbox:

# -*- mode: ruby -*-
# vi: set ft=ruby :

# Brings up a vm with es and mongodb
Vagrant.configure("2") do |config|
  config.vm.box = "geerlingguy/debian9"
  config.vm.network "private_network", ip: "192.168.33.44"

  config.vm.provider "virtualbox" do |vb|
    vb.memory = "2048"
  end

  config.vm.provision "ansible_local" do |ansible|
      ansible.playbook = "ansible_playbook.yml"
      ansible.install = "true"
      ansible.install_mode = "pip"
  end
end

Cheers!

Valentin Gazzola · Accepted Answer · 2017-07-14 05:57:55Z

-3

From ansible documentation postgressql module, priv should be "PostgreSQL privileges string in the format: table:priv1,priv2" So your task should be

 - name: Create postgres user for my app
    become: yes
    become_user: postgres
    postgresql_user:
      db: "mydatabase"
      name: "myappuser"
      password: "supersecretpassword"
      priv: ALL:SELECT,INSERT,UPDATE,DELETE,CONNECT

answered Jul 14, 2017 at 5:57

Valentin Gazzola

583 bronze badges

1 Comment

Inti Over a year ago

This didn't work for me. I got an error psycopg2.ProgrammingError: relation \"ALL\" does not exist

Collectives™ on Stack Overflow

Ansible create postgresql user with access to all tables?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related