Sunday, July 31, 2011

Some Oracle Workarounds in Postgres Plus Advanced Server 9.0

Today, I though to mention some of Oracle workarounds which can be use for Migration of Oracle Database to PPAS 9.0(EnterpriseDB).

1. UTL_RAW.CAST_TO_RAW
This is a Oracle Packaged Function, which can be use to convert VARCHAR2 value into RAW Value. Lets see its example in Oracle:
SQL> select utl_raw.cast_to_raw('TESTING TESTING') from dual;

UTL_RAW.CAST_TO_RAW('TESTINGTESTING')
--------------------------------------------------------------------------------
54455354494E472054455354494E47
Since PPAS 9.0, doesn't have this package in 9.0, therefore user can use postgresql function decode() for converting of varchar datatype to RAW/bytea data. Following is an example:
edb=# select decode('TESTING TESTING','escape') from dual;
              decode              
----------------------------------
 \x54455354494e472054455354494e47
(1 row)
which is a same output.

2. UTL_RAW.CAST_TO_VARCHAR2

Using this packaged function user can convert raw value into a value of data type VARCHAR2 with the same number of data bytes. Following is an example:
SQL> select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw('TESTING TESTING')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CAST_TO_RAW('TESTINGTESTING'))
--------------------------------------------------------------------------------
TESTING TESTING

For this function, PPAS 9.0 is having encode() function which can be use to convert raw/bytea value to VARCHAR/TEXT. Following is an example:
edb=# select encode(decode('TESTING TESTING','escape'),'escape') from dual;
     encode      
-----------------
 TESTING TESTING
(1 row)
For more details of above two function, following link can be use:
http://www.enterprisedb.com/docs/en/9.0/pg/functions-binarystring.html
Using above basic function, user can immitate behavior of UTL_RAW Package in PPAS/PostgreSQL.

3. DBMS_OBFUSCATION.DESEncrypt & DBMS_OBFUSCATION.DESDecrypt Procedure
The DESEncrypt/DESDecrypt procedures generates the encrypted/decrypted form of the input data. It uses DES algorithm to encrypt data. Following is an Oracle Example:
DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST =========');
   dbms_output.put_line('> Raw input                        : ' || 
                 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN 
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, 
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' || 
               rawtohex(encrypted_raw));
      dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, 
               key => raw_key, decrypted_data => decrypted_raw);
      dbms_output.put_line('> Decrypted raw output             : ' || 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
      dbms_output.put_line('>  ');      
      if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

Oracle Output:
========= BEGIN TEST =========
 Raw input      : tigertigertigert
 encrypted hex value     : 5AAB8C0D278AD75CA1968790D00FD75A
 Decrypted raw output     : tigertigertigert

 Raw DES Encyption and Decryption successful

PL/SQL procedure successfully completed.

In PPAS/PostgreSQL, user can use encrypt()/decrypt() function, which is a part of pgcrypto module supplied with PostgreSQL/PPAS. Following is an example in PPAS 9.0:
DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := decode(input_string,'escape');
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := decode(key_string,'escape');
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -20010);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -20012);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' || 
                 encode(raw_input,'escape'));
   BEGIN 
     select encrypt(raw_input,raw_key,'des') into encrypted_raw from dual;
      dbms_output.put_line('> encrypted hex value              : ' || 
               encode(encrypted_raw,'hex'));

     select decrypt(encrypted_raw,raw_key,'des') into decrypted_raw from dual;
      dbms_output.put_line('> Decrypted raw output             : ' || 
                    encode(decrypted_raw,'escape'));
      dbms_output.put_line('>  ');      
      if encode(raw_input,'escape') = 
                    encode(decrypted_raw,'escape') THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;
Output:
========= BEGIN TEST RAW DATA =========
 Raw input                        : tigertigertigert
 encrypted hex value              : 5aab8c0d278ad75ca1968790d00fd75a9931b6116d9d4346
 Decrypted raw output             : tigertigertigert
  
 Raw DES Encyption and Decryption successful  

EDB-SPL Procedure successfully completed

4. UTL_HTTP.REQUEST Function.
Oracle provides UTL_HTTP.RQUEST function, which user can use to access data on the Internet. This function returns 2000 bytes of the data retrieved from the given URL.
Since PPAS/PostgreSQL doesn't have direct support for this Function, so user can write plperlu function to get same features. Following is a small function/example:
CREATE OR REPLACE FUNCTION view_web_source(text) returns text   
as  
$$  
use LWP::Simple;
use LWP::UserAgent;  
$ENV{PERL_LWP_SSL_VERIFY_HOSTNAME}=0; 
my $ua = LWP::UserAgent->new;   
$pageURL=$_[0];  
$URLsource=get($pageURL);  
my $req = HTTP::Request->new('GET', $pageURL);  
my $res = $ua->request($req);  
if ($res->status_line == "200 OK")  
{ return $URLsource; }  
 else  
{  
   return $res->status_line;  
}  
$$ language plperlu;  
  

