¿Cómo utilizo la extensión pgAudit para auditar mi instancia de base de datos de Amazon RDS para PostgreSQL?

Resolución

Hay diferentes parámetros que puede configurar para registrar la actividad en su instancia de base de datos de RDS para PostgreSQL. Después de activar la extensión pgAudit, puede configurar el parámetro pgaudit.log para auditar bases de datos, roles, tablas y columnas específicas. En primer lugar, defina el valor del parámetro pgaudit.log como ninguno en el grupo de parámetros de una base de datos, un rol o una tabla:

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

Uso de la extensión pgAudit para auditar bases de datos

Siga estos pasos:

1. Ejecute el siguiente comando para anular la configuración del sistema para el parámetro pgaudit.log solo en su base de datos:

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

   Nota: Sustituya test_database por el nombre de su base de datos. El comando anterior cambia el valor del parámetro pgaudit.log a Todos para que pgAudit audite solo la test_database.
2. Conéctese a la base de datos y, a continuación, ejecute la siguiente consulta para auditarla:

   select * from aud_table;

   Nota: Sustituya aud_table por la tabla que desea auditar.
   El resultado del registro de errores es similar al siguiente:

   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>

Uso de la extensión pgAudit para auditar los roles

También puede modificar los roles del parámetro pgaudit.log para que tengan valores diferentes. Para establecer valores diferentes para el parámetro pgaudit.log para sus roles, ejecute los siguientes comandos:

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

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

Nota: Sustituya test1 y test2 por los nombres de sus roles.

Para comprobar si las modificaciones se realizan a nivel de rol, ejecute la siguiente consulta:

> 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

Nota: Sustituya test1 y test2 por los nombres de sus roles.

Para probar el comportamiento de registro de pgAudit de sus roles, ejecute las siguientes consultas para cada rol:

CREATE TABLE test_table (id int);CREATE TABLE

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

La salida del registro para dos funciones diferentes es similar a la de los siguientes ejemplos.

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>
...

test2:

...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>
...

En el resultado del ejemplo de test2 no hay ninguna entrada de auditoría para la consulta SELECT porque el parámetro pgaudit.log de test2 solo está establecido en DDL. Esta prueba verifica que el rol test2 registre solo las operaciones de DDL.

Uso de la extensión pgAudit para auditar tablas

Nota: La extensión pgAudit solo puede registrar los comandos SELECT, INSERT, UPDATE y DELETE. La extensión no puede auditar TRUNCATE.

Para auditar las tablas, ejecute el siguiente comando para conceder al rol rds_pgaudit acceso a los comandos:

grant select, delete on test_table to rds_pgaudit;

Nota: El comando anterior permite al rol rds_pgaudit acceder a SELECT y DELETE. Sustituya la select y delete por los comandos que desee auditar.

Para comprobar que el registro de auditoría está configurado correctamente, ejecute una instrucción en la tabla. El siguiente comando de ejemplo ejecuta una instrucción DELETE en test_table:

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

El resultado de la instrucción DELETE es similar al siguiente:

2019-06-25 17:13:02UTC: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>

Uso de la extensión pgAudit para auditar columnas

Puede auditar las columnas de una tabla específica, por ejemplo, cuando solo hay datos confidenciales en una columna. El siguiente comando de ejemplo crea una tabla de nóminas que tiene una columna confidencial con datos salariales que deben auditarse:

create table payroll(    
    name text,
    salary text
);

Para auditar la columna salarial, primero ejecute el siguiente comando para conceder al rol rds_pgaudit acceso a SELECT en la columna de salarios:

grant select (salary)  on payroll to rds_pgaudit;

A continuación, ejecute el siguiente comando para SELECCIONAR todas las columnas de la tabla, incluida la columna de salarios:

select * from payroll;

Si la consulta SELECT no contiene la columna de salarios, pgAudit no audita esa columna.

En el siguiente ejemplo de resultado, pgAudit audita cualquier SELECT que incluya la columna de salarios:

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>

Información relacionada

Tareas comunes de los administradores de base de datos (DBA) para Amazon RDS para PostgreSQL

Uso de pgAudit para registrar la actividad de la base de datos