postgresql extension pgAudit - ghdrako/doc

Settings

Settings can be specified:

globally (in postgresql.conf or using ALTER SYSTEM ... SET),
at the database level (using ALTER DATABASE ... SET), or
at the role level (using ALTER ROLE ... SET). Note that settings are not inherited through normal role inheritance and SET ROLE will not alter a user's pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit.

Generally with pgaudit we can have two modes of operation or use them combined:

SESSION audit logging
OBJECT audit logging

Session audit logging

Supports most DML, DDL, privilege and misc commands via classes:

READ (select, copy from)
WRITE (insert, update, delete, truncate, copy to)
FUNCTION (function calls and DO blocks)
ROLE (grant, revoke, create/alter/drop role)
DDL (all DDL except those in ROLE)
MISC (discard, fetch, checkpoint, vacuum)

Metaclass all includes all classes. – excludes a class.

Example - configure Session audit logging for all except MISC

pgaudit.log_catalog = off
pgaudit.log = 'all, -misc'
pgaudit.log_relation = 'on'
pgaudit.log_parameter = 'on'

Using session audit logging will give us audit log entries for all operations belonging to the classes defined by pgaudit.log parameter on all tables.

Show audit parameters

SHOW pgaudit.log;
SHOW pgaudit.role;
SHOW log_min_duration_statement;  -- -1 oznacza wszystkie bez wzgledu na czas wykonania
SHOW log_statement;

Exclude user from audit.

Set global audit log

and:

ALTER ROLE acp_bth SET   pgaudit.log="none"

Object audit logging

Gives us fine grained criteria to selected tables/columns via the PostgreSQL’s privilege system. In order to start using Object audit logging we must first configure the pgaudit.role parameter which defines the master role that pgaudit will use. It makes sense not to give this user any login rights.

CREATE ROLE auditor;
ALTER ROLE auditor WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 0;

The we specify this value for pgaudit.role in postgresql.conf:

pgaudit.log = none # no need for extensive SESSION logging
pgaudit.role = auditor

Pgaudit OBJECT logging will work by finding if user auditor is granted (directly or inherited) the right to execute the specified action performed on the relations/columns used in a statement. So if we need to ignore all tables, but have detailed logging to table orders, this is the way to do it:

grant ALL on orders to auditor ;

By the above grant we enable full SELECT, INSERT, UPDATE and DELETE logging on table orders.

One caveat with OBJECT logging is that TRUNCATEs are not logged. We have to resort to SESSION logging for this. But in this case we end up getting all WRITE activity for all tables.

Another thing to keep in mind is that in the case of inheritance if we GRANT access to the auditor on some child table, and not the parent,** actions on the parent table which translate to actions on rows of the child table will not be logged.**

Since logs are generally stored with the OS this may lead to disk space being exhausted very quickly. In cases where it is not possible to limit audit logging to certain tables, be sure to assess the performance impact while testing and allocate plenty of space on the log volume. This may also be true for OLTP environments. Even if the insert volume is not as high, the performance impact of audit logging may still noticeably affect latency.

To limit the number of relations audit logged for SELECT and DML statements, consider using object audit logging (see Object Auditing). Object audit logging allows selection of the relations to be logged allowing for reduction of the overall log volume. However, when new relations are added they must be explicitly added to object audit logging. A programmatic solution where specified tables are excluded from logging and all others are included may be a good option in this case.

Install

Install extension

$ yum install pgaudit14_12

The pgAudit extension must be loaded in shared_preload_libraries. It will need restart instance.

shared_preload_libraries = 'pgaudit, pg_stat_statements'

$ psql -c "show shared_preload_libraries;" # checked first what is the shared_preload_libraries, because if there was anything there (like pg_stat_statements), I should keep it and set new value accordingly.
$ psql -c "alter system set shared_preload_libraries = 'pg_audit';"
$ pg_ctl restart # or $ sudo systemctl restart postgresql.service
$ psql -c 'show shared_preload_libraries'

CREATE EXTENSION pgaudit - The extension installs event triggers which add additional auditing for DDL. pgAudit will work without the extension installed but DDL statements will not have information about the object type and name.

CREATE EXTENSION pgaudit;

pgaudit.log

set pgaudit.log = 'read, ddl';

$ set pg_audit.log = 'all';
$ set pg_audit.log_relation = on;

ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log = 'all, -misc';
ALTER SYSTEM SET pgaudit.log_relation = 'on';
ALTER SYSTEM SET pgaudit.log_parameter = 'on';
SELECT pg_reload_conf();

Verify pbAudit configuration:

SELECT name,setting FROM pg_settings WHERE name LIKE 'pgaudit%';

Configures log record to include user and host

$ alter system set log_line_prefix to '%m [%p] %u %h';
$ select pg_reload_conf();

This GUC variable will change all log lines of PostgreSQL, not only pgaudit's.

Opis parametrow w https://github.com/pgaudit/pgaudit/blob/master/README.md

Object audit logging logs statements that affect a particular relation. Only SELECT, INSERT, UPDATE and DELETE commands are supported. TRUNCATE is not included in object audit logging.

