Connecting to a remote postgresql server on amazon ec2

Question

I started an instance of ecazon ec2 and installed postgresql 9.1 above it. Then I went to the Security Group: quicklaunch-1 (there was one more default`, which I did not change) and opened TCP port 5432, the table looks like this:

 (Service) Source Action 22 0.0.0.0/0 Delete 5432 0.0.0.0/32 Delete 5433 0.0.0.0/32 Delete 6432 0.0.0.0/32 Delete 

I created a database and a user. My /etc/postgresql/9.1/main/pg_hba.conf looks like this:

 # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 host db_name user_name 0.0.0.0/0 md5 # "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 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer host replication postgres 127.0.0.1/32 md5 host replication postgres ::1/128 md5 

and /etc/postgresql/9.1/main/postgresql.conf looks like this:

 # - Connection Settings - listen_addresses = '*' #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) port = 5432 # (change requires restart) 

Then I try to connect to the remote computer as follows:

 psql -h ec2-xxx-xx-xxx-xxx.compute-1.amazonaws.com -d <database_name> -U <username> 

where ec2-xxx-xx-xxx-xxx.compute-1.amazonaws.com is my public DNS .

The above command does not lead to any connections, how can I connect?

Answer 1

In this table:

 5432 0.0.0.0/32 Delete 5433 0.0.0.0/32 Delete 6432 0.0.0.0/32 Delete 

CIDRs look like you are not allowing any IP address to be used. Shouldn't they be 0.0.0.0/0 instead of what you have for port 22 (ssh)?

Answer 2

I found a solution to this problem. Two things are required.

• Use a text editor to modify pg_hba.conf. Find the local host at just 127.0.0.1/0 md5. Immediately after that add this new line: host all all 0.0.0.0/0 md5

• Editing the postgresql.conf PostgreSQL file:

  Use a text editor to modify postgresql.conf. Find the line starting with C # listen_addresses = 'localhost'. Uncomment the line by removing # and change localhost to. Now the line should look like this: listen_addresses = '' # what is the IP address to listen to;

Now just restart the postgres service and connect

Answer 3

your psql command needs the -W option, which allows you to enter the password for the db user and the -p option, followed by the postgres port number (5432 by default)

Hooray!