PostgreSQL Database ve Table Boyutları

21 Şub

PostgreSQL veri tabanında zamanla veri boyutları artar ve disklerdeki boş alanlar azalır. Bakım yapılmak istendiğinde öncelikle tüketilen kaynaklar öğrenilmelidir.

  • Veritabanı disk üzerinde ne kadar yer kaplıyor.
  • Tablola disk üzerinde ne kadar yer kaplıyor.

Veritabanı toplam boyutunu veren sorgu;

select pg_size_pretty(pg_database_size('my_db'));

Bir tablonun boyutunu veren sorgu;

select pg_size_pretty(pg_total_relation_size('my_table'));

Bazı şemalardaki tablolara ait boyutları veren sorgu;

SELECT schemaname,
       tablename,
       pg_size_pretty(
           pg_total_relation_size(
               schemaname || '.' || tablename
           )
       ) size
FROM pg_catalog.pg_tables
WHERE schemaname in ('my_schema1', 'my_schema2')
ORDER BY schemaname, tablename;

SQL Tablo Kolonlarından C# class Property Nasıl üretilir?

28 Ağu

Entity Framwork gibi ORM araçları ile veritabanı işlemleri yaparken bir tablonun her kolonunu C# sınıfına bir property olarak atamak durumunda kalırız. Hele ki veritabanında çok fazla kolon varsa, tek tek property oluşturmak biraz sıkıcı olabilir. Bu drumda kolon isimleri adım adım C# property tipine dönüştürülebilir.

Postgresql’de bir tablodaki kolon isimlerini almak için gerekli sorgu şu şekildedir:

SELECT column_name
  FROM information_schema.columns
 WHERE table_schema = 'cms'
   AND table_name   = 'consumptions';

Result >

 id
 subscription_id
 created_at
 meter_number
 meter_value
 meter_reading_type

Kolon isimlerini elde ettikten sonra bunları değişik formatlarda çıktılar haline dönüştürebiliriz. SQL kodu ile c# class property üretmek de mümkündür.

SELECT 'public string ' || 
        replace(initcap(column_name),'_','') || 
        ' { get; set; }'
 FROM information_schema.columns
 WHERE table_schema = 'cms'
    AND table_name   = 'consumptions';

Result >
 public string Id { get; set; }
 public string SubscriptionId { get; set; }
 public string CreatedAt { get; set; }
 public string MeterNumber { get; set; }
 public string MeterValue { get; set; }
 public string MeterReadingType { get; set; }

Ancak bütün propert tipleri string oldu ve pek kullanışlı olmadı. Bir adım daha ilerleterek tipleri de data_type kolonundan elde edebiliriz.

SELECT 'public' ||
            (CASE
                WHEN data_type = 'uuid' THEN ' string '
                WHEN data_type = 'integer' THEN ' int '
                WHEN data_type = 'numeric' THEN ' double '
                WHEN data_type = 'timestamp without time zone' THEN ' DateTime '
                ELSE ' string '
            END) ||
       replace(initcap(column_name),'_','') ||
       ' { get; set; }'
  FROM information_schema.columns
 WHERE table_schema = 'cms'
   AND table_name   = 'consumptions';

Result >
 public string Id { get; set; }
 public string SubscriptionId { get; set; }
 public DateTime CreatedAt { get; set; }
 public int MeterNumber { get; set; }
 public double MeterValue { get; set; }
 public int MeterReadingType { get; set; }

Property’ler Column attribute ile biraz daha süslenerek Entity Framework’ün kolay mapping yapmasına imkan sağlanabilir.

SELECT '[Column("' || column_name || '")] '
       'public' ||
            (CASE
                WHEN data_type = 'uuid' THEN ' string '
                WHEN data_type = 'integer' THEN ' int '
                WHEN data_type = 'numeric' THEN ' double '
                WHEN data_type = 'timestamp without time zone' THEN ' DateTime '
                ELSE ' string '
            END) ||
       replace(initcap(column_name),'_','') ||
       ' { get; set; }'
  FROM information_schema.columns
 WHERE table_schema = 'cms'
   AND table_name   = 'consumptions';

Result >
 [Column("id")] public string Id { get; set; }
 [Column("subscription_id")] public string SubscriptionId { get; set; }
 [Column("created_at")] public DateTime CreatedAt { get; set; }
 [Column("meter_number")] public int MeterNumber { get; set; }
 [Column("meter_value")] public double MeterValue { get; set; }
 [Column("meter_reading_type")] public int MeterReadingType { get; set; }

