Connect to remote postgresql server on amazon ec2
Solution 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
the CIDRs look like you're not allowing any IP in. Shouldn't they be 0.0.0.0/0
instead, like what you have for port 22 (ssh)?
Solution 2
I Found the resolution to this problem. Two things are required.
Use a text editor to modify pg_hba.conf. Locate the line host all all 127.0.0.1/0 md5. Immediately below it, add this new line: host all all 0.0.0.0/0 md5
Editing the PostgreSQL postgresql.conf file:
Use a text editor to modify postgresql.conf. Locate the line that starts with #listen_addresses = 'localhost'. Uncomment the line by deleting the #, and change localhost to
*
. The line should now look like this:listen_addresses = '*'
# what IP address(es) to listen on;.
Now Just restart your postgres service and it will connect
whatf
Updated on June 07, 2022Comments
-
whatf almost 2 years
I started an amazon ec2 instance, and installed postgresql 9.1 over it. I then went to the Security Group: quicklaunch-1
(there was one more
default` which i did not change) and opened the 5432 TCP Port, 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 have created a database and 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)
I then try to connect on to the remote machine 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 result in any connection, how can i connect?
-
whatf over 11 yearswhat is the difference between port 32 and port 0.
-
Daniel Vérité over 11 years@whatf:
32
is not a port number, it's the prefix size in CIDR notation.0.0.0.0/0
means allowing connections from any address.5432
is the port number. -
chhantyal almost 8 yearsYou probably need to enable SSL/TLS if you go with this option.
-
Kalob Taulien about 6 yearsIf you want to allow all addresses to access your database, don't use
listen_addresses = ''
on Postgres9.5, uselisten_addresses = '*'
(note the asterisk) -
Fisher Coder almost 4 yearsOMG! I've been looking for this answer this entire two-day weekend! Thank you!!!