Step by Step PostgreSQL installation on Linux

In this post we will install the latest version of PostgreSQL using the yum package manager.

•  SSH into EC2 box and run the following commands 

yum update  yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs

• Start your DB Service 

service postgresql initdb

• Edit the pg_hba.conf file
• the pg_hba.conf file enables client authentication between the PostgreSQL server and the client application. This file consists of a series of entries , which define a host and its associated permissions (e.g., the database it is allowed to connect to, the authentication method to use, and so on).

# TYPE  DATABASE        USER            ADDRESS                 METHOD
 
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             power_user      0.0.0.0/0               md5
host    all             other_user      0.0.0.0/0               md5
host    all             storageLoader   0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

How the pg_hba.conf works

When PostgreSQL receives a connection request it will check the pg_hba.conf file to verify that the machine from which the application is requesting a connection has rights to connect to the specified database. If the machine requesting access has permission to connect, PostgreSQL will check the conditions that the application must meet in order to successfully authenticate. This affects connections that are initiated locally as well as remotely.

PostgreSQL will check the authentication method via the pg_hba.conf for every connection request. This check is performed every time a new connection is requested from the PostgreSQL server, so there is no need to re-start PostgreSQL after you add, modify or remove an entry in the pg_hba.conf file.

When a connection is initialized, PostgreSQL will read through the pg_hba.conf one entry at a time, from the top down. As soon a matching record is found, PostgreSQL will stop searching and allow or reject the connection, based on the found entry. If PostgreSQL does not find a matching entry in the pg_hba.conf file, the connection fails completely.

Table-level permissions still apply to a database, even if a user has permissions to connect to the database. If you can connect, but cannot select data from a table, you may want to verify that your connected user has permission to use SELECT on that table.

• Edit the postgresql.conf

Edit the following lines -

1. #listen_addresses = 'localhost'  change to  listen_addresses='*'
2. #port = 5432 change to port = 5432

• Restart PostgreSQL Server 

service postgresql start

• Login and change the password for your posgres user.

ALTER USER postgres WITH PASSWORD 'mynewpasswd';