Postgresql’de kullanıcı yönetimi

Bu yazıda Postgresql için, aklımızda mutlaka yer edinmesi gereken kullanıcı yönetim işlemlerini derledim.

Kullanıcı oluşturmak

Superuser kullanıcısı oluşturmak

Superuser kullanıcıları Postgresql’de (bundan sonra Postgres diyeceğim), LOGIN hariç tüm izinlere sahip, tam erişimi olan kullanıcılardır.

İlk metotta bunu Postgres ile birlikte gelen createuser programını kullanarak yapacağız. Bu program (veya komut da denebilir) Postgres için kullanıcılar açmak için kullanılmaktadır. -Bence ismi fazla jenerik olduğu için Linux’taki adduser ile karıştırılabilmesi muhtemeldir.-

sudo -u postgres /bin/bash
createuser --superuser --login --pwprompt dual

Program hakkında daha fazla bilgi edinmek için man createuser komutunu kullanabilirsiniz. Yukarıdaki örnekte sudo ile Postgres’in yönetici kullanıcısı olan postgres olarak yeni bir bash terminali açıyoruz. Ardından terminal içerisinden createuser programını kullanıyoruz. Kullanıcı oluşturmaya --superuser parametresi ile başlıyoruz, daha sonra --login ile kullanıcıya giriş yapabilme iznini veriyoruz ve en son ise --pwprompt ile kullanıcı için şifre oluşturulması talebinde bulunuyoruz. Fakat bu parametreyi kullanmamız zorunlu değildir, zira ilgili kullanıcı “pg_hba.conf” dosyasından giriş metodu olarak “peer” ayarlanırsa şifresiz olarak local UNIX kullanıcısı olarak da Postgres’e erişim sağlayabilir. Şifreler genelde uzaktan erişim için tercih edilmektedir.

İkinci metodda ise “superuser” kullanıcısını, bir Postgresql client’ı olan psql programını kullanarak oluşturuyoruz.

sudo -u postgres psql
postgres=# CREATE USER dual WITH SUPERUSER LOGIN PASSWORD 'Dual1234';

Yukarıdaki örnekte önce sudo ile postgres kullanıcısı olarak Postgres client’ına bağlanıyoruz. CREATE USER SQL komutuyla SUPERUSER ve LOGIN izinlerine sahip “superuser” kullanıcısı oluşturuyoruz.

Belirli izinlere sahip kullanıcı oluşturmak

Superuser yerine kendi seçtiğimiz izinlere sahip bir kullanıcı da oluşturabiliriz. Daha önce gördüğümüz örnekteki gibi, createuser programıyla veya SQL komutuyla kullanıcılar oluşturabiliyoruz.

sudo -u postgres /bin/bash
createuser --login --pwprompt dev1

Kullanıcıyı bu şekilde oluşturduğumuzda sadece belirttiğimiz veritabanlarına, belirttiğimiz izinlerle gelen yetkilerle erişime sahip olacaktır. man createuser ile programın dökümanına baktığımızda yukarıdaki parametrelere ek olarak --createdb ve --createrole kullanılabilir. Kullanıcının veritabanı oluşturabilmesini istersek --createdb, yeni bir role ekleyebilmesini istersek --createrole kullanabiliriz.

Diğer yöntemde ise SQL komutu kullanıyoruz:

sudo -u postgres psql
postgres=# CREATE USER dev1 WITH LOGIN PASSWORD 'Dev1234';

Burada LOGIN ve PASSWORD dışında CREATEDB ve CREATEROLE izinlerini de tanımlayabiliriz. Kaynak.

Yeni oluşturulan kullanıcılar izin vermediğimiz taktirde başka veritabanlarına bağlanabilir, tablolarını ve yapısını görebilir; fakat içeriklerini göremezler. Biz yetkilendirme yapmadığımız sürece de CRUD operasyonlarının hiç birini yapamazlar.

Kullanıcıları yetkilendirmek

Yeni bir kullanıcı oluşturduğumuzda (superuser kullanıcıları hariç) bu kullanıcıların yetkili olacakları veritabanlarını, şemaları ve burada neler yapabileceklerini de tanımlamamız gerekir. CREATEROLE izni bulunan kullanıcılar başka kullanıcıların haklarında değişiklik yapabilirler.

Tüm hakları şema bazlı vermek

Bir kullanıcıya bir veritabanı için tüm hakları (SELECT, UPDATE, DELETE, DROP, TRUNCATE, CONNECT gibi..) vermek istersek, önce ilgili veritabanına bağlanmamız, daha sonra hakları hangi şema için vereceğimizi SQL komutuyla belirtmemiz gerekir. Kaynak.

sudo -u postgres psql
postgres=# \connect databaseName
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev1;

Yukarıdaki örnekte kullanıcıya, bağlandığımız veritabanının public şemasındaki tüm tablolar için tüm hakları verdik. Böylece yeni tablolar oluşturabilirler, tablolarda CRUD operasyonları yapabilirler ve tabloyu silebilirler. Fakat bununla sequence ve fonksiyon ile ilgili işlemleri yapamazlar. Mesela eğer tabloda bigserial türünde bir kolon oluşturmak ve ilgili tabloya yeni kayıt eklemek isterse bile SEQUENCES alanında yetkisi olması gerekir. Aynı şekilde fonksiyon oluşturup kullanabilmesi için FUNCTIONS için de gerekir. Bu yüzden ilgili yetkileri vermeliyiz:

postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dev1;
postgres=# GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO dev1;

Belirli hakları şema bazlı vermek

