PostgreSQL'de Güvenlik ve Operasyon Temelleri: Role, GRANT, pg_hba, SCRAM, Backup ve Major Upgrade

Sürüm notu: Bu yazıdaki örnekler PostgreSQL 18.7 (resmî postgres:18 Docker image'ı), .NET 10 ve Npgsql 10.0.3 ile, 02'de kurduğumuz Bookstore lab'ında çalıştırılmıştır; pg_upgrade ve geri yükleme denemeleri PostgreSQL 17.12 ve 18.7'nin birlikte kurulu olduğu geçici bir container'da yapılmıştır.

Önce Tahmin Et

Sitedeki serilerin ortak örnek uygulaması Bookstore (bir online kitapçı) için bir gece yarısı düzeltmesi gerekti ve Emre, migration'ı alışkanlıkla postgres superuser'ı ile çalıştırdı. Migration audit_log tablosunu ekledi. 02'de uygulama rolü için ALTER DEFAULT PRIVILEGES tanımlamıştık; uygulama yeni tabloyu okuyabilir mi? Bir de uygulamanın kendi geçici tablosunu public schema'da açmayı denedik:

CREATE TABLE public.audit_log (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text);  /* postgres ile */
SET ROLE bookstore_app;
SELECT count(*) FROM audit_log;
CREATE TABLE app_scratch (id int);

Default privileges tanımlıysa ilk sorgu çalışmalı, ikincisi de SQL Server'daki dbo alışkanlığıyla belki çalışır. Sonuç?

ERROR:  permission denied for table audit_log
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE app_scratch (id int);

İkisi de reddedildi. Default privileges yalnızca belirtilen rolün (FOR ROLE bookstore_owner) oluşturduğu nesnelere uygulanıyor; tabloyu postgres oluşturunca kural devreye girmedi. public schema'da tablo oluşturma yetkisi ise PostgreSQL 15'ten beri herkese açık değil. Bu yazıda role modelini, GRANT'ları, RLS'ye (Row-Level Security; satır bazında erişim kuralları) kısa bir girişi, pg_hba.conf, SCRAM (Salted Challenge Response Authentication Mechanism; parolayı ağda açık göndermeden doğrulayan yöntem) ve TLS (Transport Layer Security; bağlantıyı şifreleyen protokol) ile bağlantı güvenliğini, yedek ve geri yükleme testini, belirli bir ana geri dönmeyi ve major upgrade'i görüyoruz.

Role Modeli: Login ve User Tek Kavram

PostgreSQL'de yalnızca role var. LOGIN özelliği olan role bağlanabilen bir kullanıcı, olmayan role yetkileri toplayan bir grup. Yetkiler cluster seviyesinde tanımlı role'lere verilir; SQL Server'daki gibi login'i her veritabanında ayrıca bir user'a eşlemek gerekmez.

     rolname     | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin | rolbypassrls
-----------------+----------+---------------+-------------+-------------+--------------
 bookstore_app   | f        | f             | f           | t           | f
 bookstore_owner | f        | f             | f           | t           | f
 postgres        | t        | t             | t           | t           | t

SQL Server Derinlemesine serisinin güvenlik modeli yazısındaki kavramların karşılıkları:

Lab'daki ayrım bilinçli: bookstore_owner şemanın sahibi ve migration'ları çalıştırıyor, bookstore_app yalnızca veri okuyup yazıyor, superuser ise yalnızca bakım işlerinde kullanılıyor.

GRANT, ALTER DEFAULT PRIVILEGES ve public Schema

\dp bir tablonun yetkilerini role=haklar/veren biçiminde gösteriyor:

 Schema |  Name  | Type  |            Access privileges             | Column privileges | Policies
--------+--------+-------+------------------------------------------+-------------------+----------
 public | orders | table | bookstore_owner=arwdDxtm/bookstore_owner+|                   |
        |        |       | bookstore_app=arwd/bookstore_owner       |                   |

arwd, uygulama rolünün INSERT (a), SELECT (r), UPDATE (w) ve DELETE (d) yetkisi; D (TRUNCATE), x (REFERENCES), t (TRIGGER) ve 17'de gelen m (MAINTAIN; VACUUM, ANALYZE, REINDEX gibi bakım komutları) yalnızca owner'da. Bu yetkiler 00-roles.sql'deki default privileges'tan geliyor ve tahmindeki hata da oradan: kural "bookstore_owner'ın oluşturduğu tablolar" için yazılmış. Çözüm, migration'ları her zaman owner rolüyle çalıştırmak; superuser'ın oluşturduğu nesneler için ayrı bir ALTER DEFAULT PRIVILEGES FOR ROLE postgres yazmak kural dışı durumu kalıcı hâle getirir.

Raporlama için salt okunur bir rol gerekirse hazır role'ler işe yarar. pg_read_all_data, bütün tablolarda SELECT verir ve sonradan oluşturulan tabloları da kapsar:

CREATE ROLE bookstore_report NOLOGIN;
GRANT pg_read_all_data TO bookstore_report;
SET ROLE bookstore_report;
SELECT count(*) AS orders FROM orders;
UPDATE books SET price = price WHERE book_id = 1;

 orders
---------
 1000000

ERROR:  permission denied for table books

Dikkat: pg_read_all_data cluster'daki bütün veritabanlarını kapsar. Yalnızca bookstore'u okuması gereken bir rol için GRANT SELECT ON ALL TABLES IN SCHEMA public ve aynı schema için default privileges daha dar bir yetki. İzleme araçları için de pg_monitor ve 07'de kullandığımız pg_read_all_stats var.

Row-Level Security'ye Kısa Giriş

RLS, bir tabloya satır bazında filtre ekler. Uygulama her istekte müşteri kimliğini bir oturum ayarına yazıyor ve politika yalnızca o müşterinin siparişlerini gösteriyor:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_current_customer ON orders TO bookstore_app
    USING (customer_id = current_setting('app.customer_id', true)::int);

 customer_2
------------
      17359

ERROR:  invalid input syntax for type integer: ""

 as_owner
----------
  1000000

Müşteri 2 için 17.359 sipariş döndü. İkinci satır öğretici bir hata: ayar aynı oturumda bir kez SET edilip RESET edilince current_setting(..., true) NULL değil boş metin döndürüyor ve cast patlıyor. Politikada nullif(current_setting('app.customer_id', true), '')::int kullan; ayar yoksa sorgu hata yerine sıfır satır döner. Üçüncü satır da önemli: tablonun owner'ı RLS'ye takılmıyor; owner'ın da kurala uyması için ALTER TABLE ... FORCE ROW LEVEL SECURITY gerekir. Connection pool kullanırken ayarı transaction'a bağlı (set_config(..., true)) vermek, multi-tenant tasarımın kendisi ve SQL Server'daki security policy'lerle kıyas Çözüm Desenleri serisinin multi-tenancy yazısında.

pg_hba.conf, SCRAM ve TLS

Kimin, nereden, hangi yöntemle bağlanabileceğine pg_hba.conf karar verir; kurallar yukarıdan aşağı okunur ve ilk eşleşen satır geçerlidir. Parolalar 14'ten beri varsayılan olarak SCRAM-SHA-256 ile saklanıyor; 18'de md5 deprecated oldu ve md5 parola atamak artık uyarı veriyor:

SET password_encryption = 'md5';
CREATE ROLE legacy_md5 LOGIN PASSWORD :'pw';   /* pw: psql değişkeni, rastgele üretildi */

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.

Eski bir sunucudan taşınan role'lerin parolaları hâlâ md5 olabilir; pg_authid.rolpassword'ün SCRAM-SHA-256$ ile başlayıp başlamadığına bakıp md5 olanların parolasını yeniden belirle. Resmî Docker image'ında TLS kapalı geliyor (ssl = off). db/14-tls.ps1 önce localhost için self-signed bir sertifika üretip ssl'i açıyor, sonra uygulama rolünün şifresiz bağlanmasını yasaklayan bir kural ekliyor:

 line_number |   type    | database |    user_name    | address |  auth_method
-------------+-----------+----------+-----------------+---------+---------------
         128 | hostnossl | {all}    | {bookstore_app} | all     | reject
         129 | hostssl   | {all}    | {all}           | all     | scram-sha-256
         130 | host      | {all}    | {all}           | all     | scram-sha-256

.NET tarafında tls komutu aynı bağlantıyı her SSL Mode ile açıp sunucunun pg_stat_ssl'de gördüğünü yazıyor:

NpgsqlConnectionStringBuilder builder = new(connectionString) { Pooling = false };
configure(builder);   /* ör. b.SslMode = SslMode.VerifyFull; b.RootCertificate = rootCertificate; */
await using NpgsqlConnection connection = new(builder.ConnectionString);
await connection.OpenAsync();

$env:BOOKSTORE_PG_ROOT_CERT = "$PWD\server.crt"
dotnet run --project src/Bookstore.PgLab -- tls

Disable                        PostgresException: 28000: pg_hba.conf rejects connection for host "172.17.0.1", user "bookstore_app", database "bookstore", no encryption
Prefer (default)               ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384
Require                        ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384
VerifyFull                     NpgsqlException: Exception while performing SSL handshake
                               -> AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
VerifyFull + Root Certificate  ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384

Varsayılan Prefer sunucu destekliyorsa şifreliyor ama sertifikayı doğrulamıyor; Require da doğrulamıyor. İkisi de araya giren biri tarafından sunulan sahte sertifikayı kabul eder. Sunucunun kimliğini kontrol eden tek mod VerifyFull: güvenilir bir kök sertifika olmadan reddetti, Root Certificate verilince bağlandı. Aynı komutu Host=127.0.0.1 ile çalıştırınca VerifyFull yine reddetti, çünkü sertifika localhost adına. TLS sürümü istemcinin TLS kütüphanesine bağlı; container içindeki psql aynı sunucuyla TLSv1.3 konuştu. Production'da bağlantı dizesi SSL Mode=VerifyFull olmalı; managed servisler kök sertifikalarını dokümanlarında yayınlıyor.

Backup: pg_dump ve pg_restore

pg_dump tek bir veritabanının mantıksal yedeğini alır; custom format (-Fc) sıkıştırılmış olur ve pg_restore ile seçerek ve paralel geri yüklenebilir:

docker exec bookstore-pg pg_dump -U postgres -d bookstore -Fc -f /tmp/bookstore.dump
docker exec bookstore-pg createdb -U postgres -O bookstore_owner bookstore_restore
docker exec bookstore-pg pg_restore -U postgres -j 4 -d bookstore_restore /tmp/bookstore.dump

pg_dump -Fc: 5.650 s
63M     /tmp/bookstore.dump
pg_restore -j 1: 4.939 s
pg_restore -j 4: 2.267 s

Yaklaşık 650 MB'lık veritabanının yedeği 63 MB tuttu; index'ler yedekte yalnızca CREATE INDEX komutu olarak duruyor. Paralel restore süreyi yarıya indirdi; tablo verileri ve index'ler aynı anda yükleniyor. pg_dump role'leri içermez: role'ler cluster seviyesinde olduğu için pg_dumpall --globals-only ile ayrıca alınmalı. Restore testinin değerini de burada gördük: aynı yedeği superuser yerine owner rolüyle geri yükleyince:

pg_restore: error: could not execute query: ERROR:  permission denied to create extension "pg_stat_statements"
pg_restore: error: could not execute query: ERROR:  permission denied to create extension "pgstattuple"
pg_restore: warning: errors ignored on restore: 4

13'teki untrusted extension'lar owner rolüyle kurulamıyor. Gerçek bir felakette bunu ilk kez öğrenmek istemezsin. Yedeğin bir işe yaradığını ancak düzenli bir restore testi kanıtlar: yedeği ayrı bir sunucuya geri yükle, satır sayılarını ve uygulamanın açılışını kontrol et, süreyi ölç.

PITR: pg_basebackup ve WAL Arşivi

pg_dump alındığı ana döner. Gün içindeki bir hatadan hemen öncesine dönmek için PITR (point-in-time recovery; veritabanını geçmişteki belirli bir ana geri getirme) gerekir: fiziksel bir base backup ve o andan sonraki bütün WAL (Write-Ahead Log; değişikliklerin veri dosyalarından önce yazıldığı log) dosyalarının arşivi. db/14-ops/pitr.sh geçici bir container'da archive_mode'u açıp pg_basebackup ile yedek alıyor, sonra bir düzeltme ve bir hata yapıyor: WHERE'i unutulmuş bir fiyat güncellemesi.

UPDATE books SET price = 41.50 WHERE book_id = 2;   /* doğru değişiklik */
/* recovery target: 2026-11-14 03:12:39.597928+00 */
UPDATE books SET price = 9.99;                       /* WHERE unutuldu: 40.000 satır */

Geri dönmek için sunucu durduruluyor, veri klasörü base backup ile değiştiriliyor ve restore_command, recovery_target_time ile recovery.signal dosyası ekleniyor:

LOG:  starting point-in-time recovery to 2026-11-14 03:12:39.597928+00
LOG:  recovery stopping before commit of transaction 811, time 2026-11-14 03:12:42.064434+00
LOG:  selected new timeline ID: 2
 book_id | price
---------+-------
       1 | 32.77
       2 | 41.50

Doğru değişiklik duruyor, hatalı güncelleme yok. Sunucu yeni bir timeline'a geçti; aynı arşivden ikinci kez geri dönerken hangi timeline'ı izleyeceğine dikkat et. 17'den beri pg_basebackup --incremental ile yalnızca değişen blokları yedekleyebiliyorsun (summarize_wal = on gerekiyor): 682 MB'lık tam yedekten sonra 1.000 satırlık bir güncelleme, 25 MB'lık bir incremental yedek üretti. Geri yüklemeden önce pg_combinebackup zinciri tek bir klasöre birleştiriyor. Gerçek ortamda bu işi pgBackRest ya da Barman gibi araçlar yapar; mekanizma aynı.

Major Upgrade: pg_upgrade

Minor sürümler (18.6 → 18.7) yalnızca binary değişimidir ve gecikmeden uygulanmalı: 12 Kasım 2026'daki 18.7 sürümü 52 güvenlik açığını kapattı, aralarında pg_restore üzerinden kod çalıştırma (CVE-2026-116314; CVE, Common Vulnerabilities and Exposures, güvenlik açıklarına verilen standart kimlik) da var. Major sürümler arasında veri dosyalarının formatı değiştiği için pg_upgrade, dump/restore ya da logical replication gerekir. db/14-ops/upgrade.sh 17.12'deki Bookstore'u 18.7'ye taşıyor. İlk deneme 18'in yeni bir varsayılanına takıldı:

Data page checksums are enabled.
old cluster does not use data checksums but the new one does

18'in initdb'si data checksum'larını varsayılan olarak açıyor, 17 açmıyordu ve pg_upgrade ikisinin aynı olmasını istiyor. Yeni cluster'ı initdb --no-data-checksums ile oluşturunca kontrol geçti (13'te gördüğümüz eksik pg_cron kütüphanesini kaldırdıktan sonra). Checksum'ları sonradan açmak için pg_checksums --enable sunucu kapalıyken bütün dosyaları okur; bunu ayrı bir bakım işi olarak planla. Upgrade'in iki modu:

659M    /var/lib/postgresql/17/data/base
copy: 1.864 s
link: 1.336 s

Lab'daki 659 MB'ta fark küçük, ama copy modu veri boyutuyla büyür, --link ise dosyaları hard link ile bağladığı için boyuttan neredeyse bağımsızdır. Bedeli: yeni sunucu bir kez açıldıktan sonra eski cluster kullanılamaz, geri dönüş yolu yedektir. 18'in getirdiği önemli bir kolaylık da istatistikler: upgrade sonrası hiç ANALYZE çalışmadan planner'ın tahminleri yerinde:

 column_stats
--------------
           46

 relname | last_analyze | last_autoanalyze
---------+--------------+------------------
 orders  |              |

 Gather  (cost=1000.00..16931.63 rows=4133 width=47)

Gerçekte 4.800 olan Pending siparişleri için tahmin 4.133; 17'ye kadar bu noktada tablo istatistiksiz olurdu ve ilk sorgular kötü planlarla çalışırdı. 07'de oluşturduğumuz türden extended statistics ise taşınmıyor (pg_stats_ext boştu); pg_upgrade'in önerdiği vacuumdb --all --analyze-in-stages --missing-stats-only yalnızca eksikleri topluyor ve sonrasında pg_stats_ext'te bir satır görünüyor. Downtime'ı dakikaların altına indirmek gerekiyorsa logical replication ile yeni sürüme kopyalayıp geçmek mümkün; bu yöntem Veritabanı Performansı ve Scaling serisinin replication yazısında.

Üretimde Dikkat

Uygulama, migration ve raporlama için üç ayrı role kullan; superuser yalnızca bakım için ve ağdan değil, sunucunun kendisinden bağlansın. pg_hba.conf'ta en sona bir genel kural yazmak yerine her uygulamanın hangi veritabanına hangi ağdan bağlanacağını açıkça yaz.

Yedekleme stratejisini RPO (Recovery Point Objective; kabul edilebilir en fazla veri kaybı süresi) ve RTO (Recovery Time Objective; sistemin tekrar ayağa kalkması için kabul edilen süre) hedeflerinden çıkar: pg_dump tek başına günlük bir RPO verir, PITR dakikalar seviyesine indirir. Restore testini takvime koy ve süresini ölç.

Minor sürümleri yayınlandıkları ay içinde uygula; major upgrade'den önce pg_upgrade --check'i production'ın bir kopyasında çalıştır, extension sürümlerini (13) ve checksum ayarını kontrol et.

Sık Düşülen Tuzaklar

Backup alıp hiç restore etmemek: yukarıdaki extension hatası, eksik role'ler ya da arşivde eksik bir WAL dosyası ancak geri yüklerken ortaya çıkar.

Uygulamayı postgres superuser'ı ile bağlamak: SQL injection tek bir tabloyla değil bütün sunucuyla sonuçlanır; RLS de superuser'a uygulanmaz.

SSL Mode=Require'ı yeterli sanmak: şifreli ama karşıdakinin kim olduğunu doğrulamıyor.

Migration'ı yanlış rolle çalıştırıp default privileges'ın dışında kalan tablolar oluşturmak: tahmindeki permission denied genellikle ilk production isteğinde görülür.

Küçük Bir Deneme

★☆☆ pg_dumpall --globals-only --no-role-passwords çıktısında lab role'lerinin nasıl göründüğüne bak. Parolalar olmadan geri yüklenen role'lere parolayı nasıl verirsin?

★★☆ RLS politikasını nullif ile düzelt, FORCE ROW LEVEL SECURITY ekle ve owner rolünün de yalnızca bir müşterinin siparişlerini gördüğünü doğrula. pg_dump bu tabloyu yedeklerken ne oluyor?

★★★ pitr.sh'ı değiştir: hatalı güncellemeden sonra bir de yeni sipariş ekle ve geri dönüşü recovery_target_time yerine recovery_target_xid ile, yalnızca hatalı transaction'ı atlayacak şekilde dene. Yeni siparişi kurtarabildin mi, neden?

Bir sonraki yazıda Blok D ile sürümlere geçiyoruz: eski bir PostgreSQL projesini devraldığında karşına çıkacak 13'ten 17'ye değişiklikler ve destek dışı kalan PostgreSQL 14'ten çıkış önceliği.