8b3b57f5c5
We started to run the pgbouncer configuration queries on the template1 database to ensure that all new databases will have the necessary configuration ready. However, postgres database is already created and we need to run the same configuration queries on it as well.
65 lines
1.9 KiB
Ruby
Executable File
65 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"
|
|
|
|
role_creation_queries = <<~ROLE_CREATION
|
|
/**
|
|
* Create system roles.
|
|
*/
|
|
CREATE ROLE ubi_replication WITH REPLICATION LOGIN;
|
|
CREATE ROLE ubi_monitoring WITH LOGIN IN ROLE pg_monitor;
|
|
CREATE ROLE pgbouncer LOGIN;
|
|
ROLE_CREATION
|
|
|
|
database_init_queries = <<~DATABASE_INIT
|
|
/**
|
|
* 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;
|
|
DATABASE_INIT
|
|
|
|
r "sudo -u postgres psql -v 'ON_ERROR_STOP=1'", stdin: role_creation_queries
|
|
r "sudo -u postgres psql -v 'ON_ERROR_STOP=1'", stdin: database_init_queries
|
|
r "sudo -u postgres psql -d template1 -v 'ON_ERROR_STOP=1'", stdin: database_init_queries
|