roles/util/postgres-db-usr/tasks/main.yml

   1 #####################################
   2 ### someone's ansible provisioner ###
   3 #####################################
   4 # Part of: https://git.somenet.org/root/pub/somesible.git
   5 # 2017-2025 by someone <someone@somenet.org>
   6 #
   7 ---
   8 - name: ensure pg user "{{pg_name}}" exists
   9   become_user: postgres
  10   postgresql_user:
  11     name: "{{pg_name}}"
  12     password: "{{pg_pass}}"
  13     conn_limit: "{{pg_conn_limit | default(50)}}"
  14   when: pg_name != "" and pg_pass != ""
  15
  16
  17 - name: create db "{{pg_name}}"
  18   become_user: "postgres"
  19   postgresql_db:
  20     name: "{{pg_name}}"
  21     owner: "{{pg_name}}"
  22
  23
  24 - name: set owner of schema "{{pg_name}}.public" to user "{{pg_name}}"
  25   become_user: "postgres"
  26   postgresql_schema:
  27     database: "{{pg_name}}"
  28     name: public
  29     owner: "{{pg_name}}"
  30
  31
  32 - name: revoke privs for PUBLIC on db "{{pg_name}}"
  33   become_user: postgres
  34   postgresql_privs:
  35     db: "{{pg_name}}"
  36     state: absent
  37     type: database
  38     role: public
  39
  40
  41 - name: revoke privs for PUBLIC on schema "{{pg_name}}.public"
  42   become_user: postgres
  43   postgresql_privs:
  44     db: "{{pg_name}}"
  45     state: absent
  46     privs: ALL
  47     type: schema
  48     objs: public
  49     role: public
  50
  51
  52 - name: ensure group grp_spectator has necessary privs on db "{{pg_name}}"
  53   become_user: postgres
  54   postgresql_privs:
  55     db: "{{pg_name}}"
  56     obj: "{{pg_name}}"
  57     privs: CONNECT,TEMPORARY
  58     role: "grp_spectator"