CODEWITHSUNDEEP
postgresqlgoogle-cloud-platformgoogle-cloud-sqlgoogle-cloud-loggingcursors
SRJ
SRJ

Reputation: 2846

Getting Insights around PostgreSQL Cursor from Audit Logs : CloudSQL

I have mostly this audit log everywhere in my cloud logging console from cloudsql postgresql audit logs.

{
  "textPayload": "2023-04-27 08:03:17.058 UTC [980077]: [41406-1] db=xyz,user=xyz LOG:  duration: 6166.869 ms  statement: FETCH 10000 FROM c1",
  "insertId": "s=4ae93d4dfabf4fc6b09ckdanknc0f18e2;i=39eexcb;b=eca5cad5acb84ckjndkj9e5a3af5dc7aee47;m=153c87ckac099b;t=5fa4cca00kjbad2ae;x=aa1f7345cca3ckbcjb81d-0-0@a1",
  "resource": {
    "type": "cloudsql_database",
    "labels": {
      "project_id": "xyz",
      "database_id": "xyz:postgres",
      "region": "europe-west9"
    }
  },
  "timestamp": "2023-04-27T08:03:17.059246Z",
  "severity": "INFO",
  "labels": {
    "INSTANCE_UID": "12-d77d8db7-0633-4695-b4c7-0a8cdhdknec9392",
    "LOG_BUCKET_NUM": "38"
  },
  "logName": "projects/xyz/logs/cloudsql.googleapis.com%2Fpostgres.log",
  "receiveTimestamp": "2023-04-30T08:03:20.820646796Z"
}

I tried querying pg_cursors but since there is no active connection, I didn't see any data.

I would like to trace the original query behind this cursor c1 or get more details around this FETCH 10000 FROM c1 query.

How can i get more details by using cloud logging or querying postgresql ?

Upvotes: 0

Views: 383

Answers (1)

Dharani Dhar Golladasari
Dharani Dhar Golladasari

Reputation: 1012

The statement: FETCH 10000 FROM C1 which is showing in the log you mentioned is the original(backend) query which was executed in postgresql. This query will retrieve the next 10000 rows from the cursor C1 because without any direction by default the postgresql considers direction as NEXT.

Refer this link for more detailed inforatmion about FETCH command.

If you want to know more detailed information about this PgAudit log then try executing this query by changing the logName as your logName (logName: "projects/xyz/logs/cloudsql.googleapis.com%2Fpostgres.log")

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"

The output will be something like this

{
  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 C1"
      statementId: 2
      substatementId: 1
      user: "alice"
    }
  }
}

Here the statement value refers to the query executed on the backend. This query will help to get more detailed information about the Audit Logs. Refer to this document for more information about the query and audit logs.

Updated:

Database auditing in Cloud SQL for PostgreSQL is available through the open-source pgAudit extension. By using this extension, you can selectively record and track SQL operations performed against a given database instance. The extension provides you with auditing capabilities to monitor and record a select subset of operations.

The pgAudit extension applies to executed SQL commands and queries. In contrast, Cloud Audit Logs should be used to audit administrative and maintenance operations done on a Cloud SQL instance.

Refer this document to Configure the PostgreSQL pgAudit extension.

Upvotes: 0

Related Questions