wykryto właśnie poważny błąd w postgresie w obsłudze funkcji typu “security definer". błąd tyczy się wszystkich wersji postgresa od kiedy to wprowadzono – czyli od 7.3.

błąd pozwala na wykonanie dowolnego kodu z podwyższonymi uprawnieniami i daje się wykorzystać podobno w przypadku większości funkcji zdefiniowany jako security definer (ale nie wszystkich).

na szybko kod można poprawić i zabezpieczyć ustawiając na sztywno w funkcji search_path'a.

prace nad poprawieniem tego trwają.

mysql ma fajną rzecz – drop table if exists.

pozwala to na pisanie bezpiecznych skryptów sql'owych które nawet przy obudowaniu w transakcję się prawidłowo wykonają.

przykład?

załóżmy, że chcemy stworzyć tabelę i cośtam jeszcze zrobić – wszystko w transakcji.

BEGIN;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

wygląda niewinnie.

no tak, ale co jeśli ta tabela już istnieje – bo np. ktoś ma zainstalowaną wcześniejszą wersję bazy? trywiał zmieniamy na:

BEGIN;
DROP TABLE test;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

i już działa.

ale nie. jeśli jednak odpalimy tego sql'a na bazie w której naszej tabeli nie ma – drop table sie nie uda, więc cała transakcja zostanie zrollbackowania.

no cóż, to może tak?

DROP TABLE test;
BEGIN;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

teraz – drop jest poza transakcją, więc jak się wywali to nie problem.

hmm .. ale co jeśli tabela test jest, ale nasze zapytania po create table się wywalą? tabela test zostanie permanentnie skasowana, a polecenie zakładające zostanie zrollbackowane. tragedia.

czy nie da się tego sensownie zrobić? da się.

trzeba użyć funkcji execute() o której pisałem wczoraj.

dzięki niej, mogę zrobić swojego sql'a tak:

BEGIN;
SELECT execute('DROP TABLE test') WHERE exists (SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'test');
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
COMMIT;

i uzyskuję w ten sposób funkcjonalny odpowiednik DROP TABLE … IF EXISTS;

oczywiście składnia jest trudniejsza, ale nie wydaje się to być dużym problemem – jeśli jest – no cóż. zawsze można napisać własną funkcję która zrobi ‘drop table' tylko jeśli tabela istnieje. ale wykorzystanie ogólnego mechanizmu ‘execute' wydaje się być zdecydowanie potężniejsze

co jakiś czas pojawia mi się potrzeba masowego nadania praw. np. – mam nowego człowieka i muszę mu dać prawa do odczytu wszystkich tabelek w bazie. niestety postgresql nie obsługuje składni typu ‘GRANT … ON *'.

cóż więc pozostaje. pl/pgsql 🙂

zacznijmy od podstaw. funkcja która daje uprawnienia do czytania wskazanych tabel danemu użytkownikowi:

CREATE OR REPLACE FUNCTION grant_select_to(in_username TEXT, in_table_regexp TEXT) RETURNS void as $BODY$
DECLARE
temprec RECORD;
use_sql TEXT;
BEGIN
FOR temprec IN SELECT c.relname FROM pg_class c join pg_namespace n on c.relnamespace = n.oid WHERE c.relkind = 'r' AND c.relname ~* in_table_regexp AND n.nspname = 'public' LOOP
use_sql := 'GRANT SELECT ON TABLE ' || temprec.relname || ' TO ' || in_username;
raise notice 'sql to run: [%]', use_sql;
EXECUTE use_sql;
END LOOP;
RETURN;
END;
$BODY$ language plpgsql;

i już. teraz można:

select grant_select_to('depesz', '.');

co nada użytkownikowi depesz prawa do wszystkich (pasujących do regexpa ‘.', czyli wszystkich) tabel (relname = ‘r') w schemie public (inne schemy to zazwyczaj rzeczy systemowe).

do tego funkcja wypisze wszystkie wykonane sql'e.

no tak, ale co zrobić gdy chcemy dac inne prawa? albo kilku różnym użytkownikom różne zestawy praw? no i jeszcze dochodzą schemy – jak z nich korzystamy, to nie wystarczy dać prawa do tabel w schemach – trzeba dać też prawo ‘USAGE' do schemy.

można oczywiście napisać bardzo fajną funkcję która to wszystko zrobi. ale może zamiast tego, podejdziemy do sprawy sprytniej.

najpierw – to dzięki czemu mogę w plpgsql'u robić takie sztuczki to fakt, że jest tam funkcja execute. której w zwykłym sql'u nie ma. dodajmy ją więc:

CREATE OR REPLACE FUNCTION execute(in_sql TEXT) RETURNS void as $BODY$
DECLARE
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;

co teraz?

zacznijmy od takiego sql'a:

select * from information_schema.tables where table_schema = 'public';

to nam wyselectuje jakieś tam dane nt. tabelek w schemie public.

a jak zmienię tego sql'a na:

select 'grant select on table ' || table_name || ' to depesz' from information_schema.tables where table_schema = 'public';

wygląda interesująco.

więc kolejna drobna modyfikacja:

select execute('grant select on table ' || table_name || ' to depesz') from information_schema.tables where table_schema = 'public';

efekt – działa. w dodatku – ponieważ zapytanie które wykonuję mogę dowolnie zmieniać, to nadawanie różnych praw staje sie dziecinnie proste. filtrowanie po nazwie tabeli – trywiał, wystarczy dodać “and table_name ~* ‘…'".

dodatkowo – funkcja execute() ma też inne zastosowania. ale o nich następnym razem.