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-2024 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     privs: ALL
  38     type: database
  39     role: public
  40
  41
  42 - name: revoke privs for PUBLIC on schema "{{pg_name}}.public"
  43   become_user: postgres
  44   postgresql_privs:
  45     db: "{{pg_name}}"
  46     state: absent
  47     privs: ALL
  48     type: schema
  49     objs: public
  50     role: public
  51
  52
  53 - name: ensure group grp_spectator exists and grant necessary privs on db "{{pg_name}}"
  54   become_user: postgres
  55   postgresql_user:
  56     name: "grp_spectator"
  57     role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
  58     db: "{{pg_name}}"
  59     priv: CONNECT,TEMPORARY