Schematy Postgres z ograniczonym dostępem i logowaniem zapytań

Czy musiałeś kiedyś przyznać „trochę” dostępu do swojej bazy danych? Powiedzmy, że miałby to być dostęp SELECT do pewnych tabel, ale także zapis tego, jakie zapytania uruchamia dany użytkownik.

Mieliśmy taki wymóg jakiś czas temu. Pierwsze dwie części są łatwe do osiągnięcia:

1. Częściowy dostęp: utwórz nową rolę użytkownika, wybierając tylko uprawnienia.
2. Dostęp do niektórych tabel: utwórz widok na wymagane tabele

Rejestrowanie wykonywanych zapytań nie jest takie proste, ponieważ w zapytaniu SELECT nie ma żadnych wyzwalaczy.

Brzydki trick

Musimy więc rejestrować, jakie zapytania są wykonywane. Być może będziemy potrzebować nowej tabeli, możliwie, że w innym schemacie oraz funkcji Postgres, aby wstawić rekord do tabeli z logami.

Tabela

Klasyczna struktura:

• id: serial, klucz główny
• user: varchar, użytkownik bazy danych, który wykonuje zapytanie
• sql: text, samo zapytanie
• ip: varchar, adres IP, z którego jest połączony użytkownik
• executed_at: timestamp, pełna data wykonania zapytania.

Nawiasem mówiąc, dobrym pomysłem podczas tworzenia obiektów bazy danych jest dołączenie komentarzy na temat użycia każdej kolumny (jeśli jest to tabela). Może to być dodatkowa praca, ale wszyscy będą Ci później wdzięczni.

CREATE TABLE public.query_log (
  id serial NOT NULL,
  "user" varchar(150) NULL,
  "sql" text NULL,
  ip varchar(25) NULL,
  executed_at timestamp NULL DEFAULT NOW(),
  CONSTRAINT query_log_pkey PRIMARY KEY (id)
);

Tabela query_log zostanie utworzona na schemacie publicznym, możesz chcieć to zmienić lub nie, w zależności od własnych potrzeb.

Funkcja

Jest to raczej prosty insert, ale potrzebujesz go jako funkcji, abyś mógł wykonać nasz trik.

-- DROP FUNCTION public.log_query_information;
CREATE OR REPLACE FUNCTION public.log_query_information(quser character varying, qsql text, qip character varying)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
BEGIN  
 INSERT INTO public.query_log
  ("user", "sql", ip)
  VALUES(quser, qsql, qip) ON CONFLICT DO NOTHING;
 RETURN TRUE;
END;
$function$
;

DROP jest zakomentowany na wypadek, gdybyś musiał później usunąć tę funkcję.

Są tu 2 ważne rzeczy. Po pierwsze zwracane jest true, ponieważ funkcja ta musi coś zwrócić (pamiętaj, to sztuczka). Po drugie, ON CONFLICT DO NOTHING: ma to na celu uniknięcie cięższych problemów.

Sugeruję, aby przed wypróbowaniem tego na środowiskach produkcyjnych, przetestować to lokalnie na instalacji Postgres na swoim komputerze lub na testowym serwerze Postgres działającym na dockerze.

W mojej osobistej misji unikania kopiowania i wklejania celowo pomijam ważną część, abyście doczytali ten artykuł do końca i dobrze go zrozumieli.

Jak widać zarówno tabela, jak i funkcja są tworzone na schemacie publicznym. Może to być kolejny schemat, ale sugeruję użycie innego schematu niż ten, który zamierzasz utworzyć dla widoków. Dla widoków mamy view schema.

Schemat i rola

Nowy schemat musi obejmować wszystkie widoki tabel, do których chcesz przyznać dostęp.

Pomijając kilka innych dodatków, nowa rola użytkownika musi mieć uprawnienia SELECT nad tabelami (poza schematem widoku) oraz uprawnienia INSERT do tabeli query_log.

-- Create the new schema, grant authorization to 
-- postgres user (change if needed)
CREATE SCHEMA "restricted_schema" AUTHORIZATION postgres;

-- Create new role
CREATE ROLE restricted_user NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN PASSWORD 'restricted_USER_passsword';