Object audit logging is intended to be a finer-grained replacement for pgaudit.log = 'read, write'.

$ create role auditor;
$ grant select, insert, update, delete on test to auditor;
$ ALTER SYSTEM set pg_audit.role = 'auditor';
$ select pg_reload_conf();
$ grant auditor to myuser;
$ grant select secret_col on test2 to auditor;  # audit specific column in table
$ select id from test2; # will not be loged
$ select secret_col from test2; # will be loged
$ select * from test2; # will be loged

CloudSQL

In Google Cloud Platform (GCP), you can access two types of logs for your managed PostgreSQL instance:

PostgreSQL Server Logs(default) - log output from the PostgreSQL server default logging implementation that uses the postgresql.log file on the host server and stores the logs inside.These logs are then automatically collected by Google Cloud Logging.In the GCP Logging interface, when you query the logs like this:

resource.type="cloudsql_database"
logName="projects/<project_id>/logs/cloudsql.googleapis.com%2Fpostgres.log"

The log content is stored as a string in the textPayload field. This is far from ideal for searching, parsing, or analytics. 2. Audit logging for CloudSQL - detailed records of actions performed on your Cloud SQL resources. These logs help you monitor and maintain security, compliance, and operational integrity within your Google Cloud environment. You are enabling these yourself, and you get to chose what actions you want to log:

Admin Activity Logs - logs record administrative actions that modify the configuration or metadata of Cloud SQL instances. They include operations such as creating or deleting instances, modifying instance settings, changing user permissions etc. These are enabled by default, can not be disabled, and are free of charge.
Data Access Logs - capture actions related to the data stored within the Cloud SQL instances, such as queries executed on the database, connections established, and data read or written. However, it’s important to note that Data Access Logs are not enabled by default and require additional configuration to capture specific database interactions. These logs are particularly useful for auditing purposes and understanding how data is accessed and utilized within your instances.

Data Access Log

https://cloud.google.com/sql/docs/mysql/audit-logging#audited_operations CloudSQL Data Access logs, without any additional settings, record the actions on the CloudSQL only on the GCP IAM level. In simple words, you get to see who did what, where, and when on the database instance (created DB users, created DBs, get/list DBs etc.). But not on the data/SQL query level.

GCP supports pgAudit plugin configured as

Set parameter cloudsql.enable_pgaudit=on
Create extension

CREATE EXTENSION pgaudit;

Configure the pgaudit.log to log the desired actions - This step should be done after the plugin is enabled and installed in the previous steps

pgaudit.log=all

Possible actions

READ: SELECT and COPY when the source is a relation or a query.
WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
FUNCTION: Function calls and DO blocks.
ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
DDL: All DDL that is not included in the ROLE class.
MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.
ALL: Include all of the above.

The steps for audit logging using the pgAudit extension include:

Enabling the cloudsql.enable_pgaudit flag in Cloud SQL - Note: Changing the value of the cloudsql.enable_pgaudit flag restarts the instance.

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

z terraform

database-flags = {
  "cloudsql.enable_pgaudit"="on"
  "pgaudit.log_level"="log"
}

Running a command to create the pgAudit extension.

CREATE EXTENSION pgaudit;
SELECT * FROM pg_extension;

Setting values for the pgaudit.log flag.

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

Note The database instance is restarted whenever the database flag value for cloudsql.enable_pgaudit is changed.

The generated pgAudit logs for a given instance are sent to Cloud Logging as Data Access audit logs. Users can view the generated pgAudit logs through the Logs Explorer application.

Note user has the roles/logging.privateLogViewer "Private Logs Viewer" role - roles/logging.privateLogViewer (Private Logs Viewer) includes roles/logging.viewer, plus the ability to read Access Transparency logs and Data Access audit logs in the _Required and _Default buckets. "Logs Viewer" role is insufficient to read these data access logs

In the Logs Explorer application, the pgAudit logs can be viewed by selecting the cloudaudit.googleapis.com/data_access log filter.

Alternatively, you can use the following query to show all pgAudit logs for a given Cloud SQL project:

resource.type="cloudsql_database"
resource.labels.database_id="<project-name>:<instance-name>"
log_name="projects/<your-project-name>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.request.@type="type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry"
protoPayload.request.user="<user_name>"

resource.type="cloudsql_database"
resource.labels.database_id="<project-name>:<instance-name>"
resource.labels.region="europe-central2"
log_name="projects/<your-project-name>/logs/cloudsql.googleapis.com%2Fpostgres.log"
textPayload: "user=<user_id>"

Skladni do zapytan

Operatory logiczne

AND – Łączy dwa wyrażenia. Logi muszą spełniać oba warunki.

protoPayload.request.statement:"karty" AND severity="ERROR"

OR – Łączy dwa wyrażenia. Logi spełniają przynajmniej jeden z warunków.

protoPayload.request.statement:"karty" OR severity="ERROR"

NOT – Wyklucza logi spełniające warunek.

NOT protoPayload.request.statement:"karty"

Nawiasy – Grupowanie wyrażeń logicznych.

