Esta página se ha traducido con Cloud Translation API.

Auditar PostgreSQL con pgAudit

MySQL | PostgreSQL | SQL Server

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

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

Informació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.

Con esta extensión, puede registrar y monitorizar de forma selectiva las operaciones SQL realizadas en una instancia de base de datos determinada. La extensión te ofrece funciones de auditoría para monitorizar y registrar un subconjunto de operaciones.

La extensión pgAudit se aplica a los comandos y las consultas SQL ejecutados. Por el contrario, Registros de auditoría de Cloud se debe usar para auditar las operaciones de administración y 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.

Configurar la auditoría de bases de datos en Cloud SQL

Para registrar auditorías con la extensión pgAudit, sigue estos pasos:

Habilitar la marca cloudsql.enable_pgaudit en Cloud SQL.
Ejecutar un comando para crear la extensión pgAudit.
Definir valores para la marca pgaudit.log.

Una vez que hayas configurado la auditoría de la base de datos, podrás ver los registros y, si es necesario, inhabilitar el registro.

Configurar la auditoría

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

Marca inicial para habilitar la auditoría.

En Cloud SQL, se usan marcas de bases de datos para muchas operaciones, como ajustar los parámetros de PostgreSQL y configurar una instancia. La marca cloudsql.enable_pgaudit habilita la auditoría de una instancia de base de datos determinada. Puede cambiar el valor de la marca cloudsql.enable_pgaudit mediante la consola Google Cloud o con el comando gcloud.

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

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

La marca cloudsql.enable_pgaudit se incluye en la lista de 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 con un cliente psql compatible. El siguiente comando crea la extensión pgAudit para todas las bases de datos de una instancia de Cloud SQL:

CREATE EXTENSION pgaudit;

Definir valores para la marca `pgaudit.log`

Siga las instrucciones estándar para las marcas para definir los valores de la marca pgaudit.log.

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

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

Configurar otros ajustes de la base de datos

Para configurar los ajustes de auditoría de la base de datos, sigue los procedimientos que se indican en la sección Personalizar el registro de auditoría de la base de datos.

Ver registros de auditoría de bases de datos

Para ver los registros de auditoría, habilita los registros de auditoría de acceso a datos de 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 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 seleccionando el filtro de registro cloudaudit.googleapis.com/data_access.

También puedes usar la siguiente consulta para mostrar todos los registros de pgAudit de 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 de pgAudit

Cada entrada de registro de pgAudit de los registros de auditoría de acceso a datos tiene campos que representan la información recogida de 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 describen los campos de los registros de auditoría de acceso a datos:

auditClass. El tipo de instrucción que se registra. Los valores posibles son READ, WRITE, FUNCTION, ROLE, DDL, MISC y MISC_SET.
auditType. SESSION o OBJECT.
chunkCount. La fragmentación puede producirse en los datos proporcionados en los campos parameter y statement. El campo chunkCount indica el número total de fragmentos. Consulta también la descripción del campo chunkIndex.
chunkIndex. Especifica el número de índice de los fragmentos de datos en 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.
comando. 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 define el valor de pgaudit.log_parameter, el campo parameter puede contener los parámetros de la instrucción como datos CSV entre comillas. Si no hay parámetros, este campo contiene [none]. De lo contrario, este campo contiene [not logged].
statement. Declaración que se ha ejecutado en el backend. El campo chunkIndex puede determinar el contenido del campo statement. Consulta la descripción del campo chunkIndex.
statementId. ID de instrucción único de esta sesión. Cada ID de instrucción representa una llamada backend. Los IDs de las instrucciones son secuenciales, aunque no se registren algunas instrucciones.
substatementId. ID secuencial de cada subafirmación dentro de la afirmación principal.

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

Inhabilitar la auditoría

Para inhabilitar la auditoría de bases de datos, asigna el valor off a la marca cloudsql.enable_pgaudit. El valor se puede cambiar a través de la consola Google Cloud o mediante el comando gcloud. Sigue las instrucciones estándar para las marcas para inhabilitar la marca cloudsql.enable_pgaudit.

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

DROP EXTENSION pgaudit;

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

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

Para obtener más información sobre las funciones de la extensión, consulta la documentación de pgAudit.

Requisitos de los privilegios de superusuario

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

Configurar la auditoría de todas las operaciones de la base de datos en la instancia

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

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

Configurar operaciones específicas en todas las bases de datos de la instancia

Para auditar todas las bases de datos de instancias, puedes usar la consolaGoogle Cloud o el comando gcloud. Por ejemplo, para activar la auditoría solo para las operaciones de lectura y escritura en la instancia, puedes usar el siguiente comando 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 sobrescribe las marcas de la base de datos.

Configurar la auditoría de una base de datos específica

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

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

Configurar la auditoría de una relación

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

Cuando auditas una relación, se asigna un rol de auditor único al parámetro pgaudit.role. Se registra cualquier objeto o relación al que se le conceda este rol.

Por ejemplo, para configurar la auditoría de todas las consultas SELECT en la relación salary de la base de datos employee, puede 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 puede auditar un subconjunto de columnas de una relación determinada.

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

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

Configurar la auditoría de un usuario de base de datos

Para activar la auditoría de un usuario específico, defina el parámetro pgaudit.log a nivel de ROLE.

Por ejemplo, el siguiente comando SQL define la auditoría de todas las operaciones de la base de datos ejecutadas por el usuario Alice:

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

Consejos para gestionar auditorías en Cloud SQL

Cuando personalice el comportamiento de la auditoría, tenga en cuenta lo siguiente:

Cuando se desactiva la marca de la base de datos cloudsql.enable_pgaudit, se detiene inmediatamente el registro de auditoría. Sin embargo, los ajustes de pgAudit aplicados (por ejemplo, los ajustes del parámetro pgaudit.log) se conservan, a menos que se eliminen explícitamente.
La instancia de base de datos se reinicia cada vez que se cambia el valor de la marca de base de datos de cloudsql.enable_pgaudit.
Los usuarios de la base de datos creados mediante comandos CREATE ROLE explícitos no tienen el privilegio de modificar los ajustes de auditoría. Solo los usuarios de la base de datos creados a través de la consola Google Cloud y el comando gcloud pueden modificar los ajustes de auditoría.
Si habilitas tanto el registro de auditoría de sesiones como el de objetos, se añadirán a los registros las instrucciones relacionadas con ambos. El registro de sesiones y el registro 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 temporalmente en el disco de su instancia, lo que ocupa espacio en disco antes de que se envíen a Cloud Logging. Por lo tanto, revisa toda la información siguiente antes de usar esta función:

La tasa de ingestión de registros es de 4 MB por segundo. Si la carga de la generación de registros supera la tasa de ingestión, puede ocurrir lo siguiente:
- Puede producirse un aumento no deseado del uso del disco.
- El espacio en disco puede agotarse.
Si has habilitado esta función y ejecutas muchas consultas que cumplen tus criterios de auditoría, el uso del disco puede aumentar demasiado rápido.
Antes de usar esta función, debes hacer lo siguiente:
- Habilita los aumentos automáticos de almacenamiento.
- Monitoriza el uso general del disco. La carga de la generación de registros no se puede monitorizar por separado. Usa la métrica cloudsql.googleapis.com/database/disk/utilization en el explorador de métricas.
- Si es necesario, reduce el uso del disco ejecutando menos consultas o reduciendo la auditoría.
Si se agota el espacio en disco disponible, es posible que se pierdan los registros de auditoría de algunas consultas.