8000 feat: set up PgBouncer · octavetn/postgres@91ebe1e · GitHub
[go: up one dir, main page]
Skip to content

Commit 91ebe1e

Browse files
dragarciadragarcia
committed
feat: set up PgBouncer
1 parent f421689 commit 91ebe1e

File tree

5 files changed

+419
-0
lines changed

5 files changed

+419
-0
lines changed

‎ansible/files/pgbouncer.ini

Lines changed: 361 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,361 @@
1+
;;;
2+
;;; PgBouncer configuration file
3+
;;;
4+
5+
;; database name = connect string
6+
;;
7+
;; connect string params:
8+
;; dbname= host= port= user= password= auth_user=
9+
;; client_encoding= datestyle= timezone=
10+
;; pool_size= reserve_pool= max_db_connections=
11+
;; pool_mode= connect_query= application_name=
12+
[databases]
13+
* = host=localhost auth_user=pgbouncer
14+
15+
;; foodb over Unix socket
16+
;foodb =
17+
18+
;; redirect bardb to bazdb on localhost
19+
;bardb = host=localhost dbname=bazdb
20+
21+
;; access to dest database will go with single user
22+
;forcedb = host=localhost port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
23+
24+
;; use custom pool sizes
25+
;nondefaultdb = pool_size=50 reserve_pool=10
26+
27+
;; use auth_user with auth_query if user not present in auth_file
28+
;; auth_user must exist in auth_file
29+
; foodb = auth_user=bar
30+
31+
;; fallback connect string
32+
;* = host=testserver
33+
34+
;; User-specific configuration
35+
[users]
36+
37+
;user1 = pool_mode=transaction max_user_connections=10
38+
39+
;; Configuration section
40+
[pgbouncer]
41+
42+
;;;
43+
;;; Administrative settings
44+
;;;
45+
46+
logfile = /var/log/postgresql/pgbouncer.log
47+
pidfile = /var/run/postgresql/pgbouncer.pid
48+
49+
;;;
50+
;;; Where to wait for clients
51+
;;;
52+
53+
;; IP address or * which means all IPs
54+
listen_addr = *
55+
listen_port = 6543
56+
57+
;; Unix socket is also used for -R.
58+
;; On Debian it should be /var/run/postgresql
59+
;unix_socket_dir = /tmp
60+
;unix_socket_mode = 0777
61+
;unix_socket_group =
62+
unix_socket_dir = /var/run/postgresql
63+
64+
;;;
65+
;;; TLS settings for accepting clients
66+
;;;
67+
68+
;; disable, allow, require, verify-ca, verify-full
69+
;client_tls_sslmode = disable
70+
71+
;; Path to file that contains trusted CA certs
72+
;client_tls_ca_file = <system default>
73+
74+
;; Private key and cert to present to clients.
75+
;; Required for accepting TLS connections from clients.
76+
;client_tls_key_file =
77+
;client_tls_cert_file =
78+
79+
;; fast, normal, secure, legacy, <ciphersuite string>
80+
;client_tls_ciphers = fast
81+
82+
;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2, tlsv1.3
83+
;client_tls_protocols = secure
84+
85+
;; none, auto, legacy
86+
;client_tls_dheparams = auto
87+
88+
;; none, auto, <curve name>
89+
;client_tls_ecdhcurve = auto
90+
91+
;;;
92+
;;; TLS settings for connecting to backend databases
93+
;;;
94+
95+
;; disable, allow, require, verify-ca, verify-full
96+
;server_tls_sslmode = disable
97+
98+
;; Path to that contains trusted CA certs
99+
;server_tls_ca_file = <system default>
100+
101+
;; Private key and cert to present to backend.
102+
;; Needed only if backend server require client cert.
103+
;server_tls_key_file =
104+
;server_tls_cert_file =
105+
106+
;; all, secure, tlsv1.0, tlsv1.1, tlsv1.2, tlsv1.3
107+
;server_tls_protocols = secure
108+
109+
;; fast, normal, secure, legacy, <ciphersuite string>
110+
;server_tls_ciphers = fast
111+
112+
;;;
113+
;;; Authentication settings
114+
;;;
115+
116+
;; any, trust, plain, md5, cert, hba, pam
117+
auth_type = md5
118+
auth_file = /etc/pgbouncer/userlist.txt
119+
120+
;; Path to HBA-style auth config
121+
;auth_hba_file =
122+
123+
;; Query to use to fetch password from database. Result
124+
;; must have 2 columns - username and password hash.
125+
auth_query = SELECT * FROM pgbouncer.get_auth($1)
126+
127+
;;;
128+
;;; Users allowed into database 'pgbouncer'
129+
;;;
130+
131+
;; comma-separated list of users who are allowed to change settings
132+
admin_users = pgbouncer
133+
134+
;; comma-separated list of users who are just allowed to use SHOW command
135+
stats_users = pgbouncer
136+
137+
;;;
138+
;;; Pooler personality questions
139+
;;;
140+
141+
;; When server connection is released back to pool:
142+
;; session - after client disconnects (default)
143+
;; transaction - after transaction finishes
144+
;; statement - after statement finishes
145+
pool_mode = transaction
146+
147+
;; Query for cleaning connection immediately after releasing from
148+
;; client. No need to put ROLLBACK here, pgbouncer does not reuse
149+
;; connections where transaction is left open.
150+
;server_reset_query = DISCARD ALL
151+
152+
;; Whether server_reset_query should run in all pooling modes. If it
153+
;; is off, server_reset_query is used only for session-pooling.
154+
;server_reset_query_always = 0
155+
156+
;; Comma-separated list of parameters to ignore when given in startup
157+
;; packet. Newer JDBC versions require the extra_float_digits here.
158+
ignore_startup_parameters = extra_float_digits
159+
160+
;; When taking idle server into use, this query is run first.
161+
;server_check_query = select 1
162+
163+
;; If server was used more recently that this many seconds ago,
164+
; skip the check query. Value 0 may or may not run in immediately.
165+
;server_check_delay = 30
166+
167+
;; Close servers in session pooling mode after a RECONNECT, RELOAD,
168+
;; etc. when they are idle instead of at the end of the session.
169+
;server_fast_close = 0
170+
171+
;; Use <appname - host> as application_name on server.
172+
;application_name_add_host = 0
173+
174+
;; Period for updating aggregated stats.
175+
;stats_period = 60
176+
177+
;;;
178+
;;; Connection limits
179+
;;;
180+
181+
;; Total number of clients that can connect
182+
;max_client_conn = 100
183+
184+
;; Default pool size. 20 is good number when transaction pooling
185+
;; is in use, in session pooling it needs to be the number of
186+
;; max clients you want to handle at any moment
187+
default_pool_size = 15
188+
189+
;; Minimum number of server connections to keep in pool.
190+
;min_pool_size = 0
191+
192+
; how many additional connection to allow in case of trouble
193+
;reserve_pool_size = 0
194+
195+
;; If a clients needs to wait more than this many seconds, use reserve
196+
;; pool.
197+
;reserve_pool_timeout = 5
198+
199+
;; Maximum number of server connections for a database
200+
;max_db_connections = 0
201+
202+
;; Maximum number of server connections for a user
203+
;max_user_connections = 0
204+
205+
;; If off, then server connections are reused in LIFO manner
206+
;server_round_robin = 0
207+
208+
;;;
209+
;;; Logging
210+
;;;
211+
212+
;; Syslog settings
213+
;syslog = 0
214+
;syslog_facility = daemon
215+
;syslog_ident = pgbouncer
216+
217+
;; log if client connects or server connection is made
218+
;log_connections = 1
219+
220+
;; log if and why connection was closed
221+
;log_disconnections = 1
222+
223+
;; log error messages pooler sends to clients
224+
;log_pooler_errors = 1
225+
226+
;; write aggregated stats into log
227+
;log_stats = 1
228+
229+
;; Logging verbosity. Same as -v switch on command line.
230+
;verbose = 0
231+
232+
;;;
233+
;;; Timeouts
234+
;;;
235+
236+
;; Close server connection if its been connected longer.
237+
;server_lifetime = 3600
238+
239+
;; Close server connection if its not been used in this time. Allows
240+
;; to clean unnecessary connections from pool after peak.
241+
;server_idle_timeout = 600
242+
243+
;; Cancel connection attempt if server does not answer takes longer.
244+
;server_connect_timeout = 15
245+
246+
;; If server login failed (server_connect_timeout or auth failure)
247+
;; then wait this many second.
248+
;server_login_retry = 15
249+
250+
;; Dangerous. Server connection is closed if query does not return in
251+
;; this time. Should be used to survive network problems, _not_ as
252+
;; statement_timeout. (default: 0)
253+
;query_timeout = 0
254+
255+
;; Dangerous. Client connection is closed if the query is not
256+
;; assigned to a server in this time. Should be used to limit the
257+
;; number of queued queries in case of a database or network
258+
;; failure. (default: 120)
259+
;query_wait_timeout = 120
260+
261+
;; Dangerous. Client connection is closed if no activity in this
262+
;; time. Should be used to survive network problems. (default: 0)
263+
;client_idle_timeout = 0
264+
265+
;; Disconnect clients who have not managed to log in after connecting
266+
;; in this many seconds.
267+
;client_login_timeout = 60
268+
269+
;; Clean automatically created database entries (via "*") if they stay
270+
;; unused in this many seconds.
271+
; autodb_idle_timeout = 3600
272+
273+
;; Close connections which are in "IDLE in transaction" state longer
274+
;; than this many seconds.
275+
;idle_transaction_timeout = 0
276+
277+
;; How long SUSPEND/-R waits for buffer flush before closing
278+
;; connection.
279+
;suspend_timeout = 10
280+
281+
;;;
282+
;;; Low-level tuning options
283+
;;;
284+
285+
;; buffer for streaming packets
286+
;pkt_buf = 4096
287+
288+
;; man 2 listen
289+
;listen_backlog = 128
290+
291+
;; Max number pkt_buf to process in one event loop.
292+
;sbuf_loopcnt = 5
293+
294+
;; Maximum PostgreSQL protocol packet size.
295+
;max_packet_size = 2147483647
296+
297+
;; Set SO_REUSEPORT socket option
298+
;so_reuseport = 0
299+
300+
;; networking options, for info: man 7 tcp
301+
302+
;; Linux: Notify program about new connection only if there is also
303+
;; data received. (Seconds to wait.) On Linux the default is 45, on
304+
;; other OS'es 0.
305+
;tcp_defer_accept = 0
306+
307+
;; In-kernel buffer size (Linux default: 4096)
308+
;tcp_socket_buffer = 0
309+
310+
;; whether tcp keepalive should be turned on (0/1)
311+
;tcp_keepalive = 1
312+
313+
;; The following options are Linux-specific. They also require
314+
;; tcp_keepalive=1.
315+
316+
;; Count of keepalive packets
317+
;tcp_keepcnt = 0
318+
319+
;; How long the connection can be idle before sending keepalive
320+
;; packets
321+
;tcp_keepidle = 0
322+
323+
;; The time between individual keepalive probes
324+
;tcp_keepintvl = 0
325+
326+
;; How long may transmitted data remain unacknowledged before TCP
327+
;; connection is closed (in milliseconds)
328+
;tcp_user_timeout = 0
329+
330+
;; DNS lookup caching time
331+
;dns_max_ttl = 15
332+
333+
;; DNS zone SOA lookup period
334+
;dns_zone_check_period = 0
335+
336+
;; DNS negative result caching time
337+
;dns_nxdomain_ttl = 15
338+
339+
;; Custom resolv.conf file, to set custom DNS servers or other options
340+
;; (default: empty = use OS settings)
341+
;resolv_conf = /etc/pgbouncer/resolv.conf
342+
343+
;;;
344+
;;; Random stuff
345+
;;;
346+
347+
;; Hackish security feature. Helps against SQL injection: when PQexec
348+
;; is disabled, multi-statement cannot be made.
349+
;disable_pqexec = 0
350+
351+
;; Config file to use for next RELOAD/SIGHUP
352+
;; By default contains config file from command line.
353+
;conffile
354+
355+
;; Windows service name to register as. job_name is alias for
356+
;; service_name, used by some Skytools scripts.
357+
;service_name = pgbouncer
358+
;job_name = pgbouncer
359+
360+
;; Read additional config from other file
361+
;%include /etc/pgbouncer/pgbouncer-other.ini

‎ansible/files/pgbouncer_auth_schema.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
CREATE USER pgbouncer;
2+
3+
CREATE SCHEMA pgbouncer AUTHORIZATION pgbouncer;
4+
5+
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename TEXT)
6+
RETURNS TABLE(username TEXT, password TEXT) AS
7+
$$
8+
BEGIN
9+
RAISE WARNING 'PgBouncer auth request: %', p_usename;
10+
11+
RETURN QUERY
12+
SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow
13+
WHERE usename = p_usename;
14+
END;
15+
$$ LANGUAGE plpgsql SECURITY DEFINER;
16+
17+
REVOKE ALL ON FUNCTION pgbouncer.get_auth(p_usename TEXT) FROM PUBLIC;
18+
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(p_usename TEXT) TO pgbouncer;

0 commit comments

Comments
 (0)
0