SQL IN Operatoru ile Kapasiteden Fazla Çoklu Sorgulama

24 Haz

SQL sorgularında IN operatoru kullanarak WHERE şartı içerisinde çok fazla sayıda kriter belirleyerek sorgulama yapılabilmektedir.

SELECT 
   * 
FROM 
   Customers
WHERE 
   Country IN ('Germany', 'France', 'UK');

Bazı database engine’ler IN operatörü içerisinde kısıtlamalar getirir. Örneğin oracle IN içerisine default ayarlarda en fazla 1000 kayıt almaktadır. Bu gibi durumlarda kısıtlama ayarlarına müdahale edilemiyorsa IN operatorlerini OR ile birleştirerek kullanabilirsiniz.

SELECT 
   * 
FROM 
   Customers
WHERE 
   Country IN ('Türkiye', 'France', 'UK', ...) //1000 adet
   OR
   Country IN ('Italy', 'China', 'Russia', ...) //1000 adet
   OR
   Country IN ('Japan', 'Spain', 'Germany', ...) //1000 adet;

Bu yöntemi gerektirecek durumlara çok sık rastlanmasada dış kaynaklardan gelen Excel, XML, vs dosyalarından gelen verilerde arama yapmak durumunda kaldığınızda size bir çıkış yolu olabilir.

Entity Kalıtım Özelliğinin Veritabanında Temsil Edilmesi

11 Mar

Nesneye yönelik programlama(OOP-Object Oriented Programming) yönteminin üç temel özelliği bulunmaktadır. Bunlar;

  • Encapsulation (Kapsülleme)
  • Polymorphism (Çok biçimlilik)
  • Inheritance (Kalıtım)

Bu yazının konusu, inheritance(kalıtım) özelliğinin veri tabanında temsil edilmesidir. Kalıtım, bir sınıfın bazı özelliklerini üst sınıftan almasına denir.

Örneğin bir uygulamadaki kullanıcıları, bireysel ve kurumsal olarak ayırarak temsil etmek istediğimizde nesneye yönelik bir tasarım kullanarak aşağıdaki gibi bir yapı oluşturabiliriz.

public class User
{
     public int Id { get; set; }
     public string Username { get; set; }
     public string Password { get; set; }
     public string Email { get; set; }
}

public class IndividualUser: User {    
     public string FirstName { get; set; }     
     public string LastName { get; set; } 
} 

public class CorporateUser: User {     
     public string Title { get; set; }     
     public string NationalTaxNumber { get; set; } 
}

Görüldüğü üzere, tek bir class tipi kullanarak tüm özellikleri(property) içerisine doldurmadık. Bunun yerine, User adında bir ana class ve IndividualUser, CorporateUser isimli iki alt class oluşturduk. Çünkü User class içerisindeki özellikler diğer class’lar için ortaktır.

Bu yapının, sadece programlama tarafında temsil edilmesi bizim için yeterli olmayacaktır. İlgili kayıtların aynı zamanda veritabanına kaydedilebilmesi gerekmektedir. Bu nedenle, veritabanında bu yapıya uygun bir çalışma yapmak gerekmektedir. Veritabanı tasarımı yapılırken Normal Form(NF) kuralları dikkate alınmalıdır.

Normal form kurallarının ihmal edilmesinin iki sebebi olabilir. Birincisi kurallardan haberdar olmamak. İkincisi, kurallarla uğraşmadan olayın kod tarafında çözülebileceğinin düşünmektir.

Normal Form kuralları bazı durumlarda ihlal edilebilmektedir.

Tek tablo kullanarak kural ihlali

Kurumsal ve Bireysel kullanıcıları tek bir tablo içerisinde tutmak istediğinizde, tüm alanlar tek bir tablo içerisinde bulundurulacaktır. Bu durumda bireysel kullanıcılar için Title ve NatianalTaxNumber alanları boş kalacak, kurumsal kullanıcılar için FirstName ve LastName alanları boş kalacaktır. Tabloda bir kolon ya nullable ya da non-nullable özellikte olmalıdır. Nullable kolonlar, feature için henüz elde edilememiş ancak elde edildiğinde girilecek olan alanlar için kullanılır. Oysa burada FirsName, LastName, Title, NationalTaxNumber gibi alanlar kayıt türüne göre olmazsa olmaz alanlardır.

