roles/util/postgres-db-grp-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 # You likely want to use the other pg-db role.
   8 # pg has a broken permission system -> many take-own needed - or just dont care.
   9 #
  10 ---
  11 - name: ensure pg group "grp_{{pg_data.dbname}}_owner" exists
  12   become_user: postgres
  13   postgresql_user:
  14     name: "grp_{{pg_data.dbname}}_owner"
  15     role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
  16
  17
  18 - name: create db "{{pg_data.dbname}}"
  19   become_user: "postgres"
  20   postgresql_db:
  21     name: "{{pg_data.dbname}}"
  22     owner: "grp_{{pg_data.dbname}}_owner"
  23
  24
  25 - name: set owner of schema "{{pg_data.dbname}}.public"
  26   become_user: "postgres"
  27   postgresql_schema:
  28     database: "{{pg_data.dbname}}"
  29     name: public
  30     owner: "grp_{{pg_data.dbname}}_owner"
  31
  32
  33 - name: revoke privs for PUBLIC on db "{{pg_data.dbname}}"
  34   become_user: postgres
  35   postgresql_privs:
  36     db: "{{pg_data.dbname}}"
  37     state: absent
  38     privs: ALL
  39     type: database
  40     role: public
  41
  42
  43 - name: revoke privs for PUBLIC on schema "{{pg_data.dbname}}.public"
  44   become_user: postgres
  45   postgresql_privs:
  46     db: "{{pg_data.dbname}}"
  47     state: absent
  48     privs: ALL
  49     type: schema
  50     objs: public
  51     role: public
  52
  53
  54 - name: ensure group grp_spectator exists and grant necessary privs on db "{{pg_data.dbname}}"
  55   become_user: postgres
  56   postgresql_user:
  57     name: "grp_spectator"
  58     role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
  59     db: "{{pg_data.dbname}}"
  60     priv: CONNECT,TEMPORARY
  61
  62
  63 - name: ensure pg user "usr_{{pg_data.dbname}}" exists
  64   become_user: postgres
  65   postgresql_user:
  66     name: "usr_{{pg_data.dbname}}"
  67     password: "{{pg_data.pw}}"
  68   when: pg_data.dbname != "" and pg_data.pw != ""
  69
  70
  71 - name: add user "usr_{{pg_data.dbname}}" to group "grp_{{pg_data.dbname}}_owner"
  72   become_user: postgres
  73   postgresql_privs:
  74     # always use postgres here
  75     db: "postgres"
  76     role: "usr_{{pg_data.dbname}}"
  77     objs: "grp_{{pg_data.dbname}}_owner"
  78     type: group
  79   when: pg_data.dbname != "" and pg_data.pw != ""