Audit trigger PL/Python

priklady

Audit v jazyku PL/Python

Jedná sa o ďalšiu formu audit tiggera, tentokrát však v PL jazyku Python. Log sa ukladá do dočasného súboru v TMP vo formáte JSON. Vytvoríme tiež trigger, ktorý bude zasielať informácie cez socketový server na UDP porte 9999. Ktoré odchytíme pomocou jednoduchého programu(ang. UDP listener), vytvorenom v jazyku Python.

SQL DUMP
Download SQL

Štruktúra DB - Diagram

audit_trigger_diagram

Vytvorenie zjednodušenej tabuľky

1
2
3
4
5
CREATE TABLE test (
  id   SERIAL PRIMARY KEY,
  data TEXT,
  ts   TIMESTAMP DEFAULT clock_timestamp()
);

PL/Python funkcia

Vytvoríme funkciu, ktorá bude zapisovať logy do súboru. Ide o najjednoduchšiu formu ako permanentne zapisovať vykonané zmeny v DB.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE OR REPLACE FUNCTION log_trigger() RETURNS TRIGGER AS $$
args = tuple(TD["args"])
if not SD.has_key(args):
    protocol = args[0]
    if protocol == 'udp':
        import socket
        sock = socket.socket(socket.AF_INET,
                             socket.SOCK_DGRAM)
        def logfunc(msg, addr=args[1],
                    port=int(args[2]), sock=sock):
            sock.sendto(msg, (addr, port))
    elif protocol == 'file':
        f = open(args[1], 'a+')
        def logfunc(msg, f=f):
            f.write(msg + '\n')
            f.flush()
    else:
        raise ValueError, 'Zlá cesta v CREATE TRIGGER'
    SD[args] = logfunc
    SD['env_plan'] = plpy.prepare("""
                select clock_timestamp(),
                    txid_current(),
                    current_user,
                    current_database()""", [])
logfunc = SD[args]
env_info_row = plpy.execute(SD['env_plan'])[0]
import json
log_msg = json.dumps(
    {'txid': env_info_row['txid_current'],
     'time': env_info_row['clock_timestamp'],
     'user': env_info_row['current_user'],
     'db': env_info_row['current_database'],
     'table': '%s.%s' % (TD['table_name'],
                         TD['table_schema']),
     'event': TD['event'],
     'old': TD['old'],
     'new': TD['new'],
     }
)
logfunc(log_msg)
$$ LANGUAGE plpythonu;

Tento trigger najprv skontroluje, či funkcia už nie je definovaná v miestom adresári funkcie SD[]. Vzhľadom k tomu, že rovnaký trigger môže byt použitý pod rozličným umiestnením logovacieho súboru, log funkcia je uložená pod kľúčom skonštruovaným ako Python n-tica (tuple) z argumentov trigger funkcie v príkaze CREATE TRIGGER. Nie je možné používať TD[„args“] zoznam ako kľúč, pretože ten musí byt nemenný (immutable), a tým Python list nie je, ale n-tica (tuple) áno. Pokiaľ kľúč nie je prítomný, čo by znamenalo, že toto je prvé volanie triggera, musíme vytvoriť vhodnú funkciu a uložiť ho.

Pre UDP log vytvoríme UDP socket pre zápis. Následne definujeme funkciu, ktorá odovzdáva informácie socketu. Vytvoríme tiež dva argumenty pre trigger ako predvolené argumenty pre túto funkciu. Jedná sa o najpohodlnejší spôsob ako v Pythone vytvoriť uzáver (closure – takéto funkcie majú prístup k premenným mimo vlastnú definíciu).

Pre log do súboru jednoducho otvoríme požadovaný súbor v režime pripojenia (append a+) a vytvoríme log funkciu, ktorá zapisuje správu do tohoto súboru a následne vyprázdni zápis (flush). Vďaka čomu sú dáta zapísané okamžite, a nie o nejaký čas neskôr, ak by sa napríklad zaplnila vyrovnávacia pamäť. Funkcia vytvorená v oboch prípadoch je uložená v SD[tuple(TD[“args”])].

V ďalšej časti pripravíme a uložíme plán dopytov (query plan) pre získanie ďalších dát, ktoré chceme logovať a uložiť do SD[‘env_plan’]. Po čom zavoláme pripravenú funkciu (logfunc = SD[args]) a získame riadok ďalších dát logu:
env_info_row = plpy.execute(SD[‘env_plan’])[0].

Nakoniec konvertujeme všetky dáta do JSON objektu (log_msg = json.dumps({…})) a následne použijeme logovaciu funkciu pre zápis do log súboru logfunc(log_msg).

JSON Trigger

