Audita para PostgreSQL mediante pgAudit

MySQL | PostgreSQL | SQL Server

En esta página, se describe la auditoría de la base de datos mediante la extensión pgAudit, que te ayuda a configurar muchos de los registros que suelen requerirse para cumplir con las certificaciones gubernamentales, financieras y de ISO.

Para obtener información general sobre las extensiones de PostgreSQL en Cloud SQL, consulta Extensiones de PostgreSQL.

Descripción general

La auditoría de bases de datos en Cloud SQL para PostgreSQL está disponible a través de la extensión de código abierto pgAudit.

Mediante esta extensión, puedes registrar y realizar un seguimiento de operaciones de SQL de forma selectiva en una instancia de base de datos determinada. La extensión te brinda capacidades de auditoría para supervisar y registrar un subconjunto de operaciones determinado.

La extensión pgAudit se aplica a las consultas y los comandos de SQL ejecutados. Por el contrario, los registros de auditoría de Cloud deben usarse para auditar las operaciones administrativas y de mantenimiento que se realizan en una instancia de Cloud SQL.

Consulta la página Registros de auditoría para obtener más información sobre el registro de auditoría en Cloud SQL.

Configura la auditoría de la base de datos en Cloud SQL

Estos son los pasos para usar el registro de auditoría con la extensión pgAudit:

Habilita la marca cloudsql.enable_pgaudit en Cloud SQL.
Ejecuta un comando para crear la extensión pgAudit.
Establece valores para la marca pgaudit.log.

Después de configurar la auditoría de la base de datos, puedes ver los registros y, si es necesario, inhabilitar el registro.

Configura la auditoría

En esta sección, se describen los conceptos básicos para configurar las operaciones de auditoría de la base de datos.

Marca inicial para habilitar la auditoría

En Cloud SQL, se usan marcas de bases de datos para muchas operaciones, incluidos el ajuste de parámetros de PostgreSQL y la configuración de una instancia. La marca cloudsql.enable_pgaudit habilita la auditoría para una instancia de base de datos determinada. Puedes cambiar el valor de la marca cloudsql.enable_pgaudit mediante la consola de Cloud o el comando de gcloud.

Usa las instrucciones estándar para las marcas a fin de habilitar la marca cloudsql.enable_pgaudit y establece el valor en on. Por ejemplo, a fin de usar el comando de gcloud, especifica lo siguiente y sustituye el nombre de tu instancia por [INSTANCE_NAME]:

gcloud sql instances patch [INSTANCE_NAME] --database-flags cloudsql.enable_pgaudit=on

La marca cloudsql.enable_pgaudit se enumera con las otras marcas admitidas y es específica de Cloud SQL.

Ejecuta el comando para crear la extensión pgAudit

Después de habilitar la marca de la base de datos, ejecuta el comando CREATE EXTENSION mediante un cliente psql compatible. Mediante el siguiente comando, se crea la extensión pgAudit para todas las bases de datos en una instancia de Cloud SQL:

CREATE EXTENSION pgaudit;

Establece valores para la marca `pgaudit.log`

Usa las instrucciones estándar para las marcas a fin de establecer valores para la marca pgaudit.log.

Por ejemplo, para activar la auditoría de todas las operaciones de la base de datos de una instancia, puedes usar el siguiente comando de gcloud:

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Establece otra configuración para la base de datos

Para establecer la configuración de auditoría de la base de datos, sigue los procedimientos que se indican en la sección Personaliza el registro de auditoría de la base de datos.

Ve registros de auditoría de bases de datos

Para ver los registros de auditoría, habilita los registros de auditoría de acceso a los datos en tu proyecto. Los registros de pgAudit generados para una instancia determinada se envían a Cloud Logging como registros de auditoría de acceso a los datos. Los usuarios pueden ver los registros de pgAudit generados a través de la aplicación Explorador de registros.

En la aplicación Explorador de registros, los registros de pgAudit se pueden ver si seleccionas el filtro de registros cloudaudit.googleapis.com/data_access.

Como alternativa, puedes usar la siguiente consulta para mostrar todos los registros de pgAudit para un proyecto de Cloud SQL determinado:

resource.type="cloudsql_database"
logName="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"

Formato de registro para pgAudit

Cada entrada de registro de pgAudit en los registros de auditoría de acceso a los datos tiene campos que representan la información recopilada para una consulta.

