Monitor Hot Standby Continue…

As discussed in previous Blogs about Hot Standby in following links:
1. Monitoring Hot Standby
2. HotStandby in PostgreSQL 9.0.
I came up with the following Monitoring Script for Hot Standby.

#!/bin/bash

# Filename: monitor_hotstandby
#  Usage is $0 -m master:port -s slave:port -b "PostgreSQL Bin Directory"
#  Author: Vibhor Kumar
#  Date: Jan 4th 2011
#  E-mail: vibhor.aim@gmail.com

while getopts "m:s:b:" opt;do
 case $opt in
 m)
   h1=`echo $OPTARG|cut -d":" -f1`
   p1=`echo $OPTARG|cut -d":" -f2`;;
 s)
   h2=`echo $OPTARG|cut -d":" -f1`
   p2=`echo $OPTARG|cut -d":" -f2`;;
 b) PGHOME="$OPTARG"
esac
done
PSQL=$PGHOME/psql

function usage()
{
 if [  -z $h1 ];then
   echo "USAGE: "
   echo "$0 -m master:port -s slave:port -b pg bin directory"
   exit 1
 fi
 if [  -z $h2 ];then
   echo "USAGE: "
   echo "$0 -m master:port -s slave:port -b pg bin directory"
   exit 1
 fi
 if [  -z $p1 ];then
   echo "USAGE: "
   echo "$0 -m master:port -s slave:port -b pg bin directory"
   exit 1
 fi
 if [  -z $p2 ];then
   echo "USAGE: "
   echo "$0 -m master:port -s slave:port -b pg bin directory"
   exit 1
 fi
 if [  -z $PGHOME ];then
   echo "USAGE: "
   echo "$0 -m master:port -s slave:port -b pg bin directory"
   exit 1
 fi
}

function verifybin_connect()
{
  if [ -f $PGHOME/psql ];then
    :
  else
      echo "ERROR: psql Not Found!"
      exit 1
  fi
  Q="SELECT 'ping';"
  $PGHOME/psql -h $1 -p $2 -c "$Q1" >/dev/null 2>/dev/null
   if [ $? -ne 0 ];then
       echo "ERROR: Master is not pinging on $h1"
       exit 1
  fi
   $PGHOME/psql -h $3 -p $4 -c "$Q1" >/dev/null 2>/dev/null
   if [ $? -ne 0 ];then
       echo "ERROR: Slave is not pinging on $h2"
       exit 1
  fi
}

function verify_is_recovery()
{
  Q="select pg_is_in_recovery()::int;"
  status=`$PGHOME/psql -c "$Q" -t -h $1 -p $2 template1|sed '/^$/d'`
  if [ $status -eq 1 ];then
     echo "MESSAGE: PG is in Recovery Mode"
  else
      echo "ERROR: Slave is out of Recovery Mode"
      exit 1
  fi
}
function convert_decimal()
{
  decimalval=`echo "ibase=16;obase=A;$1"|bc`
  echo $decimalval
}

function get_xlog_name()
{
 Q1="select pg_xlogfile_name('$1');"
 xlogname=`$PSQL -h $2 -p $3 -t -c "$Q1" template1|sed '/^$/d'`
 echo $xlogname
}

function main()
{
 verifybin_connect $1 $2 $3 $4
 verify_is_recovery $3 $4
 Q1="select pg_current_xlog_location();"
 Q2="select pg_last_xlog_receive_location();"
 Primxlog=`$PSQL -t -c "$Q1" -h $1 -p $2 template1|sed '/^$/d'`
 Secxlog=`$PSQL -t -c "$Q2" -h $3 -p $4 template1|sed '/^$/d'`
 secwal=`get_xlog_name $Secxlog $1 $2`
 primwal=`get_xlog_name $Primxlog $1 $2`
 primloc=`convert_decimal $primwal`
 secloc=`convert_decimal $secwal`
 result=`echo $primloc - $secloc|bc`
 if [ $result -ne 0 ];then
     echo "ALERT:: Seconday is lagging behind by $result files"
 else
     echo "Streaming Replication between $h1 -> $h2 is in Sync"
 fi

}
usage
main $h1 $p1 $h2 $p2

Usage:

monitor_hotstandby -m masterhost:masterport -s slavehost:slaveport -b PG Bin directory.

Example is given below:

./monitor_hotstandby -m master:5432 -s slave:5432 -b /opt/PostgreSQL/9.0/bin
MESSAGE: PG is in Recovery Mode
Streaming Rep

Directed Graph nodes traverse using WITH RECURSIVE Query of PG

This is a Question which I had seen people generally ask, when they Move their DBs to PG or PGPlus Advanced Server and Look for Workaround of Oracle Feature of Traverse of Directed Graph in PG.

