Better/more documentation is needed

[HeavyHorst]

No description provided.

[dsielert]

Really would be helpful to see a fully functional example.. the breakup of the docs is confusing

[bendem]

We have this setup to generate pgbouncer configs from our patroni clusters. It's a bit complex because we use pgbouncer to redirect to the correct patroni cluster based on what we write in etcd. If that can help, I'm happy to answer questions.

Config remco:

# {{ ansible_managed }}

log_level   = "info"
log_format  = "text"
pid_file    = "/var/run/remco/remco.pid"
log_file    = "/var/log/remco/remco.log"

[[resource]]
    name = "pgbouncer"

    [[resource.template]]
        src = "/etc/remco/templates/pgbouncer.ini.tpl"
        dst = "/etc/pgbouncer/pgbouncer.ini"
        mode = "0640"
        reload_cmd = "systemctl reload pgbouncer"

    [[resource.template]]
        src = "/etc/remco/templates/auth_file.tpl"
        dst = "/etc/pgbouncer/auth_file.txt"
        mode = "0640"
        reload_cmd = "systemctl reload pgbouncer"

    [resource.backend]
        [resource.backend.etcd]
            nodes = ["https://{{ postgresql_etcd_cluster_members | join(':2378", "https://') }}:2378"]
            keys = ["/patroni/", "/postgres/", "/pgbouncer/"]
            # see https://github.com/HeavyHorst/remco/issues/92
            watch = false
            interval = 10
            username = "pgbouncer"
            password = "{{ etcd_pgbouncer_password }}"
            version = 3

Service systemd remco:

[Unit]
Description=Remco - Etcd templating
After=network.target

[Service]
Type=simple

User=remco

ExecStart=/usr/local/bin/remco -config /etc/remco/remco.toml
ExecReload=/usr/bin/kill -s HUP $MAINPID

RuntimeDirectory=remco

# specific to how we deliver certificates so they can be transmitted by remco to pgbouncer
Environment=TLS_KEYFILE={{ sslcerts_key_directory }}/{{ sslcerts_domains_id }}.pem
Environment=TLS_CERTFILE={{ sslcerts_cert_directory }}/{{ sslcerts_domains_id }}-bundle.pem

Restart=on-failure
RestartSec=5

PrivateTmp=yes
PrivateDevices=yes
ProtectHome=yes

{% if (ansible_facts['distribution_major_version'] | int) > (7) %}
ProtectSystem=strict
ProtectKernelTunables=yes
ProtectControlGroups=yes
ReadWritePaths=/var/log/remco
ReadWritePaths=/var/run/remco
ReadWritePaths=/etc/pgbouncer
{% endif %}

[Install]
WantedBy=multi-user.target

Polkit to authorize remco to reload pgbouncer:

//
// This file is managed by ansible
//
polkit.addRule(function (action, subject) {
  if (action.id == "org.freedesktop.systemd1.manage-units") {
    var unit = action.lookup("unit")
    var verb = action.lookup("verb")
    // polkit.log("Checking if " + subject.user + " is allowed to " + verb + " " + unit)
    if (unit == "pgbouncer.service" && verb == "reload" && subject.user == "remco") {
      return polkit.Result.YES;
    }
  }
});

Template pgbouncer.ini:

#
# This file is managed by ansible
#
[databases]
{% for database in lsdir("/pgbouncer") %}
{% with instance = getv(printf("/pgbouncer/%s/postgres-instance", database)) %}
{% with leader=getv(printf("/patroni/%s/leader", instance)) port=getv(printf("/postgres/%s/port", instance)) username=getv(printf("/postgres/%s/pgbouncer/username", instance)) %}
{{ database }} = host={{ leader}} port={{ port }} auth_user={{ username }} auth_dbname=maintenance-{{ instance }}
{% endwith %}
{% endwith %}
{% endfor %}

{% for instance in lsdir("/postgres") %}
{% with leader=getv(printf("/patroni/%s/leader", instance)) port=getv(printf("/postgres/%s/port", instance)) username=getv(printf("/postgres/%s/pgbouncer/username", instance)) password=getv(printf("/postgres/%s/pgbouncer/password", instance)) %}
maintenance-{{ instance }} = host={{ leader }} port={{ port }} auth_user={{ username }} auth_dbname=maintenance-{{ instance }}
{% endwith %}
{% endfor %}

[pgbouncer]
pool_mode = session
listen_port = 5432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/auth_file.txt
auth_query = select * from pgbouncer_user_lookup($1)
server_login_retry = 2

logfile = /var/log/pgbouncer/pgbouncer.log
admin_users =
stats_users =

client_tls_sslmode = require
client_tls_key_file = {{ getenv("TLS_KEYFILE") }}
client_tls_cert_file = {{ getenv("TLS_CERTFILE") }}
client_tls_ca_file = /etc/ssl/certs/ca-bundle.crt
client_tls_protocols = secure
client_tls_ciphers = secure
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/ssl/certs/ca-bundle.crt
server_tls_protocols = secure
server_tls_ciphers = secure

query_wait_timeout = 10

ignore_startup_parameters = search_path,extra_float_digits,ssl_renegotiation_limit

Template auth_file:

{% for instance in lsdir("/postgres") %}
{% with username=getv(printf("/postgres/%s/pgbouncer/username", instance)) password=getv(printf("/postgres/%s/pgbouncer/password", instance)) %}
"{{ username }}" "{{ password }}"
{% endwith %}
{% endfor %}