From 9dda81a242288073df928b65f98706ebd5c01832 Mon Sep 17 00:00:00 2001
From: Someone <someone@somenet.org>
Date: Fri, 4 Oct 2024 13:42:46 +0200
Subject: [PATCH] [roles/util/postgres-db-usr] create postgres db and
 owner-user

---
 roles/util/postgres-db-usr/tasks/main.yml | 59 +++++++++++++++++++++++
 1 file changed, 59 insertions(+)
 create mode 100644 roles/util/postgres-db-usr/tasks/main.yml

diff --git a/roles/util/postgres-db-usr/tasks/main.yml b/roles/util/postgres-db-usr/tasks/main.yml
new file mode 100644
index 0000000..58a731f
--- /dev/null
+++ b/roles/util/postgres-db-usr/tasks/main.yml
@@ -0,0 +1,59 @@
+#####################################
+### someone's ansible provisioner ###
+#####################################
+# Part of: https://git.somenet.org/root/pub/somesible.git
+# 2017-2024 by someone <someone@somenet.org>
+#
+---
+- name: ensure pg user "{{pg_name}}" exists
+  become_user: postgres
+  postgresql_user:
+    name: "{{pg_name}}"
+    password: "{{pg_pass}}"
+    conn_limit: "{{pg_conn_limit | default(50)}}"
+  when: pg_name != "" and pg_pass != ""
+
+
+- name: create db "{{pg_name}}"
+  become_user: "postgres"
+  postgresql_db:
+    name: "{{pg_name}}"
+    owner: "{{pg_name}}"
+
+
+- name: set owner of schema "{{pg_name}}.public" to user "{{pg_name}}"
+  become_user: "postgres"
+  postgresql_schema:
+    database: "{{pg_name}}"
+    name: public
+    owner: "{{pg_name}}"
+
+
+- name: revoke privs for PUBLIC on db "{{pg_name}}"
+  become_user: postgres
+  postgresql_privs:
+    db: "{{pg_name}}"
+    state: absent
+    privs: ALL
+    type: database
+    role: public
+
+
+- name: revoke privs for PUBLIC on schema "{{pg_name}}.public"
+  become_user: postgres
+  postgresql_privs:
+    db: "{{pg_name}}"
+    state: absent
+    privs: ALL
+    type: schema
+    objs: public
+    role: public
+
+
+- name: ensure group grp_spectator exists and grant necessary privs on db "{{pg_name}}"
+  become_user: postgres
+  postgresql_user:
+    name: "grp_spectator"
+    role_attr_flags: "NOLOGIN,NOSUPERUSER,INHERIT,NOCREATEDB,NOCREATEROLE,NOREPLICATION"
+    db: "{{pg_name}}"
+    priv: CONNECT,TEMPORARY
-- 
2.43.0