Grant Privileges to User/Role on PostgreSQL Database - Ansible module postgresql_privs — Video Tutorial

How to Grant Privileges to User/Role on PostgreSQL Database with Ansible? I'm going to show you a live Playbook with some simple Ansible code. I'm Luca Berton and welcome to today's episode of Ansible Pilot Ansible Grant Privileges to User/Role on PostgreSQL Database - `community.postgresql.postgresql_privs` - Grant or revoke privileges on PostgreSQL database objects Let's talk about the Ansible module `postgresql_privs`. The full name is `community.postgresql.postgresql_privs`, which means that is part of the collection of modules "community.postgresql" maintained by the Ansible Community to interact with PostgreSQL. The collection is tested with `ansible-core` version 2.11+, prior versions such as 2.9 or 2.10 are not supported. The purpose of the module is to Grant or revoke privileges on PostgreSQL database objects. This module uses `psycopg2`, a Python PostgreSQL User library. You must ensure that `python3-psycopg2` is installed on the host before using this module. Link - [`community.postgresql.postgresql_privs`](https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_privs_module.html) ## Playbook Let's jump into a real-life Ansible Playbook to Grant Privileges to User/Role on PostgreSQL Database. I'm going to show you how to grant all the privileges to user/role `myuser` for database `testdb` in the current PostgreSQL server. code ```yaml --- - name: postgresql Playbook hosts: all become: true vars: db_user: myuser db_name: testdb tasks: - name: Utility present ansible.builtin.package: name: python3-psycopg2 state: present - name: Grant db user access to db community.postgresql.postgresql_privs: type: database database: "{{ db_name }}" roles: "{{ db_user }}" grant_option: false privs: all become: true become_user: postgres ``` execution ```bash $ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_grant.yml PLAY [postgresql Playbook] ************************************************************************************ TASK [Gathering Facts] ************************************************************************************ ok: [demo.example.com] TASK [Utility present] ************************************************************************************ ok: [demo.example.com] TASK [Grant db user access to db] ************************************************************************* changed: [demo.example.com] PLAY RECAP ************************************************************************************************ demo.example.com : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 ``` idempotency ```bash $ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_grant.yml PLAY [postgresql Playbook] ************************************************************************************ TASK [Gathering Facts] *********************