create_clips_user.yml

---
- hosts: localhost
  connection: local
  gather_facts: no
  tasks:
    - rds:
        command: facts
        instance_name: "{{ project_slug }}-database"
        region: "{{ region }}"
      register: database


    - name: Make intake users have create and usage on public
      postgresql_privs:
        login_host: "{{ database.instance.endpoint }}"
        login_user: "{{ pg_username }}"
        login_password: "{{ pg_password }}"
        db: intake
        privs: CREATE,USAGE
        type: schema
        objs: public
        roles: intake


    - name: Make default users not have create and usage on public
      postgresql_privs:
        login_host: "{{ database.instance.endpoint }}"
        login_user: "{{ pg_username }}"
        login_password: "{{ pg_password }}"
        db: intake
        state: absent
        privs: CREATE,USAGE
        type: schema
        objs: public
        roles: public


    - name: Create Clips User with Privacy Preserving Access
      postgresql_user:
        login_host: "{{ database.instance.endpoint }}"
        login_user: "{{ pg_username }}"
        login_password: "{{ pg_password }}"
        db: intake
        name: "clips"
        password: "{{ pg_clips_password }}"
        priv: "CONNECT"
        role_attr_flags: NOSUPERUSER
        no_password_changes: true


    - name: Give Clips User permissions on Purged schema
      postgresql_privs:
        login_host: "{{ database.instance.endpoint }}"
        login_user: "{{ pg_app_username }}"
        login_password: "{{ pg_app_password }}"
        db: intake
        privs: USAGE
        type: schema
        objs: purged
        roles: clips


    - name: Grant Select on tables in purged schema
      postgresql_privs:
        login_host: "{{ database.instance.endpoint }}"
        login_user: "{{ pg_app_username }}"
        login_password: "{{ pg_app_password }}"
        db: intake
        privs: SELECT
        type: table
        objs: ALL_IN_SCHEMA
        schema: purged
        roles: clips