postgresql_user readonly user

[zausel z]

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

[Dick Visser]

What does "not working" mean?

On Wed, 27 Jan 2021 at 08:46, zausel z <zau...@gmail.com> wrote:

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

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/b24eb1c8-6cf3-40f4-8548-58fee839ee3an%40googlegroups.com.

--

Sent from a mobile device - please excuse the brevity, spelling and punctuation.

[zausel z]

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}

[Stefan Hornburg (Racke)]

On 1/27/21 11:56 AM, zausel z wrote:
> 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

> dick....@geant.org schrieb am Mittwoch, 27. Januar 2021 um 09:17:19 UTC+1:
>
> What does "not working" mean?
>
> On Wed, 27 Jan 2021 at 08:46, zausel z <zau...@gmail.com> wrote:
>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> ansible-proje...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/b24eb1c8-6cf3-40f4-8548-58fee839ee3an%40googlegroups.com

> <https://groups.google.com/d/msgid/ansible-project/b24eb1c8-6cf3-40f4-8548-58fee839ee3an%40googlegroups.com?utm_medium=email&utm_source=footer>.

>
> --
> Sent from a mobile device - please excuse the brevity, spelling and punctuation.
>

> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to

> ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com?utm_medium=email&utm_source=footer>.


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration. Provisioning with Ansible.

[zausel z]

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

[Stefan Hornburg (Racke)]

On 1/27/21 4:47 PM, zausel z wrote:
> 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

> <https://groups.google.com/d/msgid/ansible-project/b24eb1c8-6cf3-40f4-8548-58fee839ee3an%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/ansible-project/b24eb1c8-6cf3-40f4-8548-58fee839ee3an%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
> >
> > --
> > Sent from a mobile device - please excuse the brevity, spelling and punctuation.
> >
> > --
> > You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> > To unsubscribe from this group and stop receiving emails from it, send an email to
> > ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> > To view this discussion on the web visit
> > https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com>
> >

> <https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/ansible-project/c009d142-9c96-4230-b150-b1aaf134e28an%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>
>
> --
> Ecommerce and Linux consulting + Perl and web application programming.
> Debian and Sympa administration. Provisioning with Ansible.
>

> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/ansible-project/1f30723c-f60d-4de3-b88a-23a35ef0a3d7n%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/1f30723c-f60d-4de3-b88a-23a35ef0a3d7n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[zausel z]

Hey Racke ,

thanks for your effort, I will test postgresql_privs :)

[zausel z]

thanks for your help, the solution with postgres_privs works great