Movatterモバイル変換

1. Addpg_proaudit to theshared_preload_libraries variable in thepostgresql.conf file:

   shared_preload_libraries = 'pg_proaudit'

2. Restart the database server for the changes to take effect. To verify that thepg_proaudit library was installed correctly, you can run the following command:

   SHOW shared_preload_libraries;

3. In each database for which you are going to log security events, create thepg_proaudit extension using the following query:

   CREATE EXTENSION pg_proaudit;

   Thepg_proaudit extension adds several functions for managing thepg_proaudit.conf file, thepg_proaudit_settings view that displays the currentpg_proaudit rules and event triggers.

1. Delete thepg_proaudit extension using the following query:

   DROP EXTENSION pg_proaudit;

2. Removepg_proaudit from theshared_preload_libraries variable in thepostgresql.conf file.

   Skip this step if you have several databases in the cluster and you want to remove the extension only for one of them. In this case, it is recommended to remove logging rules related to the corresponding database prior to uninstalling the extension.

• db_name — name of the database for which the logging rule is established. An empty string or NULL specified in this argument means that events are logged for all databases where thepg_proaudit extension is created. When set tocurrent_database(), events for the current database are logged.

• event_type — type of the event that needs to be logged, includingSQL operator names, as well asAUTHENTICATE andDISCONNECT events. When set toALL, as well as when an empty string or NULL is specified, enables logging for all events available for the specified object type. For example, for theTABLE object type, theALL keyword enables logging for commandsSELECT,INSERT,UPDATE,DELETE,TRUNCATE,COPY, as well asCREATE,ALTER,DROP. You can also set up logging of event classes by specifying the following values:ALL_DDL,ALL_DDL_NONTEMP,ALL_DML,ALL_DML_NONTEMP,ALL_MOD,ALL_PROC, andALL_ROLE. For the full list of possibleevent_type values, seeSection F.38.5.

• object_type — type of the object for which security events need to be logged. When set toALL, as well as when an empty string or the NULL is specified, enables logging of events for all object types. For example, specifyFOREIGN TABLE object type for theSELECT event to log all attempts to access foreign tables. Use NULL ifevent_type is set toAUTHENTICATE,DISCONNECT,SET, orRESET, and theROLE value for all events that reference user actions, such asCREATE USER orDROP USER. The following object types are supported:COMPOSITE TYPE,DATABASE,EVENT TRIGGER,FUNCTION,INDEX,PREPARED STATEMENT,ROLE,SEQUENCE,SCHEMA,TABLE,FOREIGN TABLE,TOAST TABLE,TABLESPACE,VIEW,MATERIALIZED VIEW,CATALOG RELATION, andCATALOG FUNCTION.

  Note

  To log security events for system catalog objects, it is required to enable thepg_proaudit.log_catalog_access configuration parameter. Otherwise, these events will not be logged, even if theCATALOG RELATION,CATALOG FUNCTION, orALL object type is specified in the rule.

• object_name — name of the object for which the logging rule is established. When an empty string or NULL is specified, enables logging of events for all object names.

• role_name — name of the role for which the logging rule is established. If specified, allows logging the actions caused by the user who has the privileges of the role. This means that at least one of the user session attributes,session_user orcurrent_user, should either be equal to therole_name, or be directly or indirectly a member of this role. When an empty string or NULL is specified, enables logging of actions caused by any user. When set tocurrent_role, the logging rule is established for the user current role.

• comment — comment to describe the created logging rule. This argument does not affect the rule execution and is not reflected in the log.

Note

If the rule that you want to remove is configured to log theDISCONNECT event type, then disconnect events may still be logged after the rule is removed. For additional information, seeSection F.38.3.1.

• db_name — name of the database for which the logging rule needs to be removed.

• event_type — type of the event for which the logging rule needs to be removed. For the full list of possibleevent_type values, seeSection F.38.5.

• object_type — type of the object for which the logging rule needs to be removed.

• object_name — name of the object for which the logging rule needs to be removed.

• role_name — name of the role for which the logging rule needs to be removed.

When a logging rule is configured forDISCONNECT event type, and a user authenticates in a database, then thepg_proaudit extension checks whether the corresponding disconnection event satisfies the rule and must be logged.

If the event must be logged, then the removal of the logging rule has no effect on the event. This event will be logged no matter if the logging rule exists at the moment of disconnect.

Consider the following example:

Comparison of string representations of names for database, user, role and other objects is case-insensitive even if the object name in the database is enclosed in quotes. These string representations are stored as lower-case strings.

Strings representing object names need to be passed to functionspg_proaudit_set_rule andpg_proaudit_remove_rule as regular strings: a single quote needs repeating, and other characters are passed as is, without escaping or wrapping.

For example, rules for tablestable1 and"TaBlE1" will be the same, and their names can be passed in upper-case or lower-case characters. The same holds for roles. As for databases, before creating a rule,pg_proaudit checks whether this database actually exists, and this check is case-sensitive.

The following examples illustrate creation of "weird" object names and specifying correct audit rules for them.

