Linux: Install and Configure PostgreSQL with pgAdmin

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. People will usually comparing PostgreSQL with MySQL, and there are several key factors which let us choose PostgreSQL over MySQL:

  • Single storage engine – no hassle to choose which storage engine like MySQL
  • PostgreSQL aims for SQL standards compliance (the current standard is ANSI-SQL:2008)
  • High level of data integrity demanded by a serious transactional database application, the current generation PostgreSQL performs better than MySQL
  • Open source and run on GPL license – no licensing cost

I will be using standard CentOS 6 with and yum installer. Variable as below:

OS: Centos 6.2 64bit
Server IP: 192.168.0.1
Client IP: 192.168.0.100
Root user: postgres
Password: Bf44Ks#1

1. Install PostgreSQL using yum:

$ yum install -y postgresql*

2. Initialize the server database:

$ service postgresql initdb

3. By default, PostgreSQL will be installed under /var/lib/pgsql/data. Open the configuration file at /var/lib/pgsql/data/postgresql.conf using text editor and enable following line:

listen_addresses = '*'

4. Open client authentication configuration file at /var/lib/pgsql/data/pg_hba.conf to configure how user can access PostgreSQL via network or locally. User postgres is the super-user and I need to allow this user to be access from my Windows 7 laptop using pgAdminIII client. I need to add following line as below:

host    all    postgres    192.168.0.0/24       md5

Explanation: host = access via network, all = can access all database, postgres = user that I want to use to access, 192.168.0.0/24 =  network address which the user used to access this server, md5 = authentication method

5. Start and enable the PostgreSQL service:

$ service postgresql start
$ chkconfig postgresql on

6. Now we need to reset the user postgres password so we can use md5 authentication when connecting through client later. We need to change to user postgres environment by ‘su’ command and then access the PostgreSQL database:

$ su - postgres
-bash-4.1$ psql
psql (8.4.9)
Type "help" for help.
 
postgres=#

Then run following command:

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'Bf44Ks#1';
postgres=# \q

After quit from psql, reload the server:

$ psql_ctl reload

7. Add following line into /etc/sysconfig/iptables to allow port 5432 for PostgreSQL communication using text editor. Make sure you put the line before any REJECT (-j REJECT) rules:

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

Dont forget to restart IPtables:

$ service iptables restart

8. Now lets download and install pgAdminIII, a client which run on Windows for PostgreSQL database server at http://www.postgresql.org/ftp/pgadmin3/release/v1.8.4/win32/. Once downloaded, unzip the compress file and run the installer. Follow the wizard by accepting default value and complete the installation.

9. Lets connect to the server and run the pgAdminIII client. It usually located under Windows > All Programs > pgAdmin III 1.8 > pgAdmin III. Enter required information as screenshot below:

Done! You may now manage your PostgreSQL server remotely!

Windows: The ‘Microsoft.ACE.OLEDB.12.0’ provider error

Usually in Windows Server 2008 R2 or any related Windows operating system which run on 64bit, you might facing following error if you want to run some applications:

Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately.
 
Object reference not set to an instance of an object.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Or in the screenshot version as below:

What you need to do to solve this problem is:

1. Download the Microsoft Access Database Engine 2010 Redistributable 64bit at here. Download the correct version which is “AccessDatabaseEngine_x64.exe”.

2. Uninstall the older version of Microsoft Access Database Engine (usually the version of 2007). Go to:

Control Panel > Add/Remove Program > Microsoft Access Database Engine 2007 > Right click > Uninstall

3. Install the one that we just download. This installation should install the latest OLE DB and ODBC version 14.0 (during this time) where you can check at:

Control Panel > System and Security > Administrative Tools > Data Sources (ODBC) > Drivers

Done. Even though the version is higher, this ODBC and OLEDB driver are backward compatible. You can try to re-run back the application and the error should disappear at this moment. Cheers!

Upgrade DELL Open Manage Server Administrator (OMSA)

Some of our servers are running on Dell which include Open Manage Server Administrator (OMSA) to manage the physical server  remotely. OMSA can be access via port 1311 in HTTPS by using web browser. Most of the time, I only use OMSA to manage and monitor our physical disk which run on RAID.

The version that I currently use is 6.3.0, which is the old one (because I have not update anything on OMSA since first usage). Following steps show on how to update Dell OMSA with the easiest way. Before upgrade, lets take a look on how OMSA’s current look:

Variable as below:

OS: Red Hat Enterprise Linux Server release 5.6 (Tikanga)
Server IP: 192.168.100.30
OMSA URL: https://192.168.100.30:1311

1. We will use yum to update current OMSA installation. Lets add the Dell Open Manage repository into it:

$ cd /usr/local/src
$ wget -q -O - http://linux.dell.com/repo/hardware/latest/bootstrap.cgi | bash

2. The safest way is to stop and remove the old version:

/opt/dell/srvadmin/sbin/srvadmin-services.sh stop
$ yum remove -y srvadmin*

3. Lets install new OMSA:

$ yum install -y srvadmin-all

4. Log out from SSH and relogin back to reset OMSA path.

5. Start OMSA service to load new update:

$ /opt/dell/srvadmin/sbin/srvadmin-services.sh start

And check whether it runs on the correct port:

$ netstat -tulpn | grep 1311
tcp     0       0 0.0.0.0:1311       0.0.0.0:*       LISTEN       29960/dsm_om_connsv

Done! Now lets see how our OMSA new look:

It seems Dell has hired great web developer for this new OMSA interface (version 6.5.0). As for me, it is not really matter as long as all required functionalities are still there. Cheers!

Linux: Log Rotation Customization

Problem Description

Our server has facing problem due to heavy development on web applications project. As the result, it cause our server hard disk turn full within a week. It all cause by the error log, which actually being rotated on weekly basis and has grow to 103GB within a week after rotation!

In the same time, it will consume CPU processing to the peak because the logrotate service will automatically compress the huge log file as what has been setup in /etc/logrotate.conf by default.

Symptom

Following process has been captured when I encounter this problem:

root     13324 38.2  0.0   4028   620 ?        RN   18:59  11:10      \_ gzip -f -

By checking the PID environment, we can see that this process is creating .gz (Gunzip) file for error log in Apache archive directory:

root@server [~] ll /proc/13324/fd
total 0
dr-x------ 2 root root  0 Jan 11 19:00 ./
dr-xr-xr-x 5 root root  0 Jan 11 19:00 ../
lr-x------ 1 root root 64 Jan 11 19:29 0 -> pipe:[1815813470]
l-wx------ 1 root root 64 Jan 11 19:29 1 -> /usr/local/apache/logs/archive/error_log-01-2012.gz
l-wx------ 1 root root 64 Jan 11 19:29 2 -> /usr/local/cpanel/logs/stats_log

Analysis

Following is what has been setup by default on my /etc/logrotate.d/httpd :