CREATE OR REPLACE FUNCTION url_request(url text) returns text
as
$$
select substring(view_web_source($1) from 1 for 2000);
$$ language sql;
Oracle Output:






PPAS output snapshot:

Sunday, July 17, 2011

How to enable SSL in PostgreSQL/PPAS

This has been asked many times, so I thought to write steps for enabling ssl:

Following are steps, which can be use to enable ssl in postgreSQL:

1. Generate a passphrase protected certificate using following command:
openssl req -new -text -out cert.req

Snapshot is given below:

Generating a 1024 bit RSA private key
....................++++++
...................................................++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Singh
An optional company name []:

Above command will create following two files.
ls -ltr 
-rw-r--r--   1 vibhor  staff     963 Jul 16 04:12 privkey.pem
-rw-r--r--   1 vibhor  staff    2096 Jul 16 04:12 cert.req

2. Now, Remove the passphrase, which is necessary to start the postmaster automatically using following command:
openssl rsa -in privkey.pem -out cert.pem

Snapshot is given below:
Enter pass phrase for privkey.pem:
writing RSA key

Above command will create cert.pem file

3. Convert the certificate into a self-signed certificate, using following command:
openssl req -x509 -in cert.req -text -key cert.pem -out cert.cert 

4. Now, copy the files in data directory of postgreSQL:
cp cert.pem $PGDATA/server.key
cp cert.cert $PGDATA/server.crt
5. Change the permission as given below:
chmod 600 $PGDATA/server.key
chmod 600 $PGDATA/server.crt

6. Change the following parameter in $PGDATA/postgresql.conf file:
ssl=on
7. Now start the server.

