如何连接到Google计算引擎上的远程PostgreSQL服务器? [英] How to connect to remote PostgreSQL server on google compute engine?
问题描述
我已经在google cloud上安装了PostgreSQL，并且希望能够使用pg-admin从我的PC远程访问它.
当我尝试连接到实例时，确实出现以下错误:
无法连接到服务器:
无法连接到服务器:连接被拒绝(0x0000274D/10061)是在主机"[我的VM实例的外部IP]"上运行的服务器，以及在端口5432上接受TCP/IP连接?
我认为这可能是因为Google云防火墙阻止了我(也许是！)，我为我的实例指定了以下防火墙，我使用我的IP地址定义了源过滤器:
我错过了一些东西，因为我仍然无法访问它，有人可以帮我吗，我不知道了，我检查了许多教程，做了很多事情，尽管我应该解决这个问题.有人知道吗?
$ nc -zv 4.3.2.1 5432 哪里
-v产生更多详细的输出.-z仅扫描侦听守护程序，而不发送任何数据到他们.不能与-l一起使用. 可能的结果:
-
与4.3.2.1port [tcp/postgresql]的连接成功！是的
-
nc:连接到4.3.2.1端口8000(tcp)失败:连接被拒绝端口已被防火墙打开，但服务未监听或拒绝连接.
- 命令刚刚挂起
防火墙正在阻止.
0.2 nmap
$ nmap 4.3.2.1在太平洋夏令时间2019-09-09 18:28开始Nmap 7.70(https://nmap.org)适用于1.2.3.4.bc.googleusercontent.com(4.3.2.1)的Nmap扫描报告主机已启动(0.12s延迟).未显示:993个过滤端口港口国服务22/tcp打开ssh80/tcp关闭http443/TCP关闭https3389/tcp关闭了ms-wbt-server4000/tcp封闭式遥控器5432/tcp open postgresql#防火墙已打开，服务已启动并正在侦听8000/tcp已关闭http-alt#防火墙已打开，服务正在启动或正在侦听? 0.3 netstat
$ netstat -tuplen(并非可以识别所有流程，非拥有的流程信息将不会显示，您必须是root用户才能看到全部内容.)活动的Internet连接(仅服务器)Proto Recv-Q Send-Q本地地址外部地址状态用户Inode PID/程序名称tcp 0 0 0.0.0.0:4000 0.0.0.0:* LISTEN 1000 4223185 29432/beam.smptcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1000 4020942 15020/postgrestcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 1000 3246566 20553/postgrestcp6 0 0 :: 1:5432 ::: * LISTEN 1000 4020941 15020/postgrestcp6 0 0 :: 1:5433 ::: *监听1000 3246565 20553/postgresudp 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/chromeudp 0 0 0.0.0.0:68 0.0.0.0:* 0 20911-udp6 0 0 ::: 546 ::: * 0 4621237- 其中
-t |--tcp-u |--udp-p，-程序显示每个套接字所属的程序的PID和名称.-l –听仅显示监听套接字.(默认情况下将省略这些内容.)-e，--extend显示其他信息.最多两次使用此选项细节.--numeric，-n显示数字地址而不是尝试确定符号主机，端口或用户名. 在运行PostgreSQL的实例上发布时，您看不到以下行，这意味着未配置PostgreSQL用于远程连接:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1001 238400 30826/postgrestcp6 0 0 ::: 5432 ::: *听1001 238401 30826/postgres 0.4 lsof
要检查实例服务是否完全在运行.
$ sudo lsof -i -P -n |grep LISTENsystemd-r 457 systemd-resolve 13u IPv4 14870 0t0 TCP 127.0.0.53:53(听)sshd 733根3u IPv4 19233 0t0 TCP *:22(LISTEN)sshd 733根4u IPv6 19244 0t0 TCP *:22(LISTEN)postgres 2733 postgres 3u IPv4 23655 0t0 TCP 127.0.0.1:5432(听)python3 26083 a_user 4u IPv4 392307 0t0 TCP *:8000(听) 1.IP地址
要从一端进行连接，您需要框的公共IP地址以及Google Compute Engine(GCE)实例的公共IP地址.
1.1盒子的公共IP地址(PC，VM，笔记本电脑等)
(来自这篇文章.)
$ dig + short myip.opendns.com @ resolver1.opendns.com4.3.2.1 1.2 GCE实例的IP地址
$ gcloud计算实例列表NAME ZONE MACHINE_TYPE PREEMPTIBLE INTERNAL_IP EXTERNAL_IP状态access-news us-east1-d n1-standard-2 10.142.0.5 34.73.156.19正在运行lynx-dev us-east1-d n1-standard-1 10.142.0.2 35.231.66.229正在运行tr2 us-east1-d n1-standard-1 10.142.0.3 35.196.195.199正在运行 如果您还需要 network-tags :
$ gcloud计算实例列表--format ='table(name，status，tags.list())'名称状态标签访问新闻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指纹= 84JxACwWD7U =，项目= [u'http服务器"，u'https服务器'，u'tr2'] 2.防火墙规则
仅处理以下GCE防火墙规则，但请确保 iptables 不会无意间阻止流量.
另请参见
2.1检查现有
$ gcloud计算防火墙规则列表名称网络方向优先级允许拒绝default-allow-http默认INGRESS 1000 tcp:80 Falsedefault-allow-https default INGRESS 1000 tcp:443否default-allow-icmp默认INGRESS 65534 icmp Falsedefault-allow-internal默认INGRESS 65534 tcp:0-65535，udp:0-65535，icmp Falsedefault-allow-rdp默认INGRESS 65534 tcp:3389 Falsedefault-allow-ssh默认INGRESS 65534 tcp:22 Falsepg-from-tag1-to-tag2默认INGRESS 1000 tcp:5432 False要显示防火墙的所有字段，请以JSON格式显示:--format = json要以表格格式显示所有字段，请参见--help中的示例. 更全面的列表，其中还包括网络标签(来自 gcloud计算防火墙规则列表--help ):
$ gcloud计算防火墙规则列表--format =" table(\名称， \网络， \方向， \优先事项， \sourceRanges.list():label = SRC_RANGES，\destinationRanges.list():label = DEST_RANGES，\allowed [].map().firewall_rule().list():label = ALLOW，\被拒绝[] .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，\禁用)"名称网络方向优先级SRC_RANGES DEST_RANGES允许拒绝SRC_TAGS SRC_SVC_ACCT TARGET_TAGS TARGET_SVC_ACCT已禁用default-allow-http默认INGRESS 1000 0.0.0.0/0 tcp:80 http-server False默认值-allow-https默认值INGRESS 1000 0.0.0.0/0 tcp:443 https-server Falsedefault-allow-icmp默认INGRESS 65534 0.0.0.0/0 icmp Falsedefault-allow-internal默认INGRESS 65534 10.128.0.0/9 tcp:0-65535，udp:0-65535，icmp Falsedefault-allow-rdp默认INGRESS 65534 0.0.0.0/0 tcp:3389 Falsedefault-allow-ssh默认INGRESS 65534 0.0.0.0/0 tcp:22 Falsepg-from-tag1-to-tag2默认INGRESS 1000 4.3.2.1 tcp:5432 tag1 tag2 False 2.2添加新的防火墙规则
要从每个源打开每个实例的默认PostgreSQL端口(5432):
$ gcloud计算防火墙规则创建\postgres-all \-网络默认-优先1000-方向入口-动作允许--rules tcp:5432 \ 要将其限制在您的计算机(源: YOUR_IP )和GCE实例(目标: INSTANCE_IP )之间:
$ gcloud计算防火墙规则创建\从您到实例的Postgres \-网络默认-优先1000-方向入口-动作允许--rules tcp:5432 \--destination-ranges INSTANCES_IP \--source-ranges YOUR_IP \ 人们可以使用源和目标网络标签或服务帐户来代替-source-ranges 和-destination-ranges .请参见>源或目标"防火墙文档中的""部分.
3.配置PostgreSQL接受远程连接
这是对 Neeraj Singh 的 两个文件的位置都可以从PostgreSQL本身查询(技巧来自此Stackoverflow线程): 配置文件附带有用的提示以使此功能正常工作: 要获得快速而又肮脏的解决方案，只需将其更改为 重新启动服务器(请参见此处如何).PostgreSQL重新启动后，它将开始侦听所有IP地址(请参见 要重新启动PostgreSQL: 来自 20.1.pg_hba.conf文件:"客户端身份验证"由配置文件控制，该文件通常名为 这是一个复杂的主题，因此阅读文档至关重要，但这足以在受信任的网络上进行开发: 这时需要重新启动. 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? 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.
postgresql.conf 和 pg_hba.conf 3.1查找上面的配置文件
<代码> $ sudo -u postgres psql -c"SHOW hba_file"-c"SHOW config_file"；
3.2
postgresql.conf
listen_addresses ='localhost'#要侦听的IP地址；#以逗号分隔的地址列表；#默认为'localhost';全部使用'*'#(更改需要重新启动)
listen_addresses ='*' netstat -tuplen ).
$ sudo systemctl restart postgresql @ 11-main# 或者$ pg_ctl重新启动 listen_addresses 文档说，它"指定服务器用来侦听来自客户端应用程序的连接的TCP/IP地址."，仅此而已.它指定接受数据包的套接字，但是如果未验证传入连接(通过 pg_hba.conf 配置)，则无论如何都将拒绝(丢弃?)数据包. 3.3
pg_hba.conf pg_hba.conf ，并存储在数据库集群的数据目录中.(HBA代表基于主机的身份验证.)"
全部托管所有0.0.0.0/0信任托管所有所有::/0信任
Steps
Outline
0.1 nc or netcat
0.2 nmap
0.3 netstat
0.4 lsof
1.1 Public IP address of your box (PC, VM, laptop, etc.)
1.2 GCE instance's IP address
2.1 Check existing
2.2 Add new firewall rules
3.1 Finding the above configuration files
3.2 postgresql.conf
3.3 pg_hba.conf0. Tools to check ports during any step
0.1
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 refusedPort open by firewall, but service either not listening or refusing connection.
- command just hangs
Firewall is blocking.
0.2 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?
0.3 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
0.4 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)
1. IP addresses
To connect from your end, you will need the public IP address of your box, and that of the Google Compute Engine (GCE) instance.
1.1 Public IP address of your box (PC, VM, laptop, etc.)
(From this article.)
$ dig +short myip.opendns.com @resolver1.opendns.com
4.3.2.1
1.2 GCE instance's IP address
$ 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']
2. Firewall rules
Dealing only with GCE firewall rules below, but make sure that iptables doesn't inadvertently blocks traffic.
See also
- "Firewall rules overview" (official docs)
- GCE firewall rules vs.
iptables - Summary of GCE firewall terms
- Behaviour of GCE firewall rules on instances (external vs internal IP addresses)
2.1 Check existing
$ 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
2.2 Add new firewall rules
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.
3. Configure PostgreSQL to accept remote connections
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, andpg_hba.conf
3.1 Finding the configuration files above
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"
3.2 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.
3.3 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.
这篇关于如何连接到Google计算引擎上的远程PostgreSQL服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
