# postgresql\_user readonly user

**URL:** <https://forum.ansible.com/t/postgresql-user-readonly-user/34520>\
**Category:** Ansible Project\
**Created:** [January 27, 2021, 7:45am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520 "2021-01-27T07:45:50Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![zausel\_z](https://avatars.discourse-cdn.com/v4/letter/z/9fc29f/32.png) [@zausel\_z](https://forum.ansible.com/u/zausel_z)\
**Post date:** [January 27, 2021, 7:45am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/1 "2021-01-27T07:45:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Dick\_Visser](https://avatars.discourse-cdn.com/v4/letter/d/d9b06d/32.png) [@Dick\_Visser](https://forum.ansible.com/u/Dick_Visser)\
**Post date:** [January 27, 2021, 8:16am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/2 "2021-01-27T08:16:53Z")

</div>

What does “not working” mean?

---

<div class="post-metadata">

**Author:** ![zausel\_z](https://avatars.discourse-cdn.com/v4/letter/z/9fc29f/32.png) [@zausel\_z](https://forum.ansible.com/u/zausel_z)\
**Post date:** [January 27, 2021, 10:56am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/3 "2021-01-27T10:56:32Z")

</div>

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}

---

<div class="post-metadata">

**Author:** ![racke](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/racke/32/4767_2.png) [@racke](https://forum.ansible.com/u/racke)\
**Post date:** [January 27, 2021, 11:30am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/4 "2021-01-27T11:30:37Z")

</div>

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

&nbsp;&nbsp;&nbsp;priv: "CONNECT/ALL:SELECT"

Regards  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Racke

---

<div class="post-metadata">

**Author:** ![zausel\_z](https://avatars.discourse-cdn.com/v4/letter/z/9fc29f/32.png) [@zausel\_z](https://forum.ansible.com/u/zausel_z)\
**Post date:** [January 27, 2021, 3:47pm UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/5 "2021-01-27T15:47:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![racke](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/racke/32/4767_2.png) [@racke](https://forum.ansible.com/u/racke)\
**Post date:** [January 27, 2021, 4:49pm UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/6 "2021-01-27T16:49:03Z")

</div>

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

&nbsp;&nbsp;&nbsp;&nbsp;- postgresql\_privs:  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;db: "{{ item.db }}"  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;privs: SELECT,CONNECT  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;objs: ALL\_IN\_SCHEMA  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;role: "{{ item.name }}"  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;become\_user: postgres  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;loop: "{{ pg\_users }}"

Regards  
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Racke

---

<div class="post-metadata">

**Author:** ![zausel\_z](https://avatars.discourse-cdn.com/v4/letter/z/9fc29f/32.png) [@zausel\_z](https://forum.ansible.com/u/zausel_z)\
**Post date:** [January 28, 2021, 8:22am UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/7 "2021-01-28T08:22:14Z")

</div>

Hey Racke ,

thanks for your effort, I will test postgresql\_privs 🙂

---

<div class="post-metadata">

**Author:** ![zausel\_z](https://avatars.discourse-cdn.com/v4/letter/z/9fc29f/32.png) [@zausel\_z](https://forum.ansible.com/u/zausel_z)\
**Post date:** [January 28, 2021, 6:13pm UTC](https://forum.ansible.com/t/postgresql-user-readonly-user/34520/8 "2021-01-28T18:13:28Z")

</div>

thanks for your help, the solution with postgres\_privs works great