It might be that there is a requirement to audit operations in the database. Maybe because of legal requirements, maybe because of security requirements or whatever. I’ve already written a post in the past describing what you can do in community PostgreSQL, this post is specific to EDB Postgres. The auditting features come be default in EDB Postgres and you do not need to install any extension such as pgaudit.
I am using EDB Postgres Enterprise version 11.2 for this post but it should work the same in previous versions:
enterprisedb@edb1:/var/lib/edb/ [pg1] psql -X postgres
psql.bin (11.2.9)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 (EnterpriseDB Advanced Server 11.2.9) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
The parameter which controls if auditing is enabled or not is “edb_audit”:
postgres=# show edb_audit; edb_audit ----------- (1 row) postgres=#
When it is not set (the default) auditing is enabled. You have two options to enable it:
- csv: Enabled autiting and will write the audit records to a csv file
- xml: Enabled autiting and will write the audit records to a csv file
Before enabling auditing you should think about where you want to store the audit files. It should be a location only the operating system user which runs EDB Postgres should have access to. You might think of $PGDATA but do you really want to have all the audit files included in every base backup you will be doing in the future? A better location is outside $PGDATA so you can keep the audit files separated. Lets go with “/var/lib/edb/audit” for the scope of this post:
postgres=# ! mkdir /var/lib/edb/audit
postgres=# ! chmod 700 /var/lib/edb/audit
postgres=# alter system set edb_audit_directory = '/var/lib/edb/audit';
ALTER SYSTEM
postgres=# alter system set edb_audit='csv';
ALTER SYSTEM
postgres=# select name,context from pg_settings where name in ('edb_audit_directory','edb_audit');
name | context
---------------------+---------
edb_audit | sighup
edb_audit_directory | sighup
(2 rows)
Both parameter changes can be made active by reloading the server, a restart is not required:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
The default file name that will be used for the audit logs is:
postgres=# show edb_audit_filename; edb_audit_filename --------------------- audit-%Y%m%d_%H%M%S (1 row)
Lets keep that as it is which is sufficient for the scope of this post. Now you need to think about what you want to audit. There are several options available:
- edb_audit_connect: Logs all connections to the instance, either successful, failed or all
- edb_audit_disconnect: The opposite of edb_audit_connect, logs all disconnections/li>
- edb_audit_statement: Here you have several options to log SQL statements such as insert,truncate, whatever, more on that later
- edb_audit_tag: When set, adds a string value to all audit log files
We start with logging connections and disconnections. When we set edb_audit_connect to all, we should see all connections to a database, no matter if successful or failed:
postgres=# alter system set edb_audit_connect = 'all'; ALTER SYSTEM postgres=# select context from pg_settings where name = 'edb_audit_connect'; context --------- sighup (1 row) postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
From now on we should have the audit information in the log file for every successful connection and every connection attempt that failed. Is it true?
postgres=# ! psql edb psql.bin (11.2.9) Type "help" for help. [local]:5444 enterprisedb@edb=# q postgres=# ! psql -U dummy edb psql.bin: FATAL: role "dummy" does not exist postgres=#
That should have produced two lines in the latest audit file:
enterprisedb@edb1:/var/lib/edb/audit/ [pg1] pwd /var/lib/edb/audit enterprisedb@edb1:/var/lib/edb/audit/ [pg1] ls -latr total 8 drwx------. 5 enterprisedb enterprisedb 183 Mar 24 14:24 .. -rw-------. 1 enterprisedb enterprisedb 611 Mar 24 14:38 audit-20190324_143640.csv drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 . -rw-------. 1 enterprisedb enterprisedb 412 Mar 24 14:41 audit-20190324_143805.csv enterprisedb@edb1:/var/lib/edb/audit/ [pg1] cat audit-20190324_143805.csv 2019-03-24 14:40:54.683 CET,"enterprisedb","edb",1534,"[local]",5c9788e6.5fe,1,"authentication",2019-03-24 14:40:54 CET,5/133,0,AUDIT,00000,"connection authorized: user=enterprisedb database=edb",,,,,,,,,"","","" 2019-03-24 14:41:16.617 CET,"dummy","edb",1563,"[local]",5c9788fc.61b,1,"authentication",2019-03-24 14:41:16 CET,5/136,0,AUDIT,00000,"connection authorized: user=dummy database=edb",,,,,,,,,"","",""
a
As expected, we can see the successful connection request and in addition the one that failed. When we want to log disconnections as well, we can do so:
postgres=# alter system set edb_audit_disconnect = 'all'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# ! psql edb psql.bin (11.2.9) Type "help" for help. [local]:5444 enterprisedb@edb=# q postgres=#
In the same audit file as before:
2019-03-24 14:47:42.447 CET,"enterprisedb","edb",1929,"[local]",5c978a7a.789,2,"idle",2019-03-24 14:47:38 CET,,0,AUDIT,00000,"disconnection: session time: 0:00:03.708 user=enterprisedb database=edb host=[local]",,,,,,,,,"psql.bin","",""
The duration of the session is logged as well. So far for the basic auditing features. Logging connections and disconnections is a good start but probably not enough. You might soon come to a point where you want to have more information, such as what the user was doing exactly in the database. This is where “edb_audit_statement” comes into the game. You can set it to something simple like “all inserts” or “all updates”:
postgres=# alter system set edb_audit_statement = 'insert'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# create table t1 ( a int ); CREATE TABLE postgres=# insert into t1 values(1); INSERT 0 1
Looking at the audit file:
2019-03-24 14:55:36.744 CET,,,9004,,5c977540.232c,3,,2019-03-24 13:17:04 CET,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,"","","" 2019-03-24 14:55:53.460 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,6,"idle",2019-03-24 14:13:30 CET,4/477,0,AUDIT,00000,"statement: insert into t1 values(1);",,,,,,,,,"psql.bin","INSERT",""
The insert is logged. You may also spot a potential issue here: Depending on how the statement is written the actual values (1 in this case) is written to the log. This might open another security hole if the audit files are not handled with care. You can not prevent that using prepared statements and in fact the “prepare” part is logged as well:
postgres=# prepare stmt as insert into t1 values($1); PREPARE postgres=# execute stmt(2); INSERT 0 1 postgres=# select * from t1; a --- 1 2 (2 rows)
The entries in the audit log:
2019-03-24 14:58:50.395 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,7,"idle",2019-03-24 14:13:30 CET,4/478,0,AUDIT,00000,"statement: prepare stmt as insert into t1 values($1);",,,,,,,,,"psql.bin","PREPARE","" 2019-03-24 14:59:02.952 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,8,"idle",2019-03-24 14:13:30 CET,4/479,0,AUDIT,00000,"statement: execute stmt(2);","prepare: prepare stmt as insert into t1 values($1);",,,,,,,,"psql.bin","EXECUTE",""
Although we only asked to log “inserts”, the prepare and execute statements are logged as well. If we prepare an update it is not logged (what is correct):
postgres=# prepare stmt2 as update t1 set a = $1; PREPARE postgres=# execute stmt2(2); UPDATE 5
The last line in the audit file is still this one:
2019-03-24 15:02:33.502 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,9,"idle",2019-03-24 14:13:30 CET,4/487,0,AUDIT,00000,"statement: execute stmt(5);","prepare: prepare stmt as insert into t1 values($1);",,,,,,,,"psql.bin","EXECUTE",""
The power of edb_audit_statement comes when you want to audit multiple kinds of statements but do not want to set it to “all” (this would log all the statements):
postgres=# alter system set edb_audit_statement='insert,update,delete,create table,drop view'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
This should log all inserts, updates and deletes and in addition every create table or drop view:
postgres=# create table t2 ( a int ); CREATE TABLE postgres=# insert into t2 values(1); INSERT 0 1 postgres=# update t2 set a = 2; UPDATE 1 postgres=# delete from t2 where a = 2; DELETE 1 postgres=# truncate t2; TRUNCATE TABLE postgres=# create view v1 as select * from t2; CREATE VIEW postgres=# drop view v1; DROP VIEW
We should see entries for the insert, the update and the delete, but not for the truncate. The drop view should be logged as well:
2019-03-24 15:08:46.245 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,10,"idle",2019-03-24 14:13:30 CET,4/496,0,AUDIT,00000,"statement: create table t2 ( a int );",,,,,,,,,"psql.bin","CREATE TABLE","" 2019-03-24 15:08:59.713 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,12,"idle",2019-03-24 14:13:30 CET,4/498,0,AUDIT,00000,"statement: insert into t2 values(1);",,,,,,,,,"psql.bin","INSERT","" 2019-03-24 15:09:21.299 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,13,"idle",2019-03-24 14:13:30 CET,4/499,0,AUDIT,00000,"statement: update t2 set a = 2;",,,,,,,,,"psql.bin","UPDATE","" 2019-03-24 15:09:29.614 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,14,"idle",2019-03-24 14:13:30 CET,4/500,0,AUDIT,00000,"statement: delete from t2 where a = 2;",,,,,,,,,"psql.bin","DELETE","" 2019-03-24 15:12:51.652 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,15,"idle",2019-03-24 14:13:30 CET,4/503,0,AUDIT,00000,"statement: drop view v1;",,,,,,,,,"psql.bin","DROP VIEW",""
Fine. Using edb_audit_statement we have control of what exactly we want to log. What we did now was valid for the whole instance, can we modify auditing to a specific role? Yes, this is possible:
edb=# alter user enterprisedb set edb_audit_statement = 'truncate'; ALTER ROLE edb=# create role test; CREATE ROLE edb=# alter role test set edb_audit_statement = 'truncate'; ALTER ROLE
The same is true on the database level:
edb=# alter database edb set edb_audit_statement = 'truncate'; ALTER DATABASE
Lets do a small test and create user and then set edb_audit_statement on the user level, and reset it on the instance level:
edb=# create user u1 with login password 'u1'; CREATE ROLE edb=# alter user u1 set edb_audit_statement = 'create table'; ALTER ROLE edb=# alter system set edb_audit_statement = 'none'; ALTER SYSTEM edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
Create table statements from that user should now be logged:
edb=# c edb u1 You are now connected to database "edb" as user "u1". edb=> create table t1 ( a int ); CREATE TABLE
The statement is indeed logged:
2019-03-24 15:44:19.793 CET,"u1","edb",6243,"[local]",5c9797b7.1863,1,"idle",2019-03-24 15:44:07 CET,5/30177,0,AUDIT,00000,"statement: create table t1 ( a int );",,,,,,,,,"psql.bin","CREATE TABLE",""
Does the same work for a role?
edb=> c edb enterprisedb You are now connected to database "edb" as user "enterprisedb". edb=# create role r1; CREATE ROLE edb=# alter role r1 set edb_audit_statement = 'drop table'; ALTER ROLE edb=# grant r1 to u1; GRANT ROLE edb=# c edb u1 You are now connected to database "edb" as user "u1". edb=> drop table t1; DROP TABLE edb=>
No, in this case the drop statement is not logged. You can set the parameter for a role, but is does not have any effect.
The last test for today: What happens when the directory we configured for the audit files is removed?
enterprisedb@edb1:/var/lib/edb/ [pg1] pwd /var/lib/edb enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local enterprisedb@edb1:/var/lib/edb/ [pg1] mv audit/ audit_org enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local
These two inserts should generate audit records:
edb=> create table t2 ( a int ); CREATE TABLE edb=> create table t3 ( a int ); CREATE TABLE edb=>
Nothing happens, not even a log entry in the server log file. I would have at least expected to get a warning that the directory does not exist. Lets restart the instance:
enterprisedb@edb1:/var/lib/edb/as11/data/log/ [pg1] pg_ctl -D /var/lib/edb/as11/data/ restart -m fast waiting for server to shut down.... done server stopped waiting for server to start....2019-03-24 15:51:59 CET LOG: listening on IPv4 address "0.0.0.0", port 5444 2019-03-24 15:51:59 CET LOG: listening on IPv6 address "::", port 5444 2019-03-24 15:51:59 CET LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" 2019-03-24 15:51:59 CET LOG: redirecting log output to logging collector process 2019-03-24 15:51:59 CET HINT: Future log output will appear in directory "log". done server started
And again: Nothing. But the audit directory is being recreated once the server starts:
enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 39 Mar 24 15:51 audit drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local
Changing the permissions so that the enterprisedb user can not write anymore to that directory will prevent the server from restarting:
enterprisedb@edb1:/var/lib/edb/ [pg1] sudo chown root:root audit enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 root root 39 Mar 24 15:51 audit drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local enterprisedb@edb1:/var/lib/edb/ [pg1] pg_ctl -D /var/lib/edb/as11/data/ restart -m fast waiting for server to shut down.... done server stopped waiting for server to start....2019-03-24 15:55:44 CET LOG: listening on IPv4 address "0.0.0.0", port 5444 2019-03-24 15:55:44 CET LOG: listening on IPv6 address "::", port 5444 2019-03-24 15:55:44 CET LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" 2019-03-24 15:55:44 CET FATAL: could not open log file "/var/lib/edb/audit/audit-20190324_155544.csv": Permission denied 2019-03-24 15:55:44 CET LOG: database system is shut down stopped waiting pg_ctl: could not start server Examine the log output.
Hope that helps…
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)