IdUsernamePasswordEmailFirstNameLastNameTitleNatiaonaTaxNumber
1bob1232b@mail.comBobSmith
2corp1235c@crp.comCorp4656512
Tek tablo kullanımı

Tek tablo çözümlerinde bazen 1NF kuralları hiçe sayılabilmektedir. Örneğin tasarım yapılırken, Name diye bir alan açarak bireysel kullanıcılar için ad ve soyad birleştirilerek bu alana yazılıp, kurumsal kullanıcılar için şirket adı aynı kolona yazılabilmektedir. Bunlar 1NF kuralına aykırıdır. 1NF kuralları;

  • Tekrarlanan sütun yapıları olmamalıdır.
  • Birden fazla bilgi tek bir sütunda olamaz.
  • Bir alan içerisindeki bilgi özel karakterlerle ayrılarak tutulmamalıdır.

İki tablo kullanarak kural ihlali

Eğer bireysel ve kurumsal kullanıcılar için iki ayrı tablo tutulursa;

IdUsernamePasswordEmailFirstNameLastName
1bob1232b@mail.comBobSmith
IndividualUsers
IdUsernamePasswordEmailTitleNatiaonaTaxNumber
1corp1235c@crp.comCorp4656512
CorporateUsers

Bu durumda, müşteri ile ilişkilendirilecek olan bir tabloda hangi Id bilgisi tutulacak? 1 numaralı id bilgisi hem kurumsal hem de bireysel kullanıcı tablosunda bulunmaktadır. Örneğin Sipariş tablosuna ilişki kurulmak istendiğinde, iki tablo ile birden ilişki kurulmak durumunda kalınacaktır.

Üç tablo kullanarak kalıtım özellikli tasarım

Kalıtım özelliğini veritabanına yansıtarak bir tasarım oluşturmak, hem SOLID prensiplerine hem de OOP mantığına uygun olacaktır.

IdUsernamePasswordEmail
1bob1232b@mail.com
2corp1235c@crp.com
Users
UserIdFirstNameLastName
1BobSmith
IndividualUsers
UserIdTitleNatiaonaTaxNumber
2Corp4656512
CorporateUsers

Bu tasarıma sayesinde, bir önceki tasarımdaki ilişki sorunu çözülmüş olacaktır. Siparis tablosu ile ilişki kurulacak tablo Users tablosu olacaktır.

Postgresql Databse Table Diagram (Datagrip)

Artık kullanıcılar tek bir merkez tablodan(users) Id bilgisi elde edecektir. Yeni bir kullanıcı türü geldiğinde sistemin mevcut özellikleri bozulmadan yeni özellik ekleme(extensibility) sağlanmış olacaktır. Örneğin iot_users gibi cihaz temelli bir kullanıcıyı sisteme eklemek kolay olacaktır. Bu sayede sistem open/closed prensibine uyugun olacaktır.

Ekstra Bilgi

Bu tasarıma göre users tablosunda user_type gibi tür bilgileri tutulmamaktadır. Sorgulamalarda kolaylık olsun diye bu tür bir alan açıldığında, eğer kurumsal bir kullanıcı eklenirken yanlışlıkla kullanıcı türü bireysel olarak işaretlenirse veri tutarlılığı bozulacaktır. Bunun yerine kullanıcıların türlerine göre sayılarını raporlamak için örnek bir sorgu aşağıdaki gibi oluşturulabilir;

select type, count(*)
from (
      (select 'Individual' as type  from individual_users)
        union all
      (select 'Corporate' from corporate_users)
        union all
      (select 'IoT' from iot_users)
     ) tables
group by (type);

Özet

Programlama dünyasında kullanılan teknik ve prensipler, veritabanı için bir engel değildir. Birinde OOP kuralları uygulanırken, diğerinde Normal Form kuralları uygulanarak doğru tasarımlar yapılabilmektedir. Programlama yaparken kolaylık olsun diye veya SQL sorgulamalarında kolaylık olsun diye hatalı tasarıma yönelmek veri bütünlüğünü ve tutarlılığını bozabilir.

Kullandığım Faydalı PostgreSQL Fonksiyonları

20 Oca