create table "TAbLe''123"();SELECT pg_proaudit_set_rule(current_database(), null, null, 'public.TabLe''''123', null, 'tst cmnt');

create table "taBlE""123"();SELECT pg_proaudit_set_rule(null, null, null, 'public.taBlE"123', null);

create schema "(BIG!) Schema";create table "(BIG!) Schema"."My Tab.lE"();SELECT pg_proaudit_set_rule(null, null, null, '(BIG!) Schema.My Tab.lE', null);

create role "Some '@#$%' person" with login;SELECT pg_proaudit_set_rule(null, 'authenticate', null, null, 'Some ''@#$%'' person');

create role "Some '@#$%' person" with superuser;\c - "Some '@#$%' person"SELECT pg_proaudit_set_rule(null, 'disconnect', null, null, current_user);

create database " D B 1";SELECT pg_proaudit_set_rule(' D B 1', 'authenticate', null, null, null);

create database " D b 2";\c " D b 2"create extension pg_proaudit;SELECT pg_proaudit_set_rule(current_database(), 'disconnect', null, null, null);

• db_name (text) — name of the database for which to log security events.

• event_type (text) — event type to log.

• object_type (text) — type of the object for which security events are to be logged.

• object_name (text) — name of the object for which security events are to be logged.

• role_name (text) — the role on behalf of which logged actions are performed.

• comment (text) — comment to describe the created logging rule.

• ALL_DDL:CREATE,ALTER,DROP for any database object except stored procedures and functions.

• ALL_DDL_NONTEMP: same asALL_DDL but the scope is limited to the objects that are not contained inpg_temp_nnn temporary schemas.

• ALL_DML:SELECT,INSERT,UPDATE,DELETE,TRUNCATE for any table type;EXECUTE for functions and stored procedures.

• ALL_DML_NONTEMP: same asALL_DML but the scope is limited to the objects that are not contained inpg_temp_nnn temporary schemas.

• ALL_MOD:INSERT,UPDATE,DELETE,TRUNCATE for any table type.

• ALL_PROC:CREATE,ALTER,DROP for any function and stored procedure.

• ALL_ROLE:CREATE,ALTER,DROP forUSER,ROLE,GROUP,PROFILE, as well as execution of theGRANT command.

• AUTHENTICATE

• DISCONNECT

• ALTER AGGREGATE

• ALTER COLLATION

• ALTER CONVERSION

• ALTER DATABASE

• ALTER DEFAULT PRIVILEGES

• ALTER DOMAIN

• ALTER EVENT TRIGGER

• ALTER EXTENSION

• ALTER FOREIGN DATA WRAPPER

• ALTER FOREIGN TABLE

• ALTER FUNCTION

• ALTER INDEX

• ALTER LANGUAGE

• ALTER LARGE OBJECT

• ALTER MATERIALIZED VIEW

• ALTER OPERATOR

• ALTER OPERATOR CLASS

• ALTER OPERATOR FAMILY

• ALTER POLICY

• ALTER PROFILE

• ALTER ROLE, ALTER USER, ALTER GROUP

• ALTER RULE

• ALTER SCHEMA

• ALTER SEQUENCE

• ALTER SERVER

• ALTER SYSTEM

• ALTER TABLE

• ALTER TABLESPACE

• ALTER TEXT SEARCH CONFIGURATION

• ALTER TEXT SEARCH DICTIONARY

• ALTER TEXT SEARCH PARSER

• ALTER TEXT SEARCH TEMPLATE

• ALTER TRIGGER

• ALTER TYPE

• ALTER USER MAPPING

• ALTER VIEW

• CLUSTER

• COMMENT

• COPY

• CREATE ACCESS METHOD

• CREATE AGGREGATE

• CREATE CAST

• CREATE COLLATION

• CREATE CONVERSION

• CREATE DATABASE

• CREATE DOMAIN

• CREATE EVENT TRIGGER

• CREATE EXTENSION

• CREATE FOREIGN DATA WRAPPER

• CREATE FOREIGN TABLE

• CREATE FUNCTION

• CREATE INDEX

• CREATE LANGUAGE

• CREATE MATERIALIZED VIEW

• CREATE OPERATOR

• CREATE OPERATOR CLASS

• CREATE OPERATOR FAMILY

• CREATE POLICY

• CREATE PROFILE

• CREATE ROLE, CREATE USER, CREATE GROUP

• CREATE RULE

• CREATE SCHEMA

• CREATE SEQUENCE

• CREATE SERVER

• CREATE TABLE, CREATE TABLE AS, SELECT INTO

• CREATE TABLESPACE

• CREATE TEXT SEARCH CONFIGURATION

• CREATE TEXT SEARCH DICTIONARY

• CREATE TEXT SEARCH PARSER

• CREATE TEXT SEARCH TEMPLATE

• CREATE TRANSFORM

• CREATE TRIGGER

• CREATE TYPE

• CREATE USER MAPPING

• CREATE VIEW

• DEALLOCATE

• DELETE

• DO

• DROP ACCESS METHOD

• DROP AGGREGATE

• DROP CAST

• DROP COLLATION

• DROP CONVERSION

• DROP DATABASE

• DROP DOMAIN

• DROP EVENT TRIGGER

• DROP EXTENSION

• DROP FOREIGN DATA WRAPPER

• DROP FOREIGN TABLE

• DROP FUNCTION

• DROP INDEX

• DROP LANGUAGE

• DROP MATERIALIZED VIEW

• DROP OPERATOR

• DROP OPERATOR CLASS

• DROP OPERATOR FAMILY

• DROP OWNED

• DROP POLICY

• DROP PROFILE

• DROP ROLE, DROP USER, DROP GROUP

• DROP RULE

• DROP SCHEMA

• DROP SEQUENCE

• DROP SERVER

• DROP TABLE

• DROP TABLESPACE

• DROP TEXT SEARCH CONFIGURATION

• DROP TEXT SEARCH DICTIONARY

• DROP TEXT SEARCH PARSER

• DROP TEXT SEARCH TEMPLATE

• DROP TRANSFORM

• DROP TRIGGER

• DROP TYPE

• DROP USER MAPPING

• DROP VIEW

• EXECUTE

• GRANT

• INSERT

• PREPARE

• REASSIGN OWNED

• REFRESH MATERIALIZED VIEW

• REINDEX

• RESET

• REVOKE

• SECURITY LABEL

• SELECT

• SET

• UPDATE

• TRUNCATE TABLE

pg_proaudit.log_destination (string)

Defines the method for logging security events. Possible values are:

• csvlog — log security events in a CSV file.

• syslog — log security events insyslog.

You can specify one or more values separated by commas.

Default:csvlog

pg_proaudit.log_catalog_access (boolean)

Specifies whether to log access to system catalog objects in thepg_catalog schema.

When set tooff, no events will be logged, even if there are rules for theCATALOG RELATION,CATALOG FUNCTION, orALL object types.

It is not recommended to set the parameter toon, if you do not plan to log events for system catalog objects. Doing otherwise may impactPostgres Pro performance, even if there are no rules for the aforementioned object types.

Default:off

pg_proaudit.log_command_text (boolean)

Specifies whether to log theSQL command text for security events.

Default:on

pg_proaudit.log_directory (string)

Specifies the path to the directory that stores CSV log files. This can be an absolute path, or a relative path to the cluster data directory (PGDATA). This parameter is used ifpg_proaudit.log_destination contains thecsvlog value.

Default:pg_proaudit

pg_proaudit.log_filename (string)

Defines the filenames of the created security event log files. The filename template can contain %-escapes, similar to the ones listed in thestrftime specification of the Open Group (http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html). This parameter is used ifpg_proaudit.log_destination contains thecsvlog value.

Default:postgresql-%Y-%m-%d_%H%M%S.log

pg_proaudit.log_rotation_size (integer)

Sets the maximum size of theCSV log file, in kilobytes. When this size is achieved,pg_proaudit creates a new file for logging security events. This parameter is used ifpg_proaudit.log_destination contains thecsvlog. If set to 0, disables size-based creation of new log files.

Default: 10MB

pg_proaudit.log_rotation_age (integer)

Sets the maximum lifetime of a log file, in minutes. After this timeframe has elapsed,pg_proaudit creates a new file for logging security events. This parameter is used ifpg_proaudit.log_destination contains thecsvlog value. If set to 0, disables time-based creation of new log files.

Default: 1 day

pg_proaudit.log_truncate_on_rotation (boolean)

Specifies whether to truncate log files when logging is switched to an existing log file. If set tooff,pg_proaudit appends new log entries to the end of the file. This parameter is used ifpg_proaudit.log_destination contains thecsvlog value.

Default:off

pg_proaudit.max_rules_count (integer)

Specifies the maximum number of rules allowed. For the parameter changes to take effect, the database server must be restarted.

Default: 500

1. Install thefile_fdw and create an external server:

   CREATE EXTENSION file_fdw;CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;

2. Create a foreign table, specifying the columns and the absolute path to the log file. The actual log file location is determined by thepg_proaudit.log_directory andpg_proaudit.log_filename parameters.

   CREATE FOREIGN TABLE pg_proaudit_log      ( log_time timestamp(3) with time zone,        current_usr_name text,        database_name text,        session_pid text,        error_severity text,        session_line_num bigint,        session_line_subcommand_num bigint,        event_type text,        object_type text,        object_name text,        status text,        error_message text,        query_text text,        query_args text,        session_usr_name text,        uuid text,        xid text,        vxid text )SERVER pg_proauditlogOPTIONS (filename 'absolute_file_path_to_log_file.csv', FORMAT 'csv' );

• authentications/disconnections to thepostgres database

• all actions of the user if at least one of the user session attributes,session_user orcurrent_user, is either explicitly set to thepostgres role, or is directly or indirectly a member of this role

• creating, updating, and deleting any tables

• all operations on theapp_table table that belongs to thepublic schema