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.