pgaudit/pgauditExtensión de auditoría de PostgreSQL
La Extensión de Auditoría de PostgreSQL (pgAudit) proporciona un registro de auditoría detallado de sesión y/u objeto a través del sistema de registro estándar de PostgreSQL.
El objetivo de pgAudit es ofrecer a los usuarios de PostgreSQL la capacidad de generar registros de auditoría que a menudo se requieren para cumplir con certificaciones gubernamentales, financieras o ISO.
Una auditoría es una inspección oficial de las cuentas de un individuo u organización, generalmente realizada por un organismo independiente. La información recopilada por pgAudit se denomina correctamente pista de auditoría o registro de auditoría. En esta documentación se utiliza el término registro de auditoría.
El registro básico de sentencias puede ser proporcionado por la facilidad de registro estándar con log_statement = all. Esto es aceptable para monitoreo y otros usos, pero no proporciona el nivel de detalle generalmente requerido para una auditoría. No basta con tener una lista de todas las operaciones realizadas contra la base de datos. También debe ser posible encontrar sentencias particulares que sean de interés para un auditor. La facilidad de registro estándar muestra lo que el usuario solicitó, mientras que pgAudit se enfoca en los detalles de lo que sucedió mientras la base de datos satisfacía la solicitud.
Por ejemplo, un auditor puede querer verificar que una tabla en particular fue creada dentro de una ventana de mantenimiento documentada. Esto podría parecer un trabajo sencillo para grep, pero ¿qué sucede si se presenta algo como este ejemplo (intencionalmente ofuscado)?
DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;
El registro estándar le dará esto:
LOG: statement: DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;
Parece que encontrar la tabla de interés puede requerir algo de conocimiento del código en los casos en que las tablas se crean dinámicamente. Esto no es ideal ya que sería preferible buscar simplemente por el nombre de la tabla. Aquí es donde entra pgAudit. Para la misma entrada, producirá esta salida en el registro:
AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)
No solo se registra el bloque DO, sino que la sub-sentencia 2 contiene el texto completo de CREATE TABLE con el tipo de sentencia, tipo de objeto y nombre completamente calificado para facilitar las búsquedas.
Al registrar sentencias SELECT y DML, pgAudit se puede configurar para registrar una entrada separada para cada relación referenciada en una sentencia. No se requiere análisis para encontrar todas las sentencias que tocan una tabla en particular. De hecho, el objetivo es que el texto de la sentencia se proporcione principalmente para análisis forense profundo y no debería ser necesario para una auditoría.
Dependiendo de la configuración, es posible que pgAudit genere un volumen enorme de registros. Tenga cuidado de determinar exactamente qué necesita ser auditado en su entorno para evitar registrar demasiado.
Por ejemplo, cuando se trabaja en un entorno OLAP, probablemente no sea prudente registrar auditorías de inserciones en una tabla de hechos grande. El tamaño del archivo de registro probablemente será varias veces el tamaño real de los datos de las inserciones, ya que el archivo de registro se expresa como texto. Dado que los registros generalmente se almacenan con el sistema operativo, esto puede llevar a que el espacio en disco se agote muy rápidamente. En los casos en que no sea posible limitar el registro de auditoría a ciertas tablas, asegúrese de evaluar el impacto en el rendimiento durante las pruebas y asigne suficiente espacio en el volumen de registros. Esto también puede ser cierto para entornos OLTP. Incluso si el volumen de inserción no es tan alto, el impacto en el rendimiento del registro de auditoría aún puede afectar notablemente la latencia.
Para limitar la cantidad de relaciones auditadas registradas para sentencias SELECT y DML, considere usar el registro de auditoría de objetos (consulte Auditoría de objetos). El registro de auditoría de objetos permite seleccionar las relaciones a registrar, lo que permite reducir el volumen total de registros. Sin embargo, cuando se agregan nuevas relaciones, deben agregarse explícitamente al registro de auditoría de objetos. Una solución programática donde se excluyen del registro tablas específicas y se incluyen todas las demás podría ser una buena opción en este caso.
pgAudit es compatible con PostgreSQL 14 o superior.
Para admitir la nueva funcionalidad introducida en cada versión de PostgreSQL, pgAudit mantiene una rama separada para cada versión principal de PostgreSQL (actualmente PostgreSQL 14 - 19) que se mantendrá de manera similar al proyecto PostgreSQL.
Aparte de las correcciones de errores, no se permite desarrollo adicional para las ramas estables. El nuevo desarrollo, si lo hay, será estrictamente para la próxima versión principal no publicada de PostgreSQL.
Las versiones de pgAudit se relacionan con las versiones principales de PostgreSQL de la siguiente manera:
pgAudit v19.X está diseñado para ser compatible con PostgreSQL 19.
pgAudit v18.X está diseñado para ser compatible con PostgreSQL 18.
pgAudit v17.X está diseñado para ser compatible con PostgreSQL 17.
pgAudit v16.X está diseñado para ser compatible con PostgreSQL 16.
pgAudit v1.7.X está diseñado para ser compatible con PostgreSQL 15.
pgAudit v1.6.X está diseñado para ser compatible con PostgreSQL 14.
pgAudit se puede compilar frente a una copia instalada de PostgreSQL con paquetes de desarrollo usando PGXS. Las siguientes instrucciones deberían funcionar en la mayoría de los sistemas operativos tipo Unix.
Clone la extensión pgAudit:
git clone https://github.com/pgaudit/pgaudit.git
Cambie al directorio pgAudit:
cd pgaudit
Obtenga la rama REL_19_STABLE (tenga en cuenta que la rama estable puede no existir para versiones no publicadas de PostgreSQL):
git checkout REL_19_STABLE
Construya e instale pgAudit:
make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config
Las instrucciones para pruebas y desarrollo se pueden encontrar en test.
Los ajustes solo pueden ser modificados por un superusuario. Permitir que los usuarios normales cambien su configuración anularía el propósito de un registro de auditoría.
Los ajustes se pueden especificar globalmente (en postgresql.conf o usando ALTER SYSTEM ... SET), a nivel de base de datos (usando ALTER DATABASE ... SET) o a nivel de rol (usando ALTER ROLE ... SET). Tenga en cuenta que los ajustes no se heredan a través de la herencia de roles normal y SET ROLE no alterará la configuración de pgAudit de un usuario. Esta es una limitación del sistema de roles y no inherente a pgAudit.
La extensión pgAudit debe cargarse en shared_preload_libraries. De lo contrario, se generará un error al cargar y no se producirá ningún registro de auditoría.
Además, se debe llamar a CREATE EXTENSION pgaudit antes de que se establezca pgaudit.log para garantizar la funcionalidad adecuada de pgaudit. La extensión instala disparadores de eventos que agregan auditoría adicional para DDL. pgAudit funcionará sin la extensión instalada, pero las sentencias DDL no tendrán información sobre el tipo y nombre del objeto.
Si la extensión pgaudit se elimina y necesita ser recreada, entonces pgaudit.log debe desactivarse primero; de lo contrario, se generará un error.
Especifica qué clases de sentencias serán registradas por el registro de auditoría de sesión. Los valores posibles son:
READ: SELECT y COPY cuando el origen es una relación o una consulta.
WRITE: INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una relación.
FUNCTION: Llamadas a funciones y bloques DO.
ROLE: Sentencias relacionadas con roles y privilegios: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: Todo DDL que no esté incluido en la clase ROLE.
Se pueden proporcionar varias clases usando una lista separada por comas y las clases se pueden restar prefijando la clase con un signo - (consulte Registro de auditoría de sesión).
El valor predeterminado es none.
Especifica que el registro de sesión debe estar habilitado en el caso de que todas las relaciones en una sentencia estén en pg_catalog. Deshabilitar este ajuste reducirá el ruido en el registro de herramientas como psql y PgAdmin que consultan el catálogo en gran medida.
El valor predeterminado es on.
Especifica si los mensajes de registro serán visibles para un proceso cliente como psql. Este ajuste generalmente debe dejarse deshabilitado, pero puede ser útil para depuración u otros fines.
Tenga en cuenta que pgaudit.log_level solo está habilitado cuando pgaudit.log_client está en on.
El valor predeterminado es off.
Especifica el nivel de registro que se utilizará para las entradas de registro (consulte Niveles de severidad de mensajes para conocer los niveles válidos), pero tenga en cuenta que ERROR, FATAL y PANIC no están permitidos. Este ajuste se utiliza para pruebas de regresión y también puede ser útil para los usuarios finales con fines de prueba u otros.
Tenga en cuenta que pgaudit.log_level solo está habilitado cuando pgaudit.log_client está en on; de lo contrario, se usará el valor predeterminado.
El valor predeterminado es log.
Especifica que el registro de auditoría debe incluir los parámetros que se pasaron con la sentencia. Cuando hay parámetros, se incluirán en formato CSV después del texto de la sentencia.
El valor predeterminado es off.
Especifica que los valores de los parámetros más largos que este ajuste (en bytes) no deben registrarse, sino reemplazarse con <long param suppressed>. Esto se establece en bytes, no en caracteres, por lo que no tiene en cuenta los caracteres multibyte en la codificación de un parámetro de texto. Este ajuste no tiene efecto si log_parameter está en off. Si este ajuste es 0 (el valor predeterminado), todos los parámetros se registran independientemente de su longitud.
El valor predeterminado es 0.
Especifica si el registro de auditoría de sesión debe crear una entrada de registro separada para cada relación (TABLE, VIEW, etc.) referenciada en una sentencia SELECT o DML. Este es un atajo útil para el registro exhaustivo sin usar el registro de auditoría de objetos.
El valor predeterminado es off.
Especifica que el registro de auditoría debe incluir el número de filas recuperadas o afectadas por una sentencia. Cuando está habilitado, el campo de filas se incluirá después del campo de parámetros.
El valor predeterminado es off.
Especifica si el registro incluirá el texto de la sentencia y los parámetros (si está habilitado). Dependiendo de los requisitos, un registro de auditoría podría no necesitar esto y hace que los registros sean menos verbosos.
El valor predeterminado es on.
Especifica si el registro incluirá el texto de la sentencia y los parámetros con la primera entrada de registro para una combinación de sentencia/sub-sentencia o con cada entrada. Habilitar este ajuste resultará en un registro menos verboso pero puede dificultar la determinación de la sentencia que generó una entrada de registro, aunque el par sentencia/sub-sentencia junto con el id del proceso debería ser suficiente para identificar el texto de la sentencia registrado con una entrada anterior.
El valor predeterminado es off.
Especifica el rol maestro que se utilizará para el registro de auditoría de objetos. Se pueden definir múltiples roles de auditoría otorgándolos al rol maestro. Esto permite que varios grupos estén a cargo de diferentes aspectos del registro de auditoría.
No hay valor predeterminado.
El registro de auditoría de sesión proporciona registros detallados de todas las sentencias ejecutadas por un usuario en el backend.
El registro de sesión se habilita con el ajuste pgaudit.log.
Habilite el registro de sesión para todas las DML y DDL y registre todas las relaciones en sentencias DML:
set pgaudit.log = 'write, ddl';
set pgaudit.log_relation = on;
Habilite el registro de sesión para todos los comandos excepto MISC y eleve los mensajes de registro de auditoría como NOTICE:
set pgaudit.log = 'all, -misc';
set pgaudit.log_level = notice;
En este ejemplo, el registro de auditoría de sesión se utiliza para registrar sentencias DDL y SELECT. Tenga en cuenta que la sentencia insert no se registra porque la clase WRITE no está habilitada.
SQL:
set pgaudit.log = 'read, ddl';
create table account
(
id int,
name text,
password text,
description text
);
insert into account (id, name, password, description)
values (1, 'user1', 'HASH1', 'blah, blah');
select *
from account;
Salida del registro:
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account
(
id int,
name text,
password text,
description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select *
from account,,<not logged>
El registro de auditoría de objetos registra sentencias que afectan a una relación en particular. Solo se admiten los comandos SELECT, INSERT, UPDATE y DELETE. TRUNCATE no está incluido en el registro de auditoría de objetos.
El registro de auditoría de objetos está diseñado para ser un reemplazo más detallado de pgaudit.log = 'read, write'. Como tal, puede que no tenga sentido usarlos juntos, pero un posible escenario sería usar el registro de sesión para capturar cada sentencia y luego complementarlo con el registro de objetos para obtener más detalles sobre relaciones específicas.
El registro de auditoría a nivel de objeto se implementa a través del sistema de roles. El ajuste pgaudit.role define el rol que se utilizará para el registro de auditoría. Se registrará una relación (TABLE, VIEW, etc.) cuando el rol de auditoría tenga permisos para el comando ejecutado o herede los permisos de otro rol. Esto le permite tener efectivamente múltiples roles de auditoría aunque haya un solo rol maestro en cualquier contexto.
Establezca pgaudit.role en auditor y otorgue privilegios SELECT y DELETE en la tabla account. Cualquier sentencia SELECT o DELETE en la tabla account ahora será registrada:
set pgaudit.role = 'auditor';
grant select, delete
on public.account
to auditor;
En este ejemplo, el registro de auditoría de objetos se utiliza para ilustrar cómo se puede adoptar un enfoque granular para el registro de sentencias SELECT y DML. Tenga en cuenta que el registro en la tabla account está controlado por permisos a nivel de columna, mientras que el registro en la tabla account_role_map está a nivel de tabla.
SQL:
set pgaudit.role = 'auditor';
create table account
(
id int,
name text,
password text,
description text
);
grant select (password)
on public.account
to auditor;
select id, name
from account;
select password
from account;
grant update (name, password)
on public.account
to auditor;
update account
set description = 'yada, yada';
update account
set password = 'HASH2';
create table account_role_map
(
account_id int,
role_id int
);
grant select
on public.account_role_map
to auditor;
select account.password,
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id
Salida del registro:
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.account,select password
from account,<not logged>
AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.account,update account
set password = 'HASH2',<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account,select account.password,
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id,<not logged>
AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.account_role_map,select account.password,
account_role_map.role_id
from account
inner join account_role_map
on account.id = account_role_map.account_id,<not logged>
Las entradas de auditoría se escriben en la facilidad de registro estándar y contienen las siguientes columnas en formato separado por comas. La salida tiene formato CSV compatible solo si se elimina la parte del prefijo de línea de registro de cada entrada de registro.
AUDIT_TYPE - SESSION u OBJECT.
STATEMENT_ID - ID de sentencia único para esta sesión. Cada ID de sentencia representa una llamada al backend. Los ID de sentencia son secuenciales incluso si algunas sentencias no se registran. Puede haber múltiples entradas para un ID de sentencia cuando se registra más de una relación.
SUBSTATEMENT_ID - ID secuencial para cada sub-sentencia dentro de la sentencia principal. Por ejemplo, llamar a una función desde una consulta. Los ID de sub-sentencia son continuos incluso si algunas sub-sentencias no se registran. Puede haber múltiples entradas para un ID de sub-sentencia cuando se registra más de una relación.
CLASS - p.ej. READ, ROLE (consulte pgaudit.log).
COMMAND - p.ej. ALTER TABLE, SELECT.
OBJECT_TYPE - TABLE, INDEX, VIEW, etc. Disponible para sentencias SELECT, y la mayoría de sentencias .
Use log_line_prefix para agregar cualquier otro campo necesario para cumplir con sus requisitos de registro de auditoría. Un prefijo de línea de registro típico podría ser '%m %u %d [%p]: ', que proporcionaría la fecha/hora, nombre de usuario, nombre de base de datos e id de proceso para cada registro de auditoría.
El registro de auditoría se realiza con el mejor esfuerzo y no es transaccional. pgAudit escribe las entradas de auditoría a través del sistema de registro estándar de PostgreSQL, que no vacía cada entrada en el disco de forma sincrónica con la transacción que la produjo, ni propaga los errores de escritura de vuelta a la sesión. No hay garantía de que una transacción confirmada tenga una entrada de registro de auditoría correspondiente. Si el servidor se bloquea o pierde energía, o el destino del registro deja de estar disponible (por ejemplo, el volumen de registro se llena) después de que una transacción se confirma pero antes de que sus entradas de auditoría se escriban de forma duradera, esas entradas pueden perderse. Por el contrario, una sentencia se registra cuando se ejecuta, por lo que se puede escribir una entrada incluso si su transacción posteriormente se revierte.
Los cambios de nombre de objetos se registran bajo el nombre al que se renombraron. Por ejemplo, renombrar una tabla producirá el siguiente resultado:
ALTER TABLE test RENAME TO test2;
AUDIT: SESSION,36,1,DDL,ALTER TABLE,TABLE,public.test2,ALTER TABLE test RENAME TO test2,<not logged>
Es posible que un comando se registre más de una vez. Por ejemplo, cuando se crea una tabla con una clave primaria especificada en el momento de la creación, el índice de la clave primaria se registrará de forma independiente y se realizará otro registro de auditoría para el índice bajo la entrada de creación. Las múltiples entradas, sin embargo, estarán contenidas dentro de un ID de sentencia.
Autovacuum y Autoanalyze no se registran.
Las sentencias que se ejecutan después de que una transacción entra en estado de aborto no serán registradas en la auditoría. Sin embargo, la sentencia que causó el error y cualquier sentencia posterior ejecutada en la transacción abortada se registrarán como ERRORes por la facilidad de registro estándar.
No es posible auditar de manera confiable a los superusuarios con pgAudit. Una solución es restringir el acceso a las cuentas de superusuario y usar la extensión set_user para escalar permisos cuando sea necesario.
La Extensión de Auditoría de PostgreSQL se basa en el proyecto pgaudit de 2ndQuadrant escrito por Simon Riggs, Abhijit Menon-Sen e Ian Barwick, y presentado como una extensión para el núcleo de PostgreSQL. Desarrollo adicional ha sido realizado por David Steele de Crunchy Data.
MISC: Comandos varios, p.ej. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
MISC_SET: Comandos SET varios, p.ej. SET ROLE.
ALL: Incluye todos los anteriores.
DMLDDLOBJECT_NAME - El nombre de objeto completamente calificado (p.ej. public.account). Disponible para sentencias SELECT, DML y la mayoría de sentencias DDL.
STATEMENT - Sentencia ejecutada en el backend.
PARAMETER - Si pgaudit.log_parameter está configurado, este campo contendrá los parámetros de la sentencia como CSV citado o <none> si no hay parámetros. De lo contrario, el campo es <not logged>.