Come posso utilizzare l'estensione pgaudit per controllare la mia istanza database Amazon RDS che esegue PostgreSQL?

Risoluzione

Esistono diversi parametri che puoi impostare per registrare l'attività sulla tua istanza database PostgreSQL. Per controllare diversi database, ruoli, tabelle o colonne, puoi usare l'estensione pgaudit. Dopo aver attivato l'estensione pgaudit, puoi configurare il parametro pgaudit.log per controllare database, ruoli, tabelle e colonne specifici.

Attivazione dell'estensione pgaudit su un'istanza Amazon RDS che esegue PostgreSQL

1.    Crea un ruolo specifico del database chiamato rds_pgaudit eseguendo il comando seguente:

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    Modifica i seguenti parametri nel gruppo di parametri del database personalizzato associato alla tua istanza database:
Aggiungi pgaudit a shared_preload_libraries
Configura pgaudit.role su rds_pgaudit, il ruolo creato nel passaggio 1

3.    Riavvia l'istanza in modo che le modifiche al gruppo di parametri vengano applicate all'istanza.

4.    Verifica che pgaudit sia inizializzato eseguendo il comando seguente:

show shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)

5.    Crea l'estensione pgaudit eseguendo il comando seguente:

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    Verifica che pgaudit.role sia impostato su rds_pgaudit eseguendo il comando seguente:

show pgaudit.role;
pgaudit.role
------------------
rds_pgaudit

7.    Configura il parametro pgaudit.log per controllare uno dei seguenti elementi:

• ALL controlla i seguenti comandi.
• MISC controlla vari comandi, ad esempio DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
• DDL controlla tutto il DDL (data description language) non incluso nella classe ROLE.
• ROLE controlla le istruzioni relative a ruoli e privilegi, come GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
• FUNCTION controlla le chiamate a funzioni e i blocchi DO.
• WRITE controlla INSERT, UPDATE, DELETE, TRUNCATE e COPY quando la destinazione è una relazione.
• READ controlla SELECT e COPY quando l'origine è una relazione o una query.

A seconda di ciò che desideri controllare, imposta il valore del parametro pgaudit.log per un database, un ruolo o una tabella.

Utilizzo dell'estensione pgaudit per controllare i database

1.    Per impostare il valore del parametro pgaudit.log per un database, un ruolo o una tabella, imposta il parametro pgaudit.log su none a livello di gruppo di parametri:

> show pgaudit.log;
+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

2. Esegui il comando seguente per sovrascrivere la configurazione di sistema per questo parametro solo in questo database:

ALTER DATABASE test_database set pgaudit.log='All';

Questo modificherà il valore del parametro pgaudit.log in All, in modo che test_database sia l'unico database controllato nell'istanza database RDS.

3.    Connettiti a test_database ed esegui la query seguente:

select * from test_table;

L'output del log degli errori è simile al seguente:

2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

Utilizzo dell'estensione pgaudit per controllare i ruoli

Analogamente alla configurazione del parametro pgaudit.log a livello di database, il ruolo viene modificato per avere un valore diverso per il parametro pgaudit.log. Nei seguenti comandi di esempio, i ruoli test1 e test2 vengono modificati per avere configurazioni pgaudit.log diverse.

1.    Imposta valori diversi per il parametro pgaudit.log per test1 e test2 eseguendo i comandi seguenti:

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

2.    Verifica che le modifiche siano state apportate a livello di ruolo eseguendo la query seguente:

> select rolname,rolconfig from pg_roles where rolname in ('test1','
                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

3.    Esegui le query seguenti sia per test1 che per test2:

CREATE TABLE test_table (id int);
CREATE TABLE

select * from test_table;
id
----
(0 rows)

L'output del log è simile al seguente per test1:

...
2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not
logged>
...

L'output del log è simile al seguente per test2 dopo aver eseguito le stesse query:

...
2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>
...

Nota: non esiste una voce di audit per la query SELECT, perché il parametro pgaudit.log per test2 è configurato solo su DDL.

Utilizzo dell'estensione pgaudit per controllare le tabelle

La configurazione del parametro pgaudit.log controlla e registra le istruzioni che influiscono su una relazione specifica. Solo i comandi SELECT, INSERT, UPDATE e DELETE possono essere registrati dall'estensione pgaudit. TRUNCATE non è incluso nella registrazione di audit degli oggetti. Se si concede al ruolo rds_pgaudit l'accesso a un'operazione (ad esempio SELECT, DELETE, INSERT o UPDATE) sulla tabella che si desidera controllare, qualsiasi audit di concessione registrerà l'istruzione corrispondente. L'esempio seguente concede al ruolo rds_pgaudit l'accesso a SELECT e DELETE, in modo che vengano controllate tutte le istruzioni SELECT e DELETE su test_table.

1.    Concedi al ruolo rds_pgaudit l'accesso a SELECT e DELETE eseguendo il comando seguente:

grant select, delete on test_table to rds_pgaudit;

2.    Verifica che la registrazione di audit sia configurata correttamente eseguendo un'istruzione DELETE su test_table:

Time: 0.008s
DELETE 1
>delete from test_table where pid=5050;

L'output per l'istruzione DELETE è simile al seguente:

2019-06-25 17:13:02
UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG:
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where
pid=5050,<not logged>

Utilizzo dell'estensione pgaudit per controllare le colonne

È inoltre possibile impostare l'audit a livello di colonna per una tabella specifica. Ad esempio, quando sono presenti dati sensibili solo in una colonna. Nel seguente comando di esempio, viene creata una tabella di retribuzioni e la tabella ha una colonna con dati salariali sensibili che deve essere verificata:

create table payroll
(    
    name text,
    salary text
);

1.    Concedi al ruolo rds_pgaudit l'accesso a SELECT nella colonna salary in modo che qualsiasi operazione SELECT in questa colonna venga verificata:

grant select (salary)  on payroll to rds_pgaudit;

2.    SELEZIONA tutte le colonne della tabella, inclusa la colonna salary:

select * from payroll;

Nel seguente output di esempio, viene verificata qualsiasi operazione SELECT che includa la colonna salary. Tuttavia, se un'operazione SELECT non contiene la colonna salary non verrà verificata.

2019-06-25
 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG:
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from
payroll,<not logged>

---

Informazioni correlate

Common DBA tasks for PostgreSQL