¿Cómo uso la extensión pgaudit para auditar mi instancia de base de datos de Amazon RDS que está ejecutando PostgreSQL?

Resolución

Hay diferentes parámetros que puede configurar para registrar la actividad en su instancia de base de datos de PostgreSQL. Para auditar diferentes bases de datos, roles, tablas o columnas, puede usar la extensión pgaudit. 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.

Activación de la extensión pgaudit en una instancia de Amazon RDS que ejecuta PostgreSQL

1.    Cree un rol de base de datos específica llamada rds_pgaudit ejecutando el siguiente comando:

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    Modifique los siguientes parámetros en su grupo de parámetros de base de datos personalizado que está asociado a su instancia de base de datos:
Agregue o añada pgaudit a shared_preload_libraries
Configure pgaudit.role en rds_pgaudit, el rol creado en el paso 1

3.    Reinicie la instancia para que los cambios en el grupo de parámetros se apliquen a la instancia.

4.    Confirme que pgaudit esté inicializado ejecutando el siguiente comando:

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

5.    Cree la extensión pgaudit ejecutando el siguiente comando:

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    Confirme que pgaudit.role esté configurado en rds\ _pgaudit ejecutando el siguiente comando:

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

7.    Configure el parámetro pgaudit.log para auditar cualquiera de los siguientes elementos:

• ALL audita los siguientes comandos.
• MISC audita varios comandos diferentes, como DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
• DDL audita todo el lenguaje de descripción de datos (DDL) que no está incluido en la clase ROLE.
• ROLE audita las instrucciones relacionadas con roles y privilegios, como GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
• FUNCTION audita las llamadas a funciones y los bloques de DO.
• WRITE audita INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una relación.
• READ audita SELECT y COPY cuando la fuente es una relación o una consulta.

Según lo que desee auditar, defina el valor del parámetro pgaudit.log para una base de datos, un rol o una tabla.

Usando la extensión pgaudit para auditar bases de datos

1.    Para establecer el valor del parámetro pgaudit.log para una base de datos, un rol o una tabla, defina el parámetro pgaudit.log a ninguno a nivel de grupo de parámetros:

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

2. Ejecute el siguiente comando para anular la configuración del sistema para este parámetro solo en esta base de datos:

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

Esto cambia el valor del parámetro pgaudit.log a All, de modo que test\ _database es la única base de datos que se audita en la instancia de base de datos de RDS.

3.    Conéctese a test_database y ejecute la siguiente consulta:

select * from test_table;

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>

Usando la extensión pgaudit para auditar los roles

De forma similar a la configuración del parámetro pgaudit.log al nivel de base de datos, el rol se modifica para que tenga un valor diferente para el parámetro pgaudit.log. En los siguientes comandos de ejemplo, los roles test1 y test2 se modifican para tener diferentes configuraciones de pgaudit.log.

1.    Establezca valores diferentes para el parámetro pgaudit.log para test1 y test2 ejecutando los siguientes comandos:

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

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

2.    Compruebe que las modificaciones se realizan a nivel de rol ejecutando 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

3.    Ejecute las siguientes consultas para test1 y test2:

CREATE TABLE test_table (id int);
CREATE TABLE

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

El resultado del registro es similar al siguiente para 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>
...

El resultado del registro es similar al siguiente para test2 después de ejecutar las mismas consultas:

...
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: No hay ninguna entrada de auditoría para la consulta SELECT porque el parámetro pgaudit.log de test2 está configurado solo para DDL.

Usando la extensión pgaudit para auditar tablas

La configuración del parámetro pgaudit.log audita y registra las instrucciones que afectan a una relación específica. La extensión pgaudit solo puede registrar los comandos SELECT, INSERT, UPDATE y DELETE. TRUNCATE no está incluido en el registro de auditoría de objetos. Si concede al rol rds_pgaudit acceso a una operación (como SELECT, DELETE, INSERT o UPDATE) de la tabla que desea auditar, cualquier auditoría de subvenciones registrará la instrucción correspondiente. En el siguiente ejemplo, se concede al rol rds_pgaudit acceso a SELECT y DELETE, de modo que se auditen todas instrucciones SELECT y DELETE en test_table.

1.    Otorgue al rol rds_pgaudit acceso a SELECT y DELETE ejecutando el siguiente comando:

grant select, delete on test_table to rds_pgaudit;

2.    Compruebe que el registro de auditoría está configurado correctamente ejecutando una instrucción DELETE en test_table:

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

El resultado del estado DELETE es similar al siguiente:

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>

Usando la extensión pgaudit para auditar columnas

También puede configurar la auditoría a nivel de columna para una tabla específica. Por ejemplo, cuando los datos confidenciales solo existen en una columna. En el siguiente comando de ejemplo, se crea una tabla de nóminas y la tabla tiene una columna confidencial que incluye datos salariales que deben auditarse:

create table payroll
(    
    name text,
    salary text
);

1.    Conceda al rol rds\ _pgaudit acceso a SELECT en la columna de salarios para que se audite cualquier SELECT de esta columna:

grant select (salary)  on payroll to rds_pgaudit;

2.    SELECCIONE todas las columnas de la tabla, incluida la columna de salarios:

select * from payroll;

En el siguiente ejemplo de resultado, se audita cualquier SELECT que incluya la columna de salarios. Sin embargo, un SELECT que no contiene la columna de salarios no se audita.

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 DBA para PostgreSQL