How to enable PostgreSQL auditing
The PostgreSQL server includes various system variables that specify the configuration of logging. All these variables are stored in the configuration file postgresql.conf. This file is by default stored in the directory: /etc/postgresql/12/main/.
Enabling Log Collector
You can enable log collector daemon by editing the postgresql.conf:
sudo nano /etc/postgresql/12/main/postgresql.conf
A.To configure log collector, perform the following actions:
1.Uncomment the variable log_destination and set to syslog.
# - Where to Log -
log_destination = 'syslog'
# Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
2.Uncomment the following lines:
syslog_facility = 'LOCAL7'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on
3.Uncomment the following variables: log_min_messages, log_min_messages and log_min_messages. Set log_min_messages to info, log_min_messages to error and log_min_messages to 0.
log_min_messages = info
# values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
log_min_error_statement = error
# values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
log_min_duration_statement = 0
# -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
4.Uncomment the following lines and set to on:
log_connections = on
log_disconnections = on
5.Uncomment log_statement variable and set to all:
log_statement = 'all'
B.Configuring Log Collector Structure
You can configure the structure of each log record by various configuration variables. Firstly, let's set up a record header. The record prefix structure is determined by the variable log_line_prefix, which holds the printf style string. The following list shows the most important escape characters:
- %t: Timestamp without milliseconds (%m is with miliseconds). If you want to configure timestamp format to a specific local time then you can set up variable log_timezone to chosen geographical location. For example America/New_York, Europe/Paris, or any other name from the IANA timezone database .
- %p: Process ID.
- %q: If it is non-session process then stop record at this point.
- %d: Name of database.
- %u: User name.
- %h: Remote hostname or IP address. By default, the IP address is recorded. You can set up DNS translation to hostname by enabling variable log_hostname to value on. However, this setting is usually too expensive as it consumes a lot of resources.
- %a: Application name.
- %l: Numbering the records in each session (every session start from number 1).
Set log_line_prefix variable to: %m [%p] %q%u@%d %r
log_line_prefix = '%m [%p] %q%u@%d %r '
# special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
C.To apply the new configuration rules then you must restart the PostgreSQL server:
sudo systemctl restart postgresql.service
Summary:
If you set up all described variables in postgresql.conf and restart the server then you can view the content of the new logs.
For example, we will use following postgresql.conf configuration:
log_destination = 'syslog'
syslog_facility = 'LOCAL7'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on
log_min_messages = info
log_min_error_statement = error
log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_statement = 'all'
log_line_prefix = '%m [%p] %q%u@%d %r '