From 9dda81a242288073df928b65f98706ebd5c01832 Mon Sep 17 00:00:00 2001 From: Someone 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 +# +--- +- 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