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" ==============
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

image

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

image

Sunday, July 16, 2017

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)
VirtualBox_PEGAAD_15_07_2017_15_27_06
VirtualBox_PEGAAD_15_07_2017_15_27_56
VirtualBox_PEGAAD_15_07_2017_15_28_05
VirtualBox_PEGAAD_15_07_2017_15_29_14
VirtualBox_PEGAAD_15_07_2017_15_29_21
VirtualBox_PEGAAD_15_07_2017_15_29_39
VirtualBox_PEGAAD_15_07_2017_15_29_57
VirtualBox_PEGAAD_15_07_2017_16_37_08

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.
InkedVirtualBox_PEGAAD_15_07_2017_15_03_22_LI

VirtualBox_PEGAAD_15_07_2017_15_03_29

VirtualBox_PEGAAD_15_07_2017_15_04_01

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.
VirtualBox_PEGAAD_15_07_2017_15_05_56

VirtualBox_PEGAAD_15_07_2017_15_04_18
VirtualBox_PEGAAD_15_07_2017_15_04_46
VirtualBox_PEGAAD_15_07_2017_15_04_55
VirtualBox_PEGAAD_15_07_2017_15_05_02
VirtualBox_PEGAAD_15_07_2017_15_06_08
VirtualBox_PEGAAD_15_07_2017_15_07_15

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.
VirtualBox_SQL01_15_07_2017_15_15_37


VirtualBox_SQL01_15_07_2017_15_15_32

VirtualBox_SQL01_15_07_2017_15_18_38
VirtualBox_SQL01_15_07_2017_15_30_10

Otherwise connection to AG Listener won’t be successfully.
VirtualBox_PEGAAD_15_07_2017_15_11_33

VirtualBox_SQL01_15_07_2017_15_11_23

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
VirtualBox_SQL01_15_07_2017_13_19_39VirtualBox_SQL01_15_07_2017_13_20_04VirtualBox_SQL01_15_07_2017_13_21_19VirtualBox_SQL01_15_07_2017_13_21_37VirtualBox_SQL01_15_07_2017_13_21_42VirtualBox_SQL01_15_07_2017_13_23_57
VirtualBox_SQL01_15_07_2017_13_41_14
VirtualBox_SQL01_15_07_2017_13_44_42
--- Steps for SQL02\AGINST4 omitted
  • Configure AG1 (DB/translog backup is required)
VirtualBox_PEGAAD_15_07_2017_14_16_08VirtualBox_PEGAAD_15_07_2017_14_25_16
InkedVirtualBox_PEGAAD_15_07_2017_14_26_43_LI
VirtualBox_PEGAAD_15_07_2017_14_26_49VirtualBox_PEGAAD_15_07_2017_14_28_20VirtualBox_PEGAAD_15_07_2017_14_28_04VirtualBox_PEGAAD_15_07_2017_14_28_09

VirtualBox_PEGAAD_15_07_2017_14_28_57

VirtualBox_PEGAAD_15_07_2017_14_30_28
VirtualBox_PEGAAD_15_07_2017_14_30_40VirtualBox_PEGAAD_15_07_2017_14_35_57

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

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)
VirtualBox_PEGAAD_15_07_2017_12_09_26
VirtualBox_PEGAAD_15_07_2017_12_09_32
grant read/write permission on \\winad\clustershare\sql to dbaglobe\sqlsvcs
VirtualBox_PEGAAD_15_07_2017_12_15_16
  • Install SQL FCI cluster 1st Node (hostname: SQL01)
VirtualBox_SQL01_15_07_2017_12_04_27
Click “New failover clustering installation”
VirtualBox_SQL01_15_07_2017_12_05_11VirtualBox_SQL01_15_07_2017_12_05_23VirtualBox_SQL01_15_07_2017_12_05_29VirtualBox_SQL01_15_07_2017_12_05_42VirtualBox_SQL01_15_07_2017_12_06_16VirtualBox_SQL01_15_07_2017_12_06_31VirtualBox_SQL01_15_07_2017_12_16_34VirtualBox_SQL01_15_07_2017_12_16_50VirtualBox_SQL01_15_07_2017_12_59_07VirtualBox_SQL01_15_07_2017_12_59_54
  • Preform “Add node to a SQL Server Failover cluster” action
VirtualBox_SQL02_15_07_2017_13_04_59VirtualBox_SQL02_15_07_2017_13_05_37VirtualBox_SQL02_15_07_2017_13_05_46VirtualBox_SQL02_15_07_2017_13_06_10VirtualBox_SQL02_15_07_2017_13_06_36VirtualBox_SQL02_15_07_2017_13_06_55VirtualBox_SQL02_15_07_2017_13_16_33
VirtualBox_SQL01_15_07_2017_13_17_35
  • Post Action: change the port from default dynamic port to non-default port 2443