-
Notifications
You must be signed in to change notification settings - Fork 234
/
pgsql-user.yml
executable file
·159 lines (142 loc) · 8.13 KB
/
pgsql-user.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File : pgsql-user.yml
# Desc : create or modify user/role on pgsql cluster
# Ctime : 2021-02-27
# Mtime : 2022-12-07
# Path : pgsql-user.yml
# Deps : templates/pg-user.sql
# Author : Ruohang Feng (rh@vonng.com)
# License : AGPLv3
#==============================================================#
#--------------------------------------------------------------#
# Usage
#--------------------------------------------------------------#
#
# 1. Define new user/role in inventory (cmdb or config)
# `all.children.<pg_cluster>.vars.pg_users[i]`
#
# 2. Execute this playbook on target cluster with arg `username`
# `pgsql-user.yml -l <pg_cluster> -e username=<name>
#
# This playbook will:
# 1. create user sql definition on `/pg/tmp/pg-user-{{ user.name }}.sql`
# 2. execute database creation/update sql on cluster leader instance
# 3. update /etc/pgbouncer/userlist.txt & useropts.txt
# 4. and reload pgbouncer to take effect
#
#--------------------------------------------------------------#
# Utils
#--------------------------------------------------------------#
# Create pgsql user 'username' on pgsql cluster 'cls'
# bin/pgsql-user <cls> <username>
# bin/pgsql-user pg-meta dbuser_meta
#
#--------------------------------------------------------------#
# Example
#--------------------------------------------------------------#
# pg-meta:
# vars:
# pg_users: # define business users/roles on this cluster, array of user definition
# - name: dbuser_meta # REQUIRED, `name` is the only mandatory field of a user definition
# password: DBUser.Meta # optional, password, can be a scram-sha-256 hash string or plain text
# login: true # optional, can log in, true by default (new biz ROLE should be false)
# superuser: false # optional, is superuser? false by default
# createdb: false # optional, can create database? false by default
# createrole: false # optional, can create role? false by default
# inherit: true # optional, can this role use inherited privileges? true by default
# replication: false # optional, can this role do replication? false by default
# bypassrls: false # optional, can this role bypass row level security? false by default
# pgbouncer: true # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
# connlimit: -1 # optional, user connection limit, default -1 disable limit
# expire_in: 3650 # optional, now + n days when this role is expired (OVERWRITE expire_at)
# expire_at: '2030-12-31' # optional, YYYY-MM-DD 'timestamp' when this role is expired (OVERWRITTEN by expire_in)
# comment: pigsty admin user # optional, comment string for this user/role
# roles: [dbrole_admin] # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
# parameters: {} # optional, role level parameters with `ALTER ROLE SET`
# pool_mode: transaction # optional, pgbouncer pool mode at user level, transaction by default
# pool_connlimit: -1 # optional, max database connections at user level, default -1 disable limit
# search_path: public # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)
# - {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database}
# - {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
# - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
# - {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
# - {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
# - {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
#--------------------------------------------------------------#
- name: PGSQL USER
become: yes
hosts: all
gather_facts: no
tasks:
#----------------------------------------------------------#
# Validate username and user definition [preflight]
# ---------------------------------------------------------#
- name: preflight
tags: [ preflight, always ]
connection: local
block:
- name: validate username parameter
assert:
that:
- username is defined
- username != ''
- username != 'postgres'
fail_msg: variable 'username' should be specified (-e username=<name>)
- name: fetch user definition
set_fact: user_def={{ pg_users | json_query(user_def_query) }}
vars: { user_def_query: "[?name=='{{ username }}'] | [0]" }
- name: validate user definition
assert:
that:
- user_def is defined
- user_def != None
- user_def != ''
- user_def != {}
fail_msg: define user {{ username }} in pg_users first
- debug: { msg: "{{ user_def }}" }
#----------------------------------------------------------#
# Create Postgres User [postgres]
# ---------------------------------------------------------#
# create user according to user definition
- include_tasks: roles/pgsql/tasks/user.yml
tags: postgres
when: pg_role == 'primary'
vars: { user: "{{ user_def }}" }
# write biz user plain password to .pgpass file for citus cluster
- name: write plain biz user password to pgpass for citus cluster
tags: postgres
become_user: "{{ pg_dbsu|default('postgres') }}"
when: pg_mode|default('pgsql') == 'citus' and user_def.password is defined and user_def.password != '' and not user_def.password.startswith('md5') and not user_def.password.startswith('scram')
shell: /bin/bash /pg/bin/pg-pass-add "{{ user_def.name }}" "{{ user_def.password }}"
args: { executable: /bin/bash }
#----------------------------------------------------------#
# Refresh Pgbouncer User Configuration [pgbouncer]
# ---------------------------------------------------------#
- name: refresh pgbouncer users
tags: pgbouncer
when: user_def.pgbouncer is defined and user_def.pgbouncer|bool
block:
# regenerate user level parameters for pgbouncer if specified
- name: refresh pgbouncer useropts.txt
when: user_def.pool_mode is defined or user_def.pool_connlimit is defined
copy:
dest: /etc/pgbouncer/useropts.txt
owner: "{{ pg_dbsu|default('postgres') }}"
group: postgres
mode: 0600
content: |
{% for user in pg_default_roles|default([]) + pg_users|default([]) %}
{% if 'pool_mode' in user or 'pool_connlimit' in user %}
{{ "%-27s" | format(user.name) }} = {% if 'pool_mode' in user %}pool_mode={{ user.pool_mode }}{% endif %} {% if 'pool_connlimit' in user %}max_user_connections={{ user.pool_connlimit }}{% endif %}
{% endif %}
{% endfor %}
- name: add business users to pgbouncer
ignore_errors: true
become_user: "{{ pg_dbsu|default('postgres') }}"
shell: /bin/bash /pg/bin/pgb-user '{{ username }}' AUTO
args: { executable: /bin/bash }
- name: reload pgbouncer
systemd: name=pgbouncer state=reloaded enabled=yes daemon_reload=yes
...