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.
First open the Host Based Authentication configuration file
|Centos 6 Restart Posgresql|
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.
Labels: Centos 6, Host Based Authentication, remote access Postgresql, RHEL