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
72 lines
1.9 KiB
Ruby
Executable File
72 lines
1.9 KiB
Ruby
Executable File
#!/bin/env ruby
|
|
# frozen_string_literal: true
|
|
|
|
require_relative "../../common/lib/util"
|
|
|
|
if ARGV.count != 1
|
|
fail "Wrong number of arguments. Expected 1, Given #{ARGV.count}"
|
|
end
|
|
|
|
v = ARGV[0]
|
|
|
|
r "chown postgres /dat"
|
|
|
|
# Below commands are required for idempotency
|
|
r "rm -rf /dat/#{v}"
|
|
r "rm -rf /etc/postgresql/#{v}"
|
|
|
|
r "pg_createcluster #{v} main --start --locale=C.UTF8"
|
|
|
|
r "sudo -u postgres psql -c 'CREATE ROLE ubi_replication WITH REPLICATION LOGIN'"
|
|
r "sudo -u postgres psql -c 'CREATE ROLE ubi_monitoring WITH LOGIN IN ROLE pg_monitor'"
|
|
|
|
pgbouncer_setup_query = <<~PGBOUNCER_SETUP
|
|
BEGIN;
|
|
|
|
/**
|
|
* Create pgbouncer role if it does not exist.
|
|
*/
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'pgbouncer') THEN
|
|
CREATE ROLE pgbouncer LOGIN;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
/**
|
|
* Lock down the privileges of the pgbouncer role.
|
|
*/
|
|
REVOKE ALL PRIVILEGES ON SCHEMA public FROM pgbouncer;
|
|
|
|
/**
|
|
* Create the pgbouncer schema if it does not exist. All of the
|
|
* administrative functions for pgbouncer will live in its own schema.
|
|
*/
|
|
CREATE SCHEMA IF NOT EXISTS pgbouncer;
|
|
|
|
/**
|
|
* Lock down the privileges of the pgbouncer schema.
|
|
*/
|
|
REVOKE ALL PRIVILEGES ON SCHEMA pgbouncer FROM pgbouncer;
|
|
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;
|
|
|
|
/**
|
|
* The "get_auth" function is used by pgbouncer to authenticate users.
|
|
* See: http://www.pgbouncer.org/config.html#auth_query
|
|
*/
|
|
CREATE OR REPLACE FUNCTION pgbouncer.get_auth (
|
|
INOUT p_user name,
|
|
OUT p_password text
|
|
) RETURNS record
|
|
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
|
|
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;
|
|
|
|
REVOKE ALL ON FUNCTION pgbouncer.get_auth(name) FROM PUBLIC, pgbouncer;
|
|
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(name) TO pgbouncer;
|
|
|
|
COMMIT;
|
|
PGBOUNCER_SETUP
|
|
|
|
r "sudo -u postgres psql", stdin: pgbouncer_setup_query
|