On initial installation of PostgreSQL typically you will also download and install pgAdmin III on your local PC to assist in Postgres management. The pgAdmin GUI will assist in viewing database information quickly, etc. In one of my installations I was not able to connect to the new Postgres installation via pgAdmin and I was not receiving errors. Typically the issues I might have are related to the password not being correct or various GRANT permissions.
I had configured all of the initial items that I usually do which included the below.
- Listen Address: I modified the listen address from localhost to * in the postgresql.conf file. Below is the line from the postgresql.conf file located in /var/lib/pgsql/data directory. Having * means that PostgreSQL will listen all addresses.
- listen_addresses = '*' # what IP address(es) to listen on;
- Provide Access: I added the proper networks to have access in the pg_hba.conf file. Below are some example lines that allow password protected access from other networks. The pg_hba.conf file is located in /var/lib/pgsql/data directory by default.
- # Other Internal Networks
- host all all 192.168.0.0/24 md5
- host all all 192.168.1.0/24 md5
- # External Networks
- host all all 10.10.10.0/24 md5
- host all all 10.0.0.0/16 md5
- Users: I added the necessary users with the proper GRANT/SELECT/DELETE/Etc. permissions.
After reloading Postgres I was still unable to connect via pgAdmin at which time I then tried to connect via the CLI with psql from another server. After the above I decided to make sure the traffic was actually getting to the server as this server was on a private network behind a Linksys router and I thought maybe something was different here since it was a Linksys model I was not familiar with.
To verify traffic was getting to the server I used tcpdump and was able to see traffic reaching the server as shown below. In the below tcpdump command you can see SSH is not included to minimize the output and I wanted to make sure there was not a return communication on another port.
- [root@server ~]# tcpdump -n not dst port 22 and not src port 22
- tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
- listening on eth0, link-type EN10MB (Ethernet), capture size 96 bytes
- 16:10:42.180401 IP 220.127.116.11.43572 > 192.168.1.44.postgres: S 2683350879:2683350879(0) win 5840
- 16:10:42.180552 IP 192.168.1.44 > 18.104.22.168: ICMP host 192.168.1.44 unreachable - admin prohibited, length 68
- 16:10:45.179733 IP 22.214.171.124.43572 > 192.168.1.44.postgres: S 2683350879:2683350879(0) win 5840
- 16:10:45.179745 IP 192.168.1.44 > 126.96.36.199: ICMP host 192.168.1.44 unreachable - admin prohibited, length 68
As you can see above traffic was getting to the server however no return traffic was showing so I finally realized that during the install I had not specified to the technician to turn off selinux as well as iptables. Also the admin prohibited was another clue for this.
The issue ended up being iptables being on and not configured for Postgres so since this server was behind a firewall and minimal ports are open iptables could be turned off as shown below.
- chkconfig iptables off
- /etc/init.d/iptables stop
After stopping iptables I was able to connect to PostgreSQL via pgAdmin without issue.