From a9f0ecb3be078e3c34fe4e0152a53980616da77b Mon Sep 17 00:00:00 2001
From: Someone <someone@somenet.org>
Date: Mon, 29 Apr 2024 10:46:28 +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 <someone@somenet.org>
+#
+# 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