Part 1: Install Ops Agent and DB Extention — Audit VM and Cloud PostgreSQL on Google Cloud Platform

What is an Audit?

An audit especially in IT is the examination and evaluation of an organization’s information technology infrastructure, applications, data use and management, policies, procedures and operational processes against recognized standards or established policies. Audits evaluate if the controls to protect information technology assets ensure integrity and are aligned with organizational goals and objectives [1].

Why Audit is important?

One of the most important IT audits is to ensure that our system is secure and properly tracked. It’s also helping an organization understand the potential risks it faces, IT audit gives an organization a clear strategy on how to action those risks, whether they can be eliminated, mitigated, or tempered by the use of proper controls.

What is the Audit that will be Implemented?

a. OS Logs

• New User Created
• User Updated
• User Deleted

We’ll use a cmdline audit logs to achieve all of that (you’ll find it in Part 2)

b. DB Logs

• New User Created
• User Updated
• User Deleted
• Delete Row
• Insert Tables
• Drop Tables

All of the DB logs can be achieved using pg_audit extension that we’ll enable later in the next section.

We can add more audit items based on our requirements, but for this time we’ll only implement the items listed above.

How to Implement Audit in GCP?

In this Part 1, we’ll start with installing the Agent namely Ops Agent and an Extention need to install in Postgresql.

Pre-requisites:

— Compute Engine / Virtual Machine in GCP

— Cloud SQL (PostgreSQL)

1. Install Ops Agent

For the details to install the Ops Agent on an individual VM you can read in the official documentation[2].

Run this command to install Ops Agent:

curl -sSO https://dl.google.com/cloudagents/add-google-cloud-ops-agent-repo.sh
sudo bash add-google-cloud-ops-agent-repo.sh --also-install 

Check the status Ops Agent

sudo systemctl status google-cloud-ops-agent"*"

This Agent consists of 3 services:

• Google Ops Agent
• Google Ops Agent — Metric Agent
• Google Ops Agent — Logging Agent

After we have installed the Ops Agent, we can see in the Monitoring dashboard for the instance that already install the Agent.

2. Install or Enable DB Extensions

The extension in PostgreSQL that can be used is pg_audit. The goal of PostgreSQL Audit is to provide the tools needed to produce audit logs required to pass certain government, financial, or ISO certification audits [3]. There are two ways to install or enable the pg_audit extension from the GCP console or via terminal using Cloud Shell [4], below are the details:

a. GCP Console

• Go to your Cloud SQL (Postgres) and edit the configuration

• Enabling the cloudsql.enable_pgaudit flag in Cloud SQL.

go to the Flags section -> add flag -> fill the flag name “cloudsql.enable_pgaudit” and set the value “on” -> Done -> Save

*required downtime/restart after adding the flag:

• Running a command to create the pgAudit extension inside of Postgresql

Connect with Cloud SQL first by running this command in CloudShel

gcloud sql connect instance-name --user=postgres
CREATE EXTENSION pgaudit;

• Setting values for the pgaudit.log flag.

go to your cloud sql instance and edit the configuration as the previous step then add the new flag “pgaudit.log” + set the value as “all” and save.

*required downtime/restart after adding the flag

b. Cloud Shell

Using Cloud Shell it more easy to implement, here you go:

• Enabling the cloudsql.enable_pgaudit flag in Cloud SQL.

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

• Running a command to create the pgAudit extension (login to the postgresql first)

CREATE EXTENSION pgaudit;

• Setting values for the pgaudit.log flag.

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

After we enable the flag, it’s also reflected in the console:

Until this step, we have done installing the Ops Agent and pg_audit extension and we’ll continue configuring the logs and alerts. See you in Part 2….

References:

[1] https://rmas.fad.harvard.edu/

[2] https://cloud.google.com/stackdriver/docs/solutions/agents/ops-agent/installation

[3] https://www.pgaudit.org/#:~:text=The%20PostgreSQL%20Audit%20Extension%20(or,financial%2C%20or%20ISO%20certification%20audits.

[4] https://cloud.google.com/sql/docs/postgres/pg-audit#:~:text=Initial%20flag%20to%20enable%20auditing&text=You%20can%20change%20the%20value,enable_pgaudit%20flag%20restarts%20the%20instance.