Topics

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


By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close