Blog JSystems - uwalniamy wiedzę!

Szukaj







Rozszerzenie PG_AUDIT umożliwia śledzenie działań użytkowników, a także zmian i selectów na tabelach.


Z tego artykułu dowiesz się:


  • co to jest pg_audit i w jakim celu się go stosuje,
  • jakie są alternatywy dla pg_audit,
  • jak zainstalować pg_audit na Ubuntu,
  • jak zainstalować pg_audit na systemach z rodziny Red Hat,
  • jak za pomocą pg_audit audytować wybrane obiekty.



Audytowanie w PostgreSQL


PostgreSQL udostępnia zestaw funkcjonalności umożliwiających skonfigurowanie podstawowego audytowania aktywności do logu. Między innymi audytowanie połączeń do bazy za pomocą parametrów: log_connection, log_disconnection, audytowanie zapytań dzięki parametrom: log_statement czy audytowanie cięższych zapytań: log_temp_files, log_lock_waits. Rozwiązanie to nie wymaga instalowania żadnych dodatkowych rozszerzeń, nie tworzy nowych obiektów i triggerów w bazie, dzięki temu, że wyniki są zapisywane poza klastrem, w pliku loga, nie powoduje obciążenia Postgresa dodatkowymi transakcjami i obiektami, które muszą być obsłużone przez autovacuum. Ale z drugiej strony, nie ma możliwości ograniczenia audytowanych obiektów. Zawsze zapisujemy aktywność w całej instancji, dla wszystkich baz, przez wszystkich użytkowników, przez co dobrze sprawdza się tylko dla baz o małej aktywności. Przy bazach z większym obciążeniem metoda ta może wygenerować bardzo dużo wpisów do pliku loga, nawet kilkadziesiąt GB na godzinę lub więcej.


Innym bardzo prostym sposobem na stworzenie audytowania obiektów, również bez instalacji żadnych dodatkowych rozszerzeń, jest skorzystanie z triggerów. Tutaj już z możliwością wskazania obiektów, które będę audytował.

Na początek stworzę tabelę, do której będę logował wszystkie zapytania na wybranych obiektach.


CREATE TABLE audyt.dziennik_aktywnosci (
schemat text NOT NULL,
tabela text NOT NULL,
uzytkownik text,
typ text,
data TIMESTAMP WITH TIME zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
stara_wartosc text,
nowa_wartosc text,
zapytanie text
);

Następnie tworzę funkcję, którą będziemy wywoływać triggerem po każdej wykonanej operacji DML (Data Modification Language - INSERT, UPDATE, DELETE).


CREATE OR REPLACE FUNCTION audyt.zmiana_wartosci() RETURNS TRIGGER AS $body$
DECLARE
stara_wartosc TEXT;
nowa_wartosc TEXT;
BEGIN
IF (TG_OP = 'UPDATE') THEN
stara_wartosc:= ROW(OLD.*);
nowa_wartosc:= ROW(NEW.*);
INSERT INTO audyt.dziennik_aktywnosci (schemat,tabela,uzytkownik,typ,stara_wartosc,nowa_wartosc,zapytanie)
VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),stara_wartosc,nowa_wartosc, current_query());
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
stara_wartosc:= ROW(OLD.*);
INSERT INTO audyt.dziennik_aktywnosci (schemat,tabela,uzytkownik,typ,stara_wartosc,zapytanie)
VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),stara_wartosc, current_query());
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
nowa_wartosc:= ROW(NEW.*);
INSERT INTO audyt.dziennik_aktywnosci (schemat,tabela,uzytkownik,typ,nowa_wartosc,zapytanie)
VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),nowa_wartosc, current_query());
RETURN NEW;
ELSE
RAISE WARNING '[audyt.zmiana_wartosci] - Other action occurred: %, at %',TG_OP,now();
RETURN NULL;
END IF;

EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[audyt.zmiana_wartosci] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[audyt.zmiana_wartosci] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[audyt.zmiana_wartosci] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, audyt;


Stworzę trigger dla tabeli, którą chcę audytować.


CREATE TRIGGER audyt_test
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE audyt.zmiana_wartosci();

Po wykonaniu każdej operacji podanej w triggerze do tabeli audyt.dziennik_aktywnosci zostanie dodany nowy wpis.