PostgreSQL veritabanında kullandığım ve herkese faydası olabilecek bazı istatistiksel ve yönetimsel fonksiyonları başlıklar halinde paylaşmak istiyorum. Yazı içerisindeki linklerden tüm fonksiyonlara ulaşabilirsiniz.

İstatistik Toplayıcı Fonksiyonlar

PostgreSQL vertabanının istatistik toplayıcısı, sunucu etkinliği hakkındaki bilgilerin toplanmasını ve raporlanmasını destekleyen bir alt sistemdir. Toplanan istatistikler kullanıcılara yönetimsel anlamda fayda sağlar.

pg_stat_activity

Veritabanında yürütülen işlemin mevcut faaliyeti ile ilgili bilgileri pg_stat_activity fonksiyonu gösterir. Gerçekleşen her işlemin bilgisi tek bir satırda listelenir. Sorgunun yapıldığı anda veritabanında hangi işlemlerin aktif olduğunu görüntüleyebilirsiniz. O anda yürütülmekte olan sorguları, sorguları hangi kullanıcının ne zaman başlattığını, process kimlik numarasını öğrenebilirsiniz.

select * from pg_stat_activity;
select * from pg_stat_activity order by query; // Sıralı

Sistem Yönetim Fonksiyonları

Sistemsel fonksiyonlar, veritabanını kontrol etmek ve izlemek için kullanılırlar.

current_setting(setting_name text [, missing_ok boolean ])

Bu fonksiyon, setting_name parametresi ile verilen ayarın değerini gösterir.

select current_setting('datestyle'); // ISO, MDY
select current_setting('timezone');  // UTC

set_setting(setting_name text, new_value text, is_local boolean)

Bu fonksiyon, setting_name adındaki ayarın değerini new_value değeri ile değiştirir. Eğer is_local değeri true olarak işaretlenirse yeni ayar yalnızca mevcut işlem için geçerli olacaktır. Mevcut oturumda geçerli olması için is_local false olarak işaretlenmelidir.

SELECT set_config('log_statement_stats', 'off', false);

pg_cancel_backend (pid integer)

Arka planda çalışan pid numaralı işlem kimliğine sahip olan sorguyu iptal eder. Örneğin ABC kullanıcısı farkında olmadan sistem kaynaklarını aşırı tüketen ve saatler süren bir sorgu çalıştırdı. Bunu iptal etmek için önce pg_stat_activity ile ilgili sorguyu bulduktan sonra ilgili sorguyu iptal edebilirsiniz. Ancak sadece superuser olanlar superuser olanların işlemlerini iptal edebilirler.

select pg_cancel_backend (1145);

pg_terminate_backend (pid integer)

Arka planda çalışan pid numaralı işlem kimliğine sahip olan oturumu sonlandırır. Ancak sadece superuser olanlar superuser olanların oturumlarını sonlandırabilir.

select pg_terminate_backend (1145);

PostGIS GeoJSON Query

29 May

PostGIS ile SQL sorguları kullanarak GeoJSON sonuçları üretmek mümkündür. Bu işlem için gerekli olan PostgreSQL fonksiyonları:

  • jsonb_build_object()
  • jsonb_agg()
  • jsonb_build_object()
  • ST_AsGeoJSON()
  • to_jsonb

SQL sorgusu:

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
    SELECT jsonb_build_object(
      'type',       'Feature',
      'id',         gis_id,
      'geometry',   ST_AsGeoJSON(shape)::jsonb,
      'properties', to_jsonb(inputs) - 'shape' - 'gdb_geomattr_data'
      ) AS feature
    FROM (
          SELECT 
               * 
          FROM 
              sde.icmesuyu_hatlari 
          WHERE 
              objectid=121710
    ) inputs
) features;

Sorgu sonucu:

{
  "type": "FeatureCollection",
  "features": [
    {
      "id": "a71bca31-22d1-11e8-8590-0050568bf4f1",
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [
          458791.489,
          4520951.706,
          0
        ]
      },
      "properties": {
        "layer": "N_VANA_SİSTEM",
        "cbs_id": "811bca31-22d1-11e8-8590-0050568bf4f1",
        "aciklama": "AKTİF",
        "objectid": 121710,
        "zemin_kot": 43.57
      }
    }
  ]
}

PostGIS Kurulumu

26 May

