how to create a postgres readonly user (role)

[Vernon D. Cole]

I am tasked with converting an Ansible script to Salt.  The Ansible script reads:

- name: Make users read-only
  postgresql_privs: db={{ dynamux_postgres_databases[0].name }} priv=SELECT objs=ALL_IN_SCHEMA roles={{ item.name }} state=present
  with_items: dynamux_postgres_read_only_users

My latest attempt is:

{% for user in pillar['dynamux_postgres_read_only_users'] %}

django_user_{{ user.name }}:
    postgres_user.present:
    - name: {{ user.name }}
    - password: {{ user.password }}
    - refresh_password: true
    - user: postgres
    - db_host: {{ salt['pillar.get']('db-servers'[0]) }}
    - db_port: {{ salt['pillar.get']('postgres:port') }}
    - require:
      - pkg: postgresql
django_ro_user_{{ user.name }}:
  postgres_privileges.present:
    - name: {{ user.name }}
    - object_name: ALL_IN_SCHEMA
    - object_type: table
    - privileges:
      - SELECT
    - user: postgres
    - db_host: {{ salt['pillar.get']('db-servers'[0]) }}
    - db_port: {{ salt['pillar.get']('postgres:port') }}
    - require:
      - pkg: postgresql
{% endfor %}

which fails with:

... , 'GRANT SELECT ON TABLE public.ALL_IN_SCHEMA TO cliplist']' failed with return code: 1
[ERROR   ] stderr: ERROR:  relation "public.all_in_schema" does not exist

I have tried a dozen variations with wildcards, different object types, and whatnot. None work.

Research says that the SQL code needed is: "GRANT SELECT ON ALL TABLES IN SCHEMA <schema name> TO <user name>;"
perhaps followed by: "ALTER DEFAULT PRIVILEGES IN SCHEMA <schema name> GRANT SELECT ON TABLES TO <user name>;"

Is there some way to do that without resorting to "cmd.run" or an uglified postgres.psql_query module call?





[Fanch Hamard]

Hello,

Do you have solve your issue?

I have the same problem here.

Thanks in advance.

[Vernon D. Cole]

No, no elegant solution has been discovered.

--
You received this message because you are subscribed to a topic in the Google Groups "Salt-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/salt-users/Jh8VsMQrg4w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to salt-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.