0

My client installed several AWS instances, all in the same region/datacenter.

My task is to install a Redis and a Postgresql instance on one of the machines and make the data store/database accessible from another box. The database server has LAN IP 172.26.14.232 and the server running the db client has IP 172.26.0.215.

Postgres is up and running on the database server, checked this using netstat:

[ubuntu@ip-172-26-14-232 DATABA ~]$ sudo netstat -tulpn | grep postgre
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      4539/postgres

When I try to connect client box, I get a "connection refused", as if there would be a firewall blocking the access, netcat confirms this:

[ubuntu@ip-172-26-0-215 GATE01 ~]$ sudo psql "sslmode=require"  --host 172.26.14.232 --user=postgres  --password
Password for user postgres:
psql: could not connect to server: Connection refused
        Is the server running on host "172.26.14.232" and accepting
        TCP/IP connections on port 5432?
        
[ubuntu@ip-172-26-0-215 GATE01 ~]$ nc -vz 172.26.14.232  5432
nc: connect to 172.26.14.232 port 5432 (tcp) failed: Connection refused

As far as I a can see there is just one port to which I can connect using nc:

[ubuntu@ip-172-26-0-215 GATE01 ~]$ nc -vz 172.26.14.232 22
Connection to 172.26.14.232 22 port [tcp/ssh] succeeded!

My client says he removed all firewall rules, but then I do not understand the result given above.

Somewhere I have read that AWS blocks outbound traffic in such networks, but I cannot check this myself since I have no access to the management console.

Bonus question ;-)

Found the following on this site:

netcat -vzw 15 domain.com 21

If successful, you’ll see:

Connection to domain.com 21 port [tcp/ftp] succeeded!

If the port connection is blocked or rejected, you’ll see:

nc: connect to domain port 21 (tcp) failed: Connection refused

The author possibly makes a difference between "blocked" and "rejected", whereby I assume that, in the latter case, it is meant that there is a fw rule "DENY" installed for this port, but I have no clue what he means by "blocked". Is there a second technical possibility or does he just use "blocked" and "rejected" synonymously?

EDIT:

Astonishingly, it is possible to setup a client-server connection using nc (after shutting down the db):

[ubuntu@ip-172-26-14-232 DATABA ~]$ nc -l 5432

[ubuntu@ip-172-26-0-215 GATE01 ~]$ nc 172.26.14.232 5432
hello

And on 172.26.14.232 we get:

hello
5
  • @Archemar: You are right. I changed it so that the netstat output looks like this now: tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 5959/postgres tcp6 0 0 :::5432 :::* LISTEN 5959/postgres
    – Bloehdian
    Commented Jun 28, 2022 at 15:19
  • Last time I used AWS, trafic was blocked by default, you have to allow explicitly every incoming/out coming connections.
    – Archemar
    Commented Jun 28, 2022 at 15:41
  • @Archemar: Exactly! I read something similar somewhere else. Problem is: It is a while ago that I worked with the management console. Client claims, he disabled the firewall, but I cannot exclude that there are settings which he has not touched. Do you have a hint where he would find the settings?
    – Bloehdian
    Commented Jun 28, 2022 at 17:35
  • Then signup for a free trial and test/look!
    – Archemar
    Commented Jun 28, 2022 at 18:01
  • Did you change pg_hba.conf (which defines where and how users can connect and authenticate) as well as postgresql.conf (the listen_address variable)? You probably want a rule like host all all 172.26.0.215/32 password (or hostssl instead of host, and maybe md5 instead of password) in addition to any existing rules allowing access from local domain sockets and 127.0.0.1/32 and/or ::1/128
    – cas
    Commented Jun 29, 2022 at 1:00

1 Answer 1

1

According to your netstat output, your postgres server is only listening for connections on 127.0.0.1, the loopback interface.

It won't accept connections on any other interface, including the one with the 172.26.14.232 address.

You have three main options:

  1. Reconfigure postgres to accept connections on the 172.26.14.232 address.

    To be safe, you will also need to add firewall rules to make sure that ONLY certain hosts (e.g. 172.26.0.215) are able to connect to postgres.

    I can see that you're using 172.16/12 RFC-1918 private addresses, but I don't know if your subnets are completely isolated from other AWS customers or not. If your subnets are completely isolated, it should be safe to run without firewall rules (although I'd be inclined to set up firewall rules anyway - you can always open up access later if needed, but you can't undo being hacked)

  2. Establish some sort of VPN between your two servers. Configure postgres to accept connections on the VPN interface.

  3. Use ssh port-forwarding. e.g. if you run the following on 172.26.0.215:

    ssh -N -L 5432:127.0.0.1:5432 [email protected]
    

    You will probably want to run this in the background with & if you're using ssh keys to authenticate (or press Ctrl-Z and then run bg to send it to the background after you've authenticated with a password), or in a separate ssh session (from the one you're going to run psql or whatever in), or in a tmux or screen session.

    This forwards connections to 127.0.0.1:5432 on the local machine to 127.0.0.1:5432 on the remote machine. You can connect to it with psql (or any other postgres client, including perl scripts using DBI and DBD::Pg or python with psychopg3).

    For example (on 172.26.0.215):

    psql "sslmode=require"  --host localhost --user=postgres  --password
    

    psql connects to localhost:5432 on the local machine, but ssh forwards that to localhost:5432 on the remote machine.

    You will need to either:

    1. run this whenever you need to connect to postgresql on the remote machine, or
    2. set up a systemd unit file (or whatever, if you're not using systemd) to start up the connection and restart it if it ever dies. For this to be automated, you will need to use ssh keys for authentication rather than passwords - you should be doing this anyway.

    You can do the same for redis (which typically listens on localhost:6379), if needed.


BTW, you don't need sudo to run psql.

4
  • O.k., as mentioned in my comment above, I changed the configuration so that postgres listens on all interfaces. But probably I mislead you. The predominant problem is that I cannot establish a tcp connection from the client host to the database server. See the netcat output in the third code block.
    – Bloehdian
    Commented Jun 28, 2022 at 15:23
  • @Bloehdian then change your question to reflect that you made this change. A comment is not enough. And re-verify that you get the same problem. Listening on 127.0.0.1 triggers the exact same error from remote with netcat.
    – A.B
    Commented Jun 28, 2022 at 17:14
  • @cas: Thx for the hint. I removed the explanation on the local db access. It is more embarrassing than helpful. It is actually not a db problem, but a network problem.
    – Bloehdian
    Commented Jun 28, 2022 at 17:32
  • Try the ssh port-forwarding anyway. Since port 22 is known to work - i.e. ssh is allowed - it will work no matter what other blocks might be in place.
    – cas
    Commented Jun 29, 2022 at 0:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .