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

Las instituciones financieras, las agencias gubernamentales y muchas industrias necesitan mantener registros de auditoría para cumplir con los requisitos reglamentarios. Al utilizar la extensión de auditoría de PostgreSQL (PGAudit) con su Clúster de base de datos de Aurora PostgreSQL, puede capturar los registros detallados que suelen necesitar los auditores o para cumplir con los requisitos reglamentarios. Por ejemplo, puede configurar la extensión pgAudit para realizar un seguimiento de los cambios realizados en bases de datos y tablas específicas, para registrar el usuario que realizó el cambio y muchos otros detalles.

La extensión pgAudit se basa en la funcionalidad de la infraestructura de registro nativa de PostgreSQL ampliando los mensajes de registro con más detalle. En otras palabras, utiliza el mismo método para ver el registro de auditoría que para ver cualquier mensaje de registro. Para obtener más información sobre los registros de PostgreSQL, consulte Archivos de registro de bases de datos de Aurora PostgreSQL.

La extensión PGAudit elimina los datos confidenciales, como las contraseñas de texto no cifrado, de los registros. Si su clúster de base de datos de Aurora PostgreSQL está configurado para registrar las instrucciones del lenguaje de manipulación de datos (DML) tal como se detalla en Activación de registro de consultas para su clúster de base de datos de Aurora PostgreSQL, puede evitar el problema de la contraseña de texto sin cifrar mediante la extensión de auditoría de PostgreSQL.

Puede configurar la auditoría en las instancias de la base de datos con un alto grado de especificidad. Puede auditar todas las bases de datos y todos los usuarios. O bien, puede optar por auditar solo determinadas bases de datos, usuarios y otros objetos. También puede excluir explícitamente a determinados usuarios y bases de datos de la auditoría. Para obtener más información, consulte Exclusión de usuarios o bases de datos del registro de auditoría.

Dada la cantidad de detalles que se pueden capturar, le recomendamos que, si usa pgAudit, controle su consumo de almacenamiento.

La extensión pgAudit es compatible con todas las versiones de Aurora PostgreSQL disponibles. Para obtener una lista de las versiones de pgAudit compatibles con la versión de Aurora PostgreSQL, consulte Versiones de extensión para Amazon Aurora PostgreSQL en las Notas de la versión de Aurora PostgreSQL.

Configuración de la extensión pgAudit

Para configurar la extensión pgAudit en el clúster de base de datos de Aurora PostgreSQL, primero hay que añadir pgAudit a las bibliotecas compartidas en el grupo de parámetros de clústeres de bases de datos personalizados para su clúster de bases de datos de Aurora PostgreSQL. Para obtener información acerca de cómo crear el grupo de parámetros del clúster de base de datos, consulte Working with parameter groups (Trabajar con grupos de parámetros). A continuación, instale la extensión pgAudit. Por último, especifique las bases de datos y objetos que desea auditar. Los procedimientos de esta sección le muestran cómo hacerlo. Puede utilizar la AWS Management Console o la AWS CLI.

Debe tener permisos como el rol rds_superuser para realizar todas estas tareas.

En los pasos siguientes se supone que el clúster de ase de datos de Aurora PostgreSQL está asociado a un grupo de parámetros de clúster de base de datos.

Consola

Para configurar la extensión pgAudit

1. Inicie sesión en la AWS Management Console y abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

2. En el panel de navegación, elija la instancia del escritorio del clúster de base de datos de Aurora PostgreSQL .

3. Abra la pestaña Configuration (Configuración) para su instancia de escritor del clúster de base de datos de Aurora PostgreSQL. Entre los detalles de la instancia, busque el enlace del grupo de parámetros.

4. Elija el enlace para abrir los parámetros personalizados asociados al clúster de base de datos de Aurora PostgreSQL.

5. En el campo de búsqueda Parametes (Parámetros), escriba shared_pre para buscar el parámetro shared_preload_libraries.

6. Seleccione Edit parameters (Editar parámetros) para acceder a los valores de las propiedades.

7. Añada pgaudit a la lista en el campo Values (Valores). Utilice una coma para separar los elementos de la lista de valores.

   

8. Reinicie la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL para que surta efecto el cambio en el parámetro shared_preload_libraries.

9. Cuando la instancia esté disponible, compruebe que pgAudit se haya inicializado. Use psql para conectarse a la instancia de escritor de su clúster de bases de datos Aurora PostgreSQL, y, a continuación, ejecute el siguiente comando.

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

10. Con pgAudit inicializado, ahora puede crear la extensión. Debe crear la extensión después de inicializar la biblioteca, ya que la extensión pgaudit instala activadores de eventos para auditar las sentencias del lenguaje de definición de datos (DDL).

    CREATE EXTENSION pgaudit;

11. Cierre la sesión de psql.

    labdb=> \q

12. Inicie sesión en la AWS Management Console y abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

13. Busque el parámetro pgaudit.log en la lista y configúrelo con el valor adecuado para su caso de uso. Por ejemplo, al establecer el parámetro pgaudit.log en write como se muestra en la siguiente imagen, se capturan las inserciones, las actualizaciones, las eliminaciones y algunos otros tipos de cambios en el registro.

    

    También puede elegir uno de los siguientes valores para el parámetro pgaudit.log.

    • none: es el valor predeterminado. No se registran cambios en la base de datos.

    • all: registra todo (read, write, function, role, ddl, misc).

    • ddl: registra todas las instrucciones del lenguaje de definición de datos (DDL) que no están incluidas en la clase ROLE.

    • function: registra llamadas a funciones y bloques DO.

    • misc: registra comandos variados como, por ejemplo, DISCARD, FETCH, CHECKPOINT, VACUUM y SET.

    • read: registra SELECT y COPY cuando el origen es una relación (como una tabla) o una consulta.

    • role: registra instrucciones relacionadas con roles y privilegios, como GRANT, REVOKE, CREATE ROLE, ALTER ROLE y DROP ROLE.

    • write: registra INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una relación (tabla).

14. Elija Guardar cambios.

15. Abra la consola de Amazon RDS en https://console.aws.amazon.com/rds/.

16. Elija la instancia de escritura del clúster de RDS for PostgreSQL en la lista de bases de datos para seleccionarla y, a continuación, elija Reboot (Reiniciar) en el menú Actions (Acciones).

AWS CLI

Para configurar pgAudit

Para configurar pgAudit mediante AWS CLI, llame a la operación modify-db-parameter-group para modificar los parámetros del registro de auditoría de su grupo de parámetros personalizado, como se muestra en el siguiente procedimiento.

1. Utilice el siguiente comando AWS CLI para añadir pgaudit al parámetro shared_preload_libraries.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
      --region aws-region

2. Utilice el siguiente comando AWS CLI para reiniciar la instancia de escritura de la instancia de base de datos de Aurora PostgreSQL para que se inicialice la biblioteca pgaudit.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

3. Cuando la instancia esté disponible, puede verificar si pgaudit se ha inicializado. Use psql para conectarse a la instancia de escritor de su clúster de bases de datos Aurora PostgreSQL, y, a continuación, ejecute el siguiente comando.

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

   Con pgAudit inicializado, ahora puede crear la extensión.

   CREATE EXTENSION pgaudit;

4. Cierre la sesión de psql para poder utilizar AWS CLI.

   labdb=> \q

5. Utilice el siguiente comando AWS CLI para especificar las clases de instrucciones que desea registrar con el registro de auditoría de sesión. El ejemplo establece el parámetro pgaudit.log enwrite, que captura las inserciones, las actualizaciones y las eliminaciones del registro.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
      --region aws-region

   También puede elegir uno de los siguientes valores para el parámetro pgaudit.log.

   • none: es el valor predeterminado. No se registran cambios en la base de datos.

   • all: registra todo (read, write, function, role, ddl, misc).

   • ddl: registra todas las instrucciones del lenguaje de definición de datos (DDL) que no están incluidas en la clase ROLE.

   • function: registra llamadas a funciones y bloques DO.

   • misc: registra comandos variados como, por ejemplo, DISCARD, FETCH, CHECKPOINT, VACUUM y SET.

   • read: registra SELECT y COPY cuando el origen es una relación (como una tabla) o una consulta.

   • role: registra instrucciones relacionadas con roles y privilegios, como GRANT, REVOKE, CREATE ROLE, ALTER ROLE y DROP ROLE.

   • write: registra INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una relación (tabla).

   Reinicie la instancia de escritor de su clúster de bases de datos Aurora PostgreSQL mediante el siguiente comando AWS CLI.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

Auditoría de objetos de base de datos

Con PGAudit configurado en su clúster de base de datos de Aurora PostgreSQL y configurado según sus requisitos, se captura información más detallada en el registro de PostgreSQL. Por ejemplo, si bien la configuración de registro predeterminada de PostgreSQL identifica la fecha y la hora en que se realizó un cambio en una tabla de base de datos, con la extensión pgAudit la entrada de registro puede incluir el esquema, el usuario que realizó el cambio y otros detalles, según cómo estén configurados los parámetros de la extensión. Puede configurar la auditoría para realizar un seguimiento de los cambios de las siguientes maneras.

• Para cada sesión, por usuario. Para el nivel de sesión, puede capturar el texto completo del comando.

• Para cada objeto, por usuario y por base de datos.

La capacidad de auditoría de objetos se activa cuando se crea el rol rds_pgaudit en el sistema y, a continuación, se agrega este rol al parámetro pgaudit.role del grupo de parámetros personalizados. De forma predeterminada, el parámetro pgaudit.role no está configurado y el único valor permitido es rds_pgaudit. En los siguientes pasos se asume que pgaudit se ha inicializado y que ha se ha creado la extensión pgaudit siguiendo el procedimiento descrito en Configuración de la extensión pgAudit.

Como se muestra en este ejemplo, la línea «LOG: AUDIT: SESSION» proporciona información sobre la tabla y su esquema, entre otros detalles.

Para configurar la auditoría de objetos

1. Use psql para conectarse a la instancia de escritor de su clúster de base de datos de Aurora PostgreSQL.

   psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb

2. Cree un rol de base de datos llamado rds_pgaudit mediante el siguiente comando.

   labdb=> CREATE ROLE rds_pgaudit;
   CREATE ROLE
   labdb=> 

3. Cierre la sesión de psql.

   labdb=> \q

   En los siguientes pasos, use elAWS CLI para modificar los parámetros del registro de auditoría en el grupo de parámetros personalizado.

4. Utilice el siguiente comando AWS CLI para establecer el parámetro pgaudit.role en rds_pgaudit. De forma predeterminada, este parámetro está vacío y rds_pgaudit es el único valor permitido.

   aws rds modify-db-parameter-group \
      --db-parameter-group-name custom-param-group-name \
      --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
      --region aws-region

5. Reinicie AWS CLIla instancia de escritor de su clúster de base de datos de Aurora PostgreSQL para que sus cambios en los parámetros surtan efecto.

   aws rds reboot-db-instance \
       --db-instance-identifier writer-instance \
       --region aws-region

6. Ejecute el siguiente comando para confirmar que pgaudit.role se establece en rds_pgaudit.

   SHOW pgaudit.role;
   pgaudit.role 
   ------------------
   rds_pgaudit 

Para probar el registro de pgAudit, puede ejecutar varios comandos de ejemplo que desee auditar. Por ejemplo, podría ejecutar los siguientes comandos.

CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id 
----
(0 rows)

Los registros de base de datos contendrán una entrada similar a la siguiente.

...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...

Para obtener información acerca de la visualización de los registros, consulte Supervisión de archivos de registro de Amazon Aurora.

Para obtener más información sobre la extensión pgAudit, consulte pgAudit en GitHub.

Exclusión de usuarios o bases de datos del registro de auditoría

Como se explica en Archivos de registro de bases de datos de Aurora PostgreSQL, los registros de PostgreSQL consumen espacio de almacenamiento. El uso de la extensión pgAudit aumenta el volumen de datos recopilados en los registros en diversos grados, según los cambios de los que realice un seguimiento. Es posible que no necesite auditar todos los usuarios o bases de datos de su clúster de base de datos de Aurora PostgreSQL.

Para minimizar los impactos en el almacenamiento y evitar la captura innecesaria de registros de auditoría, puede excluir a los usuarios y las bases de datos de la auditoría. También puede cambiar el registro dentro de una sesión determinada. Los siguientes ejemplos muestran cola forma de hacerlo.

nota

La configuración de los parámetros a nivel de sesión tiene prioridad sobre la configuración del grupo de parámetros del grupo de parámetros del clúster DB personalizado para la instancia de escritor del clúster DB de Aurora PostgreSQL. Si no desea que los usuarios de la base de datos omitan los ajustes de configuración del registro de auditoría, asegúrese de cambiar sus permisos.

Supongamos que su clúster de base de datos de Aurora PostgreSQL está configurado para auditar el mismo nivel de actividad para todos los usuarios y bases de datos. A continuación, decide que no desea auditar al usuario myuser. Puede desactivar la auditoría de myuser con el siguiente comando de SQL.

ALTER USER myuser SET pgaudit.log TO 'NONE';

A continuación, puede utilizar la siguiente consulta para comprobar la columna user_specific_settings de pgaudit.log para confirmar que el parámetro está establecido en NONE.

SELECT
    usename AS user_name,
    useconfig AS user_specific_settings
FROM
    pg_user
WHERE
    usename = 'myuser';

Debería ver una salida como la siguiente.

 user_name | user_specific_settings
-----------+------------------------
 myuser    | {pgaudit.log=NONE}
(1 row)

Puede desactivar el registro de un usuario determinado en medio de su sesión con la base de datos con el siguiente comando.

ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';

Utilice la siguiente consulta para comprobar la columna de configuración de pgaudit.log para una combinación específica de usuario y base de datos.

