{"id":807,"date":"2024-12-20T10:17:30","date_gmt":"2024-12-20T09:17:30","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=807"},"modified":"2024-12-20T10:28:53","modified_gmt":"2024-12-20T09:28:53","slug":"extracting-pgaudit-data-as-metrics-in-aws","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2024\/12\/extracting-pgaudit-data-as-metrics-in-aws\/","title":{"rendered":"Extracting pgAudit data as metrics in AWS"},"content":{"rendered":"\n

WARNING! This post contains ClickOps methods.<\/p>\n\n\n\n

I have a customer running Autota PostgreSQL in AWS and they wanted to monitor and alert certain database users on how much they read rows from the database.<\/p>\n\n\n\n

For auditing PostgreSQL database there is a extension pgAudit, which is also by default installed in Aurora PostgreSQL. You can read about how to set it up for your AWS database here.<\/a><\/p>\n\n\n\n

To enable auditing only on a specific user<\/p>\n\n\n\n

ALTER USER \"ilmar.kerm\" SET pgaudit.log TO 'all';\nALTER USER \"ilmar.kerm\" SET pgaudit.log_catalog TO 'off';\nALTER USER \"ilmar.kerm\" SET pgaudit.log_parameter TO 'on';\nALTER USER \"ilmar.kerm\" SET pgaudit.log_rows TO 'on';<\/code><\/pre>\n\n\n\n
For my use case, the important bit is also to turn ON pgaudit.log_rows.<\/p>\n\n\n\n
After that is done, pgAudit will emit a log record into the main PostgreSQL text log, that looks like this:<\/p>\n\n\n\n
2024-12-18 02:25:44 UTC:10.20.30.40(53162):ilmar.kerm@egp:[32766]:LOG:  AUDIT: SESSION,94,1,READ,SELECT,,,\"SELECT id, value, somethingelse FROM \"\"APPLSCHEMA1\"\".\"\"TableName\"\"\",<none>,42<\/pre>\n\n\n\n
This audit record tells me that user “ilmar.kerm”<\/strong> executed SELECT id, value, somethingelse FROM “APPLSCHEMA1″.”TableName”<\/strong>, and that query returned 42<\/strong> rows.<\/p>\n\n\n\n
I now need to extract that value 42 from these audit rows and turn them into CloudWatch metric values.<\/p>\n\n\n\n
Cloudwatch Metrics<\/h2>\n\n\n\n
To extract metric values from CloudWatch Logs, there are Metric filters<\/a>, but they can only extract values from JSON or SPACE-DELIMITED log records. The pg_audit record above was COMMA-DELIMITED.<\/p>\n\n\n\n
Very recently AWS announced an ability to transform logs (into JSON) on ingestion using pre-built or custom parsers<\/a>. And the transformed logs can also be used to create Metric filters.<\/p>\n\n\n\n
Don’t worry, the transformed log is store in addition to the old format, the old format is still accessible as-is. This ofcourse means that the amount of logs CloudWatch stores for PostgreSQL will double. A thing to keep in mind, and not enable auditing for the busy application users.<\/p>\n\n\n\n
NB! The examples below use the dreaded ClickOps methodology, since currently AWS Terraform modules do not support this new feature.<\/p>\n\n\n\n
First have to turn on the log transformer:<\/p>\n\n\n\n
    \n
  1. Open the PostgreSQL database CloudWatch Log Group \/aws\/rds\/cluster\/CLUSTER_NAME\/postgresql<\/strong><\/li>\n\n\n\n
  2. Navigate to Transformer<\/strong> and click Create transformer<\/strong><\/li>\n\n\n\n
  3. Choose a parser: Postgres<\/strong><\/li>\n\n\n\n
  4. Press Add processor<\/strong><\/li>\n\n\n\n
  5. Processor: CSV<\/strong> Source: body<\/strong> Delimiter: ,<\/strong> Quote character: “<\/strong><\/li>\n\n\n\n
  6. Under Columns it would be possible to give each column under body a proper name, but I’m not going to, since the transformations apply to all PostgreSQL log records, not just pgAudit.<\/li>\n\n\n\n
  7. Press Save<\/strong><\/li>\n<\/ol>\n\n\n\n
    Here is how the transformer settings look and also how a new transformed audit record will look like.<\/p>\n\n\n\n
    \"\"<\/a><\/figure>\n\n\n\n
    Every field is beautifully split into JSON attributes that we can extract for metric values. This is what we’ll do next.<\/p>\n\n\n\n
    Creating a metric to count all rows affected by audited operations.<\/p>\n\n\n\n
      \n
    1. Navigate to Metric filters<\/strong> and press Create metric filter<\/strong><\/li>\n\n\n\n
    2. Create filter pattern: { $.body.column_1 = “AUDIT: SESSION” }<\/strong><\/li>\n\n\n\n
    3. Enable metric filter on transformed logs: ON<\/strong><\/li>\n\n\n\n
    4. Next<\/strong><\/li>\n\n\n\n
    5. Filter name: write something yourself<\/li>\n\n\n\n
    6. Metric namespace<\/strong> and Metric name<\/strong> – write something yourself, this is how the metric will be named under CloudWatch Metrics<\/strong><\/li>\n\n\n\n
    7. Metric value: $.body.column_10<\/strong><\/li>\n\n\n\n
    8. Unit: Count<\/strong><\/li>\n\n\n\n
    9. Dimension name: operation<\/strong>, Dimension value: $.body.column_4<\/strong><\/li>\n<\/ol>\n\n\n\n
      \"\"<\/a><\/figure>\n\n\n\n
      \"\"<\/a><\/figure>\n\n\n\n
      Something to keep in mind – auditing COPY command<\/h2>\n\n\n\n
      pg_audit always reports that COPY command returns 0 rows. I would alert on all executed COPY commands.<\/p>\n\n\n\n
      To just count the COPY operations executed, create a new metric filter.<\/p>\n\n\n\n
        \n
      1. Navigate to Metric filters<\/strong> and press Create metric filter<\/strong><\/li>\n\n\n\n
      2. Create filter pattern: { ($.body.column_1 = “AUDIT: SESSION”) && ($.body.column_4 = “READ”) && ($.body.column_5 = “COPY”) }<\/strong><\/li>\n\n\n\n
      3. Enable metric filter on transformed logs: ON<\/strong><\/li>\n\n\n\n
      4. Next<\/strong><\/li>\n\n\n\n
      5. Filter name: write something yourself<\/li>\n\n\n\n
      6. Metric namespace<\/strong> and Metric name<\/strong> – write something yourself, this is how the metric will be named under CloudWatch Metrics<\/strong><\/li>\n\n\n\n
      7. Metric value: 1<\/strong><\/li>\n\n\n\n
      8. Unit: Count<\/strong><\/li>\n<\/ol>\n\n\n\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        WARNING! This post contains ClickOps methods. I have a customer running Autota PostgreSQL in AWS and they wanted to monitor and alert certain database users on how much they read rows from the database. For auditing PostgreSQL database there is a extension pgAudit, which is also by default installed in Aurora PostgreSQL. You can read […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[70,19],"class_list":["post-807","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-aws","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/807","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=807"}],"version-history":[{"count":7,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/807\/revisions"}],"predecessor-version":[{"id":817,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/807\/revisions\/817"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=807"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=807"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=807"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}