SELECT * FROM audyt.dziennik_aktywnosci ;
-[ RECORD 1 ]-+---------------------------------
schemat | public
tabela | test
uzytkownik | postgres
typ | I
data | 2023-05-19 19:55:28.370979+00
stara_wartosc |
nowa_wartosc | (1,2)
zapytanie | INSERT INTO test VALUES (1,2);
-[ RECORD 2 ]-+---------------------------------
schemat | public
tabela | test
uzytkownik | postgres
typ | U
data | 2023-05-19 19:56:06.715819+00
stara_wartosc | (2,2)
nowa_wartosc | (2,2)
zapytanie | UPDATE test SET x=2 WHERE y = 2;
-[ RECORD 3 ]-+---------------------------------
schemat | public
tabela | test
uzytkownik | postgres
typ | D
data | 2023-05-19 19:56:57.534169+00
stara_wartosc | (2,2)
nowa_wartosc |
zapytanie | DELETE FROM test WHERE x=2;
(3 rows)

Od razu można zauważyć, że tutaj audytować można tylko zmiany wartości w tabeli (AFTER INSERT OR UPDATE OR DELETE). Nie ma możliwości wywołania triggera dla zapytań, które tylko odczytują dane. Kolejnym problemem jest fakt, że każda transakcja zmieniająca dane w audytowanej tabeli zostanie wydłużona o czas potrzebny na wywołanie funkcji, co przy często modyfikowanej tabeli może się przełożyć sumarycznie na znacznie niższą liczbę transakcji na sekundę.


PgAudit - zaawansowane audytowanie obiektów i sesji użytkowników


PgAudit rozszerza możliwości audytowania w Postgresie o bardziej precyzyjną kontrolę nad logowaniem sesji użytkowników i obiektów. Logowanie odbywa się do pliku na dysku. Rozwiązanie powyższe jest dużo lżejsze niż to z wykorzystaniem triggerów, więc jego wpływ na audytowaną instancję jest znacznie mniejszy.


Pakiety instalacyjne dla rozszerzenia dostępne są w oficjalnych repozytoriach PGDG (PostgreSQL Global Development Group).


### Ubuntu/Debian
sudo apt-get -y install postgresql-15-pgaudit

### RHEL/CentOS
sudo yum install -y pgaudit17_15.x86_64


Przed stworzeniem rozszerzenia w bazie muszę je dodać też do parametru “shared_preload_libraries” i zrestartować instancję.


postgres@vagrant:~$ psql -c "ALTER SYSTEM SET shared_preload_libraries=pg_cron,pgaudit"

# restart na Debian/Ubuntu
/usr/lib/postgresql/15/bin/pg_ctl -D /katalog/pg/data restart -m fast

# restart na RHEL/Centos
/usr/pgsql-15/bin/pg_ctl -D /katalog/pg/data restart -m fast


Po restarcie upewniam się, że rozszerzenie zostało poprawnie załadowane.


SHOW shared_preload_libraries;
shared_preload_libraries
-----------------------------
pg_cron, pgaudit

SELECT * FROM pg_available_extensions WHERE name LIKE '%audit';
name | default_version | installed_version | comment
---------+-----------------+-------------------+---------------------------------
pgaudit | 1.7 | | provides auditing functionality


Teraz mogę przejść do stworzenia rozszerzenia w bazie oraz ustawienia poziomu audytowania.


CREATE EXTENSION pgaudit;

SELECT * FROM pg_available_extensions WHERE name LIKE '%audit';
name | default_version | installed_version | comment
---------+-----------------+-------------------+---------------------------------
pgaudit | 1.7 | 1.7 | provides auditing functionality

ALTER SYSTEM SET pgaudit.log TO 'read, write';

SELECT pg_reload_conf();

SHOW pgaudit.log;
pgaudit.log
-------------
read, write


Parameter pgaudit.log przyjmuje następujące wartości:



  • READ - zapytań odczytujących dane (SELECT, COPY),

  • WRITE - zapytań zmieniających dane (INSERT, UPDATE, TRUNCATE oraz COPY, jeżeli miejscem docelowym jest relacja w bazie),

  • FUNCTION - funkcji oraz bloków DO,

  • ROLE - roli i użytkowników (GRANT, REVOKE, CREATE/ALTER/DROP ROLE),

  • DDL (zmiany obiektów, które nie należą do klasy ROLE),

  • MISC (pozostałe, np. DISCARD, FETCH, CHECKPOINT, VACUUM),

  • ALL - wszystkie powyższe.


