Procedury składowane w PostgreSQL/Bezpieczeństwo

Przy tworzeniu procedury można podać dwie opcje związane z uprawnieniami:

SECURITY INVOKER [domyślnie]: procedura wywoływana jest z takimi uprawnieniami, jak aktualnie zalogowany użytkownik;
SECURITY DEFINER: procedura wywoływana jest z takimi uprawnieniami, jak użytkownik, który ją zdefiniował.

Dzięki drugiej opcji można częściowo lub całkowicie odizolować zwykłych użytkowników od tabel, i umożliwić modyfikację lub dostęp wyłącznie przez dedykowane funkcje. W szczególności mogą to być np. jakieś krytyczne ustawienia aplikacji, dane finansowe itp.

Ponadto można w chwili tworzenia funkcji określić ścieżkę wyszukiwania (search_path), ograniczając tym samym dostęp do innych schematów.

Funkcje CURRENT_USER i SESSION_USER

Czasem zachodzi konieczność np. logowania, kto używał procedury. Do tego celu należy używać wyłącznie funkcji SESSION_USER, która zawsze zwraca nazwę użytkownika.

Funkcja CURRENT_USER, trochę wbrew nazwie, wywołana w funkcji zdefiniowanej jako SECURITY DEFINER zwróci użytkownika, który jest właścicielem funkcji.

Przykład

W tym prostym przykładzie zostanie założona tabela pracowników, której tylko wybrane wiersze będą pokazywane użytkownikowi za pośrednictwem procedury.

CREATE TABLE pracownicy (
    imie        text,
    nazwisko    text,
    tajny       boolean
);

INSERT INTO pracownicy (imie, nazwisko, tajny) VALUES
    ('Jan',    'Kowalski', false),
    ('Tomasz', 'Nowak',    false),
    ('Anna',   'Kot',      false),
    ('James',  'Bond',     true)
;

CREATE FUNCTION lista_pracownikow() RETURNS TABLE(imie text, nazwisko text)
    LANGUAGE SQL
AS $$
    SELECT imie, nazwisko
      FROM pracownicy
     WHERE tajny = false;
$$
    SECURITY DEFINER
;

CREATE ROLE uzytkownik WITH LOGIN UNENCRYPTED PASSWORD '123';

-- użytkownik nie może nawet wykonać instrukcji SELECT
REVOKE ALL PRIVILEGES ON TABLE pracownicy FROM uzytkownik;

I przykładowa sesja

$ SELECT current_user;
 current_user 
--------------
 uzytkownik
(1 row)

$ SELECT * FROM pracownicy;
ERROR:  permission denied for relation pracownicy

$ SELECT * FROM lista_pracownikow();
  imie  | nazwisko 
--------+----------
 Jan    | Kowalski
 Tomasz | Nowak
 Anna   | Kot