A continuación, se muestra un ejemplo:

{
  protoPayload: {
    @type: "type.googleapis.com/google.cloud.audit.AuditLog"
    methodName: "cloudsql.instances.query"
    request: {
      @type: "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"
      auditClass: "READ"
      auditType: "SESSION"
      chunkCount: "1"
      chunkIndex: "1"
      command: "SELECT"
      database: "finance"
      databaseSessionId: 2209692
      parameter: "[not logged]"
      statement: "SELECT * FROM revenue"
      statementId: 2
      substatementId: 1
      user: "alice"
    }
  }
}

A continuación, se presentan descripciones de los campos en los registros de auditoría de acceso a los datos:

auditClass. El tipo de declaración que se registra. Los valores posibles son READ, WRITE, FUNCTION, ROLE, DDL, MISC y MISC_SET.
auditType. Es SESSION o OBJECT.
chunkCount. Puede producirse una fragmentación en los datos proporcionados en los campos parameter y statement. El campo chunkCount indica la cantidad total de fragmentos. Consulta también la descripción del campo chunkIndex.
chunkIndex. Especifica la cantidad de índices de los fragmentos de datos de los campos parameter y statement (en el contenedor request actual). El número inicial es 1. Consulta también la descripción del campo chunkCount.
command. Por ejemplo, ALTER TABLE o SELECT.
parameter. El campo chunkIndex puede determinar el contenido de este campo; consulta la descripción del campo chunkIndex. Si se establece el valor de pgaudit.log_parameter, el campo parameter puede contener los parámetros de declaración como datos CSV entrecomillados. Si no hay parámetros, este campo contiene [none]. De lo contrario, este campo contiene [not logged].
statement. Es la declaración que se ejecutó en el backend. El campo chunkIndex puede determinar el contenido del campo statement. Consulta la descripción del campo chunkIndex.
statementId. Es el ID de declaración único para esta sesión. Cada ID de declaración representa una llamada al backend. Los ID de declaración son secuenciales, incluso si no se registran algunas declaraciones.
substatementId. Es el ID secuencial para cada subdeclaración dentro de la declaración principal.

Algunos de estos campos también se describen en la documentación de pgAudit.

Inhabilita la auditoría

Para inhabilitar la auditoría de la base de datos, establece el valor de la marca cloudsql.enable_pgaudit en off. Se puede cambiar el valor mediante la consola de Cloud o el comando de gcloud. Usa las instrucciones estándar para marcas a fin de inhabilitar la marca cloudsql.enable_pgaudit.

Además, ejecuta el comando DROP EXTENSION mediante un cliente psql compatible para quitar el estado de la extensión:

DROP EXTENSION pgaudit;

Personaliza el registro de auditoría de bases de datos en Cloud SQL

En esta sección, se describen las maneras de personalizar el comportamiento de auditoría de una instancia de base de datos.

Para conocer las capacidades adicionales de la extensión, consulta la documentación de pgAudit.

Requisitos para los privilegios de los superusuarios

En Cloud SQL, solo los usuarios que forman parte de la función cloudsqlsuperuser pueden crear extensiones. Cuando creas una instancia nueva de PostgreSQL, se crea el usuario predeterminado de PostgreSQL (aunque debes configurar la contraseña del usuario). El usuario predeterminado de PostgreSQL es parte de la función cloudsqlsuperuser. Para obtener más información, consulta Usuarios de PostgreSQL.

Configura la auditoría para todas las operaciones de bases de datos en la instancia

Para configurar la auditoría de todas las bases de datos de una instancia, debes aplicar la configuración de pgAudit a nivel del sistema. Los parámetros de auditoría a nivel del sistema pueden establecerse solo como marcas de la base de datos mediante la consola de Google Cloud o el comando de gcloud. Por ejemplo, para activar la auditoría de todas las operaciones de la base de datos de una instancia, puedes usar el siguiente comando de gcloud:

  gcloud sql instances patch [INSTANCE_NAME] --database-flags \
  cloudsql.enable_pgaudit=on,pgaudit.log=all

Configura operaciones específicas en todas las bases de datos de una instancia