Wartości można podawać oddzielone przecinkiem, na powyższym przykładzie audytowane będą wszystkie zapytania, które odczytują i zmieniają dane (READ, WRITE). Można też użyć wartości 'all, -misc, -ddl', za pomocą którego rozpocznę audytowanie wszystkiego oprócz MISC oraz DDL.


Poziom audytu można ustawić też dla każdego z użytkowników oraz dla każdej bazy z osobna, na przykład:


ALTER USER superuser SET pgaudit.log TO 'read, write, ddl';
ALTER DATABASE testdb SET pgaudit.log=read,write,function;

Po włączeniu audytowania Postgres zacznie dodawać do swojego pliku loga dodatkowe wpisy:


postgres@vagrant:~/15/main$ less /var/log/postgresql/postgresql-15-main.log
2023-05-19 19:51:00.153 UTC [5458] test@postgres LOG: AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1,<not logged>
2023-05-19 19:51:12.648 UTC [5400] postgres@postgres LOG: AUDIT: SESSION,38,1,WRITE,INSERT,,,"INSERT INTO test VALUES (1,2);",<not logged>
2023-05-19 19:51:58.831 UTC [5400] postgres@postgres LOG: AUDIT: SESSION,43,1,WRITE,UPDATE,,,UPDATE test SET x=2 WHERE y = 2;,<not logged>

<not logged> na końcu każdej linii oznacza, że pgaudit nie loguje wartości parametrów dla zapytań. Jeżeli potrzebowałbym logować parametry zapytań, można je dodać za pomocą parametru pgaudit.log_parameter = on, po czym należy przeładować konfigurację. Parametry pojawią się na końcu linii, oddzielone przecinkiem, a dla zapytań bez parametrów dodana zostanie końcówka <none>.


2023-05-19 20:12:00.043 UTC [1683] LOG:  AUDIT: SESSION,181,1,WRITE,INSERT,,,"INSERT INTO cron.job_run_details (jobid, runid, database, username, command, status) VALUES ($1,$2,$3,$4,$5,$6)","1,5273,postgres,test,SELECT 1,starting"
2023-05-19 20:12:00.057 UTC [5556] test@postgres LOG: AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1,<none>

Audytowanie poszczególnych obiektów


PgAudit umożliwia logowanie zapytań na wybranych obiektach. Aby z niego skorzystać, należy stworzyć dedykową rolę, która zostanie “audytorem” i dodać ją do parametru pgaudit.role. Zanim rozpocznę, wyłączę też poprzednie globalne ustawienia audytowania.


ALTER SYSTEM RESET pgaudit.log;
SELECT pg_reload_conf();
SHOW pgaudit.log;
pgaudit.log
-------------
none

CREATE ROLE audytor NOLOGIN;
ALTER SYSTEM SET pgaudit.role TO 'audytor';
SELECT pg_reload_conf();


Od tego momentu każda relacja i kolumna, do której “audytor” ma dostęp, będzie audytowana do pliku loga pod kątem operacji, do której ma uprawnienia.


CREATE TABLE tabela_audytowana (x int, y int);
GRANT SELECT ON tabela_audytowana TO audytor;

INSERT INTO tabela_audytowana VALUES (1,2);
SELECT * FROM tabela_audytowana ;
x | y
---+---
1 | 2

2023-05-19 20:27:30.697 UTC [5622] postgres@postgres LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.tabela_audytowana,SELECT * FROM tabela_audytowana ;,<none>


W tym przypadku audytorowi nadałem uprawnienia SELECT, dlatego w logu pojawił się tylko jeden wpis odnośnie SELECT, INSERT został pominięty. Zmienił się też kontekst, AUDIT: OBJECT zamiast wcześniejszego AUDIT: SESSION.


Parametr pgaudit.role przyjmuje jako wartość tylko jednego użytkownika, jeżeli potrzebowałbym stworzyć kilku audytorów, na przykład dla każdej bazy lub aplikacji osobną rolę, mógłbym skorzystać z możliwości Postgresa do dziedziczenia roli.


