Working with PostgreSQL 9.3 & WSO2 products

As you all know, WSO2 products support a wide range of RDBMs. In this post, I will explain how you can install PostgreSQL 9.3 on your Linux machine and what commands you have to run to create users/databases and grant permission for databases etc. In the latter part of the post, I will briefly explain on the configuration that needs to be done to connect WSO2 products to a PostgreSQL database.

Installing PostgreSQL

You can install using the apt-get command on Linux as below.

Step 1 - To get information on the newest versions of packages and dependencies
sudo apt-get update


Step 2 - To install PostgreSQL v9.3

sudo apt-get install postgresql-9.3 

Configure PostgreSQL password

To setup a password to login to PostgreSQL, issue the below commands.

sudo -u postgres psql template1

ALTER USER postgres with encrypted password 'postgres';

Note: To quit from the console, press Ctrl + D

Additional configuration for PostgreSQL

Step 1 - Configure md5 Authentication

1. Open the pg_hba.conf

sudo vim /etc/postgresql/9.3/main/pg_hba.conf

2. Find the below line

local all postgres

and change it to

local all postgres md5

Step 2 - Enable TCP/IP connections

1.  Open the postgresql.conf file

sudo vim /etc/postgresql/9.3/main/postgresql.conf

2. Locate the line and uncomment it

#listen_addresses = 'localhost'

Creating a user, a database and then granting permission for the database.

Step 1 - Creating a Linux user

CREATE USER apim WITH PASSWORD 'apim';

Note: Give the password as 'apim'

Step 2 - Login as super user

sudo su - postgres

If successfully logged in, you should get a prompt as below

postgres@ubuntu2:~$

Step 3 - Then connect to the database serve

psql -d template1 -U postgres

Step 4 - Create a user

CREATE USER apim WITH PASSWORD 'apim';

Step 5 - Create a database

CREATE DATABASE apim;


Step 6 - Grant permission for the user to the database


GRANT ALL PRIVILEGES ON DATABASE apim to apim;
 
Now that you have created the user, database and granted permission, you can provide these credentials and try to connect your WSO2 server with this newly created database.

Configuring WSO2 Products with PostgreSQL

Step 1 - Configuring master-datasources.xml

Open the master-datasources.xml of the product you have selected and provide the configuration as below

        <datasource>
            <name>WSO2AM_DB</name>
            <description>The datasource used for API Manager database</description>
            <jndiConfig>
                <name>jdbc/WSO2AM_DB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:postgresql://localhost:5432/apim</url>
                    <username>apim</username>
                    <password>apim</password>
                    <defaultAutoCommit>false</defaultAutoCommit>
                    <driverClassName>org.postgresql.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

Step 2 - Downloading the correct DB driver

You have to download the correct database driver from the PostgreSQL site.  Once downloaded, drop it to $CARBON_HOME/repository/component/lib.

Step 3 - Starting up the server

Now start up the server using the -Dsetup command and it will create the relevant tables in the database.