PostgreSql’de Schema Kavramı

Selamlar arkadaşlar bu yazımda sizlere PostgreSQL’de DB içerisinde tablolarda kullanılan Schema kavramından bahsediyor olacağım.

DB Cluster’lar da bir ya da daha fazla Database içerebilen PostgreSql, bu veritabanları içerisinde bir çok Role ve farklı türde Objeler paylaşır ve bunlar Global nesnelerdir. Client’lar PostgreSql’e bağlantı isteklerinde ilettikleri belirli bir DB’ye erişebileceklerdir.

Veritabanı tarafında ise, bir DB içerisinde birden fazla Schema olabilir ve bunlarla fazlaca tablo oluşturabilirler. Bu şemalar Data Types, Functions ve Operator’ler de içerebilirler. Farklı Schema adlarıyla benzer adlara sahip tablolarda oluşturulabilir. Bu tabloları ise bir birinden ayırmak için ise şema adları kullanılır. Örnek olarak Schema1 ve Schema2 olsun, bu iki şema adıyla Mytable adında iki adet table oluşturabiliriz.

Neden Şemaları kullanıyoruz derseniz ise;

1 – Birden çok kullanıcının, birbirine müdahale etmeden veritabanlarını kullanmasına izin vermek için kullanabiliriz.
2 – Veritabanı içerisindeki nesneleri mantıksal gruplara ayırarak daha iyi yönetebiliriz.
3 – 3rd Party uygulamaların oluşturduğu nesneler, DB içerisindeki diğer nesnelerin adlarıyla çakışmaması için kullanabiliriz.

Şemalar aslında OS düzeyinde dizin yapılarına benzetilebilir ancak iç içe oluşturulan dizinler olurken, Schema’lar da bu olmaz. Yani Schema içerisinde başka bir Schema oluşturamazsınız.

– Create Schema :

> CREATE SCHEMA myschema;

Komutuyula Şema oluşturabilirsiniz.

> create table myschema.testtable (no int, name text,created_at TIMESTAMPTZ DEFAULT Now());

Tablonuzu Şema’yla birlikte oluşturmanız için komudunu kullanabilirsiniz.

> DROP SCHEMA myfirst;

Komutu yardımıyla Şemanızı silersiniz. Benim örneğimde testtable bu şemayı kullandığı için silmeme izin vermedi.

> DROP SCHEMA myfirst CASCADE;

Şemanızda ekli nesnelerle birlikte silmek istersenizde bu kodu kullanırsınız. Bu sayede testtable’da şema ile birlikte silindi.

> CREATE SCHEMA myschema AUTHORIZATION semihtest;

semihtest kullanıcıma izinli myschema oluşturacaktım ancak buda kullanımda olduğundan oluşturmadı. Ama siz ayarlamalarınızı yaparken bu şekilde kullanıcıya izinli bir şema oluşturabilirsiniz.

NOT : Sistemde bulunan şemaların adları pg_ ile başlamaktadır. Bundan dolayı oluşturacağınız şemalarda bu ön eki kullanmamalısınız.

– List Schema :

> \dn

bağlı olduğunuz db’deki schema adlarını gösterecektir.

> select * from pg_namespace ;

Aynı şekilde ayrıntılı bilgi vererek Schemaları gösterecektir.

– Public Schema :

Eğer bir obje, tablo oluştururken herhangi bir schema belirtmezseniz, oluşturmuş olduğunuz nesne varsayılan olarak Public şema ile oluşturulacaktır. Public Schema ile oluşturulan her nesnenin ise tüm kullanıcılar tarafından yetkisi vardır, siz kaldırmadığınız sürece.

Bundan dolayı;

CREATE TABLE mytable ( … );
ile
CREATE TABLE public.mytable ( … );
komutları birbirleri ile eşdeğer olacaktır.

– Schema Search Path :

Bir kullanıcının Search_Path’inde yazan Schema’lara izini olduğu anlamına gelir. Sistemde herhangi bir işlem yaptığınızda ilk olarak gider ve Search_Path’de bu adı bulabiliyormuyum diye kontrol eder, sonrasında ok alması durumunda işlemi gerçekleştirir. Bu sayede kullanıcıların erişebileceği Schema’ları bile ayarlayabiliriz. Ayarlamaları yaptıktan sonra o kullanıcı Schema üzerinde işlemler yapmaya başlayabilir.

> SHOW search_path;

Komutuyla kullanıcıya ekli olan şemalara bakarız. “$user” cümlesi aslında kullanıcı adı ile aynı şemayı arar ve eğer oluşturmadıysanız Varsayılan Şema’yı Public olarak belirleyecek ve oluşturulan nesneleri Public şeması ile oluşturacaktır.

> SET search_path TO myschema,”$user”,public;

Komutu yardımıyla gerekli şema düzenlemesi yapabilirsiniz.

> SELECT current_schema();

Komutuylada varsayılan şemanızı görebilirsiniz. Set komutu ile sırayı değiştirdiğimiz için yukarıdaki şekilde myschema’nın varsayılan şema olduğunu görebiliriz. Bu durumla birlikte artık oluşturulan nesneler Public şeması ile değil myschema şeması ile oluşturulacaktır.

– Schema and Privileges :

Varsayılan olarak kullanıcılar izinleri olmayan schemadaki hiçbir şeye erişemezler. Bu erişimi alabilmeleri için, Schema sahipleri şema üzerinde USAGE ayrıcalığını vermesi gerekir. Public şemada ise varsayılan olarak bu yetki herkes için vardır. Kullanıcıların bir şemadaki nesneleri kullanabilmesi için, nesneye uygun olarak ayrıcalıkların verilmesi gerekebilir.

USAGE schemaya verilen bir yetkilendirmedir.

Başkasına ait bir şemanın üzerinde, farklı bir kullanıcının işlem yapabilmesi için şema üzerinde CREATE ayrıcalığının tanımlanması gerekir. PostgreSQL 14 veya öncesi bir sürümden yükseltilen veritabanlarında, Public Schema da herkes bu ayrılacalığa sahiptir. bazı durumlarda iptal edilmesi gerekir.

postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

postgres=# GRANT CREATE ON SCHEMA public FROM PUBLIC;

Yukarıdaki kodda bulunan birinci public Schema adıdır ikinci PUBLIC ise herkes anlamına gelmektedir.

– System Catalog Schema :

Her veritabanı built-in data types, functions ve operators’leri içeren pg_catalog şemasını içerir. Bu pg_catalog’u Search Path’inizin en sonuna koymanız durumunda, oluşturduğunuz şema bu sistem şemasından daha üstün olacaktır.

Sistem şemaları pg_ ile başladığından dolayı şema adlarınızı kullanırken bu etiket ile başlamamanız gerekir.

– Kullanım Modelleri :

Sıradan kullanıcılar için, kullanıcıya özel şemalarla sınırlandırın. Bunu uygulamak için ilk önce REVOKE CREATE ON SCHEMA public FROM PUBLIC komutunu kullanın. Sonrasında kalıcı nesneler oluşturabilmesi için kullanıcı adıyla aynı ada sahip olan bir schema oluşturun. Bu sayede search path’de “$user” ile başlayan ayar geçerli olacaktır ve o şemada izni olacaktır.

DB admini dışında CREATEROLE yetkisine sahip kullanıcı olmamalıdır.

Postgresql.conf dosyasından ya da ALTER ROLE ALL SET search_path = “$user” komutunu kullanarak Public şemayı kullanıcılardan kaldırın. Sonrasında kullanıcıların hangi şemaya erişebileceğini belirlersiniz. CREATEROLE yetkisine sahip olmadıkları sürece güvenli bir yöntem olacaktır bu.

Evet arkadaşlar şema ile ilgili yazımın sonuna geldik. Oluşturma işlemlerini Youtube’a video olarak ilerleyen günlerde atacağım arkadaşlar. Hepinize kolaylıklar diliyorum.