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

Use standard logging for audit purpose in PostgreSQL 9.6

Configuration:

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

Sample pg_log output:

<2017-07-18 20:25:12.374 +08:127.0.0.1(57640):[unknown]@[unknown]:[3541]:> LOG:  connection received: host=127.0.0.1 port=57640
< 2017-07-18 20:25:12.375 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:13.037 +08:127.0.0.1(57640):admin1@testdb:[3541]:> LOG:  disconnection: session time: 0:00:00.662 user=admin1 database=testdb host=127.0.0.1 port=57640
< 2017-07-18 20:25:17.622 +08:127.0.0.1(57642):[unknown]@[unknown]:[3543]:> LOG:  connection received: host=127.0.0.1 port=57642
< 2017-07-18 20:25:17.623 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  connection authorized: user=admin1 database=testdb
< 2017-07-18 20:25:32.728 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: create table t1(id integer);
< 2017-07-18 20:25:41.154 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: insert into t1 values (1);
< 2017-07-18 20:25:47.598 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: select * from t1;
< 2017-07-18 20:25:50.049 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  statement: drop table t1;
< 2017-07-18 20:25:54.762 +08:127.0.0.1(57642):admin1@testdb:[3543]:> LOG:  disconnection: session time: 0:00:37.139 user=admin1 database=testdb host=127.0.0.1 port=57642

SQL Server 2016 AG Setup Part 6–AG3 between SQL01\AGINST2 and SQL02\AGINST2

This is the demonstrate 2nd AG added to same set of instances already have AG in place. (refer to http://www.dbaglobe.com/2017/07/sql-server-2016-ag-setup-part-5ag2.html for AG2 setup)

SQL Server 2016 AG Setup Part 5–AG2 between SQL01\AGINST2 and SQL02\AGINST2

During this example, same port has been used for as part of illustration. Different port number between AG and instance is not a requirement, it’s a best practice instead as one instance can support multiple AG groups.






Important point: Since we already have AG1 running with different SQL instances on port 5022, we need to choose different port here to avoid conflict.









Make sure SQL01\AGINST2 and SQL02\AGINST2 only listen on specific IP, instead of “LISTEN ALL”, that will block AG Listener binding to same port.








Otherwise connection to AG Listener won’t be successfully.

SQL Server 2016 AG Setup Part 4–AG1 between SQL01\AGINST1 and SQL02\AGINST4

During this example, different port used for as part of illustration. Different port number between AG and instance is not a requirement, it’s a best practice instead as one instance can support multiple AG groups.

• Install SQL instance, configure port number, as well as enable for AlwaysOn

SQL01\AGINST1: port 2444, SQL02\AGINST4: port 2446, AG1: port 2447

--- Steps for SQL02\AGINST4 omitted

• Configure AG1 (DB/translog backup is required)

I was able to resolve above error by a new translog backup/restore performed for this DB1.


Additional, it’s not possible to select instances on the same SQL Server Node as part of AG group replica.

SQL Server 2016 AG Setup Part 3–SQL AG FCI Cluster Instance installation

• Preparation work (domain account dbaglobe\sqlsvcs, as well as shared folder permission)

grant read/write permission on \\winad\clustershare\sql to dbaglobe\sqlsvcs

• Install SQL FCI cluster 1st Node (hostname: SQL01)

Click “New failover clustering installation”

• Preform “Add node to a SQL Server Failover cluster” action

• Post Action: change the port from default dynamic port to non-default port 2443