postgresql_priv: Granting privileges using ALL_IN_SCHEMA does not use ALL TABLES in SCHEMA

[SantiRaposo]

SUMMARY

When using the postgresql_priv module, selecting the ALL_IN_SCHEMA option for the obj argument does not produce the expected statement.

EXPECTED:
GRANT <privileges> ON ALL TABLES IN SCHEMA <schema> TO <role>;

ACTUAL:
GRANT <privileges> ON schema.table1, schema.table2, schema.table3, ... schema.tableN <schema> TO <role>;

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_priv

ANSIBLE VERSION

ansible [core 2.12.4]
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.10/site-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.10.4 (main, Apr  7 2022, 03:26:33) [GCC 10.2.1 20210110]
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

Collection           Version
-------------------- -------
community.postgresql 2.1.4

CONFIGURATION

""

OS / ENVIRONMENT

Debian bullseye

STEPS TO REPRODUCE

Run the following statemens in a postgresql database:
CREATE TABLE public.table1(s varchar);
CREATE TABLE public.table2(s varchar);
CREATE TABLE public.table3(s varchar);
CREATE TABLE public.table4(s varchar);
CREATE TABLE public.table5(s varchar);
CREATE ROLE test;

Then run the following ansible task:

- name: "Example"
  postgresql_privs:
      login_host: "{{ host }}"
      login_user: "{{ user }}"
      login_password: "{{ password }}"
      db: postgres
      role: test
      priv: "SELECT"
      type: table
      objs: ALL_IN_SCHEMA
      schema: public
      state: present

EXPECTED RESULTS

The executed query should use the ALL TABLES IN SCHEMA {schema} statement.

ACTUAL RESULTS

The module builds a list of all tables in the schema and constructs a query specifying each one individually. This creates extremely long logs for databases with many tables.

root@aa4d13fa304b:~/ansible$ ansible-playbook -vv test.yaml
ansible-playbook [core 2.12.4]
  config file = /root/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.10/site-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible-playbook
  python version = 3.10.4 (main, Apr  7 2022, 03:26:33) [GCC 10.2.1 20210110]
  jinja version = 3.1.2
  libyaml = True
Using /root/ansible/ansible.cfg as config file
redirecting (type: modules) ansible.builtin.postgresql_privs to community.postgresql.postgresql_privs
redirecting (type: callback) ansible.builtin.unixy to community.general.unixy
redirecting (type: callback) ansible.builtin.unixy to community.general.unixy
Skipping callback 'default', as we already have a stdout callback.
Skipping callback 'minimal', as we already have a stdout callback.
Skipping callback 'oneline', as we already have a stdout callback.
Executing playbook test.yaml
1 plays in test.yaml

- test on hosts: localhost -
Gathering Facts...
  localhost ok
META: ran handlers
Validate configuration json...
redirecting (type: modules) ansible.builtin.postgresql_privs to community.postgresql.postgresql_privs
  localhost ok: {
    "changed": false,
    "queries": [
        "GRANT SELECT ON table \"public\".\"table1\",\"public\".\"table2\",\"public\".\"table3\",\"public\".\"table4\",\"public\".\"table5\" TO \"test\";"
    ]
}
META: ran handlers
META: ran handlers

[hunleyd]

thanks for the report @SantiRaposo !
if i'm reading this correctly, the end result is correct with either statement? it's just the excessive logging that's an issue?

[SantiRaposo]

Hi!

Yes the end result is apparently fine, it's just that the optimal way to execute changes to all tables in a schema is to use the ALL TABLES option rather than retrieving a complete list of tables and constructing a query that way.

[hunleyd]

Thanks for confirming. Did you wanna look at this @jchancojr ? If so, feel free to self-assign, otherwise, I'll take a stab at it soon

[jchancojr]

Sure, I'll take a look at this and reach back out with my findings.

[jchancojr]

@SantiRaposo Hi,

The reason the code behaves this way is because we currently support versions < 9.0 where the ALL TABLES IN SCHEMA option doesn't exist. We'll work on adding logic to allow ALL TABLES IN SCHEMA in versions > 9.0 as well as what is already in place.

Thanks for pointing this out!

[jchancojr]

Hi @SantiRaposo

Created PR #282 to solve for this! Thanks for pointing it out.