Menu
I have installed a PostgreSQL on google cloud and I want to be able to access it remotely from my PC using preferably pg-admin.
I do get the following error when I try to connect to my instance:
Unable to connect to server:
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "[external IP of my VM instance]" and accepting TCP/IP connections on port 5432?
I think this could because google cloud firewall is blocking me (maybe!), I specified the following firewall for my instance, I use my ip address to define Source filters:
am I missing something because I cant still access this, can someone please help me I have no idea anymore, I have check many tutorials and I have done many things that I though should fix this. anyone any idea?
224
PostgreSQL must also be configured to allow remote connections, otherwise the connection request will fail, even if all firewalls rules are correct and PostgreSQL server is listening on the right port.
Couldn't create links, but this is a rather long answer so this may helps.
nc or netcatnmapnetstatlsof
postgresql.confpg_hba.conf
nc or netcat
$ nc -zv 4.3.2.1 5432
Where
-v Produce more verbose output.
-z Only scan for listening daemons, without sending any data to
them. Cannot be used together with -l.
Possible outcomes:
Connection to 4.3.2.1port [tcp/postgresql] succeeded!
Yay.
nc: connect to 4.3.2.1 port 8000 (tcp) failed: Connection refused
Port open by firewall, but service either not listening or refusing connection.
Firewall is blocking.
nmap
$ nmap 4.3.2.1
Starting Nmap 7.70 ( https://nmap.org ) at 2019-09-09 18:28 PDT
Nmap scan report for 1.2.3.4.bc.googleusercontent.com (4.3.2.1)
Host is up (0.12s latency).
Not shown: 993 filtered ports
PORT STATE SERVICE
22/tcp open ssh
80/tcp closed http
443/tcp closed https
3389/tcp closed ms-wbt-server
4000/tcp closed remoteanything
5432/tcp open postgresql # firewall open, service up and listening
8000/tcp closed http-alt # firewall open, is service up or listening?
netstat
$ netstat -tuplen
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name
tcp 0 0 0.0.0.0:4000 0.0.0.0:* LISTEN 1000 4223185 29432/beam.smp
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1000 4020942 15020/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 1000 3246566 20553/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 1000 4020941 15020/postgres
tcp6 0 0 ::1:5433 :::* LISTEN 1000 3246565 20553/postgres
udp 0 0 224.0.0.251:5353 0.0.0.0:* 1000 4624644 6311/chrome --type=
udp 0 0 224.0.0.251:5353 0.0.0.0:* 1000 4624643 6311/chrome --type=
udp 0 0 224.0.0.251:5353 0.0.0.0:* 1000 4625649 6230/chrome
udp 0 0 0.0.0.0:68 0.0.0.0:* 0 20911 -
udp6 0 0 :::546 :::* 0 4621237 -
where
-t | --tcp
-u | --udp
-p, --program
Show the PID and name of the program to which each socket belongs.
-l, --listening
Show only listening sockets. (These are omitted by default.)
-e, --extend
Display additional information. Use this option twice for maximum
detail.
--numeric, -n
Show numerical addresses instead of trying to determine symbolic host,
port or user names.
When issued on the instance where PostgreSQL is running, and you don't see the lines below, it means that PostgreSQL is not configured for remote connections:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1001 238400 30826/postgres
tcp6 0 0 :::5432 :::* LISTEN 1001 238401 30826/postgres
lsof
To check on instance whether service is running at all.
$ sudo lsof -i -P -n | grep LISTEN
systemd-r 457 systemd-resolve 13u IPv4 14870 0t0 TCP 127.0.0.53:53 (LISTEN)
sshd 733 root 3u IPv4 19233 0t0 TCP *:22 (LISTEN)
sshd 733 root 4u IPv6 19244 0t0 TCP *:22 (LISTEN)
postgres 2733 postgres 3u IPv4 23655 0t0 TCP 127.0.0.1:5432 (LISTEN)
python3 26083 a_user 4u IPv4 392307 0t0 TCP *:8000 (LISTEN)
To connect from your end, you will need the public IP address of your box, and that of the Google Compute Engine (GCE) instance.
(From this article.)
$ dig +short myip.opendns.com @resolver1.opendns.com
4.3.2.1
$ gcloud compute instances list
NAME ZONE MACHINE_TYPE PREEMPTIBLE INTERNAL_IP EXTERNAL_IP STATUS
access-news us-east1-d n1-standard-2 10.142.0.5 34.73.156.19 RUNNING
lynx-dev us-east1-d n1-standard-1 10.142.0.2 35.231.66.229 RUNNING
tr2 us-east1-d n1-standard-1 10.142.0.3 35.196.195.199 RUNNING
If you also need the network-tags of the instances:
$ gcloud compute instances list --format='table(name,status,tags.list())'
NAME STATUS TAGS
access-news RUNNING fingerprint=mdTPd8rXoQM=,items=[u'access-news', u'http-server', u'https-server']
lynx-dev RUNNING fingerprint=CpSmrCTD0LE=,items=[u'http-server', u'https-server', u'lynx-dev']
tr2 RUNNING fingerprint=84JxACwWD7U=,items=[u'http-server', u'https-server', u'tr2']
Dealing only with GCE firewall rules below, but make sure that iptables doesn't inadvertently blocks traffic.
See also
iptables$ gcloud compute firewall-rules list
NAME NETWORK DIRECTION PRIORITY ALLOW DENY DISABLED
default-allow-http default INGRESS 1000 tcp:80 False
default-allow-https default INGRESS 1000 tcp:443 False
default-allow-icmp default INGRESS 65534 icmp False
default-allow-internal default INGRESS 65534 tcp:0-65535,udp:0-65535,icmp False
default-allow-rdp default INGRESS 65534 tcp:3389 False
default-allow-ssh default INGRESS 65534 tcp:22 False
pg-from-tag1-to-tag2 default INGRESS 1000 tcp:5432 False
To show all fields of the firewall, please show in JSON format: --format=json
To show all fields in table format, please see the examples in --help.
A more comprehensive list that includes network-tags as well (from gcloud compute firewall-rules list --help):
$ gcloud compute firewall-rules list --format="table( \
name, \
network, \
direction, \
priority, \
sourceRanges.list():label=SRC_RANGES, \
destinationRanges.list():label=DEST_RANGES, \
allowed[].map().firewall_rule().list():label=ALLOW, \
denied[].map().firewall_rule().list():label=DENY, \
sourceTags.list():label=SRC_TAGS, \
sourceServiceAccounts.list():label=SRC_SVC_ACCT, \
targetTags.list():label=TARGET_TAGS, \
targetServiceAccounts.list():label=TARGET_SVC_ACCT, \
disabled \
)"
NAME NETWORK DIRECTION PRIORITY SRC_RANGES DEST_RANGES ALLOW DENY SRC_TAGS SRC_SVC_ACCT TARGET_TAGS TARGET_SVC_ACCT DISABLED
default-allow-http default INGRESS 1000 0.0.0.0/0 tcp:80 http-server False
default-allow-https default INGRESS 1000 0.0.0.0/0 tcp:443 https-server False
default-allow-icmp default INGRESS 65534 0.0.0.0/0 icmp False
default-allow-internal default INGRESS 65534 10.128.0.0/9 tcp:0-65535,udp:0-65535,icmp False
default-allow-rdp default INGRESS 65534 0.0.0.0/0 tcp:3389 False
default-allow-ssh default INGRESS 65534 0.0.0.0/0 tcp:22 False
pg-from-tag1-to-tag2 default INGRESS 1000 4.3.2.1 tcp:5432 tag1 tag2 False
To open the default PostgreSQL port (5432) from every source to every instance:
$ gcloud compute firewall-rules create \
postgres-all \
--network default \
--priority 1000 \
--direction ingress \
--action allow \
--rules tcp:5432 \
To restrict it between your computer (source: YOUR_IP) and the GCE instance (destination: INSTANCE_IP):
$ gcloud compute firewall-rules create \
postgres-from-you-to-instance \
--network default \
--priority 1000 \
--direction ingress \
--action allow \
--rules tcp:5432 \
--destination-ranges INSTANCES_IP \
--source-ranges YOUR_IP \
Instead of --source-ranges and --destination-ranges one could use source and target network tags or service accounts as well. See the "Source or destination" section in the firewall docs.
This is an update to Neeraj Singh's post.
By default PostgreSQL is configured to be bound to “localhost”, therefore the configuration files below will need to be updated:
postgresql.conf, and
pg_hba.conf
The location of both files can be queried from PostgreSQL itself (trick taken from this Stackoverflow thread):
$ sudo -u postgres psql -c "SHOW hba_file" -c "SHOW config_file"
postgresql.conf
The configuration file comes with helpful hints to get this working:
listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
For a quick and dirty solution just change it to
listen_addresses = '*'
Restart the server (see here how). Once PostgreSQL is restarted, it will start listening on all IP addresses (see netstat -tuplen).
To restart PostgreSQL:
$ sudo systemctl restart postgresql@11-main
# or
$ pg_ctl restart
The listen_addresses documentation says that it "Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications.", but that's all. It specifies the sockets the packets are accepted from, but if the incoming connections are not authenticated (configured via pg_hba.conf), then the packets will be rejected (dropped?) regardless.
pg_hba.conf
From 20.1. The pg_hba.conf File: "Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster's data directory. (HBA stands for host-based authentication.)"
This is a complex topic so reading the documentation is crucial, but this will suffice for development on trusted networks:
host all all 0.0.0.0/0 trust
host all all ::/0 trust
Another restart is required at this point.
162
I followed the tutorial on 1 and I could connect remotely from one machine to another. I could reproduce your case by miss configuring the firewall rules. You have set the firewall rule with a tag. That tag needs to be on the VM too. If you create a firewall rule with a tag, that tag needs to be on(in my case the tag name for the tag is postgre-5432) Network Tags:
You can Edit your Compute Engine instance on the VM instance section.
In this case tags are used to be able to set firewall rules in the same network for a specific machine.
Here is the definition of my firewall rule for ingress traffic. This is the tag that my Compute Engine Instance running postgresql server has configured on the Network Tags section:
In source Ip ranges you need to place the PUBLIC IP address of the computer that you want to connect from, to the computer running the postgresql server as this is an ingress firewall rule. And with that ip as source ip you shouldn’t have any tag problem. I encourage you to use tags as everything will be more secure.
You can check the PUBLIC IP of your local connection in this address: http://ip4.me/
Then on the configuration files you mentioned you need to place the Public IP of your connection . This IP needs to be the same that you are using on your ingress firewall rule.
sudo nano /etc/postgresql/9.5/main/pg_hba.conf: host all all PUBLIC_IP/32 md5
And here you need to set * for the listen_addresses as * means any address. Anyway you are controlling the ips able to connect to your instance in pg_hba.conf:
sudo nano /etc/postgresql/9.5/main/postgresql.conf listen_addresses = '*' # what IP address(es) to listen on;
pgAdmin:
Here you can see how to install pgAdmin4 on ubuntu 2 Here you can see how to connect to postgres from the command line. [3
To be able to connect to your postgres remote instance from pgAdmin4 you need to set the host-name/address to your Compute Engine Instance Ip running your postgresql server. If you followed the tutorial here 1 you should have a default database named postgres too.
Hope this helps!
1 https://cloud.google.com/community/tutorials/setting-up-postgres#creating-a-compute-engine-instance
2 https://askubuntu.com/questions/831262/how-to-install-pgadmin-4-in-desktop-mode-on-ubuntu
3 https://www.postgresql.org/message-id/001f01c018c2$830133b0$64898cd5%40northlink.gr
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
