Ubuntu'da pg_Audit Kurulumu
Published: 2024-01-09 09:32:13

- Ubuntu’nun son çıkardığı güncel sürüm olan 22.04 LTS işletim sistemi üzerinden Postgresql 15 kurulumunu yapıp.

- Gerekli bir kaç ayar ile DB’nin uzaktan erişim için bir kaç yetkilendirmesini yapacağız.

- SQL komutlarımızı psql üzerinden yürüteceğiz.

1. Postgresql paketlerinin yüklenmesini sağlıyoruz.

- root kullanıcısına geçiş yapılır.
```
sudo su - postgres
```

- Postgresql paketleri işletim sistemi paketleri reposuna eklenir.

```
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
```

- Apt Key eklenir.

```
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
```

- Repoya eklenen Postgresql paketlerinin eklenmesi için paket güncelleme komutunu çalıştırıyoruz.

```
apt-get update
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/olygf10z9dx7o0ozz0oc.png)


2.Paketlerin yüklendiğini gördükten sonra Postgresql 15 kurulumuna
geçiyoruz. Ardından işletim sistemindeki “postgres” kullanıcısı ile psql geçiş sağlıyoruz.Veritabanında tanımlı olan “postgres” kullanıcısının şifresini değiştiriyoruz.

- Postgresql 15 ve Psql kullanımı için postgresql-client kuralım.

```
apt install postgresql-15 postgresql-client -y

```

- Postgresql kullanıcısına geçiş sağlanır.

```
su - postgres
```

- psql giriş yapılır.

```
psql
```

- Kullanıcıları Listeliyoruz.

```
\du
```

- postgres kullanıcısının şifresini değiştiriyoruz.

```
ALTER USER postgres PASSWORD '123456';
```

- Postgresql postgresql.conf dosyasının nerede olduğu bulunur.

```
show config_file;
```

- Postgresql pg_hba.conf dosyasının nerede olduğu bulunur.

```
show hba_file;
```

- psql'den çıkış yapılır.

```
\q 
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vt1nf81k30t0njf8931h.png)

3. Postgresql servisimize dışardan erişimler için aşağıdaki ayarları tanımlıyoruz.

- postgresql.conf dosyasını düzenleyerek. “listen_addresses” değişkeninin değerini “*” 
olarak tanımlıyoruz. Kayıt edip çıkıyoruz.

- postgresql.conf dosyasını düzenle.

```
nano /etc/postgresql/15/main/postgresql.conf
           	veya
vi /etc/postgresql/15/main/postgresql.conf
```

- pg_hba.conf dosyasını düzenleyerek. Bütün ip lere erişimini sağlıyoruz. Siz sadece bazı
makinelerin erişimini istiyorsanız “0.0.0.0/0” adresi yerine istediğiniz IP 
tanımlayabilirsiniz.

- pg_hba.conf dosyasını düzenle.

```
nano /etc/postgresql/15/main/pg_hba.conf
		 veya
vi /etc/postgresql/15/main/pg_hba.conf
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ne18me7qcacv64xhzz06.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8utqx0fsve8vzhj5ltzw.png)

4. Şimdi tanımladığımız ayarların geçerli olması için postgresql servisini yeniden başlatıyoruz ve çalıştığına emin oluyoruz.

-  root kullanıcısına geçiş sağlayın.

```
exit
```

- Postgresql servisini yeniden başlat.

```
systemctl restart postgresql-15
```

- Postgresql servisinin durumunu kontrol et.

```
systemctl status postgresql-15
```

5. pg_Audit

- Postgresql Loglarını incelemek veritabanları üstünde ne işlemlerin yapıldığını izlemek,
veritabanın üstündeki hataları görmek incelemek sistemin sağlıklı işlediğini kontrol etmek
önemlidir. Ayrıca kimin ne zaman hangi makine üzerinden hangi sorguyu çalıştırdığı gibi
bir çok logların tutulması da bir yanlışlık yapıldığında suçluyu bulmak için önemlidir :)

6. İlk olarak logları nasıl izleriz onun üzerinde kısa duralım.

- Ubuntu sisteminde Postgresql paketleri genelde /var/log dizinin altına atmaktadır.

```
cd /var/log/postgresql
```

- Dizinin içeriğini listeliyoruz.

```
ls -l
```

- tail komutu ile file dosyasının son loglarını basıyoruz.-f komutu ile log file canlı olarak izliyoruz. 

```
tail -123f postgresql-15-main.log
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mohngu2g8b4sgrpriyks.png)

- Gördüğünüz gibi önceki yazıda yaptığım hataları otomatik olarak ERROR tagı başlığı
altında loglamakta.Bu hataların zamanla izlenmesi tespit edilmesi önemlidir.

7. Log dosyasını biraz daha kendimize göre kişiseleştirelim.

- postgres kullanıcısına geçin.

```
sudo su - postgres
```

