Friday, 9 August 2013

Centos 6/RHEL configure remote access to Postgresql with Host Based Authentication

PostgreSQL can be configured to allow remote access using a method called Host Based Authentication and here we look at client authentication in order to provide the access rights.


Centos 6 Restart Posgresql
First open the Host Based Authentication configuration file

vi /var/lib/pgsql/data/pg_hba.conf


And alter the values to appear similar to below

# TYPE DATABASE  USER      CIDR-ADDRESS    METHOD

# "local" is for Unix domain socket connections only
local     all     all                       trust
# IPv4 local connections:
host      all     all       127.0.0.1/32    trust
host      all     all     192.168.0.0/24    md5

# IPv6 local connections:
host      all     all      
::1/128         indent

Save and close the file.

The IPv4 entry above as an example gives the range of available addresses to use from the router, so typically the above entry would suit an IP address of 192.168.0.100

Each of the above records specifies a connection type, database name, a user name, a client IP address range, and the authentication method. An IP address range may not always be relevant but PostgreSQL will read this file in order and if record indicates that access is not allowed, then access will be denied.         

There are several different methods of authentication
  •  trust: allows the connection unconditionally and it enables anyone to connect with the database server without the need for a password.
  •  reject: allows the database server to reject a connection unconditionally. A feature that remains useful when filtering certain IP addresses or certain hosts from a group.  
  •  md5: implies that the client needs to supply an MD5-encrypted passwordfor authentication.
Now open the PostgreSQL configuration file

# vi /var/lib/pgsql/data/postgresql.conf

And adjust the following


listen_addresses = '*'
port = 5432


Remote connections will not be possible unless the server
is started with an appropriate value for listen_addresses, and here we adjusted the default value from a loopback address to allow the server to listen to all IP addresses (signified by the use of a star symbol or *) on the 5432 port.


Save and close the file , and restart the server.

$ sudo service postgresql restart

So with Host Based Authentication set up you will have the ability to access your PostgreSQL server both locally and remotely.