Infrastructure at your Service

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:

[email protected]:/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 [email protected]=# \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:

[email protected]:/var/lib/edb/audit/ [pg1] pwd
/var/lib/edb/audit
[email protected]:/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
[email protected]:/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 [email protected]=# \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?

[email protected]:/var/lib/edb/ [pg1] pwd
/var/lib/edb
[email protected]:/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
[email protected]:/var/lib/edb/ [pg1] mv audit/ audit_org
[email protected]:/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:

[email protected]:/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:

[email protected]:/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:

[email protected]:/var/lib/edb/ [pg1] sudo chown root:root audit
[email protected]:/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
[email protected]:/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…

5 Comments

  • tony tran says:

    Can you audit certain action for certain user and at the system level different audit?

    I want to audit all dml action by enterprisedb user
    at the system level to audit dml

    alter command doesn’t work.
    alter user app_user set edb_audit_statement=’dml’;
    ERROR: parameter “edb_audit_statement” cannot be changed now

    • Daniel Westermann says:

      Which version of PPAS are you using? That is just working fine on my side:

      edb=# create user test with login;
      CREATE ROLE
      edb=# alter user test set edb_audit_statement=’ddl’;
      ALTER ROLE
      edb=# select version();
      version
      ———————————————————————————————————————————————–
      PostgreSQL 12.0 (EnterpriseDB Advanced Server 12.0.1) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
      (1 row)

      edb=#

  • Tony Tran says:

    Hi
    I am running 9.6

    csappdb=# select version();
    version

    ——————————————————————————–
    ——————————-
    EnterpriseDB 9.6.13.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 2015
    0623 (Red Hat 4.8.5-4), 64-bit
    (1 row)

  • Tony Tran says:

    csappdb=# select version();
    version

    ——————————————————————————–
    ——————————-
    EnterpriseDB 9.6.13.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 2015
    0623 (Red Hat 4.8.5-4), 64-bit
    (1 row)

    csappdb=# create user test with login;
    CREATE ROLE
    csappdb=# alter user test set edb_audit_statement=’ddl’;
    ERROR: parameter “edb_audit_statement” cannot be changed now
    csappdb=#

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure