PostgreSQL'i hazırlamak: izleme rolü, sorgu istatistikleri ve SSH tüneli

Bu sayfa, Spitfire'ın PostgreSQL'inizi okuyabilmesi için veritabanı ve ağ tarafında yapılacakları anlatır: veriye dokunamayan bir izleme rolü, sorgu istatistikleri ve gerekiyorsa port açmadan, tek hedefe kilitli bir SSH tüneli. Spitfire veritabanınızda hiçbir şeyi değiştirmez; erişimi istediğiniz an kesebilirsiniz. Bağlantı türleri ve kurulum betiği: Veritabanları. Tünelin Spitfire tarafı: SSH tüneli.

#Ne gerekecek

Güvenlik ekibinizin ilk soruları genelde şunlardır:

• Ne okuyabilir: yalnız istatistik görünümleri (pg_stat_*, pg_settings). pg_monitor hiçbir tablonun satırlarını göstermez; sorgu metinlerinde sabitler $1, $2 olarak gelir.
• Ne yazabilir: hiçbir şey. Rol hiçbir tabloya yazamaz, şema değiştiremez.
• Ağdan ne açılır: tünelde kabuğu olmayan bir kullanıcı, yalnız <db-adresi>:5432'ye yönlendirme yapabilir. Sunucunun öteki kullanıcıları ve genel sshd ayarı değişmez.

#Sürümünüzü bulun

psql -h <db-adresi> -U postgres -c "select version();"

#Sürüme göre adımlar

#PostgreSQL 10 ve sonrası

Rolü bir süper kullanıcıyla açın:

CREATE ROLE spitfire_monitor LOGIN CONNECTION LIMIT 3 PASSWORD '<parola>' IN ROLE pg_monitor;

• IN ROLE pg_monitor istatistik görünümlerini açar, veriyi açmaz.
• CONNECTION LIMIT 3 Spitfire'ın havuzu yanlış ayarlansa bile rolün en çok üç bağlantı açabilmesini sağlar; uygulamanızın bağlantıları tükenmez. Spitfire'da En çok açık bağlantı bu sınırdan küçük olmalıdır (Spitfire'da).
• Doğrudan bağlanacaksanız pg_hba.conf'a Spitfire'ın çıkış adresi için bir satır ekleyin (hostssl <veritabanı> spitfire_monitor <spitfire-ip>/32 scram-sha-256) ve select pg_reload_conf(); çalıştırın.

#Kubernetes operatörüyle

Operatörün yönettiği kümede elle CREATE ROLE yazmayın: rol operatörün bilgisi dışında kalır, yedekten kurulumda ya da küme yeniden oluşturulunca kaybolabilir. Rolü kümenin tanımına ekleyin.

CloudNativePG: önce parolayı bir Secret'a koyun (kubernetes.io/basic-auth, username spitfire_monitor), sonra:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: <küme>
spec:
  managed:
    roles:
      - name: spitfire_monitor
        ensure: present
        login: true
        connectionLimit: 3
        inRoles: [pg_monitor]
        passwordSecret:
          name: spitfire-monitor

Durum: kubectl get cluster <küme> -o jsonpath='{.status.managedRolesStatus}'.

Zalando postgres-operator: users altında rolü açın; operatör parolayı spitfire-monitor.<küme>.credentials.postgresql.acid.zalan.do Secret'ına yazar. pg_monitor üyeliğini operatör vermez, bir kez elle verin (rol operatörün olduğu için kalıcıdır):

apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
  name: <küme>
spec:
  users:
    spitfire_monitor: [login]

GRANT pg_monitor TO spitfire_monitor;
ALTER ROLE spitfire_monitor CONNECTION LIMIT 3;

Crunchy PGO: spec.users altında; parola <küme>-pguser-spitfire-monitor Secret'ındadır. options alanı CREATE ROLE seçeneklerini alır, üyelik yine bir kez GRANT ile verilir:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: <küme>
spec:
  users:
    - name: spitfire-monitor
      options: "LOGIN CONNECTION LIMIT 3"

GRANT pg_monitor TO "spitfire-monitor";

Kubernetes'te Spitfire'daki Sunucu, yazma servisi <küme>-rw'nin adresidir (tünelde ClusterIP'si, SSH tüneli). İzleme sorgularını replikaya göndermek için -ro servisi kullanılabilir, ama replikasyon gecikmesi ve yazma istatistikleri yalnız primary'de görünür; ana bağlantıyı primary'ye (-rw) kurun.

#Bulut veritabanları

RDS / Aurora'da rds_superuser gerekmez; ana kullanıcıyla yukarıdaki CREATE ROLE yeterlidir. Cloud SQL ve Azure Database for PostgreSQL'de de pg_monitor hazırdır, komut aynıdır. pg_stat_statements kütüphanesi RDS / Aurora ve Cloud SQL'de varsayılan olarak yüklüdür; Azure'da Server parameters → shared_preload_libraries ile açılır. Üçünde de sonra izlenen veritabanında CREATE EXTENSION yeterlidir.

