2

I have installed Postgres 10 on my EC2 ubuntu(18). But I can't access it from my Local Mac. Get this error.

enter image description here

My SETUP is:

I updated Postgres configs:

  1. to /etc/postgresql/10/main/pg_hba.conf added these lines:
    host    all             all             0.0.0.0/0               md5
    host    all             all             ::/0                    md5
  1. in /etc/postgresql/10/main/postgresql.conf changed this line:
    listen_addresses = '*'                  # what IP address(es) to listen on;
  1. I did set password to default 'postgres' user, which I am using to connect to DB from outside;

On EC2 instance I changed:

  1. added "Inbound rule" for port 5432 from any IPv4: enter image description here

On EC2 instance this command returns that port is exposed:

ubuntu@ip-XXXXXXXXXX:~$ netstat -nat |grep :5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN

Looks like EC2 instance did not expose this port in fact. Other ports like :80 or :22 are accessible fine, but port :5432 returns error:

➜  ~ nc -zv XX.XXX.XXX.XXX 80  
Connection to XX.XXX.XXX.XXX port 80 [tcp/http] succeeded!

➜  ~ nc -zv XX.XXX.XXX.XXX 5432
nc: connectx to XX.XXX.XXX.XXX port 5432 (tcp) failed: Operation timed out

I also have Nginx installed on my EC2 instance, its config is:

server {
  charset utf-8;
  listen 80;
  server_name XXXXXXXXXXXXXXX.ca; # <--- hidden domain name
  location / {
    root /opt/frontend/develop/dist/tweeter-ui/;
    try_files $uri /index.html;
  }
  location /api/ {
    proxy_pass http://localhost:8080/api/;
  }
}

PostgreSQL service is running:

ubuntu@ip-XXXXXXXXXX:~$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Wed 2022-11-23 22:41:33 UTC; 1h 6min ago
  Process: 6283 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 6283 (code=exited, status=0/SUCCESS)

Nov 23 22:41:33 ip-XXXXXXXXXX systemd[1]: Starting PostgreSQL RDBMS...
Nov 23 22:41:33 ip-XXXXXXXXXX systemd[1]: Started PostgreSQL RDBMS.

What is wrong in my setup? I tried many different posts in this forum and on Internet, nothing helps. (( Do I have to also configure Nginx with Postgres routing?

Renat Gatin
  • 6,053
  • 5
  • 37
  • 58
  • 1
    Did you check your postgres service is running? You can run `service PostgreSQL status` – DreamBold Nov 23 '22 at 23:40
  • 1
    **From the instance itself**, can you run `psql` and connect to the database on `localhost`? Next, try connecting to it on the **private IP address** of the instance. Then, try connecting on the **public IP address**. – John Rotenstein Nov 23 '22 at 23:49
  • @DreamBold, yes service is running, I updated my post with the printout of command "$ service postgresql status" – Renat Gatin Nov 23 '22 at 23:51
  • 1
    As @JohnRotenstein mentioned, did you manage to connect `psql` from the terminal on the server with the current creds? `sudo -u postgres psql` – DreamBold Nov 23 '22 at 23:54
  • @JohnRotenstein I just tried on EC2 instance`psql -d postgres -h localhost -p 5432 -U postgres` with my password - and it works. – Renat Gatin Nov 24 '22 at 00:01
  • @DreamBold the command `sudo -u postgres psql` also works, but it doesn't ask for password, is this normal? – Renat Gatin Nov 24 '22 at 00:02
  • 1
    Your password doesn't seem to work, you can try to run `\password postgres` to change the password for `postgres` user in the psql teminal – DreamBold Nov 24 '22 at 00:03
  • @JohnRotenstein Private IP also worked `psql -d postgres -h XXX.XX.XX.XX -p 5432 -U postgres` – Renat Gatin Nov 24 '22 at 00:05
  • 1
    When connecting to the private ip, doesn't it ask the password? – DreamBold Nov 24 '22 at 00:06
  • @DreamBold, ok I just changed password with your command `\password postgres`. Do I need to restart the server? – Renat Gatin Nov 24 '22 at 00:06
  • 1
    Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249843/discussion-between-dream-bold-and-renat-gatin). – DreamBold Nov 24 '22 at 00:06
  • @DreamBold it does ask for password - when connecting to the private ip – Renat Gatin Nov 24 '22 at 00:07

1 Answers1

2

You followed the correct steps. The issue is that the firewall is blocking the port of Postgres, 5432, so need to add it to the firewall allowed list.

sudo ufw allow 5432/tcp

When you run $ sudo ufw status, you'll see:

Status: active

To Action From
-- ------ ----
OpenSSH ALLOW Anywhere
Nginx Full ALLOW Anywhere
5432/tcp ALLOW Anywhere
OpenSSH (v6) ALLOW Anywhere (v6)
Nginx Full (v6) ALLOW Anywhere (v6)
5432/tcp (v6) ALLOW Anywhere (v6)

And then run sudo firewall-cmd --reload, when you see success, you're done!

You will be able to connect from outside of the instance

DreamBold
  • 2,727
  • 1
  • 9
  • 24