Linux postgresql открыть порт

Open Port in Ubuntu

So I’m using AWS using EC2 and I’m trying to open up a port for Postgresql. In AWS I already have it open:

TCP Port (Service) Source Action 0 - 65535 sg-92aadda2 (default) Delete 22 (SSH) 0.0.0.0/0 Delete 80 (HTTP) 0.0.0.0/0 Delete 5432 0.0.0.0/0 Delete 

# netstat -an | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 

 Nmap scan report for localhost (127.0.0.1) Host is up (0.000010s latency). Not shown: 997 closed ports PORT STATE SERVICE 22/tcp open ssh 80/tcp open http 5432/tcp open postgresql 

PORT STATE SERVICE 22/tcp open ssh 80/tcp open http 5432/tcp closed postgresql 

I also looked at my iptables to see if I was missing something, but the iptables look empty (which should mean they aren’t really doing much)

$ iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere REJECT all -- anywhere 127.0.0.0/8 reject-with icmp-port-unreachable ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT tcp -- anywhere anywhere tcp dpt:http ACCEPT tcp -- anywhere anywhere tcp dpt:https ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql ACCEPT icmp -- anywhere anywhere LOG all -- anywhere anywhere limit: avg 5/min burst 5 LOG level debug prefix "iptables denied: " DROP all -- anywhere anywhere Chain FORWARD (policy ACCEPT) target prot opt source destination DROP all -- anywhere anywhere Chain OUTPUT (policy ACCEPT) target prot opt source destination ACCEPT all -- anywhere anywhere 

Am I missing something cause I can’t seem to figure out how to access the ip. Whenever I try I get the following error:

Is the server running on host "xx.xx.xx.xx" and accepting TCP/IP connections on port 5432? 

How do I make it so that I can open up the port so that external servers have access to it? Thanks in advance =) Lemme know if you need any additional data. EDIT: As asked below, I tested telnetting, and I was able to telnet into the localhost, but when attempting from the outside I get:

$ telnet xx.xx.xx.xx 5432 Trying xx.xx.xx.xx. telnet: Unable to connect to remote host: Connection refused 

$ telnet xx.xx.xx.xx 22 Trying xx.xx.xx.xx. Connected to xx.xx.xx.xx. Escape character is '^]'. SSH-2.0-OpenSSH_5.9p1 Debian-5ubuntu1.1 

Источник

Configure PostgreSQL to allow remote connection

By default PostgreSQL is configured to be bound to «localhost».

1 2$ netstat -nlt 3Proto Recv-Q Send-Q Local Address Foreign Address State 4tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 5tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN 6tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 7tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 8tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 9tcp 0 0 127.0.0.1:3737 0.0.0.0:* LISTEN 10tcp6 0 0 . 22 . * LISTEN 11 
 
As we can see above port 5432 is bound to 127.0.0.1 . It means any attempt to connect to the postgresql server from outside the machine will be refused. We can try hitting the port 5432 by using telnet.
 
1 2$ telnet 107.170.11.79 5432 3Trying 107.170.11.79. 4telnet: connect to address 107.170.11.79: Connection refused 5telnet: Unable to connect to remote host 6 
 
In order to fix this issue we need to find postgresql.conf . In different systems it is located at different place. I usually search for it.
 
1 2$ find / -name "postgresql.conf" 3/var/lib/pgsql/9.4/data/postgresql.conf 4 
 
Open postgresql.conf file and replace line
 
1 2listen_addresses = 'localhost' 3 
 
Now restart postgresql server.
 
1 2$ netstat -nlt 3Proto Recv-Q Send-Q Local Address Foreign Address State 4tcp 0 0 127.0.0.1:11211 0.0.0.0:* LISTEN 5tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 6tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 8tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 9tcp 0 0 127.0.0.1:2812 0.0.0.0:* LISTEN 10tcp6 0 0 ::1:11211 . * LISTEN 11tcp6 0 0 . 22 . * LISTEN 12tcp6 0 0 . 5432 . * LISTEN 13tcp6 0 0 ::1:25 . * LISTEN 14 
 
Here we can see that "Local Address" for port 5432 has changed to 0.0.0.0 .
 
Let's try to connect to remote postgresql server using "psql".
 
1 2$ psql -h 107.170.158.89 -U postgres 3psql: could not connect to server: Connection refused 4 Is the server running on host "107.170.158.89" and accepting 5 TCP/IP connections on port 5432? 6 
 
In order to fix it, open pg_hba.conf and add following entry at the very end.
 
1 2host all all 0.0.0.0/0 md5 3host all all ::/0 md5 4 
 
The second entry is for IPv6 network.
 
Do not get confused by "md5" option mentioned above. All it means is that a password needs to be provided. If you want client to allow collection without providing any password then change "md5" to "trust" and that will allow connection unconditionally.
 
Restart postgresql server.
 
1 2$ psql -h 107.170.158.89 -U postgres 3Password for user postgres: 4psql (9.4.1, server 9.4.5) 5Type "help" for help. 6 7postgres=# \l 8 
 
You should be able to see list of databases.
 
Now we are able to connect to postgresql server remotely.
 
Please note that in the real world you should be using extra layer of security by using "iptables".
 
Источник
 
Opening port for Postgresql
 
I've been trying to make Postgres available over the network but so far have been unable to do so. According to netstat, postgres is listening on the right port:
 
#netstat -anltp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 2385/postgres 
 
#nmap -p 5432 marvin Starting Nmap 6.40 ( http://nmap.org ) at 2014-10-31 10:31 CET Nmap scan report for marvin (*.*.*.*) Host is up (0.00048s latency). rDNS record for *.*.*.*: marvin.*.* PORT STATE SERVICE 5432/tcp closed postgresql 
 
My guess is, it has to do something with the fact that I'm using a hostname instead of an IP, but since the IP changes here every so often, I'd rather use the hostname. I've already set listen_addresses = '*', which was the solution to another similar problem I've found here, but to no avail. I've also experimented with different settings in the pg_hba.conf but the port remained closed.
 
host all all .jarvis trust host all all jarvis trust host all all 0.0.0.0/24 trust 
 
Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT tcp -- anywhere anywhere tcp dpt:postgresqlflags: FIN,SYN,RST,ACK/SYN ACCEPT tcp -- anywhere Marvin tcp spts:1024:65535 dpt:postgresql state NEW,ESTABLISHED ACCEPT tcp -- anywhere anywhere tcp dpt:postgresql Chain FORWARD (policy ACCEPT) target prot opt source destination Chain OUTPUT (policy ACCEPT) target prot opt source destination ACCEPT tcp -- Marvin anywhere tcp spt:postgresql dpts:1024:65535 state ESTABLISHED 
 
telnet marvin 5432 Trying *.*.*.*. telnet: Unable to connect to remote host: Connection refusedo connect to the port 
 
telnet marvin 22 Trying *.*.*.*. Connected to marvin.*.*. Escape character is '^]'. SSH-2.0-OpenSSH_6.0p1 Debian-3ubuntu1 
 
Can anyone tell me if there's a setting somewhere I misinterpreted or of its an issue with using hostnames?
 
Источник
 
HowTo Safely Open a PostgreSQL Port for Remote Access?
 
This document describes how to open a PostgreSQL database port for remote access and includes security considerations for both Linux and Windows.
 
Why do you Need to Open a Port
 
You need to open a remote port if you want to access data in the database from a client running on a different computer in the network. The same is true, if you want to use pgAdmin for or similar tools for SQL development.
 
Check for Port IP
 
Please check if PostgreSQL is listening on a public port:
 
# netstat -nlp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 1272/postgres tcp6 0 0 ::1:5432 . * LISTEN 1272/postgres
 
C:\>netstat -a | grep 5432 TCP 127.0.0.1:5432 tarraco:0 LISTENING TCP [::1]:5432 tarraco:0 LISTENING
 
The results above (including "127.0.0.1:5432") shows that PostgreSQL is listening only for connects originating from the local computer, so we will have to edit the "postgresql.conf" configuration file. A result including "0.0.0.0:5432" indicates that PostgreSQL is already listening for remote connections.
 
You can usually ignore the line with "::1", as it refers to the IP v6 protocol, which is rarely used.
 
Edit postgresql.conf
 
This file is usually located in /var/lib/pgsql/data/ on Linux or C:\PostgreSQL\data\ on Windows or similar.
 
In this file we will edit the "listen_address" and "port" parameters, so that they look like below:
 
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '0.0.0.0' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart)
 
Restart PostgreSQL
 
After that we need to restart PG to activate the changes.
 
On recent Linux system you have to enter as user root:
 
systemctl restart postgresql.service
 
On Windows you can use Control Panel -> Administrative Tools -> Services and restart the PostgreSQL service. For ]po[, the PostgreSQL service is called "]po[ PostgreSQL".
 
Repeating the "Check for Port IP" step above, you should now see that the port IP is "0.0.0.0", meaning that it will accept connections from any remote computer.
 
Authentication Configuration using pg_hba.conf
 
pg_hba.conf is located in the same directly as postgresql.conf.
 
Please add the following two lines at the end of the file:
 
host all all 0.0.0.0/0 md5 host all all ::/0 md5
 
This means that remote access is allowed using IP v4 and IP v6 to all databases and all users using the "md5" authentication protocol.
 
Please "restart postgresql" again.
 
Open Linux Firewall Port
 
Does your PostgreSQL database run on a Linux server with the firewall enabled (like the [[https://sourceforge.net/projects/project-open/files/project-open/V5.0/|CentOS 7 ]project-open[ virtual appliance]])?
 
In this case you will have to poke a hole (as root):
 
# firewall-cmd --zone=public --add-port=5432/tcp --permanent success # firewall-cmd --reload success
 
Open Windows Firewall Port
 
Does your PostgreSQL database run on a Windows server with firewall enabled?
 
In this case you can just turn off the firewall for a first test in Control Panel -> Systems and Security -> Windows Firewall -> Turn Windows Firewall on or off.
 
As an alternative you can go to Control Panel -> Systems and Security -> Windows Firewall -> Allow a program or feature through Windows Firewall -> Advanced Settings -> New Rule:
 
 
Rule Type: Port
 
TCP or UDP: TCP
 
Specific local ports: 5432
 
Action: Allow the connection
 
When does this rule apply: Domain, Private and Public (all three checked)
 
Name: "PostgreSQL Incoming"
 
 
Setting PostgreSQL Passwords
 
In order to set the PostgreSQL password for the user "projop" (or whatever user. ) you need to connect locally to the database using an account with administration rights.
 
In the ]project-open[ Centos 7 virtual appliance just enter "psql" in a local terminal window running as user "projop".
 
Then please issue the following SQL command:
 
alter user projop with password 'secret';
 
Connection Check via "psql"
 
Now you should be able to connect to the server using the standard PostgreSQL client "psql", the PG administration tool pgAdmin or any other application with a PostgreSQL interface. We will check with the command line "psql" tool on the remote computer:
 
As a result you should see "Password for user projop". This means that the port is open and active.
 
Then enter the "secret" password set above.
 
An error message like "psql: could not connect to server: No route to host. Is the server running on host "192.168.0.10" and accepting TCP/IP connections on port 5432?" indicates that some of the steps above did not work out.
 
Источник
 
Читайте также:  Linux change console color