#PostgreSQL 9.6 ve öncesi

pg_monitor yoktur. Rolü açıp istatistik görünümlerini tek tek açın; öteki oturumların sorgu metni süper kullanıcı dışında gizlidir, bunu bir SECURITY DEFINER sarmalayıcıyla açın:

CREATE ROLE spitfire_monitor LOGIN CONNECTION LIMIT 3 PASSWORD '<parola>';
GRANT SELECT ON pg_stat_database, pg_stat_user_tables, pg_stat_replication TO spitfire_monitor;

CREATE SCHEMA IF NOT EXISTS spitfire AUTHORIZATION postgres;
CREATE FUNCTION spitfire.pg_stat_activity() RETURNS SETOF pg_stat_activity
  LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog
  AS 'SELECT * FROM pg_catalog.pg_stat_activity';
REVOKE ALL ON FUNCTION spitfire.pg_stat_activity() FROM PUBLIC;
GRANT USAGE ON SCHEMA spitfire TO spitfire_monitor;
GRANT EXECUTE ON FUNCTION spitfire.pg_stat_activity() TO spitfire_monitor;

Spitfire'ın Test et'i bu sürümlerde pg_monitor adımını bilgi olarak geçer; desteklenen en eski sürümleri yükseltmenizi öneririz (9.6 2021'de destek dışı kaldı).

#Sorgu istatistikleri (pg_stat_statements)

Yavaş ve sık sorgular ekranı bu eklentiyi okur. Kapalı mı:

psql -h <db-adresi> -U postgres -c "show shared_preload_libraries;"

Listede pg_stat_statements yoksa açmak yeniden başlatma ister:

1. ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; (liste doluysa mevcut değerin sonuna ,pg_stat_statements ekleyerek yazın).
2. PostgreSQL'i yeniden başlatın (sudo systemctl restart postgresql).
3. İzlenen veritabanında: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CloudNativePG'de elle yapmayın: kümenin spec.postgresql.parameters altına bir pg_stat_statements.* parametresi (ör. pg_stat_statements.max: "10000") yazmak yeterlidir. Operatör kütüphaneyi yükler, eklentiyi her veritabanında kurar ve önce replikayı, sonra devirle primary'yi yeniden başlatır.

Eklenti sorgu metinlerindeki sabitleri $1, $2 ile değiştirir; metinde kişisel veri kalmaz.

#Ön kapı: doğrudan mı, SSH tüneliyle mi

#Doğrudan

Spitfire'ın çıkış adresinden 5432'ye izin verebiliyorsanız (güvenlik duvarı + pg_hba.conf) tünel gerekmez. Spitfire'da SSH tüneli kapalı kalır, Sunucu veritabanının adresidir. Bağlantıyı sslmode=require ile kurun.

#SSH tüneli

Veritabanı özel ağdaysa ve önünde bir sıçrama sunucusu varsa port açmadan tünel kurulur. Tüneli Spitfire'da yalnız kurulum yöneticisi ekler (SSH tüneli). Komutları sıçrama sunucusunda, root olarak çalıştırın (istem satırı root@<sunucu>:~#).

1. Hedefe ulaşılıyor mu: sıçrama sunucusundan veritabanının portu açık olmalı.

   bashKopyala

   timeout 3 bash -c '</dev/tcp/<db-adresi>/5432' && echo ACIK

   Kubernetes'te hedef <küme>-rw servisinin ClusterIP'sidir (kubectl get svc <küme>-rw -o jsonpath='{.spec.clusterIP}'). Düğümden pod'a gelen trafiğe NetworkPolicy denetleyicisinin izin verdiğini bu deneme gösterir.

2. Kabuğu olmayan kullanıcı:

   bashKopyala

   useradd -r -m -s /usr/sbin/nologin spitfire-tunnel
   install -d -m 700 -o spitfire-tunnel -g spitfire-tunnel ~spitfire-tunnel/.ssh

3. Anahtar: Spitfire'da bağlantı penceresinin SSH tüneli bölümünde Anahtar çifti oluştur'a basın. Özel anahtar Spitfire'da şifreli kalır, hiç gösterilmez; ekranda açık anahtar ve formdaki Sunucu / Port'tan doldurulmuş hazır satır çıkar. Kendi anahtarınızı kullanacaksanız Kendi anahtarım'ı seçip özel anahtarı yapıştırın; satırın biçimi aynıdır.

4. Tek hedefe kilitli satır: ~spitfire-tunnel/.ssh/authorized_keys dosyasına, tek satır:

   textKopyala

   restrict,port-forwarding,permitopen="<db-adresi>:5432" ssh-ed25519 AAAA… spitfire-tunnel

   restrict her şeyi kapatır (kabuk, PTY, agent, X11), port-forwarding yalnız yönlendirmeyi açar, permitopen hedefi o adres ve porta sınırlar. Ardından:

   bashKopyala

   chown spitfire-tunnel: ~spitfire-tunnel/.ssh/authorized_keys
   chmod 600 ~spitfire-tunnel/.ssh/authorized_keys

5. Host anahtarlarının parmak izleri, hepsi:

   bashKopyala

   for f in /etc/ssh/ssh_host_*_key.pub; do ssh-keygen -lf "$f"; done

   Sunucunun genelde birden çok anahtarı vardır (ED25519, ECDSA, RSA). Spitfire'ın Host key'i getir'i sunucunun sunduğu her türü türüyle gösterir; aynı türün satırıyla karşılaştırın.

#Rol taraması

İzleme için veriyi okuyabilen bir rol kullanılmaz. Başka bir izleme ya da okuma rolü var mı, spitfire_monitor gereğinden geniş mi:

SELECT r.rolname, r.rolsuper, r.rolbypassrls, r.rolconnlimit,
       array(SELECT g.rolname FROM pg_auth_members m JOIN pg_roles g ON g.oid = m.roleid
             WHERE m.member = r.oid) AS uyelikler
FROM pg_roles r
WHERE r.rolcanlogin AND r.rolname NOT LIKE 'pg\_%'
ORDER BY 1;

CloudNativePG'de tanımlı roller: kubectl get cluster <küme> -o jsonpath='{.status.managedRolesStatus}'. pg_read_all_data, pg_write_all_data, BYPASSRLS ya da süper kullanıcı olan bir rolü Spitfire'a vermeyin. Spitfire'ın Test et'i de bunları uyarı olarak gösterir.

#Spitfire'a girmeden deneyin

Önce rol (veritabanına erişebilen bir makineden):

psql "host=<db-adresi> dbname=<veritabanı> user=spitfire_monitor sslmode=require" \
  -c "select pg_has_role('pg_monitor','member') as izleme, rolconnlimit from pg_roles where rolname = current_user;"

Tünel varsa üç kısıtı kendi makinenizden deneyin (~/.ssh/spitfire yerine denemek için eklediğiniz anahtar; Spitfire'ın ürettiği anahtarla bu denemeyi Spitfire'ın Test et'i yapar):

1. Kabuk açılmamalı:

   bashKopyala

   ssh -i ~/.ssh/spitfire spitfire-tunnel@<sıçrama-sunucusu>

   Beklenen: PTY allocation request failed ve This account is currently not available.

2. Başka bir hedef reddedilmeli:

   bashKopyala

   ssh -i ~/.ssh/spitfire -N -L 15433:127.0.0.1:22 spitfire-tunnel@<sıçrama-sunucusu> &
   nc -z localhost 15433; kill %1

   Beklenen: channel 2: open failed: administratively prohibited: open failed

3. Veritabanı açık olmalı:

   bashKopyala

   ssh -i ~/.ssh/spitfire -N -L 15432:<db-adresi>:5432 spitfire-tunnel@<sıçrama-sunucusu> &
   nc -z localhost 15432 && echo ACIK; kill %1

   Beklenen: ACIK

#Spitfire'da

Bağlantılar → Bağlantı ekle → PostgreSQL penceresinde:

Tünel bölümünde:

• Host key'i getir sunucunun sunduğu anahtarları türüyle (ED25519, ECDSA, RSA) ve bütün türleri listeleyen komutla gösterir. Sunucudaki çıktıyla karşılaştırıp Fingerprint'ler aynı: bu anahtara güven deyin; Spitfire bir kez daha onay ister.
• Hazırlık komutları kartı yukarıdaki komutları formdaki adreslerle doldurur, kopyalanabilir.

Sonra pencerede Test et. Sonuç katman katman gelir: SSH bağlantısı, host anahtarı, kimlik doğrulama, yönlendirme izni, PostgreSQL girişi, pg_monitor, pg_stat_statements, rolün kapsamı, bağlantı sınırı ve primary / replika. Başarısız adımın yanında bu sayfanın ilgili başlığına link vardır. Test etmeden Kaydet'e basarsanız Spitfire bir kez sorar.

#Sık hatalar

#Geri almak

1. Spitfire'da bağlantıyı silin (Bağlantılar → çöp kutusu). Tünelle birlikte anahtar ve parolalar da silinir.
2. Sıçrama sunucusunda satırı ve kullanıcıyı kaldırın: authorized_keys'ten satırı silin, sonra userdel -r spitfire-tunnel.
3. Rolü kaldırın: DROP ROLE spitfire_monitor; Operatörde rolün ensure: absent olmasını (CloudNativePG) ya da users'tan çıkarılmasını (Zalando, Crunchy) sağlayın.

pg_stat_statements açık kalabilir, zararsızdır; kapatmak yine yeniden başlatma ister.

#İlgili sayfalar

• SSH tüneli
• Veritabanları: PostgreSQL
• Zabbix'i hazırlamak