Kullanıcı ve Yetki Yönetimi

Kullanıcı Oluşturma

-- Basit kullanıcı
CREATE USER app_user WITH PASSWORD 'secure_password123';

-- Detaylı
CREATE ROLE app_user WITH
    LOGIN
    PASSWORD 'secure_password123'
    CREATEDB
    VALID UNTIL '2025-12-31'
    CONNECTION LIMIT 10;

Rol Özellikleri

Yetki Verme (GRANT)

-- Veritabanı yetkileri
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT ALL PRIVILEGES ON DATABASE myapp TO admin_user;

-- Schema yetkileri
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

-- Tablo yetkileri
GRANT SELECT ON users TO readonly_user;
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT ALL ON products TO admin_user;

-- Tüm tablolara
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Gelecekte oluşturulacak tablolara
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

Yetki İptal Etme (REVOKE)

REVOKE DELETE ON users FROM app_user;
REVOKE ALL ON products FROM temp_user;
REVOKE CONNECT ON DATABASE myapp FROM ex_employee;

Role Hierarchy

-- Rol oluştur
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;

-- Yetkileri rollere ver
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- Rolleri birbirine miras ver
GRANT readonly TO readwrite;
GRANT readwrite TO admin;

-- Kullanıcılara rol ver
GRANT readonly TO reporting_user;
GRANT readwrite TO app_user;
GRANT admin TO admin_user;

Kullanıcı Yönetimi

-- Şifre değiştir
ALTER USER app_user WITH PASSWORD 'new_password';

-- Özellik değiştir
ALTER USER app_user CREATEDB;
ALTER USER app_user NOCREATEDB;
ALTER USER app_user CONNECTION LIMIT 20;

-- Kullanıcı sil
DROP USER IF EXISTS temp_user;

-- Kullanıcıları listele
SELECT usename, usecreatedb, usesuper FROM pg_user;

-- Rolleri listele
\du  -- psql'de

Row-Level Security (RLS)

-- RLS etkinleştir
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy oluştur
CREATE POLICY user_orders ON orders
    FOR ALL
    USING (user_id = current_user_id());

-- Sadece okuma policy
CREATE POLICY view_own_data ON sensitive_data
    FOR SELECT
    USING (owner_id = current_user_id());