Properly setup postgreSQL RDS using Ansible #updated here https://gadelkareem.com/2018/04/10/setup-postgresql-rds-using-ansible/

rds-postgres.yml

- set_fact:
    env: staging
    postgresql_admin_user: "pgadmin"
    postgresql_admin_password: "some_pass"
    postgresql_databases:
      - owner: some_user
        name: some_db
    postgresql_users:
      - name: some_user
        pass: some_pass
        encrypted: no
    postgresql_user_privileges:
      - name: some_user                   # user name
        db: some_db                  # database
        priv: "ALL"                 # privilege string format: example: INSERT,UPDATE/table:SELECT/anothertable:ALL
    #    role_attr_flags: ""

# the variables here are left out
- name: Start Postgres RDS server instances
  rds:
    command: create
    instance_name: "mypostgres-db-{{env}}"
    region: "{{ aws_region }}"
    instance_type: "{{ postgres_instance_type }}"
    db_engine: "{{postgres_engine}}"
    size: "{{postgres_size}}"
    engine_version: "{{postgres_engine_version}}"
    parameter_group: "{{postgres_parameter_group}}"
    multi_zone: "{{postgres_multi_zone}}"
    zone: "{{ aws_zones[0] }}"
    publicly_accessible: "{{postgres_publicly_accessible}}"
    port: "{{postgres_port}}"
    db_name: "{{ postgresql_admin_user }}"
    username: "{{ postgresql_admin_user }}"
    password: "{{ postgresql_admin_password }}"
    vpc_security_groups: "{{db_security_group.group_id}}"
#    iops: "{{postgres_iops}}"
    maint_window: "Sun:03:00-Sun:04:30"
    backup_window: "02:00-02:30"
    backup_retention: 20
    wait: yes
    wait_timeout: 1600
  register: db
  no_log: True


- name: PostgreSQL | Install psycopg2 locally
  pip:
      name: psycopg2-binary
      state: latest
  delegate_to: 127.0.0.1

# Make sure you enable RDS in boto ini config
- name: set postgres_host
  set_fact:
    postgres_host: "{{hostvars[groups['mypostgres-db-'+env][0]].ansible_host}}"

- name: PostgreSQL | Make sure the PostgreSQL databases are present
  postgresql_db:
    name: "{{item.name}}"
    encoding: 'UTF-8'
    lc_collate: 'en_US.UTF-8'
    lc_ctype: 'en_US.UTF-8'
    port: 3306
    template: "template0"
    state: present
    login_host: "{{ postgres_host }}"
    login_user: "{{postgresql_admin_user}}"
    login_password: "{{ postgresql_admin_password }}"
  delegate_to: 127.0.0.1
  with_items: "{{postgresql_databases}}"
  no_log: True

- name: PostgreSQL | Make sure the PostgreSQL users are present
  postgresql_user:
    name: "{{item.name}}"
    password: "{{ item.pass }}"
    encrypted: yes
    port: 3306
    state: present
    login_host: "{{ postgres_host }}"
    login_user: "{{postgresql_admin_user}}"
    login_password: "{{ postgresql_admin_password }}"
  with_items: "{{postgresql_users}}"
  delegate_to: 127.0.0.1
  no_log: True

- name: PostgreSQL | Update the user privileges
  postgresql_user:
    name: "{{item.name}}"
    db: "{{item.db}}"
    priv: "{{item.priv}}"
    role_attr_flags: "{{item.role_attr_flags | default(omit)}}"
    port: 3306
    state: present
    login_host: "{{ postgres_host }}"
    login_user: "{{postgresql_admin_user}}"
    login_password: "{{ postgresql_admin_password }}"
  with_items: "{{postgresql_user_privileges}}"
  delegate_to: 127.0.0.1
  no_log: True

- name: PostgreSQL | Update databases owners
  postgresql_db:
    name: "{{item.name}}"
    owner: "{{item.owner}}"
    state: present
    port: 3306
    login_host: "{{ postgres_host }}"
    login_user: "{{postgresql_admin_user}}"
    login_password: "{{ postgresql_admin_password }}"
  delegate_to: 127.0.0.1
  with_items: "{{postgresql_databases}}"
  no_log: True