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

How to automate the granting of all permission for "myuser" user/role on database "testdb" on PostgreSQL using Ansible Playbook and postgresql_privs module.

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]