From 0effb4ca73a0b43d1b0b9f77f371e9106dce214c Mon Sep 17 00:00:00 2001
From: Someone <someone@somenet.org>
Date: Thu, 8 Jan 2026 21:27:49 +0100
Subject: [PATCH] [roles/util/postgres-db-usr] create postgres db and
 owner-user

---
 roles/util/postgres-db-usr/tasks/main.yml | 60 +++++++++++++++++++++++
 1 file changed, 60 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..61392f0
--- /dev/null
+++ b/roles/util/postgres-db-usr/tasks/main.yml
@@ -0,0 +1,60 @@
+#####################################
+### someone's ansible provisioner ###
+#####################################
+# Part of: https://git.somenet.org/root/pub/somesible.git
+# 2017-2026 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}}"
+    privs: ALL
+    state: absent
+    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: GRANT CONNECT,TEMPORARY ON DATABASE "{{pg_name}}" TO "grp_spectator"
+  become_user: postgres
+  postgresql_privs:
+    db: "{{pg_name}}"
+    type: database
+    obj: "{{pg_name}}"
+    privs: CONNECT,TEMPORARY
+    role: "grp_spectator"
-- 
2.47.3