Sektörlere göre veri güvenliği hassasiyeti değişkenlik gösterir. Ama genel olarak,
güvenlik derecesi yüksek (TCKN, Kredi kartı bilgileri vb.) verilerdeki okuma, yazma,
güncelleme ve silme işlemlerinin kayıt altına alınması gerekebilmektedir.
Okullarda öğrenci notları yükseltilebilir.
Bankada bakiye artırılabilir, EFT/Havale yaptırılabilir.
GSM operatörlerinde, gönderilen mesajlara ve görüşme kayıtlarına erişilebilir.
Online satış mağazalarında ödeme yapmadan alışveriş yapılabilir.
Kamu kurumunda vergi düşürülebilir, vb. bir çok örnek sıralamak mümkün.
Özellikle KVKK (GDPR) regulasyonlarından sonra veri güvenliği önlemleri arttı.

Denetleme (audit) için postgresql ile gömülü (built-in) gelen loglama mekanizması
yeterli özelliklere sahip değildir. Bu yüzden ücretli güvenlik yazılımları ya da açık kaynak
kodlu eklentiler (extension) kullanılabilir.

pgaudit ücretsiz ve açık kaynak kodlu bir eklentidir.
pgaudit eklentisinin (extension) kurulumu için iki yöntem takip edilebilir ;

1- Kaynak koddan kurulum. srv1 makinesinde postgresql, kaynak koddan derlenerek
kurulduğu için pgaudit de kaynak koddan kurulmalıdır.

# git kurulu değilse
apt install git -y

# pgaudit paketi indirilir.
git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit
git checkout REL_13_STABLE
Branch ‘REL_13_STABLE’ set up to track remote branch ‘REL_13_
STABLE’ from ‘origin’.
Switched to a new branch ‘REL_13_STABLE’

# pgdata yolu öğrenilir.
echo $PGDATA
/pg/data/13.6
make install USE_PGXS=1 PG_CONFIG=/pg/home/13.6/bin/pg_config

2- Postgresql’in apt ile kurulduğu sunucularda apt ile pgaudit kurulumu
yapılabilir.

apt-get install postgresql-13-pgaudit

Kurulum tamamlandıktan sonra eklenti oluşturulur.

# pgaudit eklenir.
alter system set shared_preload_libraries='pg_stat_statements,pgaudit' ;

# restart edilirken açılmazsa conf dosyası manuel
editlenebilir.
vim /pg/data/13.6/postgresql.auto.conf
shared_preload_libraries = 'pg_stat_statements,pgaudit' # Olmalı

create extension pgaudit;
SELECT name,setting FROM pg_settings WHERE name LIKE
'pgaudit%';

AYARLAR;
Ayarlar sadece superuser tarafından değiştirilebilir.
Global olarak postgresql.conf dosyasından ya da alter system set ya da
veritabanı seviyesinde alter database komutları ile ayarlanabilir.

pgaudit.log : Denetlenecek ifadeleri (statement) belirler. Default değer “none” dır.
Birden fazla değer tanımlanabilir.

alter system set pgaudit.log = ’read, write’;

ya da

set pgaudit.log = 'read, write’;

pgaudit.log_catalog : pg_catalog schema’sı ile ilgili ifadelerin denetimini açar ya da kapatır. Defaultta
açıktır. Psql ve pgadmin gibi araçlar fazla loglamaya neden olabilir.

alter system set pgaudit.log_catalog = 'on/off';

pgaudit.log_client : psql vb. istemcilere (client) log mesajlarının görünmesini açar ya da kapatır.
Debug vb. amaçlar haricinde genellikle kapalı tutulur. Default değer kapalıdır.

alter system set pgaudit.log_client = 'on/off';

pgaudit.log_level : Log seviyesini belirler. Performans ve son kullanıcı testleri için kullanılabilir. ERROR,
FATAL ve PANIC seviyelerine izin verilmez.
pgaudit.log_client aktif iken ayarlanabilir aksi halde default ayar olan log
seviyesi geçerli olacaktır.
postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS

alter system set pgaudit.log_level = 'DEBUG1../DEBUG5/INFO/
NOTICE/WARNING/LOG';

pgaudit.log_parameter: Logların, parametreleri içerip içermeyeceğini belirler. Aktif edildiğinde, parametreler,
ifadelerden sonra CSV (comma separated values) formatında verilir. Defaultta kapalıdır.

alter system set pgaudit.log_parameter = 'on/off';

pgaudit.log_relation: Loglara obje bilgisini ekler (tablo view vb.). Defaultta kapalıdır.
Örnek
AUDIT: SESSION,14,1,READ,SELECT,TABLE,public.ekip,select * from ekip ;, AUDIT: SESSION,13,1,MISC,???,VIEW,public.kilit,select * from kilit ;,

alter system set pgaudit.log_relation = 'on/off';

pgaudit.log_rows: Geri alınan ya da diğer ifadelerden (statement) etkilenmiş satırların loglanmasını sağlar.
Defaultta kapalıdır.

