Postgres (Pgsql) Databases are not being showed on cpanel.

Problem : Postgres Databases are not being showed on cpanel.

This can be fixed by modifying /var/lib/pgsql/data/pg_hba.conf from ‘md5′ to ‘trust’.

It should contain the following:

local all all trust
host all all 127.0.0.1 255.255.255.255 trust

Now the databases are showing up on cPanel.

Remote Postgres (pgsql) database access

Inorder to access the pgsql database remotely, you need to make following changes in the server.

1) Open the Port 5432 in the server.

iptables -A INPUT -p tcp –dport 5432 -j ACCEPT

service iptables restart

2) Edit the Pgsql configuration file (postgresql.conf).

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

Make the following changes

tcpip_socket = true
port = 5432

3) Edit PostgreSQL HOST ACCESS CONTROL FILE (pg_hba.conf)

This file controls what hosts are allowed to connect to what databases and specifies some options on how users on a particular host are identified. It is read each time a host tries to make a connection to a database.

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

Add the machine IP address from which database is accessing remotely.

# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
AUTH_ARGUMENT

local all trust
host all 127.0.0.1 255.255.255.255 trust
host all 61.246.232.245 255.255.255.255 trust

Second line allows any user on the local system to connect to any # database under any username.

Here the IP address 61.246.232.245 has been added, to allow users from 61.246.232.245 host to connect to any database.

Thats all

If the same connection is needed with SSL, please add the following in /var/lib/pgsql/data/pg_hba.conf .

hostssl all all 189.71.10.99 255.255.255.255 trust
host all all 189.71.10.99 255.255.255.255 trust

Postgres Start up Error

Sometimes, you may see the following error in pgstartup.log while starting postgres.

FATAL:  unsafe permissions on private key file “server.key”
DETAIL:  File must be owned by the database user and must have no permissions for “group” or “other”.

FIX:

change the permissions of the files server.crt and server.key to 600 in /var/lib/pgsql/data.