pgBouncer auth_query ile Çoklu DB Kullanıcısı

pgBouncer connection pooler olarak harika; ancak default yapılandırması bir düşmanlığı kabul ediyor: kullanıcı listenizi manuel olarak userlist.txt’ye yazmak zorundasınız. Her yeni DB kullanıcısı eklediğinizde dosyayı güncellemek, hash’i hesaplamak, pgBouncer’ı reload etmek. Operasyonel bir baş ağrısı.

auth_query bunu çözüyor: pgBouncer doğrulama için PostgreSQL’in kendisine sorgu atıyor.

auth_user kurulumu

PostgreSQL tarafında düşük yetkili bir kullanıcı ve pg_shadow’u okuyan bir fonksiyon oluşturuyoruz:

CREATE ROLE pgbouncer LOGIN PASSWORD 's3cr€t_p@ssw0rd';

CREATE SCHEMA pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text,
                                                 out uname text,
                                                 out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

SECURITY DEFINER önemli — fonksiyon pg_shadow’a erişim için tanımlayan kullanıcının yetkilerini kullanıyor. Aksi takdirde pgbouncer rolü pg_shadow’u okuyamaz.

auth_query hedef veritabanının içinde çalışır; bu yüzden fonksiyonu pgBouncer’ın hizmet vereceği her veritabanına kurmanız gerekir.

pgBouncer config

pgbouncer.ini:

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432

auth_type = scram-sha-256
auth_user = pgbouncer
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Artık userlist.txt yok. PostgreSQL’de yeni bir rol oluştursanız, pgBouncer restart’ı gerektirmeden doğrulama çalışıyor.

md5 mi, scram-sha-256 mı?

PostgreSQL 14+ default olarak scram-sha-256 kullanıyor. pgBouncer bunu 1.11’den beri destekliyor; auth_query ile SCRAM pass-through ise 1.14 ile geldi. Eski cluster’larda md5’e düşmek zorunda kalıyorsanız:

auth_type = md5

Ama yeni kurulum yapıyorsanız scram-sha-256 kullanın — md5 artık zayıf.

Hangi pool mode seçilmeli?

Üç mod var, çoğu Laravel/Django uygulaması için tercih:

• session — bağlantı baştan sona aynı. Prepared statement’lar çalışır, ama pool faydası zayıf.
• transaction — her transaction sonunda bağlantı serbest. En yaygın tercih. Protokol düzeyi prepared statement’lar 1.21+ gerektiriyor; 1.24’ten beri varsayılan olarak açık (max_prepared_statements = 200).
• statement — her query sonunda. Çoğu ORM ile sorunlu.

Pratik: transaction’la başlayın, sorun yaşarsanız session’a geçin.

Yaygın hata: read-only role’ün password’ü yok

auth_query pg_shadow.passwd’i okuyor. Eğer kullanıcı SSO ile geliyorsa veya peer authentication kullanıyorsa, passwd alanı NULL’dur — pgBouncer login’i reddeder. Bu durumda pg_hba.conf üzerinden trust veya peer kurmak gerekir, ama production’da bunu istemezsiniz.

İzleme

pgBouncer kendi admin DB’sini sağlıyor:

psql -p 6432 pgbouncer -U pgbouncer
> SHOW POOLS;
> SHOW STATS;
> SHOW CLIENTS;

cl_waiting > 0 görüyorsanız default_pool_size’ı arttırmak gerekiyor olabilir. sv_idle çok yüksekse tam tersi — pool overprovision’da.