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.