Aşağıdaki örnekte ise kullanıcıyı, bağlandığımız veritabanının public şemasında sadece SELECT, INSERT ve UPDATE komutlarını çalıştırabilmesi için kısıtlıyoruz. TABLES yerine SEQUENCES ve FUNCTIONS da kullanılabilir.

sudo -u postgres psql
postgres=# \connect databaseName
postgres=# GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO dev1;

Şema bazlı verilen hakları geri almak

Hakları verdiğimiz gibi, geri de alabiliriz. Hakları geri alırken GRANT yerine REVOKE kullanılır. Kullanıcıyı belirtirken de TO yerine FROM kullanıyoruz.

sudo -u postgres psql
postgres=# \connect databaseName
postgres=# REVOKE ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public FROM dev1;
postgres=# REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM dev1;
postgres=# REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM dev1;

Veritabanı bazlı haklar (Postgres 14+ için)

Postgres versiyon 14'de tanıtılan Predefined Roles (önceden tanımlı haklar) olarak adlandırılan bu özellik ile kullanıcılara haklarını veritabanı bazlı olarak tanımlayabiliriz. Bu yeni bir özellik olduğu için -bu yazının yazıldığı sene için diyorum-, bu alt başlığı biraz aşağıda bıraktım. İşimize yarayacak olanları ise aşağıya ekledim.

pg_read_all_data: Tüm (tablolar, viewlar, sequence) verileri okuyabilir. Tüm bu nesnelerde SELECT işlemlerini yapabilir.

pg_write_all_data: Tüm (tablolar, viewlar, sequence) verilere yazabilir. Tüm bu nesnelerde INSERT, UPDATE ve DELETE işlemlerini yapabilir.

Mesela bir kullanıcının bir veritabanındaki tüm tablo, view ve sequence’leri okuyabilmesini istersek ona pg_read_all_data hakkını tanımlayabiliriz. Eğer tüm CRUD operasyonlarını yapabilmesini istersek de pg_write_all_data hakkını tanımlayabiliriz. CREATEROLE iznine sahip tüm kullanıcılar (superuser kullanıcıları da dahil) bu yetkilendirmeyi yapabilir.

GRANT pg_read_all_data TO dev1;

Kullanıcılarda değişiklik yapmak

Şifre değişimi

İlk metodda Postgres client’ı ile \password komutunu kullanarak istediğimiz bir kullanıcının giriş şifresini değiştirebiliriz.

sudo -u postgres psql
postgres=# \password dev1

İkinci metodda yine client’tan SQL komutuyla değiştirebiliriz.

ALTER USER dev1 WITH PASSWORD 'Dev4321';

Kullanıcılara izin eklemek

Yazının başında kullanıcıları oluştururken kullandığımız izinleri, kullanıcılara oluşturulmalarından sonra da ekleyebiliriz.

ALTER USER dev1 WITH LOGIN NOCREATEDB NOCREATEROLE;

Bu arada unutmayın, WITH sonrası yazılan yetkiler bir öncekini kaldırıp yenisiyle değiştirmez; sadece yetkilerin üzerine ekleme yapar. Mesela:

ALTER USER dev1 WITH NOLOGIN;

Yukarıdaki komuttan sonra aynı kullanıcı için yeni bir yetki tanımladıktan sonra diğer tanımlanan yetkileri değişmemiş olacak, onun yerine NOLOGIN üzerine NOCREATEDB eklenmiş olacaktır. Örneğin:

ALTER USER dev1 WITH NOCREATEDB; # = NOLOGIN + NOCREATEDB

Bu yüzden kullanıcıların yetkilerini kontrol etmelisiniz.

Kullanıcıları ve yetkilerini listeleme

Kullanıcıları listelemek ve yetkilerini kontrol etmek için Postgres’in client programından veritabanına bağlanıp \du komutunu gönderiyoruz.

sudo -u postgres psql
postgres=# \du

 Role name |               Attributes           |     Member of      
-----------+------------------------------------+-------------------
 dual      | Superuser                          | {}
 dev1      |                                    | {pg_read_all_data}
 dev2      | Create role, Create DB             | {}
 postgres  | Superuser, Create role, Create DB..| {}

Kullanıcı silme

Kullanıcı silerken iki farklı metod kullanılabilir. Bunlardan ilki dropuser programıyla, diğeri ise SQL komutuyla.

İlk metodu kullanırken createdb programında olduğu gibi postgres kullanıcısı ile bir terminal açmamız gerekir.

sudo -u postgres /bin/bash
dropuser dev1

Kullanıcıyı silerken bir hata alınabilir. Oluşan hatalar kullanıcıyı silmenize engel olur. Bu yüzden engel olan sorunu çözmemiz gerekir. Örneğin silinmesini istediğimiz kullanıcı bir veritabanı ile hala ilişkili ise, ya veritabanını silmemiz gerekir ya da ilişkili olduğu veritabanının sahipliğini başka bir kullanıcıya devretmemiz gerekir.

sudo -u postgres psql
postgres=# \connect databaseName
postgres=# REASSIGN OWNED BY dev1 TO postgres;
postgres=# DROP OWNED BY dev1;

Yukarıdaki örnekte kullanıcının ilişkili olduğu veritabanına girdikten sonra, REASSIGN komutuyla sahipliği devrediyoruz, DROP ile de sahipliği siliyoruz. Eğer kullanıcının ilişkili olduğu bir veritabanı kalmadıysa artık başarılı olarak silinebilir.

İkinci metodda bu SQL komutuyla kullanıcıyı silebiliriz: DROP USER dev1; Fakat bence kullanıcının giriş yapma yetkisini almak, silmekten daha güvenli bir yoldur.