After starting the PG instance, you can verify through postgreSQL logfile or connecting to database using psql as given below
edbs-MacBook-Pro:pg_log postgres$ psql -h localhost
psql (9.0.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# 

Above steps are for password free self-signed certificate. However, sometimes people also ask how to use passphare certificate. Following are steps to use passphase certificate.

1. Create passphrase protected key as given below:
openssl rsa -des3 -out cert2.pem -in privkey.pem 
Enter pass phrase for privkey.pem:
writing RSA key
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
2. Now, create passphrase protected certificate as given below:
openssl req -x509 -in cert.req -text -key cert2.pem -out cert.cert
Enter pass phrase for cert2.pem:
3. Now, copy the certificates in PostgreSQL/PostgresPlus Data Directory
cp cert.cert $PGDATA/server.crt
cp cert2.pem $PGDATA/data/server.key
4. Change the permission using following command:
chmod 600 $PGDATA/server.key
chmod 600 $PGDATA/server.crt
5. Now, start the PG Instance using following:
pg_ctl -D $PGDATA start -w
Please note: If user forget to use -w option then user will get following error message:
server starting
edbs-MacBook-Pro:data postgres$ Enter PEM pass phrase:
2011-07-18 00:46:07 IST FATAL:  could not load private key file "server.key": problems getting password
which shows, pg_ctl doesn't wait for user to enter the pass phrase.

Therefore -w would require to make pg_ctl command wait for passphrase:
Following is a snapshot:
pg_ctl start -w
waiting for server to start....Enter PEM pass phrase:..
 done
server started

Wednesday, July 6, 2011

Install and Configure Nagios for PostgreSQL/PPAS on Linux

One of my Colleague had asked how he can use Nagios for PostgreSQL Monitoring. So, I thought of writing steps to show the blog readers about nagios Configuration for PostgreSQL/PPAS.

Following are the steps:

1. Download Nagios Core
wget http://nagios.sourceforge.net/download/cvs/nagios-HEAD.tar.gz

2. For Installing Nagios, following packages would be required,so make sure you have them installed on your server.
gcc 
make
binutils 
cpp 
libpq-dev 
libmysqlclient15-dev
libssl0.9.8 
libssl-dev pkg-config 
apache2 
libgd2-xpm 
libgd2-xpm-dev 
libgd-tools 
libpng12-dev 
libjpeg62-dev 
perl 
libperl-dev 
libperl5.8 
libnet-snmp-perl
3. Create nagios user and group as given below:
[root@localhost]# useradd nagios
[root@localhost]# passwd nagios
[root@localhost]# groupadd nagcmd
[root@localhost]# usermod -G nagcmd nagios
[root@localhost]# usermod -G nagcmd apache

4. Now, untar Nagios Core/Nagios Plugin using following command:
    tar -xvf  nagios-HEAD.tar.gz

5. Now configure Nagios Source Code as given below:
sh configure  --prefix=/opt/nagios --with-pgsql=/opt/PostgresPlus/8.4AS
Note:: Please note I would be installing nagios in /opt/nagios location. For more options on configure command, use following command:
./configure --help

6. Now install Nagios using the following commands:
make all
make
make install
make install-commandmode
make install-config

7. Now, configure Nagios for web interface, using following command:
make install-webconf
8. Create webinteface user nagiosadmin, password using following command:
/opt/nagios/bin/htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin
New password:
Re-type new password:
Adding password for user nagiosadmin
Note:: Please remember this password, since admin will use this command to connect to nagios using web-interface(explorer like firefox, chrome etc).

9. After installing Naios Core and configuring for web-interface, lets install Nagios Plugins using following command:
wget http://nagiosplug.sourceforge.net/snapshot/nagios-plugins-HEAD.tar.gz
./configure --prefix=/opt/nagios --with-pgsql=/opt/PostgresPlus/8.4AS
make
make install
Since, we have installed Nagios and plugins, now, lets configure it for PostgreSQL/PPAS.

1. Download check_postgres.pl from following link:
wget http://bucardo.org/downloads/check_postgres.tar.gz
2. Copy check_postgres.pl to /libexec as given below:
cp /usr/local/bin/check_postgres.pl /opt/nagios/libexec
3. Configure the Nagios Command as given below by adding following line in
nagiosinstallationdir/etc/commands.cfg as given below:
define command {
command_name check_edb_bloat
command_line $USER1$/check_postgres.pl --host $HOSTADDRESS$ --dbuser=enterprisedb --dbpass=edb -db edb -p 5444 --action bloat
}
Note:: Above is an example of adding the Monitoring service for PG Bloat in Nagios.

4. Lets create a Service file postgres.cfg in nagiosinstallationdir/etc/objects directory using following line (please feel free to replace the hostname)
define service {
    use                     generic-service
    host_name               localhost
    service_description     Postgres bloat
    is_volatile             0
    check_period            24x7
    max_check_attempts      3
    normal_check_interval   5
    retry_check_interval    1
    contact_groups          admins
    notification_interval   120
    notification_period     24x7
    notification_options
    check_command           check_edb_bloat!3000000!9000000!flr
}
5. Now update the nagiosinstallationdir/etc/nagios.cfg file for the postgres.cfg, by adding following command:
cfg_file=/opt/nagios/etc/objects/postgres.cfg
6. Verify the configuration files using following command:
root@ubuntu:~# /opt/nagios/bin/nagios -v /opt/nagios/etc/nagios.cfg 

Nagios Core 3.2.3
Copyright (c) 2009-2010 Nagios Core Development Team and Community Contributors
Copyright (c) 1999-2009 Ethan Galstad
Last Modified: 10-03-2010
License: GPL

Website: http://www.nagios.org
Reading configuration data...
   Read main config file okay...
Processing object config file '/opt/nagios/etc/objects/commands.cfg'...
Processing object config file '/opt/nagios/etc/objects/contacts.cfg'...
Processing object config file '/opt/nagios/etc/objects/timeperiods.cfg'...
Processing object config file '/opt/nagios/etc/objects/templates.cfg'...
Processing object config file '/opt/nagios/etc/objects/localhost.cfg'...
Processing object config file '/opt/nagios/etc/objects/postgres.cfg'...
Processing object config file '/opt/nagios/etc/objects/switch.cfg'...
   Read object config files okay...

Running pre-flight check on configuration data...

Checking services...
 Checked 13 services.
Checking hosts...
 Checked 2 hosts.
Checking host groups...
 Checked 2 host groups.
Checking service groups...
 Checked 0 service groups.
Checking contacts...
 Checked 1 contacts.
Checking contact groups...
 Checked 1 contact groups.
Checking service escalations...
 Checked 0 service escalations.
Checking service dependencies...
 Checked 0 service dependencies.
Checking host escalations...
 Checked 0 host escalations.
Checking host dependencies...
 Checked 0 host dependencies.
Checking commands...
 Checked 25 commands.
Checking time periods...
 Checked 5 time periods.
Checking for circular paths between hosts...
Checking for circular host and service dependencies...
Checking global event handlers...
Checking obsessive compulsive processor commands...
Checking misc settings...

Total Warnings: 0
Total Errors:   0

Things look okay - No serious problems were detected during the pre-flight check

7. Now start the nagios services using following command:
/etc/init.d/nagios restart
We are set to check the services in Nagios. Following are few snapshots:




Some nagios Configuration File details:

Important Nagios Configuration files:
Location: nagiosinstallationdir/etc/
nagios.cfg – Nagios Configuration file, which contains Global Parameters.
cgi.cfg - Nagios Web Interface Configuration file.
resource.cfg – Plugin Monitors resource configuration file (It contains sensitive information like username/password etc).

Location: nagiosinstallationdir/etc/objects

contacts.cfg: This is a configuration file in which user can specify name, email address and notification information with time period.
commands.cfg – This file contains the commands which can be use for services.You can use $HOSTNAME$ and $HOSTADDRESS$ macro on the command execution that will substitute the corresponding hostname or host ip-address.
timeperiods.cfg – User can define the timeperiods.
templates.cfg – template configuraton file
localhost.cfg – contains localhost monitoring information. Using this sample file user can define similar services for other hosts too.
printer.cfg – Sample config file for printer
switch.cfg – Sample config file for switch
windows.cfg – Sample config file for a windows machine

Enjoy!! :)