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.

June 10, 2022

Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons

#ansible #database #module #postgresql_privs #permission #postgresql #db #

How to Grant Privileges to User/Role on PostgreSQL Database with Ansible?

I’m going to show you a live demo 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

The Best Resources For Ansible

Video Course

Learn Ansible Automation in 200+examples & practical lessons: Learn Ansible with some real-life examples of how to use the most common modules and Ansible Playbook

Books

demo

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

---
- name: postgresql demo
  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

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_grant.yml
PLAY [postgresql demo] ************************************************************************************
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

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_grant.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Grant db user access to db] *************************************************************************
ok: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

before execution

$ ssh [email protected]
Last login: Fri Jun 10 16:17:33 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Fri Jun 10 16:17:38 UTC 2022 on pts/0
[[email protected] ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \l testdb
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)
postgres=#

after execution

$ ssh [email protected]
Last login: Fri Jun 10 16:50:22 2022 from 192.168.178.26
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Fri Jun 10 16:49:06 UTC 2022 on pts/0
[[email protected] ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \l testdb
                                List of databases
  Name  |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
--------+----------+----------+-------------+-------------+-----------------------
 testdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
        |          |          |             |             | postgres=CTc/postgres+
        |          |          |             |             | myuser=CTc/postgres
(1 row)
postgres=#

code with ❤️ in GitHub

Recap

Now you know how to Grant Privileges to Users/Roles on PostgreSQL databases with Ansible. Subscribe to the YouTube channel, Medium, Website, Twitter, and Substack to not miss the next episode of the Ansible Pilot.

Academy

Learn the Ansible automation technology with some real-life examples in my

My book Ansible By Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps

BUY the Complete PDF BOOK to easily Copy and Paste the 200+ Ansible code

Donate

Want to keep this project going? Please donate