postgresql_privs fails with schema name which contain '-' (dash) character

[peterkrawiec1]

Summary

Task with module postgresql_privs fails with syntax error when it's trying to apply privileges to group roles on schema with a name which contain '-' (dash) character.

Issue Type

Bug Report

Component Name

postgresql_privs

Ansible Version

$ ansible --version
ansible [core 2.14.3]
ansible 7.0.0
python 3.9.6
jinja 3.0.2

Community.general Version

$ ansible-galaxy collection list community.general
community.general 6.0.1

Configuration

$ ansible-config dump --only-changed

OS / Environment

MacOS Ventura 13.2.1

Steps to Reproduce

roles:
  - name: test-role
    role_flags: "NOLOGIN,NOCREATEROLE,NOCREATEDB"
    state: present
    privs:
      - db: test
        type: table
        objs: ALL_IN_SCHEMA
        privs: SELECT,UPDATE,INSERT
        schema: public
        state: present
      - db: test
        type: sequence
        objs: ALL_IN_SCHEMA
        privs: SELECT,USAGE
        schema: public
        state: present
      - db: test
        type: schema
        objs: public
        privs: USAGE
        state: present
      - db: test
        type: table
        objs: ALL_IN_SCHEMA
        privs: SELECT,UPDATE,INSERT
        schema: test-schema
        state: present
      - db: test
        type: sequence
        objs: ALL_IN_SCHEMA
        privs: SELECT,USAGE
        schema: test-schema
        state: present
      - db: test
        type: schema
        objs: test-schema
        privs: USAGE
        state: present
      - db: test
        type: schema
        objs: sample
        privs: USAGE
        state: present
      - db: test
        type: table
        schema: sample
        objs: table1,table2
        privs: SELECT,UPDATE,INSERT,DELETE
        state: present


- name: Configure group role privileges
  postgresql_privs:
    db: "{{ item.1.db }}"
    schema: "{{ item.1.schema | default(omit) }}"
    objs: "{{ item.1.objs }}"
    privs: "{{ item.1.privs }}"
    type: "{{ item.1.type }}"
    role: "{{ item.0.name }}"
    state: "{{ item.1.state | default('present') }}"
    port: "{{ instance.port }}"
    login_host: "{{ ansible_host }}"
    login_user: "{{ instance.owner | default('postgres') }}"
    login_password: "{{ instance.password }}"
  loop: "{{ roles | subelements('privs', 'skip_missing=True') }}"
  loop_control:
    label: "{{ item.0.name }}:{{ item.1.db }}:{{ item.1.schema | default('public') }} -> {{ item.1.state | default('present') }} -> {{ item.1.privs }} ON {{ item.1.objs }}"
  register: role_privs
  tags: ["groups", "permissions"]

Expected Results

Group role privileges applied to all objects as per roles block.

Sample of the output should be:

ok: [test-db-instance] => (item=test-role:test:public -> present -> USAGE ON public)
ok: [test-db-instance] => (item=test-role:test:test-schema -> present -> SELECT,UPDATE,INSERT ON ALL_IN_SCHEMA)

Actual Results

failed: [test-db-instance] (item=test-role:test:test-schema -> present -> SELECT,UPDATE,INSERT ON ALL_IN_SCHEMA) => changed=false

ansible_loop_var: item
item:
- db: test
    objs: ALL_IN_SCHEMA
    privs: SELECT,UPDATE,INSERT
    schema: test-schema
    state: present
    type: table

msg: |-
  syntax error at or near "-"
  LINE 1: ...PDATE,INSERT,SELECT ON ALL TABLES IN SCHEMA test-schema TO "t...
                                                             ^

Code of Conduct

• [X] I agree to follow the Ansible Code of Conduct

[ansibullbot]

Files identified in the description: None

If these files are incorrect, please update the component name section of the description or use the !component bot command.

click here for bot help

[felixfontein]

@peterkrawiec1 the postgresql modules have been moved from community.general to their own collections a long time ago; I've moved your issue to the correct repository.

[peterkrawiec1]

Apologies @felixfontein and thank you for moving it across!

[betanummeric]

The postgresql collection comes with some parsing magic to detect if the identifier (e.g. schema name) is already quoted, and add quotes if necessary. While the parsing could be improved, you can work around it by providing the module with quoted variables. I did this by writing a custom ansible filter plugin that adds quotes to an identifier:

def pg_quote_identifier(*names) -> str:
    return '.'.join('"' + name.replace('"', '""') + '"' for name in names)

to be used like

schema: "{{ random_unicode1|pg_quote_identifier }}" # results in '"schema_name"'
table: "{{ random_unicode2|pg_quote_identifier(random_unicode3) }}" # results in '"schema_name"."table_name"'

[peterkrawiec1]

Thank you @betanummeric .

Interestingly, it actually only fails if the schema name contains a hyphen and the privs to be applied are on ALL TABLES IN SCHEMA. In other cases parsing seems to be working fine.

And so, the privs would get correctly applied in this case:

- db: test
  type: sequence
  objs: ALL_IN_SCHEMA
  privs: SELECT,USAGE
  schema: test-schema
  state: present

but would fail in this one:

- db: test
  type: table
  objs: ALL_IN_SCHEMA
  privs: SELECT,UPDATE,INSERT
  schema: test-schema
  state: present

[peterkrawiec1]

Has anyone had a chance to look into the above please?

[Andersson007]

I'm personally not a user, so for me it's hard to participate. If anyone wanna raise a PR, here's the Quick start guide.

UPDATE: pinged folks on Matrix in the postgresql room

[peterkrawiec1]

Thank you @Andersson007!

[betanummeric]

@peterkrawiec1 What version of the community.postgresql collection are you using? Version 2.3.2 includes #382 which hopefully already fixes your issue.

[Andersson007]

@peterkrawiec1 just in case the GH notification has drown in emails, please take a look at the question, thanks

[peterkrawiec1]

Thanks @Andersson007 ! That's exactly what happened!

Apologies @betanummeric for missing your message - I'm using 2.3.2...