(protoPayload.request.statement:"karty" AND severity="ERROR") OR resource.type="cloudsql_dat

Operatory porównania

= - Dokładne dopasowanie wartości (case-sensitive)

protoPayload.request.user="testowy"

!= – Wyklucza logi z dokładnym dopasowaniem (case-sensitive).
: - Dopasowanie częściowe (substring match, case-insensitive)

protoPayload.request.statement:"osoby"

!: – Wyklucza logi, które zawierają określony tekst (case-insensitive).
> < >= <= – Porównanie wartości liczbowych lub czasowych

timestamp >= "2024-01-01T00:00:00Z"

Wyrażenia regularne

=~ – Dopasowanie do wyrażenia regularnego (case-sensitive).

protoPayload.request.statement=~"^SELECT.*karty$"

!~ – Wyklucza logi pasujące do wyrażenia regularnego (case-sensitive).

protoPayload.request.statement!~"^SELECT.*karty$"

Dodanie (?i) – Ignorowanie wielkości liter w wyrażeniu regularnym.

protoPayload.request.statement=~"(?i)karty"

Google Cloud Logging nie jest domyślnie wrażliwy na wielkość liter.

Compare output from the same query:

postgres.out - zapytanie jest w payload trudne do parsowania

{
  "textPayload": "2024-08-13 13:38:34.877 UTC [324]: [5-1] db=postgres,user=niko STATEMENT:  SELECT now(),pg_postmaster_start_time(),now()-pg_postmaster_start_time()AS uptime;",
  "insertId": "s=f4d18680c28d4535b1801f4f7cc18fba;i=16d92;b=df8987be47b04e87bc73d3fbc9d4d2b2;m=cb52672b;t=61f90b9e611fb;x=69002d1e45d0e8f7-0@a1",
  "resource": {
    "type": "cloudsql_database",
    "labels": {
      "region": "europe-central2",
      "database_id": "sandbox-54321:my-test-db-instance-12345",
      "project_id": "sandbox-54321"
    }
  },
  "timestamp": "2024-08-13T13:38:34.878459Z",
  "severity": "INFO",
  "labels": {
    "INSTANCE_UID": "31-593ce514-610e-4696-8a92-5edbdee09098",
    "LOG_BUCKET_NUM": "40",
    "SOURCE_ID": "39666236633365636534613463343137e3b0c442"
  },
  "logName": "projects/sandbox-54321/logs/cloudsql.googleapis.com%2Fpostgres.log",
  "receiveTimestamp": "2024-08-13T13:39:18.961085035Z"
}

pgAudit - zapytanie jest w oddzielnym polu - łatwe do parsowania

{
  "protoPayload": {
    "@type": "type.googleapis.com/google.cloud.audit.AuditLog",
    "serviceName": "cloudsql.googleapis.com",
    "methodName": "cloudsql.instances.query",
    "resourceName": "instances/my-test-db-instance-12345",
    "request": {
      "objectType": "",
      "statement": "SELECT now(),pg_postmaster_start_time(),now()-pg_postmaster_start_time()AS uptime;",
      "auditType": "SESSION",
      "substatementId": 1,
      "auditClass": "READ",
      "database": "postgres",
      "@type": "type.googleapis.com/google.cloud.sql.audit.v1.PgAuditEntry",
      "databaseSessionId": 324,
      "chunkIndex": 1,
      "command": "SELECT",
      "chunkCount": 1,
      "user": "niko",
      "parameter": "<not logged>",
      "statementId": 4,
      "object": ""
    }
  },
  "insertId": "2#144348189990#my-test-db-instance-12345#audit#1723556321440000000#00000000006be0e2-0@a1",
  "resource": {
    "type": "cloudsql_database",
    "labels": {
      "database_id": "sandbox-54321:my-test-db-instance-12345",
      "region": "europe-central2",
      "project_id": "sandbox-54321"
    }
  },
  "timestamp": "2024-08-13T13:38:41.440Z",
  "severity": "INFO",
  "labels": {
    "LOG_BUCKET_NUM": "40",
    "INSTANCE_UID": "31-593ce514-610e-4696-8a92-5edbdee09098",
    "SOURCE_ID": "39666236633365636534613463343137e3b0c442"
  },
  "logName": "projects/sandbox-54321/logs/cloudaudit.googleapis.com%2Fdata_access",
  "receiveTimestamp": "2024-08-13T13:38:42.753066869Z"
}

postgresql extension pgAudit - ghdrako/doc_snipets GitHub Wiki

Settings

Session audit logging

Show audit parameters

Exclude user from audit.

Object audit logging

Install

CloudSQL

Data Access Log

Skladni do zapytan

Operatory logiczne

Operatory porównania

Wyrażenia regularne

Compare output from the same query:

⚠️ GitHub.com Fallback ⚠️

postgresql extension pgAudit - ghdrako/doc_snipets GitHub Wiki

Settings

Session audit logging

Show audit parameters

Exclude user from audit.

Object audit logging

Install

CloudSQL

Data Access Log

Skladni do zapytan

Operatory logiczne

Operatory porównania

Wyrażenia regularne

Compare output from the same query:

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️