Setup postgreSQL RDS using Ansible

Setting up PostgreSQL on RDS using ansible is a bit tricky because the main user on RDS is not a SUPERUSER and roles membership is not automatically granted for ex: “ERROR: must be member of role ..” is quite common. Here is a working solution:

- set_fact:
    local_ip: "123.456.0.0"
    env: staging
    postgresql_admin_user: "pgadmin"
    postgresql_admin_password: "some_pass"
    postgresql_admin_db: "some_db"
    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: ""

- name: PostgreSQL | Databases security group
  ec2_group:
    name: "database_{{env}}"
    description: "Database security group"
    region: "{{ aws_region }}"
    vpc_id: "{{vpc_id}}"
    purge_rules: False
    purge_rules_egress: False
    rules:
      - proto: tcp
        from_port: 5432
        to_port: 5432
        cidr_ip: "172.31.0.0/16"
      - proto: tcp
        from_port: 5432
        to_port: 5432
        cidr_ip: "{{local_ip}}" # your local IP
    rules_egress:
      - proto: tcp
        from_port: 80
        to_port: 80
        cidr_ip: 0.0.0.0/0
      - proto: tcp
        from_port: 443
        to_port: 443
        cidr_ip: 0.0.0.0/0
  register: db_security_group

- name: install aws-cli
  pip:
    name: awscli
    state: latest
  delegate_to: 127.0.0.1

# issue with full example https://github.com/ansible/ansible-modules-core/issues/633
# most of the variables on this tasks are not present in this file
- name: PostgreSQL | Create an RDS instance in a single availability zone
  command: "aws rds create-db-instance
           --db-instance-identifier my-postgressql-{{env}}
           --db-instance-class {{ postgresql_instance_type }}
           --availability-zone {{ postgresql_zone }}
           --engine {{ postgresql_engine }}
           --engine-version {{ postgresql_engine_version }}
           --db-parameter-group-name {{ postgresql_parameter_group }}
           --storage-type {{ postgresql_storage }}
           --allocated-storage {{ postgresql_size }}
           --master-username {{ postgresql_admin_user }}
           --master-user-password {{ postgresql_admin_password }}
           --db-name {{ postgresql_admin_user }}
           --vpc-security-group-ids {{ db_security_group.group_id }}
           --preferred-maintenance-window 'Sat:02:00-Sat:06:30'
           --auto-minor-version-upgrade
           --backup-retention-period 1
           --preferred-backup-window '05:00-05:30'
           --tags Key=Name,Value={{ instance_name }},Key=Env,Value={{ env }}"
  environment:
      AWS_ACCESS_KEY_ID: "{{aws_key_id}}"
      AWS_SECRET_ACCESS_KEY: "{{aws_secret_key}}"
      AWS_DEFAULT_REGION:  "{{ aws_region }}"
  become_user: root
  delegate_to: 127.0.0.1
  register: single_zone_result
  failed_when: single_zone_result.rc != 0 and ('DBInstanceAlreadyExists' not in single_zone_result.stderr)
  changed_when: "single_zone_result.rc == 0"
  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
# will need to reload boto cache for this to work
- name: set postgres_host
  set_fact:
    postgres_host: "{{hostvars[groups['my-postgressql-'+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: 5432
    template: "{{postgresql_admin_db}}" //to allow full ownership of new databases
    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: 5432
    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: 5432
    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: 5432
    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