Para realizar una auditoría en todas las bases de datos de una instancia, puedes usar la consola de Google Cloud o el comando de gcloud. Por ejemplo, a fin de activar la auditoría para operaciones de lectura y escritura en la instancia, puedes usar el siguiente comando de gcloud. En este ejemplo, se usa una sintaxis basada en listas para especificar varios valores:

  gcloud sql instances patch [INSTANCE_NAME] \
  --database-flags ^:^cloudsql.enable_pgaudit=on:pgaudit.log=read,write

El comando reemplaza las marcas de base de datos existentes.

Configura la auditoría para una base de datos específica

Para configurar la auditoría de una base de datos específica, configura los parámetros de pgAudit a nivel de la base de datos. Por ejemplo, el siguiente comando de SQL se puede usar con el fin de activar la auditoría de lectura y escritura para una base de datos llamada finance:

finance=> ALTER DATABASE finance SET pgaudit.log = 'read,write';

Configura la auditoría para una relación

La auditoría para una relación es más estrecha que la auditoría de una base de datos específica.

Cuando auditas una relación, se asigna una función de auditor única al parámetro pgaudit.role. Cualquier objeto o relación que se le otorgue a esta función se registra.

Por ejemplo, a fin de configurar la auditoría de todas las consultas SELECT en la relación salary dentro de la base de datos employee, puedes usar estos comandos:

employee=> CREATE ROLE auditor WITH NOLOGIN;
employee=> ALTER DATABASE employee SET pgaudit.role = 'auditor';
employee=> GRANT SELECT ON salary TO auditor;

También puedes auditar un subconjunto de columnas de una relación determinada.

Por ejemplo, mediante el siguiente comando, se configura el registro de auditoría para que se produzca solo cuando se accede a las columnas income y tax_status desde la relación salary:

employee=> GRANT SELECT(income, tax_status) ON salary TO auditor;

Configura la auditoría para un usuario de la base de datos

Puedes activar la auditoría para un usuario específico si configuras el parámetro pgaudit.log a nivel de ROLE.

Por ejemplo, mediante el siguiente comando de SQL, se establece la auditoría para todas las operaciones de la base de datos que ejecuta el usuario Alice:

finance=> ALTER ROLE alice SET pgaudit.log = 'all';

Sugerencias para la administración de auditorías en Cloud SQL

Cuando personalices el comportamiento de auditoría, recuerda lo siguiente:

Cuando la marca cloudsql.enable_pgaudit de la base de datos está desactivada, el registro de auditoría se detiene de inmediato. Sin embargo, la configuración de pgAudit aplicada (por ejemplo, la configuración del parámetro pgaudit.log) se conserva, a menos que se quite de forma explícita.
La instancia de la base de datos se reinicia cada vez que se cambia el valor de la marca de base de datos para cloudsql.enable_pgaudit.
Los usuarios de la base de datos que se crearon mediante comandos CREATE ROLE explícitos no poseen el privilegio para modificar la configuración de la auditoría. Solo los usuarios de la base de datos que se crearon mediante la consola de Google Cloud y el comando de gcloud pueden modificar la configuración de la auditoría.
Cuando habilitas el registro de auditoría de sesión y el registro de auditoría de objetos, las declaraciones correspondientes a ambos se agregan a los registros. Los registros de sesiones y los de objetos no se cancelan ni se modifican entre sí.

Limitaciones de la extensión pgAudit en Cloud SQL para PostgreSQL

Los registros de auditoría se escriben de forma temporal en el disco de su instancia y ocupan espacio antes de que los registros se envíen a Cloud Logging. Por lo tanto, revisa toda la siguiente información antes de usar esta función:

La frecuencia de transferencia de registros es de 4 MB por segundo. Cuando la carga de la generación de registros supera la tasa de transferencia, puede ocurrir lo siguiente:
- Puede ocurrir un crecimiento no deseado en el uso del disco.
- El espacio en el disco se puede agotar.
Si habilitaste esta función y ejecutas muchas consultas que cumplen con tus criterios de auditoría, el uso del disco puede crecer muy rápido.
Antes de usar esta función, planifica lo siguiente:
- Habilitar los aumentos de almacenamiento automáticos.
- Supervisar el uso general del disco. La carga de la generación de registros no se puede supervisar por separado. Usa la métrica cloudsql.googleapis.com/database/disk/utilization en el Explorador de métricas.
- Si es necesario, reducir el uso del disco ejecutando menos consultas o la auditoría.
Si se agota el espacio en disco disponible, es posible que se pierdan los registros de auditoría de algunas consultas.