Tuesday, July 18, 2017

Install and Configure PGAUDIT in PostgreSQL 9.6 step by step

Install prerequisites

# yum install readline readline-devel zlib zlib-devel bison bison-devel flex flex-devel

Clone the PostgreSQL repository:

git clone https://github.com/postgres/postgres.git

Checkout REL9_6_STABLE branch:

cd postgres

git checkout REL9_6_STABLE

Make PostgreSQL:

./configure --enable-debug --prefix=/var/lib/pgsql/pgsql_latest/ --with-pgport=5555
make install -s

Change to the contrib directory:

cd contrib

Clone the pgAudit extension:

git clone https://github.com/pgaudit/pgaudit.git

Change to pgAudit directory:

cd pgaudit

Build pgAudit and run regression tests:

make -s check

============== creating temporary instance            ==============
============== initializing database system           ==============
============== starting postmaster                    ==============
running on port 57835 with PID 17530
============== creating database "contrib_regression" ==============
============== running regression test queries        ==============
test pgaudit                  ... ok
============== shutting down postmaster               ==============
============== removing temporary instance            ==============

  All 1 tests passed.

Install pgAudit:

make install

/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/lib'
/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/share/extension'
/bin/mkdir -p '/var/lib/pgsql/pgsql_latest/share/extension'
/bin/install -c -m 755  pgaudit.so '/var/lib/pgsql/pgsql_latest/lib/pgaudit.so'
/bin/install -c -m 644 ./pgaudit.control '/var/lib/pgsql/pgsql_latest/share/extension/'
/bin/install -c -m 644 ./pgaudit--1.1.1.sql ./pgaudit--1.0--1.1.1.sql  '/var/lib/pgsql/pgsql_latest/share/extension/'

Configure Parameter:

$ grep -i audit postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all, -misc'
log_connections = on  # audit setting
log_disconnections = on # audit setting
log_line_prefix = '<%m:%r:%u@%d:[%p]:> '        # audit setting
log_statement = 'none'                  # audit setting

Startup Log:

$ /var/lib/pgsql/pgsql_latest/bin/pg_ctl start -D /var/lib/pgsql/9.6/data
server starting
<2017-07-18 22:10:11.455 +08::@:[17758]:> LOG:  pgaudit extension initialized
< 2017-07-18 22:10:11.470 +08::@:[17758]:> LOG:  redirecting log output to logging collector process
< 2017-07-18 22:10:11.470 +08::@:[17758]:> HINT:  Future log output will appear in directory "pg_log".

Sample Output:

<2017-07-18 22:12:05.776 +08:[unknown]@[unknown]:[17804]:> LOG:  connection received: host= port=54486
< 2017-07-18 22:12:12.429 +08:[unknown]@[unknown]:[17807]:> LOG:  connection received: host= port=54488
< 2017-07-18 22:12:12.430 +08:[17807]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 22:12:37.644 +08:[17807]:> LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,create table t1(i integer);,<not logged>
< 2017-07-18 22:13:09.207 +08:[17807]:> LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into t1 values (1);,<not logged>
< 2017-07-18 22:13:13.911 +08:[17807]:> LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,select * from t1;,<not logged>
< 2017-07-18 22:13:15.232 +08:[17807]:> LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,select * from t1;,<not logged>
< 2017-07-18 22:13:52.766 +08:[17807]:> ERROR:  column "id2" does not exist at character 8
< 2017-07-18 22:13:52.766 +08:[17807]:> STATEMENT:  select id2 from t1;
< 2017-07-18 22:14:13.596 +08:[17807]:> LOG:  AUDIT: SESSION,5,1,WRITE,DELETE,,,delete from t1;,<not logged>
< 2017-07-18 22:14:23.391 +08:[17807]:> LOG:  AUDIT: SESSION,6,1,WRITE,TRUNCATE TABLE,,,truncate table t1;,<not logged>
< 2017-07-18 22:14:26.746 +08:[17807]:> LOG:  AUDIT: SESSION,7,1,DDL,DROP TABLE,,,drop table t1;,<not logged>
< 2017-07-18 22:14:29.103 +08:[17807]:> LOG:  disconnection: session time: 0:02:16.674 user=admin1 database=testdb host= port=54488



  1. Encountered this error

    In file included from pgaudit.c:39:0:
    ../../src/include/utils/varlena.h:19:14: error: conflicting types for ‘varstr_cmp’
    extern int varstr_cmp(const char *arg1, int len1, const char *arg2, int len2, Oid collid);
    In file included from pgaudit.c:32:0:
    ../../src/include/utils/builtins.h:848:12: note: previous declaration of ‘varstr_cmp’ was here
    extern int varstr_cmp(char *arg1, int len1, char *arg2, int len2, Oid collid);
    In file included from pgaudit.c:39:0:
    ../../src/include/utils/varlena.h:20:14: error: conflicting types for ‘varstr_sortsupport’
    extern void varstr_sortsupport(SortSupport ssup, Oid typid, Oid collid);
    In file included from pgaudit.c:32:0:
    ../../src/include/utils/builtins.h:849:13: note: previous declaration of ‘varstr_sortsupport’ was here
    extern void varstr_sortsupport(SortSupport ssup, Oid collid, bool bpchar);
    pgaudit.c:1361:29: error: unknown type name ‘QueryEnvironment’
    QueryEnvironment *queryEnv,

  2. One small fix to the procedure and you are golden...
    cd pgaudit
    [root@centos6-dev pgaudit]# git checkout REL9_6_STABLE
    Branch REL9_6_STABLE set up to track remote branch REL9_6_STABLE from origin.
    Switched to a new branch 'REL9_6_STABLE'
    [root@centos6-dev pgaudit]# make check USE_PGXS=1
    make: pg_config: Command not found
    make: *** No rule to make target `check'. Stop.
    [root@centos6-dev pgaudit]# make
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pgaudit.o pgaudit.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fPIC -shared -o pgaudit.so pgaudit.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/var/lib/pgsql/pgsql_latest/lib',--enable-new-dtags
    [root@centos6-dev pgaudit]#

  3. Hey, dude, thanks MUCHO! This would have taken forever otherwise.

  4. Hi , How you install pgaudit without git clone?

  5. Hi GUys please provide vids plsss

  6. Thanks for this. It was a big help.

  7. I use only high quality materials - you can see them at: https://freshapps.com/page/349/