Add support for alter default privileges grant usage on schemas to postgresql_privs

[caseyandgina]

SUMMARY

I have encountered the same problem described on this closed issue: https://github.com/ansible/ansible/issues/63273

There was a patch made for it, but for whatever reason that merge request and the issue were closed and the problem still remains.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_privs

ANSIBLE VERSION

ansible [core 2.12.5]
  config file = /home/ubuntu/worktrees/casey_patroni_fixups/ansible/ansible.cfg
  configured module search path = ['/home/ubuntu/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.8/dist-packages/ansible
  ansible collection location = /home/ubuntu/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.8.10 (default, Jun 22 2022, 20:18:18) [GCC 9.4.0]
  jinja version = 2.10.1
  libyaml = True

COLLECTION VERSION

# /usr/local/lib/python3.8/dist-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.6.0  

CONFIGURATION

OS / ENVIRONMENT

Ubuntu 20.04

STEPS TO REPRODUCE

- name: Grant datascience usage to all schemas
  community.postgresql.postgresql_privs:
    database: template1
    type: default_privs
    privs: usage
    objs: schemas
    role: datascience

EXPECTED RESULTS

alter default privileges
        grant           usage
                on      schemas
                to      "datascience";

ACTUAL RESULTS

Invalid Object set specified: frozenset({'SCHEMAS'})

[hunleyd]

thanks for the report @caseyandgina . can you repro this w/ collection version 1.7.5 or 2.2.0?

[caseyandgina]

I looked at the current code here and confirmed that it's not supported yet: https://github.com/ansible-collections/community.postgresql/blob/main/plugins/modules/postgresql_privs.py#L465

Also note that just above that, USAGE is duplicated in VALID_PRIVS.

[Andersson007]

@caseyandgina thanks for reporting the issue! Would you like to submit a PR? We have the Quick start guide if needed.

[caseyandgina]

@caseyandgina thanks for reporting the issue! Would you like to submit a PR? We have the Quick start guide if needed.

Hi @Andersson007 - I am a DBA using Ansible to deploy database infrastructure, and unfortunately am not a developer nor able to work on this myself.

[Andersson007]

@caseyandgina i reproduced the bug in CI, looking now

[Andersson007]

After i made the changes from that PR, i get:

The full traceback is:
  File "/tmp/ansible_postgresql_privs_payload_8p69jbhy/ansible_postgresql_privs_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_privs.py", li
ne 1166, in main
  File "/tmp/ansible_postgresql_privs_payload_8p69jbhy/ansible_postgresql_privs_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_privs.py", li
ne 843, in manipulate_privs

"cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS\n"

So the issue is deeper than it can seem. I guess that's why the original reporter and author dropped it. If i have free time later, i'll take a look but no promises

[caseyandgina]

@Andersson007 This is related to https://github.com/ansible-collections/community.postgresql/issues/333 - Currently the "schema" option is defaulted to 'public', and there seems to be no way to not specify a schema which is another big limitation as sometimes default privileges should be global to all schemas. Specifying a schema when granting/revoking on a schema is impossible hence the error you got. It would be better for this to default to all schemas if unspecified.

[caseyandgina]

To be clear, when not restricting a default privilege to specific schema(s), the "in schema ____" should not be present in the SQL.

[Andersson007]

https://github.com/ansible-collections/community.postgresql/pull/335 was created some time ago, when it's tested, we can merge