postgres for fusionpbx

Install all updates to this CentOS 6 server
yum update -y
yum install -y wget nano

Postgres Repo:
pgdg is the repo that postgresql can be downloaded and installed from. Here is where you download it from.
Identify which version of rpm you want to download for installing PostgreSQL database server. Right now I am picking PostgreSQL 9.3 for CentOS [CentOS 6 - x86_64] as this is the latest version at this time. Now that we have identified which version we want to download, right click on that hyperlink and click "copy link address".


Login into your CentOS Server by SSH and run the following command to download it into your CentOS 6 Server.
cd /usr/local/src/
wget http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

Now install this rpm with this command.
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

Now that we have installed the pgdg central PostgreSQL repo, we should have postgresql rpms show up in yum package manager search. Run the following command on command line to search for all postgres related available rpms.
yum search postgres

Now install PostgreSQL client / Server programs.
Run the following commands to install PostgreSQL server/client
yum install postgresql93.x86_64 postgresql93-server.x86_64

You now have successfully installed PostgreSQL database server / client on this CentOS Server.

Now create default data/tables into this newly build database server by running command:
service postgresql-9.3 initdb
[Service name is postgresql-9.3 or corresponding PostgreSQL generated init file under /etc/init.d/ directory]

Start the PostgreSQL database server now.
service postgresql-9.3 start

Make your database server auto starts when this CentOS server starts/restarts.
chkconfig postgresql-9.3 on

Create an user other than default user for granting access to the database from outside world into this database server.

Login into database as postgres user, to do that run the following commands:
su postgres
psql -dpostgres

Now you are inside PostgreSQL database server, so create a super user for accessing database server and database for fusionpbx.

CREATE role fusionpbx WITH LOGIN PASSWORD 'password' ENCRYPTED VALID UNTIL 'infinity';
CREATE role freeswitch WITH LOGIN PASSWORD 'password' ENCRYPTED VALID UNTIL 'infinity';
CREATE DATABASE fusionpbx OWNER fusionpbx;
CREATE DATABASE freeswitch OWNER freeswitch;
GRANT ALL ON DATABASE fusionpbx TO fusionpbx;
GRANT ALL ON DATABASE fusionpbx TO freeswitch;
GRANT ALL ON DATABASE freeswitch TO freeswitch;
GRANT ALL ON DATABASE freeswitch TO fusionpbx;
\q

To grant outside world access we need to make modifications to the database server configuration files and CentOS Server firewall rules [iptables]. First exit out as postgres user on your server and log back in as root user.

Make the changes in your database configuration file :
nano /var/lib/pgsql/9.3/data/postgresql.conf

Uncomment the line #listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’

The second configuration file that you need to edit in order to grant access to outside world is below [Host based authentication file]:
nano /var/lib/pgsql/9.3/data/pg_hba.conf

Add a new line under IPv4 local connections all the way to the near bottom of the file. Make appropriate changes to network you want this new user to login from. If your subnet is 10.10.10.0/24 or 10.10.10.1/32, please use that or what ever is relevant in you case. This rule I added indicates the database server to accept connections for all users originating within 10.10.10.0/24 subnet network.

host all all 10.10.10.0/24 md5

Now restart postgres server to get these changes in effect
service postgresql-9.3 restart

Finally open the default PostgreSQL database port in server firewall for connecting from outside world to local interface.
iptables -I INPUT -p tcp -s 10.10.10.0/24 -i eth0 --sport 1024:65535 -d ServerIP  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

service iptables save

At this point you should be able to connect to this database server from clients outside this server.