From 416f69bfb8f5d9366a6bc4f4dd01f54d6d4ecc4f Mon Sep 17 00:00:00 2001 From: Someone Date: Fri, 4 Oct 2024 13:42:46 +0200 Subject: [PATCH] [roles/util/postgres-db-grp-usr] create postgres db, owner-group and user for group - DO NOT USE. --- roles/util/postgres-db-grp-usr/tasks/main.yml | 79 +++++++++++++++++++ 1 file changed, 79 insertions(+) create mode 100644 roles/util/postgres-db-grp-usr/tasks/main.yml diff --git a/roles/util/postgres-db-grp-usr/tasks/main.yml b/roles/util/postgres-db-grp-usr/tasks/main.yml new file mode 100644 index 0000000..5168f58 --- /dev/null +++ b/roles/util/postgres-db-grp-usr/tasks/main.yml @@ -0,0 +1,79 @@ +##################################### +### someone's ansible provisioner ### +##################################### +# Part of: https://git.somenet.org/root/pub/somesible.git +# 2017-2024 by someone +# +# You likely want to use the other pg-db role. +# pg has a broken permission system -> many take-own needed - or just dont care. +# +--- +- name: ensure pg group "grp_{{pg_data.dbname}}_owner" exists + become_user: postgres + postgresql_user: + name: "grp_{{pg_data.dbname}}_owner" + role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION" + + +- name: create db "{{pg_data.dbname}}" + become_user: "postgres" + postgresql_db: + name: "{{pg_data.dbname}}" + owner: "grp_{{pg_data.dbname}}_owner" + + +- name: set owner of schema "{{pg_data.dbname}}.public" + become_user: "postgres" + postgresql_schema: + database: "{{pg_data.dbname}}" + name: public + owner: "grp_{{pg_data.dbname}}_owner" + + +- name: revoke privs for PUBLIC on db "{{pg_data.dbname}}" + become_user: postgres + postgresql_privs: + db: "{{pg_data.dbname}}" + state: absent + privs: ALL + type: database + role: public + + +- name: revoke privs for PUBLIC on schema "{{pg_data.dbname}}.public" + become_user: postgres + postgresql_privs: + db: "{{pg_data.dbname}}" + state: absent + privs: ALL + type: schema + objs: public + role: public + + +- name: ensure group grp_spectator exists and grant necessary privs on db "{{pg_data.dbname}}" + become_user: postgres + postgresql_user: + name: "grp_spectator" + role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION" + db: "{{pg_data.dbname}}" + priv: CONNECT,TEMPORARY + + +- name: ensure pg user "usr_{{pg_data.dbname}}" exists + become_user: postgres + postgresql_user: + name: "usr_{{pg_data.dbname}}" + password: "{{pg_data.pw}}" + when: pg_data.dbname != "" and pg_data.pw != "" + + +- name: add user "usr_{{pg_data.dbname}}" to group "grp_{{pg_data.dbname}}_owner" + become_user: postgres + postgresql_privs: + # always use postgres here + db: "postgres" + role: "usr_{{pg_data.dbname}}" + objs: "grp_{{pg_data.dbname}}_owner" + type: group + when: pg_data.dbname != "" and pg_data.pw != "" -- 2.43.0