Support "ALL TABLES" in postgresql_user_privileges

[JeanDaniel-Fischer]

Hi,

I am trying to create an account with only CONNECT grant on a specific database and SELECT grand on all tables.

PostgreSQL documentation says "ALL TABLES" can be used (https://www.postgresql.org/docs/9.1/static/sql-grant.html) but I can't manage to use it. The following failed with:

relation "ALL TABLES" does not exist

    postgresql_user_privileges:
      - name: myaccount_ro
        db: mydb
        priv: "CONNECT/ALL TABLES:SELECT"

[aoyawale]

what version of ansible are you using?

[gclough]

This appears to be a limitation of the core postgresql_user Ansible module:

https://docs.ansible.com/ansible/latest/modules/postgresql_user_module.html

We literally pass the list of privileges in postgresql_user_privileges into postgresql_user:

- name: PostgreSQL | Update the user privileges
  postgresql_user:
    name: "{{item.name}}"
    db: "{{item.db | default(omit)}}"
    port: "{{postgresql_port}}"
    priv: "{{item.priv | default(omit)}}"
    state: present
    login_host: "{{item.host | default(omit)}}"
    login_user: "{{postgresql_admin_user}}"
    role_attr_flags: "{{item.role_attr_flags | default(omit)}}"
  become: yes
  become_user: "{{postgresql_admin_user}}"
  with_items: "{{postgresql_user_privileges}}"
  when: postgresql_users|length > 0

I'd agree that the documentation for the Ansible module is quite thin, but I think this is a generic problem. I'll have to dig in the ansible code a bit to be sure...

[JeanDaniel-Fischer]

I am using ansible 2.3.1.0.

Should I submit the issue directly to the core module ?

[aoyawale]

first try with ansible 2.4 since that is the minimum with this role then if not then I suggest you get on IRC and ask.

[alexanderkgonzalez]

Hey @JeanDaniel-Fischer , did you make any progress with this? Having a similar issue.

[n1ngu]

Indeed, this https://docs.ansible.com/ansible/latest/modules/postgresql_privs_module.html is the module that implements this feature

[n1ngu]

FWIW, we extended the anxs.postgresql role with an aditional task that implements this:

- name: PostgreSQL | Update privileges
  postgresql_privs:
    db: "{{item.db}}"
    objs: "{{item.objs | default(omit)}}"
    privs: "{{item.privs | default(omit)}}"
    roles: "{{item.roles}}"
    schema: "{{item.schema | default(omit)}}"  # defaults to 'public
    state: present
    type: "{{item.type | default(omit)}}"  # defaults to 'table'
    login_host: "{{item.host | default(omit)}}"
    login_port: "{{postgresql_port}}"
    login_user: "{{postgresql_admin_user}}"
  become: yes
  become_user: "{{postgresql_admin_user}}"
  with_items: "{{postgresql_privileges}}"
  when: postgresql_privileges|length > 0

Then make the postgresql_privileges variable available to your inventory

postgresql_privileges:
  - {roles: 'readonly', db: 'mydatabase', privs: 'SELECT', objs: 'ALL_IN_SCHEMA'}

I believe a final solution would look like this, I hope it helps both the maintainers and users missing this feature

[github-actions]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!