PostgreSQL Create roles from list

Archives Ansible Project
1

Objective:

  • Create a file under a common format (yml, json)
  • Create a PostgreSQL role for each user un file

Description:
I’m trying to create alot of users and inserting them into their respective groups, the structure isn’t very complicated, but my current issue, is trying to find documentation or a solution to achieve the objectives here. I don’t know how to create this list, but indulge me in considering this:

example users.yml|json

------
# Classe roles
name: classeA
db: database
can_login: no

name: classeA
db: database
can_login: no

# Student roles
name: student1
db: database
login: yes
password: xxxx
member_of: classeA

name: student2
db: database
login: yes
db: school
password: xxxx
member_of: classeA

name: student3
db: database
login: yes
db: school
password: xxxx
member_of: classeA

Now, ansible, provides a way to create postgresql users like so

postgresql_user:
db: acme
name: django
password: ceec4eif7ya
priv: "CONNECT/products:ALL"
expires: "Jan 31 2020"

I’ve considered the following idea:

  • using ansible.builtin.include_vars to read the yml file, insert data into a variable LIST, and foreach element in that list, call the postgresql_user module to create the user.

I would welcome any help regarding this.

2

if anyone founds this thread and has the same issue:

first of all your user list must be either a dictionnary or a list which in my example above, is neither. here is what it should look like

static_roles:
admin:
canlogin: true
member_of:
superuser: true
valid_until: ‘’
eleves_suisse:
canlogin: false
member_of:
superuser: false
valid_until: ‘’
eleves_canada:
canlogin: false
member_of:
superuser: false
valid_until: ‘’
eleve1:
canlogin: true
member_of: [eleves_suisse]
superuser: false
valid_until: ‘’
eleve2:
canlogin: true
member_of: [eleves_suisse]
superuser: false
valid_until: ‘’
eleve3:
canlogin: true
member_of: [eleves_canada]
superuser: false
valid_until: ‘’
eleve4:
canlogin: true
member_of: [eleves_canada]
superuser: false
valid_until: ‘’
eleve5:
canlogin: true
member_of: [eleves_canada]
superuser: false
valid_until: ‘’
eleve6:
canlogin: true
member_of: [eleves_suisse]
superuser: false
valid_until: ‘’
eleve7:
canlogin: true
member_of: [eleves_canada]
superuser: false
valid_until: ‘’
postgres:
canlogin: true
member_of:
superuser: true
valid_until: ‘’

now regarding the creation of the user you’ll wanna run a task that looks something like this:

  • name: “create roles”
    become: true
    become_user: postgres
    postgresql_user:
    name: “{{ item.key }}”
    role_attr_flags: “{{ ‘LOGIN’ if item.value.canlogin else ‘NOLOGIN’ }}”
    loop: “{{ static_roles|dict2items }}”
  • name: “add users to groups”
    become: true
    become_user: postgres
    community.postgresql.postgresql_membership:
    group: “{{ item.value.member_of }}”
    target_roles:
  • “{{ item.key }}”
    loop: “{{ static_roles|dict2items }}”
    when: item.value.member_of

Has you can see in the first task i test if the user can login and i display it in the attributes. So that way it will know if it’s a user or a group.
Then i have another task that actually puts the members in the respective groups.

Don’t hesitate to ask questions