SELECT
    usename AS "user_name",
    datname AS "database_name",
    pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
    pg_catalog.pg_db_role_setting s
    LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
    LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
    usename = 'myuser'
    AND datname = 'mydatabase'
ORDER BY
    1,
    2;

Se muestra una salida similar a la siguiente.

  user_name | database_name |     settings
-----------+---------------+------------------
 myuser    | mydatabase    | pgaudit.log=none
(1 row)

Tras desactivar la auditoría de myuser, decide que no desea realizar un seguimiento de los cambios en mydatabase. Puede desactivar la auditoría de esa base de datos específica mediante el siguiente comando.

ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';

A continuación, utilice la siguiente consulta para comprobar la columna database_specific_settings y confirmar que pgaudit.log tiene el valor NONE.

SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';

Debería ver una salida como la siguiente.

 database_name | database_specific_settings
---------------+----------------------------
 mydatabase    | {pgaudit.log=NONE}
(1 row)

Para restablecer la configuración predeterminada de myuser, use el siguiente comando:

ALTER USER myuser RESET pgaudit.log;

Para restablecer la configuración predeterminada de una base de datos, use el siguiente comando:

ALTER DATABASE mydatabase RESET pgaudit.log;

Para restablecer el usuario y la base de datos a la configuración por defecto, utilice el siguiente comando.

ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;

También puede capturar eventos específicos en el registro configurando pgaudit.log en uno de los otros valores permitidos para el parámetro pgaudit.log. Para obtener más información, consulte Lista de ajustes permitidos para el parámetro pgaudit.log.

ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'

Referencia para la extensión pgAudit

Puede especificar el nivel de detalle que desea para el registro de auditoría cambiando uno o más de los parámetros que se enumeran en esta sección.

Control del comportamiento de pgAudit

Puede controlar el registro de auditoría cambiando uno o más de los parámetros que aparecen en la tabla siguiente.

Parámetro	Descripción
pgaudit.log	Especifica las clases de instrucciones que se registrarán mediante el registro de auditoría de sesión. Los valores permitidos incluyen ddl, function, misc, read, role, write, none, all. Para obtener más información, consulte Lista de ajustes permitidos para el parámetro pgaudit.log.
pgaudit.log_catalog	Cuando se activa (se establece en 1), agrega instrucciones al registro de auditoría si todas las relaciones de una instrucción se encuentran en pg_catalog.
pgaudit.log_level	Especifica el nivel de registro que se usará para las entradas de registro. Valores permitidos, debug5, debug4, debug3, debug2, debug1, info, notice, warning, log.
pgaudit.log_parameter	Cuando se activa (se establece en 1), los parámetros transmitidos con la instrucción se capturan en el registro de auditoría.
pgaudit.log_relation	Cuando se activa (se establece en 1), el registro de auditoría de la sesión crea una entrada de registro separada para cada relación (TABLE, VIEW, etc.) referenciada en una instrucción SELECT o DML.
pgaudit.log_statement_once	Especifica si el registro incluirá el texto de la instrucción y los parámetros con la primera entrada de registro para una combinación de instrucción o subinstrucción o con cada entrada.
pgaudit.role	Especifica el rol maestro que se usará para el registro de auditoría de objetos. La única entrada permitida es rds_pgaudit.

Lista de ajustes permitidos para el parámetro pgaudit.log

Valor	Descripción
Ninguno	Esta es la opción predeterminada. No se registran cambios en la base de datos.
Todos	Registra todo (read, write, function, role, ddl, misc).
ddl	Registra todas las instrucciones del lenguaje de definición de datos (DDL) que no están incluidas en la clase ROLE.
función	Registra llamadas a funciones y bloques DO.
misc	Registra comandos variados como, por ejemplo, DISCARD, FETCH, CHECKPOINT, VACUUM y SET.
leer	Registra SELECT y COPY cuando el origen es una relación (como una tabla) o una consulta.
role	Registra instrucciones relacionadas con roles y privilegios, como, por ejemplo, GRANT, REVOKE, CREATE ROLE, ALTER ROLE y DROP ROLE.
write	Registra INSERT, UPDATE, DELETE, TRUNCATE y COPY cuando el destino es una relación (tabla).

Para registrar varios tipos de eventos con auditorías de sesiones, utilice una lista separada por comas. Para registrar todos los tipos de eventos, establezca pgaudit.log en ALL. Reinicie la instancia de base de datos para aplicar los cambios.

Con la auditoría de objetos, puede mejorar los registros de auditoría para que funcionen con algunas relaciones específicas. Por ejemplo, puede especificar que desea crear registros de auditoría para las operaciones READ en una o más tablas.