PostGIS bir PostgreSQL uzantısıdır. PostgreSQL üzerinde bir veritabanı üzerine eklenebilir. Burada dikkat edilmesi gereken, PostgreSQL kurulu sırasında oluşturulan postgis veritabanı üzerine kurulmamasıdır.

Ancak postgis uzantısının işletim sistemi üzerinde bulunması gerekmektedir. Bu yazıda Ubuntu üzerinde PostgreSQL 12 veritabanına PostGIS 3 sürümü kurulmaktadır. Bunun için öncelikle repository version kontrolü yapmak gerekmektedir.

apt-cache policy postgis

Bu komut ile yülenecek sürümün Candidate: 3.0.1+dfsg-2.pgdg18.04+1 olduğunu öğreniyoruz.

PostGIS’i işletim sistemine yükleme komutu:

sudo apt-get install postgis

Artık PostGIS, Ubuntu üzerine indirildiğine göre, veritabanına eklenebilir durumdadır. Psql veya pgAdmin üzerinden veritabanına bağlanarak postgis uzantısını oluşturan SQL komutunu çalıştırdığınızda eklenti kurulacaktır.

CREATE EXTENSION postgis;

PostGIS raster desteği için gerekli SQL komutu:

CREATE EXTENSION postgis_raster;

PostGIS topoloji desteği için gerekli SQL komutu:

CREATE EXTENSION postgis_topology;

PostGIS ileri seviye 3D desteği ve sfcgal algoritması gibi diğer coğrafi veri işleme yetenekleri için gerekli SQL komutu:

CREATE EXTENSION postgis_sfcgal;

Tiger için bulanık eşleme gerekliyse:

CREATE EXTENSION fuzzystrmatch;

Kural tabanlı standartlaştırma için:

CREATE EXTENSION address_standardizer;

US Tiger Geocoder uzantısı için:

CREATE EXTENSION postgis_tiger_geocoder;

PostGIS Nedir?

25 May

PostGIS, GIS (Coğrafi Bilgi Sistemleri – CBS) nesnelerinin veritabanında depolanmasına izin veren bir PostgreSQL veritabanı uzantısıdır (extension). Tamamen ücretsizdir ve açık kaynak kodludur. PostGIS, GiST tabanlı R-Tree uzamsal dizinleri için destek ve GIS nesnelerinin analizi ve işlenmesi için işlevler içerir. GIS nesnelerinin analizi ve işlenmesi için fonksiyonlar içerir.

PostGIS, temel konumsal işlevlere ek olarak, Oracle Locator/Spatial ve SQL Server gibi diğer uzamsal veritabanlarında nadiren bulunan birçok özellik sunar. Bu özellikler listesine buradan ulaşabilirsiniz.

PostGIS Özellikleri

Tamamen SQL gücünü kullanarak vektör ve raster verileri üzerinde splicing (yapıştırma), dicing (kare/tile şeklinde bölme), morphing(dönüştürme), reclassifying(yeniden sınıflandırma), ve collecting(toplam)/unioning(birleştirme) gibi işlemler yapılabilir.

Raster üzerinde işlemler yapılabilmektedir.

Raster ve vektör verileri üzerinde SQL sorguları ile projeksiyon dönüşümleri yapılabilmektedir.

Hem komut satırı hem de GUI paketli araçlar yoluyla ESRI shape vektör verilerini içe ve dışa aktarma yanında diğer 3.parti açık kaynak araçlar aracılığıyla farklı formatları da desteklmektedir.

GeoTiff, NetCDF, PNG, JPG gibi birçok standart formattaki raster verilerini içe aktarmak için hazırlanmış komut satırı bulunmaktadır.

SQL kullanarak KML, GML, GeoJSON, GeoHash ve WKT gibi standart metin formatlı verileri içe aktarma fonksiyonları bulunmaktadır.

SQL kullanara raster verilerini GeoTIFF, PNG, JPG, NetCDF gibi çeşitli standart formatlarda oluşturma.

Ağ Topolojisi desteği bulunmaktadır.

3D nesne desteği, spatial index ve fonksiyonlar bulunmaktadır.

SQL fonksiyonları ile kusursuz bir şekilde rester/vektör piksel değerlerinin geometrik bölgeye göre çıkarılması, bölgelere göre istatistikler çalıştırılması, raster veriyi belli bir geometriyle kırpma gibi özellikleri bulunmaktadır.

Açık Kaynak Masaüstü Düzenleme/Görüntüleme Araçları

