The Ansible collection includes the postgresql_privs module which is used to set or revoke privileges on database objects. So far this can be used in a programmatic way: specify the privileges, but also specify if something should be revoked. To give you an example:

Let’s say we have a table users in the database. Now an additional role audit is supposed to have SELECT privileges on this table. You solve this by adding one postgresql_privs task:

- name: GRANT privileges to role "audit"
  community.postgresql.postgresql_privs:
    database: "users"
    state: present
    privs: SELECT
    type: table
    objs: users
    schema: public
    roles: audit
    grant_option: false

And the accounting team needs read and write privileges on the table, results in another task:

- name: GRANT privileges to role "accounting"
  community.postgresql.postgresql_privs:
    database: "users"
    state: present
    privs: SELECT,INSERT,UPDATE,DELETE
    type: table
    objs: users
    schema: public
    roles: accounting
    grant_option: false

So far, so good. Now imagine the audit team comes back and says accounting is not supposed to delete any entries, for audit reasons. This requires two steps:

  • Update the existing task and remove the DELETE privileges
  • Since this change only applies to newly granted privileges, this requires a second task which REVOKEs the privileges in the database
- name: GRANT privileges to role "accounting"
  community.postgresql.postgresql_privs:
    database: "users"
    state: present
    privs: SELECT,INSERT,UPDATE
    type: table
    objs: users
    schema: public
    roles: accounting
    grant_option: false

- name: REVOKE privileges from role "accounting"
  community.postgresql.postgresql_privs:
    database: "users"
    state: absent
    privs: DELETE
    type: table
    objs: users
    schema: public
    roles: accounting

Very quickly this becomes ugly, because one not only must update existing privileges in the code base, but also remember to revoke the privileges in the database. And it’s not the Ansible way where one would define a definite state (all privileges) and let Ansible figure out what steps are necessary in order to grant and revoke all the privileges.

There is an additional problem: in order to grant privileges, one has to write code. Possibly plenty of postgresql_privs tasks. This makes it hard to audit because it spreads out the privileges as code pieces all over the code base.

What we would rather do is place the definite state of the privileges in the inventory, and then create one task which grants and revokes all the privileges on the object as necessary. Since this is a rather big change, and needs input from you, we first opened a request for comments on the ansible-collections / community.postgresql repository. The issue explains the changes we plan to implement, and is open for input and discussion.