d3acd72b4d
Introduce pgbouncer to improve the cost of connection overhead, improving the idle and short-lived connections at the database server. The pgbouncer service runs as a systemd unit listening on port 6432, relaying connections to the postgres server over a Unix domain socket. The configuration is done by the `configure` postgres rhizome, which sets up the `pgbouncer.ini`. `initialize-empty-database` creates the pgbouncer role and related items to perform authentication passthrough, discussed below. User authentication is handled via a passthrough mechanism, by creating and configuring a dedicated user called `pgbouncer` as the `auth_user`. This user uses a `SECURITY DEFINER` function to validate connections for other users. Authentication for all users is handled in according to the HBA configuration specified in `pg_hba.conf`. Ref: [1], [2] In the current state, we keep most of the default settings in place, esp. for connection pooling behaviour. As we perform more rigorous performance testing, these settings are subject to be tweaked in future patches. [1]: https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/ [2]: https://www.crunchydata.com/blog/pgbouncer-scram-authentication-postgresql
131 lines
4.4 KiB
Ruby
Executable File
131 lines
4.4 KiB
Ruby
Executable File
#!/bin/env ruby
|
|
# frozen_string_literal: true
|
|
|
|
require "json"
|
|
require_relative "../../common/lib/util"
|
|
|
|
if ARGV.count != 1
|
|
fail "Wrong number of arguments. Expected 1, Given #{ARGV.count}"
|
|
end
|
|
|
|
v = ARGV[0]
|
|
configure_hash = JSON.parse($stdin.read)
|
|
|
|
# Update /etc/hosts
|
|
hosts = <<-HOSTS
|
|
127.0.0.1 localhost
|
|
::1 ip6-localhost ip6-loopback
|
|
fe00::0 ip6-localnet
|
|
ff00::0 ip6-mcastprefix
|
|
ff02::1 ip6-allnodes
|
|
ff02::2 ip6-allrouters
|
|
ff02::3 ip6-allhosts
|
|
#{configure_hash["hosts"]}
|
|
HOSTS
|
|
safe_write_to_file("/etc/hosts", hosts)
|
|
|
|
# Update postgresql.conf
|
|
configs = configure_hash["configs"].map { |k, v| "#{k} = #{v}" }.join("\n")
|
|
safe_write_to_file("/etc/postgresql/#{v}/main/conf.d/001-service.conf", configs)
|
|
|
|
# Update pg_hba.conf
|
|
private_subnets = configure_hash["private_subnets"].flat_map {
|
|
[
|
|
"host all all #{_1["net4"]} scram-sha-256",
|
|
"host all all #{_1["net6"]} scram-sha-256"
|
|
]
|
|
}.join("\n")
|
|
|
|
pg_hba_entries = <<-PG_HBA
|
|
# PostgreSQL Client Authentication Configuration File
|
|
# ===================================================
|
|
#
|
|
# Refer to the "Client Authentication" section in the PostgreSQL
|
|
# documentation for a complete description of this file.
|
|
|
|
# TYPE DATABASE USER ADDRESS METHOD
|
|
# Database administrative login by Unix domain socket
|
|
local all postgres peer map=system2postgres
|
|
local all pgbouncer peer map=system2pgbouncer
|
|
|
|
# "local" is for Unix domain socket connections only
|
|
local all all peer
|
|
# IPv4 local connections:
|
|
host all all 127.0.0.1/32 scram-sha-256
|
|
# IPv6 local connections:
|
|
host all all ::1/128 scram-sha-256
|
|
|
|
# Allow replication connections from localhost, by a user with the
|
|
# replication privilege.
|
|
local replication all peer
|
|
host replication all 127.0.0.1/32 scram-sha-256
|
|
host replication all ::1/128 scram-sha-256
|
|
|
|
# Allow connections from localhost with ubi_monitoring OS user as
|
|
# ubi_monitoring database user. This will be used by postgres_exporter
|
|
# to scrape metrics and expose them to prometheus.
|
|
local all ubi_monitoring peer
|
|
|
|
# Allow connections from private subnet with SCRAM authentication
|
|
#{private_subnets}
|
|
|
|
# Allow replication connection using special replication user for
|
|
# HA standbys
|
|
hostssl replication ubi_replication all cert map=standby2replication
|
|
|
|
# Allow connections from public internet with SCRAM authentication
|
|
host all all all scram-sha-256
|
|
PG_HBA
|
|
safe_write_to_file("/etc/postgresql/#{v}/main/pg_hba.conf", pg_hba_entries)
|
|
|
|
identity = configure_hash["identity"]
|
|
pg_ident_entries = <<-PG_IDENT
|
|
# PostgreSQL User Name Maps
|
|
# =========================
|
|
#
|
|
# Refer to the PostgreSQL documentation, chapter "Client
|
|
# Authentication" for a complete description.
|
|
# MAPNAME SYSTEM-USERNAME PG-USERNAME
|
|
system2postgres postgres postgres
|
|
system2pgbouncer postgres pgbouncer
|
|
system2postgres ubi postgres
|
|
standby2replication #{identity} ubi_replication
|
|
PG_IDENT
|
|
safe_write_to_file("/etc/postgresql/#{v}/main/pg_ident.conf", pg_ident_entries)
|
|
|
|
# Reload the postmaster to apply changes
|
|
r "pg_ctlcluster #{v} main reload || pg_ctlcluster #{v} main restart"
|
|
|
|
# PgBouncer config
|
|
pgbouncer_config = <<-PGBOUNCER_CONFIG
|
|
# PgBouncer configuration file
|
|
# ============================
|
|
[databases]
|
|
; any db over Unix socket
|
|
* =
|
|
|
|
[pgbouncer]
|
|
listen_port = 6432
|
|
listen_addr = 0.0.0.0
|
|
|
|
unix_socket_dir = /var/run/postgresql
|
|
|
|
auth_type = hba
|
|
auth_hba_file = /etc/postgresql/#{v}/main/pg_hba.conf
|
|
auth_ident_file = /etc/postgresql/#{v}/main/pg_ident.conf
|
|
auth_user = pgbouncer
|
|
auth_query = SELECT p_user, p_password FROM pgbouncer.get_auth($1)
|
|
|
|
client_tls_sslmode = require
|
|
client_tls_protocols = tlsv1.3
|
|
client_tls_ca_file = /etc/ssl/certs/ca.crt
|
|
client_tls_cert_file = /etc/ssl/certs/server.crt
|
|
client_tls_key_file = /etc/ssl/certs/server.key
|
|
|
|
user = postgres
|
|
|
|
max_client_conn = 5000
|
|
max_db_connections = #{configure_hash["configs"]["max_connections"]}
|
|
PGBOUNCER_CONFIG
|
|
safe_write_to_file("/etc/pgbouncer/pgbouncer.ini", pgbouncer_config)
|