QGIS

PostGIS dahil olmak üzere birçok mekansal formatı destekler. Python uzantısı modeliyle PostGIS ile kullanılan masaüstü araçlarının en popüler olanıdır.

OpenJump

Java tabanlı bir yazılımdır. Bazı PostGIS de dahil olmak üzere birçok coğrafi mekansal formatı destekler.

uDig

Bazı PostGIS ve Oracle, SQL Server, ArcSDE gibi diğer uzamsal veritabanları dahil olmak üzere destek sunan Java tabanlı bir yazılımdır. Ana odak noktası OpenGIS standartlarına uymaktır.

gvSig

Java tabanlıdır ve işlevselliği QGIS’e benzer.

TileMill

Tile image görüntülerini işlemek için çok kullanışlıdır. Mapbox firmasına ait bir üründür. Ancak geliştirilmesi durdurulmuştur.

Açık Kaynak Haritalama Sunucuları

PostGIS ile en sık kullanılan sunucular aşağıda listelenmiştir. Bunların çoğu OpenGIS Konsorsiyumu (OGC) haritalama standartlarına uygundur. Harita sunucularının desteklediği formatlar şu şekildedir:

  • WMS – Web Map Service
  • WFS – Web Feature Service
  • WFS-T – Web Feature Service Transactional (ability to edit)
  • WCS – Web coverage service
  • WPS – Web Processing service
  • WMTS – Web Map Tile service

Mapserver

PostGIS’i destekleyen ve hala en popüler sunuculardan biri olan, C Tabanlı ilk harita sunucusu.

GeoServer

Mapserver ile benzer işlevlere sahiptir ve Web arayüzüne sahip bir harita sunucusudur. Yeni başlayanlara ve Enterprise GIS çalışanlarına ArcGIS Server gibi imkanlar sunar. OGC WMS, WFS, WFS-T’yi destekler.

Deegree

Java tabanlı coğrafi veri yönetimi, veri erişimi, görselleştirme, keşif ve güvenlik dahil olmak üzere bir çok yönetim imkanı sağlar.

QGIS Server

QGIS masaüstüne destek olarak, QGIS workspaces yayınlarını oluşturmak için kullaılır.

MapGuide 

Kullanıcıların web haritalama uygulamalarını ve geospatial web hizmetlerini hızlı bir şekilde geliştirmelerini ve dağıtmalarını sağlayan web tabanlı platformdur.

PostGIS ile ilgili PostgreSQL Uzantıları

pgRouting

PostGIS’e sürüş mesafesi, en kısa yol, gezgin satıcı gibi hız ve dönüş kısıtlamaları ile çeşitli maliyetleri göz önünde bulundurarak çözüm yapan ve coğrafi spatial rota yönlendirme yapan özellikler katar.

ogrfdw

Postgresql harici veri paketleyicisi.

GDAL/OGR

PostgreSQL’de diğer spatial ve spatial olmayan veri kaynaklarının tablo olarak okunmasına izin verir. Vektör verileri PostGIS geometri tipine çevrir.

pgpointcloud

Point Cloud verilerini PostgreSQL’de depolamak için bir PostgreSQL uzantısı ve yükleyicisi.

Postgresql DDL, DML, DCL, TCL Kavramları

24 May

Veri tabanı yönetim sistemlerinde, veriyi tanımlama, işleme, kontrol etme ve veri tabanına işlem yapma gibi aktiviteler için diller tanımlanmıştır.

  • DDL-Data Definition Language
  • DML-Data Manipulation Language
  • DCL-Data Control Language
  • TCL-Transactional Control Language

DDL – Data Definition Language

DDL bildirim yapan bir yapıya sahiptir ve table, view, index, namespace, tablespace, database, function, trigger ve user gibi veritabanı nesnelerini oluşturmamıza, değiştirmemize ve kaldırmamıza imkan tanır. Başlıca DDL işlemleri:

  • CREATE
  • DROP
  • RENAME
  • ALTER
  • TRUNCATE

DML – Data Manipulation Language

DML ifadeleri, veritabanındaki verilerle işlem yapmaya yarar.

  • SELECT
  • UPDATE
  • INSERT
  • DELETE

DCL – DATA CONTROL LANGUAGE

Kullanıcı yetki ve izinleri üzerinde işlem yapmaya yarar.

  • GRANT
  • REVOKE