alter system set pgaudit.log_rows = 'on/off';

pgaudit.log_statement : İfadelerin (statement) ve parametrelerin loglanmasını sağlar. Defaultta açıktır.

alter system set pgaudit.log_statement = 'on/off';

pgaudit.log_statement_once : log_statement_once kapatılırsa statement içeriği (text) ve parametreler sadece
ilk kayıtta yazdırılır. Diğerlerinde yazdırılmaz. Böylece daha az log içeriği yazılır lakin
analiz zorlaşır. Defaultta kapalıdır.

alter system set pgaudit.log_statement_once = 'on/off';

pgaudit.role: Obje bazlı denetleme için master rol tanımlar. Birden fazla master rol tanımlanabilir.

Session Audit Logging
Oturumun (session) backend prosesi ile çalıştırdığı tüm statement’ları detaylı loglar.

postgres@srv1:~$ psql -d testdb -U mehmed -W
# DDL’ ler hariç herşeyi loglar.
testdb=> set pgaudit.log = 'all, -DDL';
testdb=> set pgaudit.log_level = 'debug1' ;
testdb=> select * from ekip ;

# /pg/data/13.6/log/postgresql-%a.log dosyasına düşen loglar
|time=2022-07-16 06:58:15.853 +03 app=psql host=[local]
user=mehmed db=testdb pid=5004 line=20 trx=0|LOG: AUDIT:
OBJECT,11,1,READ,SELECT,TABLE,public.ekip,select * from ekip
;,<none>

Object Audit Logging

SELECT, INSERT, UPDATE ve DELETE işlemlerini loglar. Truncate
desteklenmiyor.
Default ayarlara geçilip örneklerle ilerliyoruz.

alter system set pgaudit.log = 'none' ;
alter system set pgaudit.log_catalog = 'off';
alter system set pgaudit.log_client = 'off';
alter system set pgaudit.log_level = 'log';
alter system set pgaudit.log_parameter = 'off';
alter system set pgaudit.log_relation = 'off';
alter system set pgaudit.log_rows = 'off';
alter system set pgaudit.log_statement = ‘on’; --testleri
yaptığımız sürüm ile uyumsuzluk var.
alter system set pgaudit.log_statement_once = ‘off’
# Default ayarların geçerli olabilmesi için restart edilir.
/pg/home/13.6/bin/pg_ctl -o ‘--config-file=/pg/data/13.6/
postgresql.conf’ -D /pg/data/13.6/ -l pg.log restart

Örnek

set pgaudit.role = 'auditor';
create table hesap
(
KKN int,
isim text,
sifre text,
TANIM text
);
--
grant select (sifre)
on public.hesap
to auditor;
--
select KKN, isim
from hesap;
--
select sifre
from hesap;
--
grant update (isim, sifre)
on public.hesap
to auditor;
--
update hesap set tanim = 'aabcd';
--
update hesap set sifre = 'HASH2';
--
create table account_role_map
(
account_id int,
role_id int
);
--
grant select on public.account_role_map to auditor;
--
select hesap.sifre, account_role_map.role_id
from hesap
inner join account_role_map
on hesap.KKN = account_role_map.account_id;

Loglar

|time=2022-07-16 09:49:55.514 +03 app=psql host=[local]
user=mehmed db=testdb pid=6804 line=4 trx=0|LOG: AUDIT:
OBJECT,4,1,READ,SELECT,TABLE,public.hesap,select * from hesap
;,<not logged>
|time=2022-07-16 10:12:04.911 +03 app=psql host=[local]
user=postgres db=testdb pid=6961 line=1 trx=0|LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.hesap,”select hesap.sifre,
account_role_map.role_id
from hesap
inner join account_role_map
on hesap.KKN = account_role_map.account_
id;”,<not logged>
|time=2022-07-16 10:12:04.911 +03 app=psql host=[local]
user=postgres db=testdb pid=6961 line=2 trx=0|LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.account_role_map,”select
hesap.sifre, account_role_map.role_id
from hesap
inner join account_role_map
on hesap.KKN = account_role_map.account_
id;”,<not logged>

Her çalışan kendisi için açılmış olan hesap (kullanıcıx) ile uygulamaya bağlanır.
Uygulamalar ise çoğunlukla, tek veritabanı kullanıcısı (örn: erp uygulaması, erpuserı)
ile veritabanına bağlanır. Yani kullanıcıların uygulama hesabı ile uygulamanının
veritabanına bağlantı yaptığı hesap birbirinden farklıdır.
Örneklerde görüleceği üzere Pgaudit erpuser’ı denetleyebilmekte. kullanıcıx’ i
denetleyememektedir.
Halbuki denetlenmek istenen kullanıcıx’ tir.
Bu yüzden uygulamayı bu farkındalıkla dizayn etmek faydalı olacaktır. Özellikle KVKK
regülasyonlarından sonra bu konu daha da önem arzetmektedir.
Logları raporlamak ve anlamlı hale getirmek için de elastik search ile entegre edilebilir.

Denetleme (Audit)

ya da