/var/log/httpd/*log {
    missingok
    notifempty
    sharedscripts
    postrotate
        /sbin/service httpd reload > /dev/null 2>/dev/null || true
    endscript
}

The log rotation will find any files which ended with ‘log’ (eg: access.log or error.log) under directory /var/log/httpd:

  • missingok – if the log file is missing, go on to the next log file without issuing error
  • notifempty – disable log rotation for empty file
  • sharedscripts – once all log files has been rotated, it will execute postrotate script once. If you are not specifying this option, logrotate will reload httpd for every log file which has been rotated
  • postrotate – bash script to what logrotate will do once the log file rotated. Apache need to be reloaded in order to write to the new files after log rotation complete
  • endscript – means the end line of the postrotate script

 

Solution

So I need to change my log rotation configuration for httpd by adding following line into /etc/logrotate.d/httpd:

/etc/httpd/logs/error_log {
    rotate 5
    nocompress
    notifempty
    size 1024M
    postrotate
        /sbin/service httpd reload > /dev/null 2>/dev/null || true
    endscript
}

Since I am using cPanel, the Apache log is located under /etc/httpd/logs/error_log:

  • rotate 5 – log rotation will kept the last 5 log files. The rest will be deleted -> For backup purpose
  • nocompress – the last log files will not be compress -> Save CPU consumption
  • notifempty – disable log rotation for empty file -> Do not waste time to process empty file
  • size 1024M – rotate when the file size reach 1GB -> Do not waste disk space
  • postrotate – bash script to what logrotate will do once the log file rotated. Httpd need to be reloaded in order to write to the new files after rotated -> Restart httpd so the new log will take place in new file
  • endscript – means the end line of the postrotate script

The Best Way to do Server Documentation

As a server administrator, it is very important to have a knowledge base section as our reference point. Forget can never be forgiven. You can use any online documentation tools like Google Docs, Zoho, Evernote and  Office 365. For me I will use Microsoft Office OneNote 2010, which is come in my office laptop as part of Microsoft Office Professional Plus 2010.

The key features that I am looking is transparent synchronization with my online docs. Since I have my Windows Live login, which previously registered for MSN Messenger, I can integrate my local OneNote in my laptop into OneNote in Windows SkyDrive. How cool is that?

The best thing about OneNote is you can write, copy/paste, erase, draw just like a note book. It also has protected section where you can put and store your sensitive and confidential information securely.

Following YouTube video is simple tutorial on how to use OneNote:

Requirement:

OS: Windows 7 Home Premium 64bit
Office application: Microsoft Office OneNote 2010
Windows Live ID: [email protected]
Web browser: Internet Explorer 9 64bit

1. First of all, register your Windows Live ID (if you dont have) at https://signup.live.com/ so we can sync our notes to SkyDrive.

2. Login into SkyDrive at https://skydrive.live.com using Internet Explorer. Click the OneNote icon as screenshot below and create new Notebooks:

 

3. You should see something like screenshot below. I will then create my own section called “Linux” and another new page called “My 1st Documentation“:

4. Now open our Microsoft OneNote in the laptop. Go to Open > Open from the Web > Sign In. Enter you Windows Live ID credentials and click OK.

5. OneNote will then try to retrieve the online Notebooks associated with the user. Once done, click to the Notebooks list “ServerDocumentation” and then it will try to load the content of this notebook on the local OneNote. Below screenshot is what it looks alike when loaded into our local OneNote:

Done! You now have your tools to save all your notes, knowledge base, clips, and much more which accessible all around the world. You can choose to use local OneNote in your laptop or use OneNote WebApp which available at SkyDrive by accessing it using your web browser (not specifically IE). This application do not have “Save” button because it is automatically saved on any changes happen on both sides. Just like your notes!

Linux: Create and Configure SSH Honeypot

Since I have a DMZ server, it is possible to setup a SSH honeypot, where we can track what hackers and crackers are trying to do when got into our system. My honeypot server setup will be like this:

Variable that I used is:

OS: CentOS 6.2 64bit
Web server IP: 202.82.109.14
User: kippo
Directory: /home/kippo

1. Before we start, we need to make sure our server SSH port has been changed to another port. In this case, I have changed my SSH port for this server to 22002. To change SSH port, simply edit SSH configuration file at /etc/ssh/sshd_config and change following line:

Port 22002

Dont forget to restart the service to apply the changes:

$ service sshd restart

2. We will use Kippo as the SSH honeypot. Download and extract it:

$ cd /usr/local/src
$ wget  http://kippo.googlecode.com/files/kippo-0.5.tar.gz
$ tar -xzf  kippo-0.5.tar.gz

3. Before we start installing Kippo, make sure you are running Python 2.6. You can check by using following command:

$ python -V

Then we need to install Twisted using yum:

$ yum install -y python-twisted*

4. Kippo need to be run as non-root user. So we need to create a user for this:

$ useradd -m kippo
$ passwd kippo

5. Lets copy Kippo folder to the user folder /home/kippo and assign ownership:

$ cp /usr/local/src/kippo-* /home/kippo/ -Rf
$ chown kippo.kippo /home/kippo/kippo-* -Rf

6. Change to normal user mode (kippo):

$ su - kippo

7. Change the SSH port value for kippo to use default SSH port 22. The configuration file is located under /home/kippo/kippo/kippo.cfg and change following line:

ssh_port = 22
hostname = web1

8. Lets start Kippo:

$ cd ~/kippo
$ ./start.sh

Now your SSH Honeypot is working. You can try to login via SSH to the server and you will realize that you are in Honeypot and not the real server. All user actions will be captured at /home/kippo/kippo/log/kippo.log. You can change the initial root password at Kippo configuration file and so on. To stop Kippo, you just need to kill the PID of the running process. You can use ps command to determine the PID.

CentOS: Enable CentOS GNOME Desktop

Mostly the CentOS servers that I am using is run on CLI (Command-Line Interface) mode. But in some cases, I need to have a desktop to install some applications with GUI (Grapich User Interface) mode. In this case, I will use GNOME, the most popular user-friendly desktop for any UNIX based system.

Variable that I used as below:

OS: CentOS 6.2 64bit
Desktop environment: GNOME

1. Using yum, we will need to install X Windows System  as the based for GUI and rich input device capability:

$ yum groupinstall -y 'X Window System'

2. Only then we can install GNOME desktop environment on top of it:

$ yum groupinstall -y 'Desktop'

3. Since previously the server is running on CLI mode, we need to change some value to tell CentOS to boot up in the GUI mode. Open /etc/inittab via text editor and change following line:

id:3:initdefault:

To:

id:5:initdefault:

4. Start the GUI:

$ init 5

Note: You can switch from GUI to CLI mode manually by using following method:

GUI to CLI: Ctrl + Alt + F6
CLI to GUI: Ctrl + Alt + F1

If you want to start the desktop from CLI console (not SSH session), use following command:

$ startx

In some cases, when you login into the system via GUI, you will see some kind of weird boxes replacing the fonts. You can overcome this problem by installing fonts group via yum:

$ yum groupinstall -y Fonts

Done! We now have user-friendly interface like below: