ansible-playbooks/roles/postgres/tasks/add_database.yml

- name: check if database is an object
  fail:
    msg: database must be an object
  when: database is not mapping


- name: check if database parameters are defined
  fail:
    msg: some database parameters are invalid or not defined
  when: (database.name is not string) or (database.user is not string) or
        (database.pass is not string)


- name: add db to postgres
  community.postgresql.postgresql_db:
    name: "{{ database.name }}"
    trust_input: no


- name: add user to postgres
  community.postgresql.postgresql_user:
    comment: "{{ database.user_comment | d('managed by ansible') }}"
    encrypted: yes
    expires: infinity
    name: "{{ database.user }}"
    password: "{{ database.pass }}"
    role_attr_flags: "{{ database.user_flags | d('NOSUPERUSER,NOCREATEROLE,NOCREATEDB') }}"
    trust_input: no


- name: grant database privileges to user
  community.postgresql.postgresql_privs:
    database: "{{ database.name }}"
    privs: CREATE,CONNECT,TEMPORARY
    type: database
    role: "{{ database.user }}"


- name: grant privileges to all tables
  community.postgresql.postgresql_privs:
    database: "{{ database.name }}"
    privs: ALL
    type: table
    objs: ALL_IN_SCHEMA
    role: "{{ database.user }}"


- name: grant privileges to all sequences
  community.postgresql.postgresql_privs:
    database: "{{ database.name }}"
    privs: ALL
    type: sequence
    objs: ALL_IN_SCHEMA
    role: "{{ database.user }}"


- name: grant privileges to public schema
  community.postgresql.postgresql_privs:
    database: "{{ database.name }}"
    privs: USAGE,CREATE
    type: schema
    objs: public
    role: "{{ database.user }}"


- name: add line to postgres hba
  community.postgresql.postgresql_pg_hba:
    dest: "{{ (postgresql_conf_dir, 'pg_hba.conf') | path_join }}"
    contype: "{{ 'host' if (database.ssl | d(false) == false) else 'hostssl' }}"
    databases: "{{ database.name }}"
    users: "{{ database.user }}"
    address: "{{ item }}/32"
    method: "{{ database.auth_method | d('scram-sha-256') }}"
  register: result
  loop: "{{ [database.addresses] if database.addresses is string else
            (database.addresses | d(['127.0.0.1' if (database.self_hosted | d(false) == true) else ansible_host])) }}"


- name: reload postgres config
  community.postgresql.postgresql_query:
    query: SELECT pg_reload_conf();
  when: result.changed