Postgresql User & Role
Postgresql de kullanıcı yetkilendirme işlemleri database, schema, table, function, sequence vb.. bir çok obje bazında yetkilendirilebilir. Ayrıca SELECT, UPDATE, DELETE, INSERT vb bir çok yetkilendirme yapılabilir. Bir DBA temel görevlerinden biri kullanıcıları yetkilendirmedir. Daha sonra Junior bir developer database erişimine full yetkisini verirseniz. Database uçurma olasılığı yüksek. Başıma geldi :) Development ortamı da olsa kullanıcılarınızı yönetmek önemlidir.
User tanımlama ile Role tanımlama aynı kavramlara sahip değil.
User ile sisteme giriş yapacak kullanıcı tanımlarken, Role tanımı ile belli bir hazır taslağınız olup. Oluşturduğunuz kullanıcı bu taslağın içerisindeki yetkileri kullansın diye tanımlıyorsunuz. Ama Role üzerinden ayrıca login tanımlanabilir. Bir örnek üzerinden gidelim.
-- psql için listeleme formatını düzenliyoruz.
\x
-- İlk halini görmek için kullanıcıları listele.
\du
-- test_user kullanıcısı oluştur.
Create User test_user;
-- test_role role oluştur.
Create Role test_role;
-- Son halini görmek için kullanıcıları listele.
\du
“test_role” dikkat ettiyseniz Cannot Login ibaresi yer almakta. Role ile sistem üzerinden giriş yapamazsınız. Ayrıca “ALTER ROLE test_role WITH LOGIN;” komutu ile login özelliği tanımlamanız gerekir. Birde şifre tanımlayı unutmuşuz. Ama şimdi bu kullanıcı sisteme yıl sonuna kadar erişim sağlasın diyorsak “ALTER USER test_user WITH PASSWORD '123456' VALID UNTIL '2023-12-31 00:00:00';” komutunu kullanıyoruz.
Bir kaç örnek veritabanı, tablo ve veri oluşturup. İhtiyaç senaryoları üzerinden anlatmaya çalışacağım. Konu kapsamı çok geniş hepsini bir yazıya sığdırmakta zor. O yüzden belli başlı konular üzerinden gideceğiz. O zaman ilk önce ortamı hazırlayalım.
-- 2 tane veritabanı oluşturuyoruz. Bu veritabanların içerisine 2 tane
-- farklı şema ve tablo oluşturuyoruz.
-- psql için listeleme formatını düzenliyoruz.
\x
-- test_db1 veritabanını oluştur.
Create Database test_db1;
-- test_db2 veritabanını oluştur.
Create Database test_db2;
-- Veritabanlarını Listele.
\l+
-- test_db1 veritabanına geçiyoruz.
\c test_db1
-- testdb1_schema1 şema oluşturuyoruz.
Create Schema testdb1_schema1;
-- testdb1_schema2 şema oluşturuyoruz.
Create Schema testdb1_schema2;
-- Şemaları listele
\dn
-- testdb1_schema1 içerisine test_tb1 tablosu oluşturuyoruz.
Create Table testdb1_schema1.test_tbl (id serial, name varchar);
-- Test verilerini basıyoruz.
insert into testdb1_schema1.test_tbl select id, md5(random()::text) FROM generate_series(1,100) id;
-- testdb1_schema2 içerisine test_tb1 tablosu oluşturuyoruz.
Create Table testdb1_schema2.test_tbl (id serial, name varchar);
-- Test verilerini basıyoruz.
insert into testdb1_schema2.test_tbl select id, md5(random()::text) FROM generate_series(1,100) id;
-- test_db2 için ise test_db1 ne yaptıksak aynı işlemleri uyguluyoruz.
Elimizdeki envanter;
test_db1 →
testdb1_schema1 → testdb1_schema1.test_tbl
testdb1_schema2 → testdb1_schema2.test_tbl
test_db2 →
testdb2_schema1 → testdb2_schema1.test_tbl
testdb2_schema2 → testdb2_schema2.test_tbl
IT direktörünüz sizden full yetkili bir kullanıcı istedi. SUPERUSER yetki tanımlı bir kullanıcı oluşturuyoruz.
-- it_director adında SUPERUSER yetkilisi ile birlikte şifresinide tanımlıyoruz.
Create User it_director With Login SUPERUSER PASSWORD '123456';
Bu kullanıcı ile sistem üzerinde bütün veritabanlarına, şemalara, tablolara kısaca bütün objeleri erişim sağlayabilir ve değiştirebilir.
1. developer takımı biz “test_db1” veritabanına erişim istiyoruz. Diğer veritabanları bizim için önemli değil. Ancak “test_db1” bütün tablolara okuma ve yazma yetkisi istiyoruz. Hay hay DBA ne güne duruyor.
-- Sade bir kullanıcı oluşturalım.
Create User team1 password '123456';
-- Veritabanı Bağlantısı Yapabilsin.
GRANT CONNECT ON DATABASE test_db1 TO team1;
-- test_db1 veritabanına geçiyoruz.
\c test_db1
-- Schemalara Kullanma Yetkisi Ver.
GRANT USAGE ON SCHEMA testdb1_schema1 TO team1;
GRANT USAGE ON SCHEMA testdb1_schema2 TO team1;
-- Schemanın Altındaki Bütün Tablolara Okuma, Yazma, Düzenleme ve Silme
-- Yetkisi Ver.
GRANT SELECT, INSERT, UPDATE, DELETE ON
ALL TABLES IN SCHEMA testdb1_schema1 TO team1;
GRANT SELECT, INSERT, UPDATE, DELETE ON
ALL TABLES IN SCHEMA testdb1_schema2 TO team1;
-- team1 kullanıcısının yetkili olduğu tabloları listele.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'team1';
-- Ama ben sistemdeki bütün şemaları tek tek tanımlayamam derseniz.
-- Aşağıdaki script kullanın.
SELECT
'GRANT USAGE ON SCHEMA '|| schema_name ||' TO team1; ' ||
'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA '|| schema_name ||' TO team1;' ||
FROM
information_schema.schemata
WHERE
schema_name not in ('pg_toast','pg_catalog','information_schema');
IT direktörünüz 1. developer takımı artık “test_db1” ile çalışmayacak onların kullanıcısını sil sistemden dedi. Mümkünse en mantıklı şey şifresini değiştirmektir. Değil ise illa silmemiz gerekiyorsa aşağıdaki işlemlere geçiyoruz.
Postgresql üstünde bir kullanıcı oluşturmak ve silmek oldukça basit create user ve drop user. Ancak kullanıcıya bir yetkilendirme verdiyseniz bu işlem bu kadar basit değil. İlk önce kullanıcıda ki bütün yetkileri silmeniz gerekmektedir. Yukarıdaki aynı işlemleri yapıyoruz. Tek fark “grant” yerine “revoke”, “to” yerine ise “from” tanımlaması yapmak.
-- İlk hata mesajı almak için kullanıcıyı silmeyi deniyoruz.
Drop User team1;
-- Veritabanı Bağlantısı Yapabilsin.
REVOKE CONNECT ON DATABASE test_db1 FROM team1;
-- test_db1 veritabanına geçiyoruz.
\c test_db1
-- Schemalara Kullanma Yetkisi Ver.
REVOKE USAGE ON SCHEMA testdb1_schema1 FROM team1;
REVOKE USAGE ON SCHEMA testdb1_schema2 FROM team1;
-- Schemanın Altındaki Bütün Tablolara Okuma, Yazma, Düzenleme ve Silme
-- Yetkisi Ver.
REVOKE SELECT, INSERT, UPDATE, DELETE ON
ALL TABLES IN SCHEMA testdb1_schema1 FROM team1;
REVOKE SELECT, INSERT, UPDATE, DELETE ON
ALL TABLES IN SCHEMA testdb1_schema2 FROM team1;
-- team1 kullanıcısının yetkili olduğu tabloları listele.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'team1';
-- Kullanıcıyı sil.
Drop User team1;
-- Ama ben sistemdeki bütün şemaları tek tek tanımlayamam derseniz.
-- Aşağıdaki script kullanın.
SELECT
'REVOKE USAGE ON SCHEMA '|| schema_name ||' FROM team1; ' ||
'REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA '|| schema_name ||' FROM team1;' ||
FROM
information_schema.schemata
WHERE
schema_name not in ('pg_toast','pg_catalog','information_schema');
Yada daha tembel işi “team1" bütün yetkileri “postgres” kullanıcısına veya istediğiniz bir kullanıcıya aktarıp. Kullanıcıyı silebilirsiniz.
-- team1 deki bütün yetkileri postgres aktar.
REASSIGN OWNED BY team1 TO postgres;
-- team1 yetkilerlini sil.
DROP OWNED BY team1;
-- Kullanıcıyı sil.
Drop User team1;
Sizden bir kullanıcı istendi. Bütün veritabanlarına, şemalara, tablolara sadece okuma yetkisi olsun.
Postgresql 14 ve üstü bir sürüm kullanıyorsanız şanslısınız. Dahası için.
-- Bir kullanıcı oluşturalım.
Create User gozlemci password '123456';
-- Bu kadar artık instance altındaki bütün tabloları okuyabilecek.
grant pg_read_all_data to gozlemci;
-- Yada sadece yazma yetkisi istendi.
grant pg_write_all_data to gozlemci;Postgresql 14 altı bir sütüm kullanıyorsanız sistemdeki bütün şemaları listelemeye başlayın. Bu işlemi her bir veritabanı için yapmanız gerekmektedir.
-- Veritabanın altındaki bütün şemalara select yetkisi ver.
SELECT
'GRANT USAGE ON SCHEMA '|| schema_name ||' TO gozlemci; ' ||
'GRANT SELECT ON ALL TABLES IN SCHEMA '|| schema_name ||' TO gozlemci;' ||
FROM
information_schema.schemata
WHERE
schema_name not in ('pg_toast','pg_catalog','information_schema');
-- Yada sadece yazma yetkisi istendi.
SELECT
'GRANT USAGE ON SCHEMA '|| schema_name ||' TO gozlemci; ' ||
'GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA '|| schema_name ||' TO gozlemci;' ||
FROM
information_schema.schemata
WHERE
schema_name not in ('pg_toast','pg_catalog','information_schema');Senaryolar çok ben ihtiyacınız olabilecekleri sizlere aktarmaya çalıştım. Umarım sizlere de faydalı olmuştur.