TCL – TRANSACTIONAL CONTROL LANGUAGE

Yapılan işlemlerin kontrolünü sağlamaya yarar.

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

PostgreSQL GRANT, REVOKE

23 May

Bir önceki yazıda PostgreSQL kullanıcı işlemlerinin nasıl yapıldığından bahsetmiştik. Bu yazıda ise PostgreSQL kullanıcısının, erişim izinlerini düzenleyen DCL (Data Control Language) ifadelerden bahsedilmektedir. PostgreSQL DCL ifadeleri şu şekildedir:

  • GRANT
  • REVOKE

Aşağıdaki örneklerde bir kullanıcı (username) için yetki verme ve alma işlemi yapılsa da, username yerine bir grup adı da kullanılabilir. Bu şekilde, kullanıcı grubuna dahil olan bütün kullanıcılar yetkilendirilmiş olur.

Bir kullanıcının yetki işlemlerini yapabilmesi için öncelikle superuser yetkilerine sahip olması gerekmektedir.

Superuser Durum Değişimi

Kullanıcıyı superuser durumuna getir.

ALTER USER myuser WITH SUPERUSER;

Kullanıcının superuser durumunu kaldır.

ALTER USER username WITH NOSUPERUSER;

Veritabanı Yetkileri

Kullanıcıya, veritabanı oluşturma yetkisi ver.

ALTER USER username CREATEDB;

Kullanıcıya, veri tabanına bağlanma yetkisi ver.

GRANT CONNECT ON DATABASE database_name TO username;

Kullanıcıdan veritabanı bağlanma yetkisini geri al.

REVOKE CONNECT ON DATABASE database_name FROM username;

Kullanıcıya, veritabanındaki tüm ayrıcalıkları ver.

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Kullanıcıdan, veritabanındaki tüm ayrıcalıkları geri al.

REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

Schema ve Object Yetkileri

Kullanıcılar, şema içerisinde bulunan table, column, sequence gibi nesneler üzerinde yetkilendirilebilirler.

Kullanıcıya şema kullanım yetkisi ver.

GRANT USAGE ON SCHEMA schema_name TO username;

Kullanıcıdan şema kullanım yetkisini geri al.

REVOKE USAGE ON SCHEMA schema_name FROM username;

Kullanıcının, bir tablo üzerinde SELECT, INSERT, UPDATE, DELETE gibi DML(Data Manipulation) ifadelerini kullanım yetkilerini vermek mümkündür.

Kullanıcıya şemadaki bütün tablolar için yetki ver.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES 
IN SCHEMA schema_name TO username;

Kullanıcının şemadaki bütün tablolarda bulunan yetkilerini geri al.

REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES 
IN SCHEMA schema_name FROM username;

Kullanıcıya tek bir tabloya için yetki ver.

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name 
IN SCHEMA schema_name TO username;

Bir şemeda bulunan bütün tablolar için, kullanıcıya bütün yetkileri ver.

GRANT ALL PRIVILEGES ON ALL TABLES 
IN SCHEMA schema_name TO username;

Bir şemeda bulunan bütün tablolara ait bütün yetkileri kullanıcıdan geri al.

REVOKE ALL PRIVILEGES ON ALL TABLES 
IN SCHEMA schema_name FROM username;

Bir şemeda bulunan bütün dizeler(sequence) için, bütün yetkileri ver.

GRANT ALL PRIVILEGES ON ALL SEQUENCES 
IN SCHEMA schema_name TO username;

Bir şemeda bulunan bütün dizeler(sequence) için, bütün yetkileri geri al.

REVOKE ALL PRIVILEGES ON ALL SEQUENCES 
IN SCHEMA schema_name FROM username;

Yukarıdaki şema içi yetkiler, veritabanına mevcut olan tablolar için geçerlidir. Eğer şemada yeni oluşturulan tablolar için de kullanıcıya tüm yetkiler vermek için şöyle bir ifade kullanılabilir:

ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT ALL ON TABLES TO username;

Tablo üzerindeki yetkilerin sorgulanması

Bir tabloda hangi kullanıcıların hangi yetkilere sahip olduğunu öğrenebilmek için gerekli komut:

SELECT 
    grantee, 
    string_agg(privilege_type, ', ') AS privileges
FROM 
    information_schema.role_table_grants
WHERE 
    table_name='table_name'
GROUP BY 
    grantee;