Oracle Provides CONNECT BY NOCYCLE for traversing a Directed Graph.

As mentioned in Following link:

http://www.adp-gmbh.ch/ora/sql/connect_by_nocycle.html

However, CONNECT BY NOCYCLE implementation, some times miss some paths for Traversing Graph as you can see in following Example:

create table directed_graph (
  node_from char(1),
  node_to   char(1)
);
insert into directed_graph values ('A', 'C');
insert into directed_graph values ('A', 'B');
insert into directed_graph values ('B', 'E');
insert into directed_graph values ('C', 'H');
insert into directed_graph values ('H', 'I');
insert into directed_graph values ('I', 'D');
insert into directed_graph values ('D', 'F');
insert into directed_graph values ('D', 'C');
insert into directed_graph values ('F', 'J');
insert into directed_graph values ('J', 'K');
insert into directed_graph values ('J', 'G');
insert into directed_graph values ('K', 'L');
insert into directed_graph values ('F', 'L');
insert into directed_graph values ('K', 'M');

Output of Oracle “CONNECT BY NOCYCLE” paths are given below:

A->C
 C->H
  H->I
   I->D
    D->F
     F->J
      J->K
       K->L
       K->M
      J->G
     F->L
A->B
 B->E

Now, lets look at the Directed Graph:

A-->C<--D-->F-->L  
|   |   ^   |   ^  
v   v   |   v   |  
B   H-->I   J-->K  
|           |   |  
v           v   v  
E           G   M

In Above, we can see that there is a path from A->C->H->I->D->C. However that path would not be visible using CONNECT BY NOCYCLE.

In such case, if user is using the PG or PGPLUS8.4, then they can use WITH RECURSIVE Query to get all the paths, as given below:

WITH RECURSIVE travel
AS( SELECT node_from, node_to, 1 as depth,
ARRAY[node_from::varchar, node_to::varchar] as path, false as cycle
FROM directed_graph
UNION ALL
SELECT a.node_from, b.node_to, a.depth+1,
a.path||ARRAY[b.node_to::varchar], (b.node_to=ANY (path)) as cycle
FROM travel a JOIN directed_graph b on (b.node_from = a.node_to)
WHERE NOT cycle )
SELECT node_from,node_to, path
FROM travel
ORDER BY node_from, node_to, depth;

Output as given below:

 node_from | node_to |        path         
-----------+---------+---------------------
 A         | B       | {A,B}
 A         | C       | {A,C}
 A         | C       | {A,C,H,I,D,C}
 A         | D       | {A,C,H,I,D}
 A         | E       | {A,B,E}
 A         | F       | {A,C,H,I,D,F}
 A         | G       | {A,C,H,I,D,F,J,G}
 A         | H       | {A,C,H}
 A         | I       | {A,C,H,I}
 A         | J       | {A,C,H,I,D,F,J}
 A         | K       | {A,C,H,I,D,F,J,K}
 A         | L       | {A,C,H,I,D,F,L}
 A         | L       | {A,C,H,I,D,F,J,K,L}
 A         | M       | {A,C,H,I,D,F,J,K,M}
 B         | E       | {B,E}
 C         | C       | {C,H,I,D,C}
 C         | D       | {C,H,I,D}
 C         | F       | {C,H,I,D,F}
 C         | G       | {C,H,I,D,F,J,G}
 C         | H       | {C,H}
 C         | I       | {C,H,I}
 C         | J       | {C,H,I,D,F,J}
 C         | K       | {C,H,I,D,F,J,K}
 C         | L       | {C,H,I,D,F,L}
 C         | L       | {C,H,I,D,F,J,K,L}
 C         | M       | {C,H,I,D,F,J,K,M}
 D         | C       | {D,C}
 D         | D       | {D,C,H,I,D}
 D         | F       | {D,F}
 D         | G       | {D,F,J,G}
 D         | H       | {D,C,H}
 D         | I       | {D,C,H,I}
 D         | J       | {D,F,J}
 D         | K       | {D,F,J,K}
 D         | L       | {D,F,L}
 D         | L       | {D,F,J,K,L}
 D         | M       | {D,F,J,K,M}
 F         | G       | {F,J,G}
 F         | J       | {F,J}
 F         | K       | {F,J,K}
 F         | L       | {F,L}
 F         | L       | {F,J,K,L}
 F         | M       | {F,J,K,M}
 H         | C       | {H,I,D,C}
 H         | D       | {H,I,D}
 H         | F       | {H,I,D,F}
 H         | G       | {H,I,D,F,J,G}
 H         | H       | {H,I,D,C,H}
 H         | I       | {H,I}
 H         | J       | {H,I,D,F,J}
 H         | K       | {H,I,D,F,J,K}
 H         | L       | {H,I,D,F,L}
 H         | L       | {H,I,D,F,J,K,L}
 H         | M       | {H,I,D,F,J,K,M}
 I         | C       | {I,D,C}
 I         | D       | {I,D}
 I         | F       | {I,D,F}
 I         | G       | {I,D,F,J,G}
 I         | H       | {I,D,C,H}
 I         | I       | {I,D,C,H,I}
 I         | J       | {I,D,F,J}
 I         | K       | {I,D,F,J,K}
 I         | L       | {I,D,F,L}
 I         | L       | {I,D,F,J,K,L}
 I         | M       | {I,D,F,J,K,M}
 J         | G       | {J,G}
 J         | K       | {J,K}
 J         | L       | {J,K,L}
 J         | M       | {J,K,M}
 K         | L       | {K,L}
 K         | M       | {K,M}
