Skip to content
This repository

The pg_log_userqueries module provides a means for logging queries launched by specific user types. Right now, it only cares about superusers.

The module must be loaded by adding pg_log_userqueries to shared_preload_libraries in postgresql.conf. This means that a server restart is needed to add or remove the module, and even to configure the module.

Configuration Parameters

pg_log_userqueries.log_level (enum)

pg_log_userqueries.log_level is the log level used to log superuser queries. It can be set to any values that log_min_messages accepts. The default value is NOTICE. This parameter can only be set at server start.

pg_log_userqueries.log_label (string)

pg_log_userqueries.log_label sets the prefix to all logs written by this module. The default value is the module's name (pg_log_userqueries). This parameter can only be set at server start.

You can use special characters in log_label:

  • %a for the application name
  • %u for the user name
  • %d for the database name
  • %p for the PID
  • %% for an actual % sign

pg_log_userqueries.log_user (string)

pg_log_userqueries.log_user sets the list of users whose queries will be logged. Use of regular expressions is allowed. Be careful that, if you set this parameter, queries by superuser won't be logged unless the superuser is part of this list. This parameter can only be set at server start.

pg_log_userqueries.log_db (string)

pg_log_userqueries.log_db sets the list of database for which queries will be logged. Use of regular expressions is allowed. Be careful that, if you set this parameter, queries by superuser won't be logged. This parameter can only be set at server start.

pg_log_userqueries.log_destination (string)

pg_log_userqueries.log_destination sets the destination of the log. It could be either stderr (AKA PostgreSQL logs) or syslog. Default value is stderr. This parameter can only be set at server start.

pg_log_userqueries.log_facility (string)

pg_log_userqueries.log_facility sets the facility when logging to syslog. If this parameter is unset (which is the default), logging occurs through the PostgreSQL log. This parameter can only be set at server start.

pg_log_userqueries.log_ident (string)

pg_log_userqueries.syslog_ident sets the identity of the program logging through syslog. The default value is the module's name (pg_log_userqueries). syslog is enabled only when pg_log_userqueries.log_facility is set. This parameter can only be set at server start.

Configuration example

In order to set any of these parameters in your postgresql.conf file, you will need to add pg_log_userqueries to custom_variable_classes. Typical usage might be:

# Configuration example
shared_preload_libraries = 'pg_log_userqueries'

custom_variable_classes = 'pg_log_userqueries'
pg_log_userqueries.log_level = LOG
pg_log_userqueries.log_label = '** SUPERUSER QUERY! **'

This configuration logs each superuser query.

Something went wrong with that request. Please try again.