This section describes the setup method of pgaudit.

1. Copy the pgaudit files

   As superuser, run the following command. Note that "<x>" in paths indicates the product version.

   $ su -
   Password:******
   # cp -r /opt/fsepv<x>server64/OSS/pgaudit/* /opt/fsepv<x>server64

   Open a command prompt as administrator privileges, run the following command. Note that "<x>" in paths indicates the product version.

   > xcopy /E "c:\Program Files\Fujitsu\fsepv<x>server64\OSS\pgaudit\*"  "c:\Program Files\Fujitsu\fsepv<x>server64"

2. Create the pgaudit configuration file

   Create the pgaudit configuration file, which describes the information required for pgaudit actions. Create the file using the same encoding as used for the database.

   In addition, set write permissions for the database administrator only in the pgaudit configuration file so that policies related to the audit log are not viewed by unintended users.

   Refer to "6.4 pgaudit Configuration File" for details.

   Note

   Do not define the rule section in the pgaudit configuration file at this point.

   Example of a pgaudit configuration file

   [output]
   logger = 'auditlog'

3. Configure postgresql.conf

   Configure the parameters below in postgresql.conf to use audit logs:

   shared_preload_libraries

   Specify "pgaudit".

   pgaudit.config_file

   Specify the deployment destination path of the pgaudit configuration file.

   If a relative path is specified, the path will be relative to the data storage directory.

   log_replication_commands

   Specify "on".

   log_min_messages

   Check if "ERROR" or higher has been specified.

   
   

   If outputting an audit log to a server log ("serverlog" is specified in the logger parameter of the pgaudit configuration file), check the parameters below relating to server logs.

   logging_collector

   Check if "on" has been specified.

   log_destination

   Check if "stderr" has been specified.

   log_file_mode

   Check if the server log permissions are appropriate, so that only the permitted persons can access it.

   Information

   The default for the log_file_mode parameter is 0600, which only allows the database administrator to have access.

   For example, to permit other members of the group to which the database administrator belongs to view the audit logs, specify 0640 for log_file_mode.

   Example

   log_file_mode = 0640

   The database administrator can also be prevented from viewing audit logs by specifying 0000. However, write privileges are assigned for outputting logs.

   
   

   If outputting an audit log to a dedicated log file ("auditlog" is specified in the logger parameter of the pgaudit configuration file), check the parameter below.

   max_worker_processes

   If the max_worker_processes parameter has been set, add 1 to the specified value.

   If you want to take advantage of the scalable audit log feature, refer to "6.3 Setting Up the Scalable Audit Log Feature".

   See

   Refer to "Error Reporting and Logging" in the PostgreSQL Documentation for details on server logs.

   If using database multiplexing, refer to "6.7 Database Multiplexing" for details.

   Example of postgresql.conf

   In the example below, only the parameters that need to be configured when using the audit log feature are described.

   shared_preload_libraries = 'pgaudit'
   pgaudit.config_file = 'pgaudit.conf'
   log_replication_commands = on
   log_min_messages = WARNING

4. Start the instance

   Start the instance and check if the message below is output.

   LOG: pgaudit extension initialized

5. Create the pgaudit extension

   Execute CREATE EXTENSION to create the pgaudit extension.

   $ psql
   =# CREATE EXTENSION pgaudit;
   =# \dx
                     List of installed extensions
   Name    | Version |   Schema   |     Description
   --------+---------+------------+---------------------------------
   pgaudit | 1.0     | public     | provides auditing functionality
   plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
   (2 rows)

6. Configure the parameters in the pgaudit configuration file

   Add or change the parameters in the pgaudit configuration file as required.

   Refer to "6.4 pgaudit Configuration File" for details.

7. Restart the instance

   Restart the instance to apply the changes to the pgaudit configuration file. After restarting, check if the changes have been reflected correctly.

   Linux

   LOG:  log_catalog = 1
   LOG:  log_level_string =
   LOG:  log_level = 15
   LOG:  log_parameter = 0
   LOG:  log_statement_once = 0
   LOG:  role =
   LOG:  logger = auditlog
   LOG:  log_directory = pgaudit_log
   LOG:  log_filename = pgaudit-%Y-%m-%d_%H%M%S.log
   LOG:  log_file_mode = 0600
   LOG:  log_rotation_age = 1440
   LOG:  log_rotation_size = 10240
   LOG:  log_truncate_on_rotation = 0
   LOG:  fifo_directory = /tmp
   LOG:  Rule 0
   LOG:  pgaudit extension initialized

   Windows

   LOG:  log_catalog = 1
   LOG:  log_level_string =
   LOG:  log_level = 15
   LOG:  log_parameter = 0
   LOG:  log_statement_once = 0
   LOG:  role =
   LOG:  logger = auditlog
   LOG:  log_directory = pgaudit_log
   LOG:  log_filename = pgaudit-%Y-%m-%d_%H%M%S.log
   LOG:  log_file_mode = 0600
   LOG:  log_rotation_age = 1440
   LOG:  log_rotation_size = 10240
   LOG:  log_truncate_on_rotation = 0
   LOG:  Rule 0
   LOG:  pgaudit extension initialized