(71 rows)

WITH RECURSIVE Query is really helpful in getting all possible path for each node.

COPY data at Client Side in PG.

Lot of People think that COPY is only a Server Based Command.

However its not completely true.

User can copy the data of a table at client side using COPY Command. The Difference is only in syntax usage.

User has to use backSlash before COPY Command as given below:

testdb=# select * from test;
 id 
----
  1
  2
  3
(3 rows)
testdb=# \copy test to '/tmp/test.copy'
testdb=# \q

We can see output of a file at Client side as given below:

cat /tmp/test.copy 
1
2
3

Similarly User can also copy the data in a file to Server using \COPY as given below:

\COPY test from '/tmp/test.copy'
iclive1460=# \COPY test from '/tmp/test.copy'
iclive1460=# select * from test;
 id
----
 1
 2
 3
 1
 2
 3
(6 rows)

Note::One should remember while using \COPY Command, one should not terminate command with semicolon ‘;’

SQL Injection Protect from Postgres Plus

SQL Injection is method of Hacking Data inside the Database.

Using SQL Injection, Hackers do multiple Attempt of SQLs to understand the Structure of tables and Try to get the hint of data inside a table.

For example, if a Hacker knows that UserName and Password of a Website are stored in a database table say member, then he can write query something like given below to fetch all the data inside the function as given below:

select * from tablename where 1=1;
Or
select * from tablename where 'x'='x';

Similarly, suppose Hacker wants to know the structure (Columns) of any table, then he can try multiple attempts of finding the column name by using the HAVING OR GROUP BY CLAUSE and can find the Columns Names Based on Error.
As given below:

postgres=# select * from test_sql_injection group by 1 having 1=1;
ERROR:  column "test_sql_injection.col" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from test_sql_injection group by 1 having 1=1;

As we can See through Error Message, a hacker can find the columnname of table test_sql_inject, by doing the multiple attempts of such query.

Similarly if hackers wants to know about the datatype of a columns then, he can write query some thing like given below to find the datatype:

postgres=# select * from test_sql_injection UNION SELECT 1,1;
ERROR:  UNION types text and integer cannot be matched
LINE 1: select * from test_sql_injection UNION SELECT 1,1;

In above error Message, Hacker can see that Message is informing him that second column is of type text and he has passed integer.

There are many ways SQL Injection can be done by Hackers to guess the tablename, structure of table and data.

Cheat Sheet of such SQL Injection is given in following link:
http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/#SyntaxBasicAttacks

Till now, such SQL Injection, used to be taken care by some Application Layer or by using parameterized queries/prepare statements.

To protect Databases from such SQL Injections, EnterpriseDB has introduced a Module called SQL/Protect, which is developed by Jim Mlodgenski, Chief Architect EnterpriseDB.

This module comes with Postgres Plus Standard Server 9.0 and Postgres Plus Advanced Server 8.4.

To enable this feature, user has to make following changes in postgresql.conf file of PPAS data directory:

shared_preload_libraries = '$libdir/plugins/plugin_debugger,$libdir/plugins/plugin_spl_debugger,$libdir/sqlprotect'
custom_variable_classes = 'edb_sql_protect'     # list of custom variable class names
edb_sql_protect.enabled = on
edb_sql_protect.level = learn
edb_sql_protect.max_protected_roles = 64
edb_sql_protect.max_protected_relations = 1024

After setting the above parameters, restart the PG instance.
and execute the share/contrib/sqlprotect.sql file using psql command as given below:

psql -f "PPAS Installation Directory"/contrib/sqlprotect.sql dbname

For more information and Examples, please refer the following link of Advanced Server.
http://www.enterprisedb.com/docs/en/8.4/asguide/Postgres_Plus_Advanced_Server_Guide-09.htm#P1013_58469