Hola a todos. Hoy queríamos contaros una funcionalidad que no se suele utilizar mucho en PostgreSQL pero que nos puede ayudar bastante en caso de tener que activar una auditoría en PostgreSQL. Para ello hay que instalar una extensión en nuestro motor llamado pgAudit y que nos permite recoger información de sesiones o de objetos en el log standard de PostgreSQL.
¿Cómo se activaba anteriormente la auditoría en PostgreSQL?
Para activar la Auditoría en PostgreSQL normalmente se han utilizado las herramientas nativas del gestor que eran:
- Activando el parámetro log_statement. Este parámetro admite las opciones:
- none. Valor por defecto en el que no captura ninguna operación.
- ddl. Captura todas las operaciones de definición de datos.
- mod. Lo mismo que ddl pero también registra los inserts, updates y deletes.
- all. Registra todas las sentencias contra la base de datos. Cuando se necesita auditar un sistema es la opción que se suele utilizar.
- Creando triggers personalizados. Utilizando las opciones before o after podemos auditar las operaciones en una tabla en concreto.
Estas formas de auditar tienen sus ventajas en inconvenientes:
- Parámetro log_statement.
- Ventaja:
- Se captura absolutamente todo con muy poca configuración.
- Inconveniente:
- Precisamente por la cantidad de información a recoger podemos tener problemas a la hora de filtrar y encontrar lo que estamos buscando.
- Ventaja:
- Triggers personalizados.
- Ventaja:
- Se puede personalizar mucho la información que capturamos.
- Inconveniente:
- Hay que mantener los triggers según las estructuras de las tablas cambian.
- Ventaja:
Para solventar estos problemas se suele utilizar la extensión de base de datos pgAudit. Esta utilidad se puede instalar desde PostgreSQL 9.5 y tenemos que seleccionar el plugin adecuado dependiendo de nuestra versión:
Una vez que tengamos la versión que necesitamos hay que compilarla y añadirla como extensión a nuestro motor para poder utilizarla.
Auditoría con pgAudit
Las opciones que tenemos de auditoría son:
- READ: Operaciones de SELECT y COPY cuando la fuete es una tabla.
- WRITE: Operaciones INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una tabla.
- FUNCTION: Llamadas a funciones.
- ROLE: Operaciones GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
- DDL: Todas las DDL que no están incluidas en la clase ROLE.
- MISC: Otras operaciones como DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
- MISC_SET: Operaciones del tipo SET como SET ROLE.
- ALL: Incluye todas las anteriores.
Aparte de las opciones anteriores podemos activar auditoría a nivel de sesión de la siguiente forma:
set pgaudit.log = 'read, ddl';
create table test
(
id int,
description text
);
insert into test (id, description)
values (1, ‘test1’);
select *
from test1;
En la salida del log tendremos:
Log Output:
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table test
(
id int,
description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
from test1,,<not logged>
Auditoría a nivel de objeto
También podemos activar auditoría a nivel de objeto. Esta auditoría se configura a nivel de roles de sistema. Para ello, lo que hacemos es configurar el parámetro pgaudit.role, en el que indicamos el rol que vamos a utilizar y las acciones que se auditen dependerán de los permisos del rol en concreto.
Por ejemplo, si creamos un rol llamado auditor que tenga permisos de SELECT y DELETE sobre la tabla que hemos creado, esas acciones sobre la tabla serán las que se registren en la auditoría. También podemos definir sobre que columnas de nuestra tabla queremos registrar la auditoría. Un ejemplo puede ser:
grant select, delete
on public.test1
to auditor;
En este caso, si lanzamos las siguientes operaciones en la base de datos:
set pgaudit.role = 'auditor';
create table test1
(
id int,
description text
);
grant select (description)
on public.test1
to auditor;
select id, description
from test1;
select id
from test1;
grant update (id)
on public. test1
to auditor;
update test1
set id = 2;
update test1
set description = ‘TEST2’;
La salida que tendremos será:
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public. test1, select id, description From test1,<not logged> AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public. test1, update test1 set id = 2,<not logged>
Aparte de la mayor versatilidad a la hora de auditar objetos la salida se muestra en formato CSV, por lo que podemos recoger los datos fácilmente en caso de necesitarlos.
Esperamos que la entrada os sea de utilidad y os simplifique el trabajo en vuestro gestor PostgreSQL. Si tenéis dificultades para implantarla, o no sabéis cómo, contactadnos sin compromiso a través de nuestra página de contacto.
Puedes ver nuestros servicios de consultoría y tuning PostgreSQL.
Un saludo y hasta la próxima.
Si no quieres perderte nuestras publicaciones, apúntante a nuestra newsletter mensual. Con un email al mes estarás al día de todas las novedades.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn