/
Setting up PGAudit for use with kasm_db

Setting up PGAudit for use with kasm_db

Owned by LaTroy, created
Last updated: Jan 17, 2024
3 min read

PGAudit provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.

 Instructions

PGAudit

PGAudit provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.

 

1. Enabling the Kasm Postgres docker image

Normally kasm uses the official postgres docker image. To utilize the pgaudit extension the postgres shared preload libraries must be installed as a prerequisite. These libraries have been preinstalled on a custom postgres image for use with the Kasm deployment. The “kasmweb/postgres:1.12.0“ image is a direct replacement for the official “postgres:12-alpine” docker image on a Kasm database role server or Kasm single server. Follow the instructions below for either “Existing deployments” or “New deployments” to modify the kasm docker compose file to start Kasm with the “kasmweb/postgres:1.12.0“ image.

Existing deployments

Stop the kasm services:

sudo /opt/kasm/bin/stop

In order to integrate this image in an existing Kasm Workspaces deployment the Docker Compose files used for the deployment will need to be modified using the following command:

sudo sed -i 's/postgres:12-alpine/kasmweb\/postgres:1.12.0/g' /opt/kasm/current/docker/docker-compose.yaml

Restart the kasm services:

sudo /opt/kasm/bin/start

New deployments

In order to use this image you will need to modify the installer's Docker Compose files to point to this new database image. For this example we will be using the current Kasm Workspaces 1.12.0 release. Change the url in the wget to the download url for your version of kasm, and the corresponding filename in the .tar.gz file. The final line (“sed …”) will keep its reference to “postgres:12-alpine" and “postgres:1.12.0“ regardless of your kasm version.:

wget https://kasm-static-content.s3.amazonaws.com/kasm_release_1.12.0.d4fd8a.tar.gz tar -xf kasm_release_1.12.0.d4fd8a.tar.gz sed -i 's/postgres:12-alpine/kasmweb\/postgres:1.12.0/g' kasm_release/docker/docker-compose-*

Before installing be sure to follow the instructions in the “Enabling the custom extension” section of this document.

 

2. Enabling the custom extension

Existing deployments

Stop the kasm services:

sudo /opt/kasm/bin/stop

The PGAudit extension will need to be enabled in /opt/kasm/current/conf/database/postgresql.conf. This can be achieved with:

sudo sed -i "/^#shared_preload_libraries/c\shared_preload_libraries = 'pgaudit'" /opt/kasm/current/conf/database/postgresql.conf

Restart the kasm services:

sudo /opt/kasm/bin/start

New deployments

From the directory your installer is extracted to run:

sed -i "/^#shared_preload_libraries/c\shared_preload_libraries = 'pgaudit'" kasm_release/conf/database/postgresql.conf

Now follow the standard installation using your modified installer with both the new image and postgresql.conf settings.

 

3. Post deployment

Once the modifications have been made to enable the PGAudit extension you will need to enter the database to configure the extension. In this example we will be enabling logging for read, write, and ddl classes of statements.

sudo docker exec -it kasm_db psql -U kasmapp -d kasm kasm=# CREATE EXTENSION pgaudit; CREATE EXTENSION kasm=# ALTER DATABASE kasm set pgaudit.log='read,write,ddl'; ALTER DATABASE

With the extension enabled and configured the default log will produce log entries for the classes of statements you defined in the file /opt/kasm/current/log/postgres/postgresql-*.log

Here are the classes available for logging:

  • pgaudit.log: Specifies which classes of statements will be logged by session audit logging. The default is none. Possible values are:

    • 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.

 

Related Docs:

  • Links to related docs in the kasm_docs project

 Related articles

  • Links to related kb articles in the Confluence project

 

Related content