Všetky zmeny v tabuľke ako INSERT, UPDATE alebo DELETE sú zaznamenané v /tmp/test.json.log. Vlastníkom súboru je zvyčajne užívateľ, na ktorom beží PostgreSQL server, štandardne postgres. Pre prezretie a prácu na súbore musíme byt prihlásený ako užívateľ postgres alebo root. Samozrejme je možné pozmeniť práva vytvoreného log súboru pomocou príkazu chmod.

1
2
3
4
CREATE TRIGGER test_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
EXECUTE PROCEDURE log_trigger('file', '/tmp/test.json.log');

Trigger pre socket server

Pokiaľ chceme testovať UDP log, musíme definovať ďalší trigger s rozličnými argumentmi. Samozrejme budeme potrebovať niečo, čo dokáže načúvať na danom porte. Pre tieto účely použijeme program UDP Listener.

1
2
3
4
CREATE TRIGGER test_audit_trigger_udp
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
EXECUTE PROCEDURE log_trigger('udp', 'localhost', 9999);

Python UDP listener

Program v jazyku Python, ktorý dokáže načúvať na danom porte. Stačí ho spustiť a program sám vypíše všetky UDP pakety prijaté na štandardný výstup (STDOUT).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/usr/bin/env python
#Skoro rovnaký SocketServer ako ukážka z http://docs.python.org/library/socketserver.html
#S malými zmenami. Vynechaná časť, ktorá odosiela dáta späť a chýbajúca 0 pridaná pre formátovanie reťazca.
#Spustiteľné príkazom python udp_log_listener.py z príkazovej riadky.
import SocketServer
class UDPLogHandler(SocketServer.BaseRequestHandler):
    def handle(self):
        data = self.request[0].strip()
        print "{0} logged:".format(self.client_address[0])
        print data
if __name__ == "__main__":
    HOST, PORT = "localhost", 9999
    server = SocketServer.UDPServer((HOST, PORT), UDPLogHandler)
    server.serve_forever()

Test

Po vložený dát do tabuľky test sa aktivujú (spustia) oba triggery.

1
2
3
INSERT INTO
  test (id,data)
VALUES (1, 'data_prveho_riadku');

Log v JSON

JSON (JavaScript Object Notation) sa v posledných rokoch stal jedným z nepoužívanejších formátov pre výmenu dat na Webe. JSON sa objavil, keď sa na webe pre výmenu dát používal prevážne formát XML. Ten v očiach javascriptových vývojárov trpel niektorými nedostatkami, napr. práca s ním bola zložitá (bolo nutné používať „neohrabaný“ DOM, riešiť prítomnosť uzlov obsahujúcich iba biele znaky apod.). A tak napriek tomu, že pri zápise celých dokumentov JSON nemôže (a ani nemá) formátu XML konkurovať, v zápise krátkych štrukturovaných dát vymieňaných webovými aplikáciami konkurenčný boj vyhral JSON. PostgreSQL od verzie 9.2 podporuje tento formát a dokáže tak konkurovať aj databázam určeným pre správu dokumentov. K dispozícií sú ja nové vstavané funkcie PostgreSQL pre správu nerelačného typu JSON, napr. funkcie slúžiace k parsovaniu JSONu – json_to_record a json_to_recordset.
Vsetky zmeny sa uložia do JSON suboru

1
2
3
4
5
6
7
8
9
10
11
12
13
14
{
   "old":null,
   "user":"postgres",
   "time":"2017-03-20 16:28:03.107487+01",
   "table":"test.public",
   "db":"audit_trigger_python_en",
   "event":"INSERT",
   "new":{
      "data":"JABLKO",
      "id":1,
      "ts":"2017-03-20 16:28:03.105999"
   },
   "txid":1433
}

Log zobrazený programom UDP listener

Sockety môžno chápať ako jeden z prostriedkov pre komunikáciu medzi procesmi. Od iných komunikačných prostriedkov sa líši predovšetkým tím, že komunikujúce procesy nemusia byť na rovnakom počítači. Čo sa týka terminológie, tak komunikáciu si možno prestaviť ako rúru, cez ktorú tečú dáta, pričom socket je pomenovanie pre ich konce. Pri komunikácií si každý proces vytvorí svoj socket a nastaví jeho parametre tak, aby pomocou neho mohol komunikovať so socketom iného procesu (na inom počítači). V tomto prípade používame protokol UDP. Ten slúži k prenášaniu krátkych blokov dát, u ktorých nie je zaistené ich doručenie a ani poradie v akom budú doručené. Výhodou tohoto druhu komunikácie je rýchlosť – nemusí sa nadväzovať žiadne spojenie a nepotvrdzuje sa, či dáta skutočne prišli.
audit_trigger_diagram

Zdroje:

https://www.zdrojak.cz/clanky/json-jednotny-format-pro-vymenu-dat/