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 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
The Best Resources For Ansible
Certifications
Video Course
Printed Book
eBooks
Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Ansible Cookbook: A Comprehensive Guide to Unleashing the Power of Ansible via Best Practices, Troubleshooting, and Linting Rules with Luca Berton
Ansible For Windows By Examples: 50+ Automation Examples For Windows System Administrator And DevOps
Ansible For Linux by Examples: 100+ Automation Examples For Linux System Administrator and DevOps
Ansible Linux Filesystem By Examples: 40+ Automation Examples on Linux File and Directory Operation for Modern IT Infrastructure
Ansible For Security by Examples: 100+ Automation Examples to Automate Security and Verify Compliance for IT Modern Infrastructure
Ansible Tips and Tricks: 10+ Ansible Examples to Save Time and Automate More Tasks
Ansible Linux Users & Groups By Examples: 20+ Automation Examples on Linux Users and Groups Operation for Modern IT Infrastructure
Ansible For PostgreSQL by Examples: 10+ Examples To Automate Your PostgreSQL database
Ansible For Amazon Web Services AWS By Examples: 10+ Examples To Automate Your AWS Modern Infrastructure
Ansible Automation Platform By Example: A step-by-step guide for the most common user scenarios
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
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Fri Jun 10 16:17:38 UTC 2022 on pts/0
[postgres@demo ~]$ 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
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Fri Jun 10 16:49:06 UTC 2022 on pts/0
[postgres@demo ~]$ 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=#
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
Donate
Want to keep this project going? Please donate