- psql giriş yapıyoruz.

```
psql
```

- conf dosyasını buluyoruz.

```
show config_file;
```

- psql çıkış yapın.

```
\q
```

- conf dosyasnı düzenliyoruz.

```
nano /etc/postgresql/15/main/postgresql.conf
		veya
vi  /etc/postgresql/15/main/postgresql.conf
```

- Logları daha etkin yönetmek için logging_collector etkinleştiriyoruz.

```
logging_collector = on;
```

- 10 saniyenin üstünde süren sorguları listele.

```
log_min_duration_statement = 10s;
```

- Logların başında hangi veriler bulunsun. Biz şu şekilde sıralayacağız.
`Zaman - Bağlantıyı Yapan IP - Kullanıcı - DB Adı - Proses ID`

```
log_line_prefix = '%m %h %u %d [%p]';
```

- Loglama seviyesini belirtiyoruz. Yukarından aşağıya kritiklik seviyesini belirtir.

- debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic

```
log_min_error_statement = NOTICE;
```

- Log dizinin nereye basılacağını belirtiyoruz.

```
log_directory = /var/log/postgresql
```

- File kayıt edip çıkıyoruz.

- postgres kullanıcısından çıkın.

```
exit
```

- Postgresql restart ediyoruz.

```
systemctl restart postgresql
```

- Postgresql çalıştığına emin oluyoruz.

```
systemctl status postgresql
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ci2ezv4ypdtuugah8s1a.png)

8. Loglara şimdi bir göz atalım. Ama ilk önce syntax hatası yapalım.

- psql giriş yapın.

```
psql
```

- hatalı bir sorgu yazın.

```
selectt 1;
```

- psql den çıkış yapın

```
\q
```

- Log dizinine gidin.

```
cd /var/log/postgresql/
```

- Dizini listeleyin. Farklı bir dosya oluşturacaktır. 

- Son oluşturulan dosyayı listeleyeceğiz.

```
ls -ltr
```

- Son 123 kaydı listeleyelim.

```
tail -123f postgresql-2023-05-20_144715.log
```

- Görüldüğü gibi artık (Zaman — Bağlantıyı Yapan IP — Kullanıcı — DB Adı — Proses ID) şeklinde listelenmektedir. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d1d5t61tbophjv97no1h.png)

9. pg_Audit Kurulumu ve Ayarlanması 

- Pgaudit Postgresql kurulum paketleri ile gelen bir extensiondır. Temel amacı Postgresql üzerinde özelleştirerek logların yapılandırmasını sağlamaktadır. Sistem üzerinde logların izlenerek sistem üzerinde oluşabilecek kötü niyetli sorguları izleyerek tespit edilebilir. pgaudit.log parametresinin detayları için.

- pg_Audit paketini kuruyoruz. 

- Siz kullandığınız Postgresql versiyonuna göre "apt search pgaudit" komutu ile bulabilirsiniz.

```
apt install postgresql-15-pgaudit
```

- conf dosyasnı düzenliyoruz.

```
nano /etc/postgresql/15/main/postgresql.conf
		veya 
vi  /etc/postgresql/15/main/postgresql.conf
```

- shared_preload_libraries parametresini düzenliyoruz.

```
shared_preload_libraries = 'pgaudit'
```

- File kayıt edip çıkıyoruz.

- Postgresql restart ediyoruz.

```
systemctl restart postgresql
```

- Postgresql çalıştığına emin oluyoruz.

```
systemctl status postgresql
```

- postgres kullanıcısına geçin.

```
su – postgres
```

- psql giriş yapıyoruz.

```
psql
```
 
- extension kuruyoruz.

```
CREATE EXTENSION pgaudit;
```

- Sistemdeki bütün logları listeleyecek şekilde ayarlıyoruz.

```
alter system set pgaudit.log = 'all';
```

- psql çıkış yapın.

```
\q
```

- Postgresql restart ediyoruz.

```
systemctl restart postgresql
```

- Postgresql çalıştığına emin oluyoruz.

```
systemctl status postgresql
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xzv01xxwmhkex6bgmb5h.png)

10. Şimdi son olarak log file izleyelim.      

- psql giriş yapıyoruz.

```
psql
```

- Sıradan bir sorgu atıyoruz.

```
SELECT 1;
```

- psql çıkış yapın.

```
\q
```

- Log dizinine gidin.

```
cd /var/log/postgresql/
```

- Dizini listeleyin. Farklı bir dosya oluşturacaktır.
 
- Son oluşturulan dosyayı listeleyeceğiz.

```
ls -ltr
```

- Son 10 kayıtı listeleyin.

```
tail -n 10 postgresql-2023-05-20_151745.log
```

- Artık her bir adım loglanmaktadır. Ancak bu logların boyutu için sağlıklı olmayacaktır. Disk doldurma gibi durumlar için arada bir log file “du -sh” komutu ile izlemeyi unutmayın.