PostgreSQL Yetkilendirme ve Güvenlik Yönetimi
Host Bazlı Yetkilendirme
Bir istemci PostgreSQL’e bağlantı yapmak istediğinde/var/lib/pgsql/13/data/pg_hba.conf dosyasından geçmelidir.pg_hba.conf dosyasından kurallar sıralı olarak okunur ve olumlu / olumsuz ilk eşleşen satırda izin verme / kısıtlama gerçekleşir.
pg_hba.conf satırlarının yazım şekli:
# TYPE DATABASE USER ADDRESS METHOD [OPTIONS]
- TYPE ⇒ local / host / hostssl / hostnossl
Yerel ya da uzaktan TCP/IP üzerinden yapılacak bağlantılar için TYPE = "host"
# TYPE DATABASE USER ADDRESS METHOD
host all zabbix 127.0.0.1/32 trust
host alfresco alfrescous 192.168.3.0/24 md5
host all postgres 0.0.0.0/0 reject
- TCP/IP üzerinden yapılacak bağlantılardan sadece SSL olanları tanımlamak için ⇒ “hostssl”
- Sadece SSL dışındakileri tanımlamak için ⇒ “hostnossl”
# TYPE DATABASE USER ADDRESS METHOD
host all zabbix 127.0.0.1/32 trust
hostssl all zabbix 192.168.1.0/0 trust
hostnossl all zabbix 0.0.0.0/0 reject
Unix soketi üzerinden yapılacak bağlantılar için “local” tipinde satır tanımlanır. Bu satırda IP/ağ adresi bulunmaz ve diğer alanlar “host” tipindekilerle aynı şekilde ayarlanır.
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
Yetkilendirmenin hangi yöntemle yapılacağı: METHOD
md5 |
Parola doğrulaması (md5 hashli) yap |
password |
Parola doğrulaması (açık) yap |
ident |
İstemcinin sistem kullanıcısını ident sunucudan alarak veritabanı kullanıcısı olarak kullan (TCP/IP bağlantılarında) |
peer |
İstemcinin sistem kullanıcısını kernel’dan alarak veritabanı kullanıcısı olarak kullan (unix soketi bağlantılarında) |
trust |
Koşulsuz izin ver |
reject |
Koşulsuz reddet |
pg_hba.conf‘ta bir satırda USER alanına şunlardan biri gelir:
all |
Tüm veritabanı kullanıcıları |
user |
“user” adlı kullanıcı/rol |
+group |
“group” adlı grup/rol ve bunun üyesi tüm kullanıcı/roller |
@file |
PostgreSQL dizininde “file” adlı dosyada yazan tüm kullanıcı/roller |
“peer” ve “ident” doğrulama metotlarında sistem kullanıcısını veritabanı kullanıcısına eşleyen bir “map” kullanmak da mümkündür. pg_ident.conf’ta yazan mappingler ile pg_hba.conf’ta yazan izinler eşleştirilir.
Veritabanına Bağlantı Yetkilendirmesi
Host bazlı yetkilendirme satırlarındaki DATABASE kısmında ne yazıldıysa sadece oralara izin verilir. Bu aşama geçildikten sonra veritabanına bağlanabilme iki koşula bağlıdır:
- datallowconn’un true olması.
- Kullanıcı/rolün veritabanı şemasını kullanma izninin olması.
datallowconn false olarak ayarlanmış veritabanlarına kimse bağlanamaz (template).
postgres=> select datname,datallowconn from pg_database;
datname | datallowconn
-----------+--------------
postgres | t
template1 | t
template0 | f
hede | t
pg02 | t
template1=# \c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept
Üzerinde oynama yapılmasını istemediğimiz ve klonlamak için kullandığımız veritabanlarında false vermek işimizi kolaylaştırabilir.
postgres=# UPDATE pg_database SET datallowconn='false'
WHERE datname='ulkeler';
UPDATE 1
postgres=# \c ulkeler
FATAL: database "ulkeler" is not currently accepting connections
Previous connection kept
Varsayılan olarak tablo, fonksiyon v.b. nesneler PUBLIC şeması ile oluşur, herkes tarafından kullanılabilir. Yani; herhangi bir kullanıcı tüm veritabanları ve tabloların listesini ve tüm tabloların yapılarını görebilir, verileri ise göremez. Birbirinden tamamen izole ortamlar isteniyorsa PUBLIC şemasının herkese açık hali kapatılır; ayrı kullanıcılar ayrı veritabanlarına belirli düzeylerde yetkilendirilir.
SQL Erişim Yetkileri
GRANT komutu ile veritabanındaki nesnelere SQL erişim yetkileri ve rollere, grup rollerinin yetkileri verilir. Verilebilecek yetkiler:
SELECT |
Tablo, view ya da sequence için okuma, COPY TO ‘yu da içerir |
INSERT |
Tabloda satır yazma, COPY FROM ‘u da içerir |
UPDATE |
Tabloda satır değiştirme (SELECT de gerektirir) |
DELETE |
Tabloda satır silme (SELECT de gerektirir) |
TRUNCATE |
Tablo boşaltma |
REFERENCES |
Foreign Key oluşturma için bağlanan iki tabloda da bu yetki olmalı |
TRIGGER |
Trigger oluşturma |
CREATE |
Veritabanı için yeni şema oluşturma, şema için yeni nesne oluşturma, tablespace için tablo ve index oluşturma |
CONNECT |
Veritabanına bağlanma |
EXECUTE |
Fonksiyonu kullanabilme |
TEMPORARY |
Veritabanında geçici tablolar oluşturma |
USAGE |
Şema için nesnelere erişme, prosedürel dili fonksiyonda kullanma |
ALL PRIVILEGES |
Tam yetki |
Kullanıcılara bir tablonun tamamına değil, sütun bazlı SELECT, INSERT, UPDATE ve REFERENCES gibi haklar da verilebilir.
örnek olarak, “hodo” tablosunda PUBLIC’e (dolayısıyla tüm rollere) SELECT yetkisi verelim:
hede=# GRANT SELECT ON hodo TO PUBLIC;
GRANT
hede=# \dp hodo
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | hodo | table | postgres=arwdDxt/postgres+| |
| | | =r/postgres | |
(1 row)
Kendi kullanıcımıza hodo tablosunda tam yetki verelim:
hede=# GRANT ALL PRIVILEGES ON hodo TO bilgemyte;
GRANT
hede=# \dp hodo
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | hodo | table | postgres=arwdDxt/postgres+| |
| | | =r/postgres +| |
| | |bilgemyte=arwdDxt/postgres | |
(1 row)
Kendi kullanıcımız ve “tubitak” kullanıcısı ile bu tabloya veri girmeyi deneyelim:
# psql -U tubitak -d hede -h 127.0.0.1
psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
hede=> INSERT INTO hodo VALUES ('1','2','1');
ERROR: permission denied for relation hodo
# psql -U bilgemyte -d hede -h 10.0.0.58 -W
Password for user bilgemyte:
psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
hede=> INSERT INTO hodo VALUES ('5','5','1');
INSERT 0 1
Kendi kullanıcımıza pg02 veritabanında “hodo” tablosunun sadece belirli kolonlarını okuma ve güncelleme yetkisi verelim:
# su - postgres
Last login: Thu Dec 7 17:35:09 +03 2017 on pts/0
-bash-4.2$ psql -d pg02
psql (11.5)
Type "help" for help.
pg02=# GRANT SELECT (x, y) ON hodo TO bilgemyte;
GRANT
pg02=# GRANT UPDATE (x) ON hodo TO bilgemyte;
GRANT
Tablolardaki yetkileri görelim:
pg02=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | hodo | table | | x: +|
| | | | bilgemyte=rw/postgres+|
| | | | y: +|
| | | | bilgemyte=r/postgres |
(1 row)
Kendi kullanıcımızla bağlanıp denemeler yapalım:
# psql -U bilgemyte -d pg02 -h 10.0.0.58
Password for user bilgemyte:
psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
pg02=> SELECT * FROM hodo;
ERROR: permission denied for relation hodo
pg02=> SELECT x,y FROM hodo;
x | y
---+---
1 | 2
1 | 2
(2 rows)
pg02=> UPDATE hodo SET x = 3, y = 1 WHERE x = 1;
ERROR: permission denied for relation hodo
pg02=>
pg02=> UPDATE hodo SET x = 3 WHERE x = 1;
UPDATE 2
SQL Erişim Yetkileri: Satır Bazlı Güvenlik Politikası
Tablo içerisinde belirli satırlar için de SQL yetkileri tanımlanabilir. Bir tabloda satır bazlı politika aktif edildiyse her kullanıcı için bir politika ile izinler tanımlanmalıdır. Aksi takdirde varsayılan tablo sahibi hariç herkes izinsizdir.
pg02=# ALTER TABLE phonebook ENABLE ROW LEVEL SECURITY;
ALTER TABLE
“tubitak” ya da kendi kullanıcımızla bağlanıp tablo satırlarını görmeye çalışalım:
# psql -U tubitak -d pg02 -h 127.0.0.1
psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
pg02=> SELECT * FROM phonebook;
ERROR: permission denied for relation phonebook
Tekrar postgres ile bağlanıp “tubitak” tüm satırları görebilsin ve değiştirebilsin politikalarını oluşturalım:
pg02=# CREATE POLICY admin_all ON phonebook TO tubitak USING (true) WITH CHECK (true);
CREATE POLICY
Diğer kullanıcılar tüm satırları görebilsin ama sadece kendiyle alakalı satırları değiştirebilsin:
pg02=# CREATE POLICY all_view ON phonebook FOR SELECT USING (true);
CREATE POLICY
pg02=# CREATE POLICY user_mod ON phonebook FOR UPDATE
USING (current_user = user_name)
WITH CHECK (current_user = user_name);
CREATE POLICY
Genel SQL yetkilendirmelerini de yapalım. “tubitak” her şeye yetkili, herkes ise sadece görme ve değiştirmeye yetkili olsun:
pg02=# GRANT SELECT, INSERT, UPDATE, DELETE ON phonebook TO tubitak;
GRANT
pg02=# GRANT SELECT ON phonebook TO PUBLIC;
GRANT
pg02=# GRANT UPDATE (phone,uid) ON phonebook TO PUBLIC;
GRANT
Tablo yetkilerine bakalım:
pg02=# \dp phonebook
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+---------
public | phonebook | table | postgres=arwdDxt/postgres+| | admin_all: +
| | | tubitak=arwd/postgres +| | (u): true +
| | | =rw/postgres | | (c): true +
| | | | | to: tubitak +
| | | | | all_view (r):
| | | | | (u): true +
| | | | | user_mod (w):
(u): (("current_user"())::text = user_name)
(c): (("current_user"())::text = user_name)
(1 row)
Şifreli Bağlantı Kullanımı
PostgreSQL kütüphaneleri SSL/TLS şifreli bağlantıyı destekler. Ayar dosyasından SSL de dinlemesi belirtilir. Desteklenen SSL metodları kısıtlanabilir. Şifreli bağlantı da açık bağlantı ile aynı porttan (5432) gerçekleşir.
Hazır SSL anahtar ve sertifikası yoksa openssl ile self-signed olarak yaratalım:
# openssl req -new -text -out pg02.req
Generating a 2048 bit RSA private key
........................................+++
...+++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
Özel anahtarın parolasını kaldıralım:
# openssl rsa -in privkey.pem -out pg02.key
Enter pass phrase for privkey.pem:
writing RSA key
# rm -rf privkey.pem
Sertifika isteğini anahtarla imzalayıp sertifika dosyasını oluşturalım:
# openssl req -x509 -in pg02.req -text -key pg02.key -out pg02.crt
# rm -rf pg02.req
Anahtar ve sertifikayı PostgreSQL dizinine atıp sahipliklerini ayarlayalım:
# mv pg02.{key,crt} /var/lib/pgsql/11/data/
# chown postgres:postgres /var/lib/pgsql/11/data/pg02.{key,crt}
# chmod 400 /var/lib/pgsql/11/data/pg02.key
Ayar dosyasında SSL dinlemeyi açıp, sertifika dosyalarının isimlerini yazalım. Şifreleme metodlarını da kısıtlayabiliriz:
# vim /var/lib/pgsql/11/data/postgresql.conf
ssl = on
ssl_ciphers = 'HIGH:!SSLv2:!SSLv3:!aNULL'
ssl_cert_file = '/var/lib/pgsql/11/data/pg02.crt'
ssl_key_file = '/var/lib/pgsql/11/data/pg02.key'
pg_hba.conf’ta istediğimiz “host” ile başlayan satırları sadece SSL bağlantıya izin vermesi için “hostssl”e çevirelim:
# vim /var/lib/pgsql/11/data/pg_hba.conf
hostssl all +dbadmin 127.0.0.1/32 trust
hostssl all +dbadmin ::1/128 trust
“host” olarak kalan satırlardaki koşullarda ise SSL ya da plain her tip bağlantıya izin var.
PostgreSQL’i yeniden başlatıp bağlantı kuralım:
# systemctl restart postgresql-11
# psql -U yildirim -d template1 -h 127.0.0.1
psql (11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.