Πώς ενεργοποιούμε το Object και Session level Auditing σε PostgreSQL
Πιστοποιήσεις:
- Πώς ενεργοποιούμε το Object και Session level Auditing σε PostgreSQL - 28 Σεπτέμβριος 2026
- Πώς κάνουμε upgrade PostgreSQL σε νεότερη έκδοση - 23 Σεπτέμβριος 2026
- Πώς κάνουμε maintenance Indexes και Statistics σε βάση δεδομένων PostgreSQL - 18 Σεπτέμβριος 2026
Όταν διαχειρίζεσαι παραγωγικές βάσεις δεδομένων, έρχεται αργά ή γρήγορα η στιγμή που οι απαιτήσεις ασφαλείας και compliance θα χτυπήσουν την πόρτα σου. Το ερώτημα δεν είναι αν θα χρειαστεί να καταγράψεις ποιος έκανε τι, αλλά πώς θα το κάνεις χωρίς να γονατίσεις τον Server, π.χ. να εξαιρέσεις τον χρήστη του application server. Σε αυτό το άρθρο θα δούμε πώς στήνουμε σωστά το pgAudit.
Η εγκατάσταση
Θα πρέπει πρώτα να κατεβάσουμε και να κάνουμε εγκατάσταση το αντίστοιχο pgAudit version για το λειτουργικό και την έκδοση PostgreSQL που έχουμε, π.χ. για RedHat Linux και PostgreSQL 11:
sudo yum install pgaudit11
Έπειτα πάμε στο αρχείο postgresql.conf και προσθέτουμε την βιβλιοθήκη του:
su postgres vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log_catalog = off
log_line_prefix = '%m [%p] %q%u@%d 'Στη συνέχεια θέλει restart:
sudo systemctl restart postgresql-11
Αφού ανέβει χρειάζεται ενεργοποίηση του extension από SQL window:
CREATE EXTENSION pgaudit;
Session level auditing
Με το session level auditing μπορούμε να παρακολουθούμε όποιον χρήστη θέλουμε για ότι action θέλουμε (read,write,ddl,misc,all,none) καθολικά.
Αν θέλουμε να παρακολουθούμε write και ddl για έναν χρήστη τρέχουμε:
ALTER ROLE stratos SET pgaudit.log = 'write, ddl';
Αν θέλουμε να παρακολουθούμε όλους τους χρήστες αλλά να εξαιρέσουμε τον χρήστη του application server ώστε να μην γεμίσουν με άχρηστες πληροφορίες:
ALTER SYSTEM SET pgaudit.log = 'write, ddl'; SELECT pg_reload_conf(); ALTER ROLE app_user SET pgaudit.log = 'none';
Εναλλακτικά μπορούμε να το προσθέσουμε στο postgresql.conf(θέλει reload):
pgaudit.log = 'write, ddl'Object level auditing
Σε αυτή τη περίπτωση έχουμε την δυνατότητα να κάνουμε ξεχωριστό auditing ανα χρήστη και άνα πίνακα με την δημιουργία ρόλων.
Μπορούμε να φτιάξουμε τον ρόλο που θέλουμε π.χ. select_auditor, να του δώσουμε δικαίωμα σε ότι θέλουμε να κάνει audit και τέλος να δώσουμε αυτον τον ρόλο στους χρήστες που θέλουμε:
CREATE ROLE select_auditor; GRANT SELECT ON public.customers TO select_auditor; ALTER ROLE stratos SET pgaudit.role = 'select_auditor';
Επειδή μπορούμε να δώσουμε μόνο έναν ρόλο ως pgaudit.role αν θέλουμε σε έναν χρήστη να δώσουμε δύο διαφορετικά auditing policies συνδέουμαι έναν master ρόλο με τους υπόλοιπους:
CREATE ROLE pgaudit_master; CREATE ROLE my_select_auditor; CREATE ROLE my_update_auditor; GRANT my_select_auditor TO pgaudit_master; GRANT my_update_auditor TO pgaudit_master; GRANT SELECT ON public.orders TO my_select_auditor; GRANT UPDATE, INSERT ON public.orders TO my_update_auditor; ALTER ROLE stratos SET pgaudit.role = 'pgaudit_master';
Αντίστοιχα αν θέλουμε πάλι να κάνουμε κάποιο object level auditing εξαιρόντας τον χρήστη του application server:
ALTER SYSTEM SET pgaudit.role = 'pgaudit_role'; SELECT pg_reload_conf();
Εναλλακτικά μπορούμε να το προσθέσουμε στο postgresql.conf(θέλει reload):
pgaudit.role = 'pgaudit_role'Και αφού δώσουμε τα δικαιώματα που θέλουμε να παρακαλουθούμε στον ρόλο, εξαιρούμε τον χρήστη απο τον ρόλο:
CREATE ROLE pgaudit_role NOLOGIN; GRANT UPDATE, DELETE ON public.order_items TO pgaudit_role; ALTER ROLE app_user SET pgaudit.role = '';
Πώς διαβάζουμε τα logs του auditing
Με το παρακάτω query μπορούμε να δούμε τι έχει τρέξει, πότε, απο ποιον χρήστη, σε ποιον πίνακα:
WITH today_log AS (
SELECT 'log/postgresql-' || to_char(now(), 'Dy') || '.log' AS log_file
),
raw_lines AS (
SELECT
line_number,
log_line,
COUNT(CASE WHEN log_line ~ '^\d{4}-\d{2}-\d{2}' THEN 1 END) OVER (ORDER BY line_number) AS event_id
FROM (
SELECT line_number, log_line
FROM unnest(string_to_array(pg_read_file((SELECT log_file FROM today_log)), e'\n')) WITH ORDINALITY AS t(log_line, line_number)
) s
),
grouped_events AS (
SELECT
event_id,
MIN(log_line) AS header_line,
string_agg(log_line, e'\n' ORDER BY line_number) AS full_multiline_block
FROM raw_lines
WHERE event_id > 0
GROUP BY event_id
),
parsed_events AS (
SELECT
event_id,
header_line,
full_multiline_block,
ac[1] AS audit_class_val,
ct[1] AS command_type_val,
COALESCE(
NULLIF(split_part(substring(header_line from 'AUDIT:.*'), ',', 7), ''),
to_obj[1],
'N/A'
) AS target_obj
FROM grouped_events
LEFT JOIN LATERAL (
SELECT regexp_matches(header_line, 'AUDIT:\s*([A-Z]+),')
) AS a(ac) ON true
LEFT JOIN LATERAL (
SELECT regexp_matches(header_line, 'AUDIT:\s*[^,]+,\d+,\d+,[^,]+,([^,]+)')
) AS c(ct) ON true
LEFT JOIN LATERAL (
SELECT regexp_matches(full_multiline_block, '(?:FROM|INTO|UPDATE|DELETE\s+FROM)\s+([a-zA-Z0-9_\.]+)', 'i')
) AS t(to_obj) ON true
)
SELECT
substring(header_line from '^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})') AS log_time,
substring(header_line from '\] ([^@]+)@') AS user_name,
substring(header_line from '@([^ ]+)') AS database_name,
audit_class_val AS audit_class,
command_type_val AS command_type,
target_obj AS target_object,
-- ΕΔΩ Η ΔΙΟΡΘΩΣΗ: Πιάνει το query είτε γράφτηκε με εισαγωγικά είτε χωρίς!
rtrim(
COALESCE(
substring(full_multiline_block from ',\s*"([^"]+)",\s*<not logged>'), -- 1. Ψάχνει αν έχει εισαγωγικά (multiline / με κόμματα)
substring(full_multiline_block from ',\s*([^",]+),\s*<not logged>'), -- 2. Ψάχνει αν δεν έχει εισαγωγικά (single line)
substring(full_multiline_block from 'QUERY:\s*(.*)$'), -- 3. Ψάχνει για Object Audit form
header_line -- 4. Fallback αν αποτύχουν όλα τα παραπάνω
),
'"'
) AS executed_query,
full_multiline_block AS full_log_line
FROM parsed_events
WHERE header_line LIKE '%LOG: AUDIT:%'
AND header_line NOT LIKE '%WITH today_log%'
AND header_line NOT LIKE '%pg_read_file%'
ORDER BY log_time DESC;
Συντήρηση των logs
Αν είναι ενεργοποιήμενη η παράμετρος log_truncate_on_rotation ουσιαστικά μας κάνει rollover τις ημέρες που έχουμε ορίσει στη παράμετρο log_rotation_age, αν δεν θέλουμε αυτό και θέλουμε να κρατάμε τις πληροφορίες για περισσότερες ημέρες θα πρέπει να φτιάξουμε και κάποιον μηχανισμό ώστε να σβήνει τα παλιά αρχεία.
