1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
# pgAudit Log to File
[pgAudit Log to File](https://github.com/fmbiete/pgauditlogtofile) is an addon to [pgAudit](https://www.pgaudit.org/) than will redirect audit log lines to an independent file, instead of using PostgreSQL server logger.
This will allow us to have an audit file that we can easily rotate without polluting server logs with those messages.
Audit logs in heavily used systems can grow very fast. This extension allows to automatically rotate the files based in a number of minutes.
## Build
```
make install USE_PGXS=1
```
## Installation
- Build the extension
- Add pgauditlogtofile to "shared_preload_libraries" in postgresql.conf
- Restart PostgreSQL to reload new shared library
- Create extension in postgres database (like pgaudit we don't need to create it in all the databases)
```
postgres=# CREATE EXTENSION pgauditlogtofile;
```
## Configuration
### pgaudit.log_directory
Name of the directory where the audit file will be created.
**Scope**: System
**Default**: 'log'
Empty or NULL will disable the extension and the audit logging will be done to PostgreSQL server logger.
### pgaudit.log_filename
Name of the file where the audit will be written. Writing to an existing file will append the new entries.
This variable can contain time patterns up to minute to allow automatic rotation.
**Scope**: System
**Default**: 'audit-%Y%m%d_%H%M.log'
Empty or NULL will disable the extension and the audit logging will be done to PostgreSQL server logger.
### pgaudit.log_rotation_age
Number of minutes after which the audit file will be rotated.
**Scope**: System
**Default**: 1440 minutes (1 day)
**Performance Notes**:
- If _log_rotation_age < 60_ the rotation background worker will wake up every 10 seconds.
- If _log_rotation_age > 60_ the rotation background worker will wake up every 1 minute.
### pgaudit.log_connections
Intercepts server log messages emited when log_connections is on
**Scope**: System
**Default**: off
**Requires**: log_connections = on
### pgaudit.log_disconnections
Intercepts server log messages emited when log_disconnections is on
**Scope**: System
**Default**: off
**Requires**: log_disconnections = on
### pgaudit.log_autoclose_minutes
**EXPERIMENTAL**: automatically closes the audit log file handler kept by a backend after N minutes of inactivity.
_This features creates a background thread that will sleep in the background and close the file handler._
**Scope**: System
**Default**: 0
### pgAudit Log To File - Record format
```
CREATE FOREIGN TABLE pgauditlogtofile_extern (
----fields from postgresql session----
log_time timestamptz(3) NULL,
user_name text NULL,
database_name text NULL,
process_id int4 NULL,
remote_client text NULL,
remote_port text NULL,
session_id text NULL,
session_line_num int8 NULL,
command_tag text NULL,
session_start_time timestamptz NULL,
virtual_transaction_id text NULL,
transaction_id int8 NULL,
sql_state_code text NULL,
-----fields from pgaudit record-------
audit_type text NULL,
statement_id text NULL,
substatement_id text NULL,
"class" text NULL,
command text NULL,
object_type text NULL,
object_name text NULL,
"statement" text NULL,
"parameter" text NULL,
----additional fields--------
detail text NULL,
hint text NULL,
internal_query text NULL,
internal_query_pos int4 NULL,
context text NULL,
debug_query text NULL,
cursor_pos int4 NULL,
function_name text NULL,
filename_linenum text NULL,
application_name text NULL
)
SERVER your_server
OPTIONS (filename 'audit_log.csv', format 'csv');
```
### Test
```
cd test
vagrant plugin install vagrant-vbguest
vagrant up
```
|