CREATE ROLE audytor_odczyt NOLOGIN;
CREATE ROLE audytor_zapis NOLOGIN;
GRANT audytor_odczyt TO audytor;
GRANT audytor_zapis TO audytor;

Następnie należałoby dodać odpowiednie uprawnienia. Chcę logować zapytania, które czytają wartości z kolumn ‘x’ i ‘y’, wszystkie INSERT i DELETE oraz UPDATE na kolumnie ‘y’.


GRANT SELECT (x,y) ON tabela_audytowana TO audytor_odczyt ;
GRANT INSERT,UPDATE(y),DELETE ON tabela_audytowana TO audytor_zapis ;

INSERT INTO tabela_audytowana VALUES (2,3);
UPDATE tabela_audytowana SET x = 5 WHERE x = 2;
UPDATE tabela_audytowana SET y = 5 WHERE x = 5;
SELECT * FROM tabela_audytowana ;
x | y
---+---
1 | 2
5 | 5

2023-05-19 20:40:15.614 UTC [5675] postgres@postgres LOG: AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.tabela_audytowana,"INSERT INTO tabela_audytowana VALUES (2,3);",<none>
2023-05-19 20:40:49.308 UTC [5675] postgres@postgres LOG: AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.tabela_audytowana,UPDATE tabela_audytowana SET y = 5 WHERE x = 5;,<none>
2023-05-19 20:42:51.538 UTC [5675] postgres@postgres LOG: AUDIT: OBJECT,5,1,READ,SELECT,TABLE,public.tabela_audytowana,SELECT * FROM tabela_audytowana ;,<none>


Ważna uwaga, jeżeli skonfigurowałbym audytowanie sesji użytkowników oraz poszczególnych obiektów jednocześnie, pgaudit dodawałby podwójne wpisy dla wszystkich pasujących zapytań, różniłby się tylko kontekst, “SESSION” lub “OBJECT”.


Tak generowane logi mogę wyparsować w bashu za pomocą narzędzi takich jak grep, awk, i tym podobne, aby zwracały interesujące mnie wiersze z audytu. Mogę również wczytać plik loga do tabeli w Postgresie i przeanalizować sobie go za pomocą SQLa lub przygotowanych wcześniej widoków, funkcji, itp.


Aby wczytać plik loga do tabeli, muszę się upewnić, że parametry w Postgresie są odpowiednio ustawione. Szczególnie na Ubuntu, gdzie często logging collector jest domyślnie wyłączony, a logowanie odbywa się za pomocą przekierowania informacji zwracanych przez proces Postgrsa do pliku tekstowego. Parametr logging_collector przy zmianie wymaga restartu Postgresa.


CREATE TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint,
PRIMARY KEY (session_id, session_line_num)
);

# ścieżka do pliku powinna odpowiadać ścieżce do pliku loga do którego zapisuje postgres

COPY postgres_log FROM '/var/log/postgresql/postgresql.csv' WITH csv;


Tak stworzoną tabelę mogę w łatwy sposób przeszukiwać za pomocą SQLa.


postgres=# select * from postgres_log where message like 'AUDIT%' AND command_tag = 'INSERT';
-[ RECORD 1] ----------+-------------------------------------------------------------------------------------------------------------------
log_time | 2023-05-23 20:07:45.589+00
user_name | postgres
database_name | postgres
process_id | 10503
connection_from | [local]
session_id | 646d16f7.2907
session_line_num | 3
command_tag | INSERT
session_start_time | 2023-05-23 19:41:43+00
virtual_transaction_id | 4/100
transaction_id | 0
error_severity | LOG
sql_state_code | 00000
message | AUDIT: OBJECT,1,1,WRITE,INSERT,TABLE,public.tabela_audytowana,"insert into tabela_audytowana values (2,3);",<none>
detail |
hint |
internal_query |
internal_query_pos |
context |
query |
query_pos |
location |
application_name | psql
backend_type | client backend
leader_pid |
query_id | 0

Komentarze (0)

Musisz być zalogowany by móc dodać komentarz. Zaloguj się przez Google

Brak komentarzy...