The 'postgresql_privs' module is not idempotent in certain cases

[drybjed]

SUMMARY

Certain grant and revoke actions in the postgresql_privs module are not idempotent since Ansible 2.10.1. I have checked this on multiple versions of Ansible:

ansible: 2.10.0
ansible-base: 2.10.1
status: idempotent

ansible: 2.10.0
ansible-base: 2.10.2
status: idempotent

ansible: 2.10.1
ansible-base: 2.10.2
status: non-idempotent

ansible: 2.10.2
ansible-base: 2.10.3
status: non-idempotent

ansible: 2.10.3
ansible-base: 2.10.4
status: non-idempotent

ansible: 2.10.4
ansible-base: 2.10.4
status: non-idempotent

ansible: 2.10.5
ansible-base: 2.10.5
status: non-idempotent

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.10.5
  config file = None
  configured module search path = ['/home/drybjed/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/drybjed/.local/lib/python3.7/site-packages/ansible
  executable location = /home/drybjed/.local/bin/ansible
  python version = 3.7.3 (default, Dec 20 2019, 18:57:59) [GCC 8.3.0]

CONFIGURATION

Nothing changed

OS / ENVIRONMENT

OS: Debian 10 (Buster)
Database: PostgreSQL 11

STEPS TO REPRODUCE

---

- hosts: 'localhost'
  become: True

  tasks:

    - name: Grant connect on postgres to PUBLIC
      postgresql_privs:
        database: 'postgres'
        port: '5432'
        role: 'PUBLIC'
        type: 'database'
        privs: 'CONNECT'
        state: 'present'
      become: True
      become_user: 'postgres'

    - name: Revoke temporary on postgres from PUBLIC
      postgresql_privs:
        database: 'postgres'
        port: '5432'
        role: 'PUBLIC'
        type: 'database'
        privs: 'TEMPORARY'
        state: 'absent'
      become: True
      become_user: 'postgres'

EXPECTED RESULTS

The playbook grants and revokes the permissions on the first run, on second run nothing is changed.

ACTUAL RESULTS

Playbook reports the changed status on each execution.

TASK [Grant connect on postgres to PUBLIC] ****************************************************************************************************************
changed: [localhost] => changed=true                                                                                                                           
  invocation:                                                                                                                                              
    module_args:                                                                                                                                           
      ca_cert: null                                                                                                                                        
      database: postgres                                                                                                                                   
      fail_on_role: true                                                                                                                                   
      grant_option: null                                                                                                                                   
      host: ''                                                                                                                                             
      login: postgres                                                                                                                                      
      login_host: ''                                                                                                                                       
      login_password: ''                                                                                                                                   
      login_unix_socket: ''                                                                                                                                
      login_user: postgres                                                                                                                                 
      objs: null                                                                                                                                           
      password: ''                                                                                                                                         
      port: 5432                                                                                                                                           
      privs: CONNECT                                                                                                                                       
      role: PUBLIC                                                                                                                                         
      roles: PUBLIC                                                                                                                                        
      schema: null                                                                                                                                         
      session_role: null                                                                                                                                   
      ssl_mode: prefer                                                                                                                                     
      state: present                                                                                                                                       
      target_roles: null                                                                                                                                   
      trust_input: true                                                                                                                                    
      type: database                                                                                                                                       
      unix_socket: ''                                                                                                                                      
      usage_on_types: true                                                                                                                                 
  queries:                                                                                                                                                 
  - GRANT CONNECT ON database "postgres" TO PUBLIC;                                                                                                        

TASK [Revoke temporary on postgres from PUBLIC] ***********************************************************************************************************
changed: [localhost] => changed=true
  invocation:
    module_args:
      ca_cert: null
      database: postgres
      fail_on_role: true
      grant_option: null
      host: ''
      login: postgres
      login_host: ''
      login_password: ''
      login_unix_socket: ''
      login_user: postgres
      objs: null
      password: ''
      port: 5432
      privs: TEMPORARY
      role: PUBLIC
      roles: PUBLIC
      schema: null
      session_role: null
      ssl_mode: prefer
      state: absent
      target_roles: null
      trust_input: true
      type: database
      unix_socket: ''
      usage_on_types: true
  queries:
  - REVOKE TEMPORARY ON database "postgres" FROM PUBLIC;

[Andersson007]

@drybjed thanks for reporting this!
CC @Dorn- @andytom @ilicmilan @jbscalia @kostiantyn-nemchenko @matburt @nerzhul @sebasmannem @tcraxs

[tcraxs]

I found the change in the documentation: f91a202#diff-7a69a7ea4df27294ee21c14d20d48cae9b9cdce68bddbf8205cae1d7aabbc9e9R172

• Note that when you use C(PUBLIC) role, the module always reports that the state has been changed.

So I guess this is intended, also not optimal.

[drybjed]

Ah, so it is a limitation of the PostgreSQL not returning any status change for this particular role? I suppose that if these particular statements are 100% idempotent on Ansible's side, I can just add changed_when: False to ignore the changed status and point to this PR for explanation.

[tcraxs]

Ah, so it is a limitation of the PostgreSQL not returning any status change for this particular role?

There is a return status, but I think it is difficult to find an easy way to figure out if this grants already exists or not.
The return status is every time GRANT or REVOKE.

I suppose that if these particular statements are 100% idempotent on Ansible's side, I can just add changed_when: False to ignore the changed status and point to this PR for explanation.

Yes, I think this is the way to go here.

If you find a good way (SQL query) to check if the statement will not change anything, then we can maybe implement this in the module, to fix this non-idempotent case.

[drybjed]

I think it there was such a query the module could use, it would be implemented by now. Since we found a workaround, I'm closing this issue.

[Andersson007]

I think it there was such a query the module could use, it would be implemented by now.

@drybjed , not sure;)
Anyway, @tcraxs @drybjed thanks you for the investigation and discussion!

[SpComb]

I think the Ansible 2.9 version of this module was able to report the changed state correctly, even for PUBLIC privs?

Looking at the code, the get_status = self.get_schema_acls seems like it should work as expected for e.g. schemas, and the return status_before != status_after reporting looks like it should be able to correctly report the changed state:

test=> SELECT nspacl FROM pg_catalog.pg_namespace WHERE nspname = ANY('{public}') ORDER BY nspname;
               nspacl                
-------------------------------------
 {postgres=UC/postgres,=UC/postgres}
(1 row)

test=> REVOKE CREATE,USAGE ON SCHEMA "public" FROM PUBLIC;
REVOKE
test=> SELECT nspacl FROM pg_catalog.pg_namespace WHERE nspname = ANY('{public}') ORDER BY nspname;
         nspacl         
------------------------
 {postgres=UC/postgres}
(1 row)

However, it seems like there is something odd going on with database objects, where the datacl is initially empty:

test=> SELECT datacl FROM pg_catalog.pg_database WHERE datname = ANY ('{test}') ORDER BY datname;
 datacl 
--------
 
(1 row)

test=> REVOKE CONNECT,TEMP ON database "test" FROM PUBLIC;
REVOKE
test=> SELECT datacl FROM pg_catalog.pg_database WHERE datname = ANY ('{test}') ORDER BY datname;
         datacl          
-------------------------
 {postgres=CTc/postgres}
(1 row)