postgresql_user readonly user

Archives Ansible Project
1

Hi, I’m using the postgresql_user_module. How can I create a read-only-user (SELECT) for all tables of a database? priv: CONNECT/ALL:SELECT is not working

thank you

2

What does “not working” mean?

3

Hi,

Error:

/ansible_postgresql_user_payload.zip/ansible/modules/database/postgresql/postgresql_user.py", line 555, in grant_table_privileges\n File "/usr/lib64/python2.7/site-packages/psycopg2/extras.py", line 120, in execute\n return super(DictCursor, self).execute(query, vars)\npsycopg2.ProgrammingError: relation "ALL" does not exist\n\n", “module_stdout”: “”, “msg”: “MODULE FAILURE\nSee stdout/stderr for the exact error”, “rc”: 1}

4

Hi,

Error:

/ansible_postgresql_user_payload.zip/ansible/modules/database/postgresql/postgresql_user.py\", line 555, in
grant_table_privileges\n File \"/usr/lib64/python2.7/site-packages/psycopg2/extras.py\", line 120, in execute\n
return super(DictCursor, self).execute(query, vars)\npsycopg2.ProgrammingError: relation \"ALL\" does not exist\n\n",
"module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

Please try to wrap it in parentheses:

   priv: "CONNECT/ALL:SELECT"

Regards
         Racke

5

Hey Racke,

this is my configuration

host_vars:

pg_users:

  • { name: dbuser, db: mydb, privileges: “CONNECT/ALL:SELECT” , password: “xxxxxxxxxxxx” }

task:

  • name: db user
    postgresql_user:
    name: “{{ item.name }}”
    db: “{{ item.db }}”
    priv: “{{ item.privileges }}”
    password: “{{ item.password | default(‘omit’) }}”
    state: “present”
    become_user: postgres
    loop: “{{ pg_users }}”
6

Hey Racke,

this is my configuration

host_vars:
pg_users:
- { name: dbuser, db: mydb, privileges: "CONNECT/ALL:SELECT" , password: "xxxxxxxxxxxx" }

task:

- name: db user
postgresql_user:
name: "{{ item.name }}"
db: "{{ item.db }}"
priv: "{{ item.privileges }}"
password: "{{ item.password | default('omit') }}"
state: "present"
become_user: postgres
loop: "{{ pg_users }}"

I tried that and got a similar error. You might consider to break out the privilege into another task:

    - postgresql_privs:
        db: "{{ item.db }}"
        privs: SELECT,CONNECT
        objs: ALL_IN_SCHEMA
        role: "{{ item.name }}"
      become_user: postgres
      loop: "{{ pg_users }}"

Regards
        Racke

7

Hey Racke ,

thanks for your effort, I will test postgresql_privs :slight_smile:

8

thanks for your help, the solution with postgres_privs works great