1

In postgresql.conf I have:

listen_addresses = "*"

My pg_hba.conf looks like:

local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
#host   all             all             myIPADDRESS           md5

If I uncomment the last line

  1. pgadmin says it cannot access and to make sure whether the port is accessible. I have flushed the iptables to have no rules, so the firewall is not stopping it.
  2. local access to the database from my scripts is not working

With the last line commented:

  1. pgadmin says that no pg_hba.con entry for my IP address
  2. I am able to access the database locally on the server from my scripts

What am I missing to make the configuration right?

Thanks.

Milind
  • 415
  • 8
  • 24
  • You should show the exact file and the exact errors. Everything looks generally correct, but there might be a small mistake in your actual configuration. – Peter Eisentraut Jul 19 '16 at 01:29
  • Is that your complete `pg_hba.conf`? You don't give any info from your `pgadmin` configuration, and that's important, too. Does the port definition match in there and in `postgresql.conf`? When you say "local access from your scripts" do you mean running locally on the server, or running on your local machine? – Feneric Jul 19 '16 at 01:29
  • 1
    `myIPADDRESS` should be like `123.456.789.123/32` (with mask). – klin Jul 19 '16 at 02:01
  • Thanks klin adding the IP address like you suggested fixed the issue of my local script (by which I mean a script running on the server hosting the postgresql server so access through localhost or 127.0.0.1). My local script is now able to access the database. But the remote access still did not work. I finally tried it through the ssh tunnel feature in pgadmin. It works! But it crashes a lot. Any other admin tool that can work through ssh tunnel? – Milind Jul 19 '16 at 19:04

2 Answers2

0

Looking at this post. After adding to pg_hba.conf:

host    all             all             ::/0                    trust
host    all             all             my.ipv4.IP/32       trust

It works now.

Community
  • 1
  • 1
Milind
  • 415
  • 8
  • 24
0

Not sure if order is important in pg_hba.conf but, I put mine at top and also tried with and without IP and both worked.

Had same problem with psql via command line connecting and pgAdmin not connecting on RDS with AWS. I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following: sudo find . -name *.conf then sudo nano ./data/pg_hba.conf then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5 and pgAdmin automatically logged me in.

This also worked in pg_hba.conf file host all all md5 without any IP address and this also worked with my IP address host all all <myip>/32 md5

As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.

max56
  • 101
  • 1
  • 3