-- Grant SELECT access to original tables on internal schema
-- Will use schema: internal and table: company_users as example
GRANT SELECT ON TABLE internal.company_users TO restricted_user;

-- Grant INSERT on query_log and allow operations on sequence
GRANT INSERT ON TABLE public.query_log TO restricted_user;
GRANT ON SEQUENCE public.query_log_id
GRANT USAGE, SELECT ON SEQUENCE public.query_log_id_seq TO restricted_user;

-- Allows the user to access the restricted schema
GRANT USAGE ON SCHEMA restricted_schema TO restricted_user;

-- Removes all other permissions to the restricted user
REVOKE ALL ON ALL TABLES IN SCHEMA  pg_catalog FROM restricted_user;   
REVOKE ALL PRIVILEGES ON TABLE pg_catalog.pg_proc FROM restricted_user;
REVOKE ALL PRIVILEGES ON TABLE pg_views FROM restricted_user;
REVOKE SELECT ON TABLE pg_proc FROM restricted_user;
REVOKE SELECT ON TABLE pg_views FROM restricted_user;

-- Allow the execution of the function
GRANT EXECUTE ON FUNCTION public.log_query_information("varchar","text","varchar") TO restricted_user;

Widok

A oto i sztuczka: udostępniamy widok, jak zwykle, ale ostatnia kolumna z Twojego "view select" wywoła funkcję public.log_query_information (to jest powód, dla którego potrzebowaliśmy zwrócenia true).

-- DROP VIEW restricted_schema.company_users;
CREATE OR REPLACE VIEW restricted_schema.company_users
AS SELECT 
    pp.id,
    pp.code,
    pp.name,
    pp.last_name,
    pp.soc_sec_number,
    pp.created_at,
    pp.updated_at,
    pp.deleted_at,
    pp.company_id
from (SELECT p.id,
    p.code,
    p.name,
    p.last_name,
    p.soc_sec_number,
    p.created_at,
    p.updated_at,
    p.deleted_at,
    p.company_id,
    public.log_query_information(current_user::varchar, current_query(), inet_server_addr()::varchar) 
   FROM internal.company_users p
     LEFT JOIN internal.company cc ON cc.id = p.company_id AND cc.id = 123) AS pp;

Nasz widok ogranicza wybór tylko do rekordów z company_id = 123. Powinniście także zauważyć podzapytanie z aliasem as pp. Jest ono tylko po to, aby zachować czystość SELECT * z widoku,bez dziwnych wywołań funkcji tu i tam.

Trik: widok zwraca wszystkie żądane informacje, ale w każdym rekordzie wstawia również nowy rekord do naszej tabeli public.query_log.

Spróbuj samodzielnie.

Widzisz problem? Sprawdź jeszcze raz… To jeszcze nie koniec.

Załóżmy, że masz 50000 użytkowników firmowych. Select wewnątrz widoku wykonuje funkcję dla każdego rekordu. Insert również zostanie wykonany tyle razy. Za dużo rekordów query_log na jedno zapytanie, co nie jest dobre.

Jak rozwiązać ten problem: Pamiętacie ON CONFLICT DO NOTHING?

W niektórych przypadkach należy się upewnić, że wstawiany jest tylko jeden rekord logujący na zapytanie. Aby to zrobić, powinniśmy użyć unikalnych indeksów. 

Utwórz nowy indeks na wszystkich kolumnach, które widzisz. Są one powtarzane po wykonaniu dowolnego wyboru w widoku.

Powinno to wyglądać w następujący sposób:

CREATE UNIQUE INDEX query_log_unique_records ON public.query_log ("user","sql",ip,executed_at);

Zadziała to w większości przypadków, ponieważ użytkownik, zapytanie sql, IP połączenia i sygnatura czasowa exec_at powinny być takie same dla pojedynczego zapytania.

Jak zawsze Twój przypadek może się trochę różnić, ale mam nadzieję, że pokazałem jak stworzyć schemę tylko do odczytu z logowaniem zapytań. Może też Ci to pomóc jako obejście na brak triggerów związanych z SELECT.

Oryginał tekstu w języku angielskim przeczytasz tutaj.