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 250+examples & practical lessons: Learn Ansible with some real-life examples of how to use the most common modules and Ansible Playbook

Printed Book

Ansible For VMware by Examples: A Step-by-Step Guide to Automating Your VMware Infrastructure

eBooks

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.