Unable to get PostgreSQL 9.4 to listen on port 5432
I’m using a Linux VM (Ubuntu 15.10) to spin up a Postgres Database, and as far as I can tell, everything should be configured right. My firewall is disabled:
user@UBUNTUMACHINE:~$ sudo ufw status numbered Status: inactive user@UBUNTUMACHINE:~$ netstat -an | grep "LISTEN " tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN tcp6 0 0 . 22 . * LISTEN user@UBUNTUMACHINE:~$ sudo ufw status verbose Status: active Logging: on (low) Default: deny (incoming), allow (outgoing), disabled (routed) New profiles: skip To Action From -- ------ ---- 22 ALLOW IN Anywhere 22/tcp ALLOW IN Anywhere 5432/tcp ALLOW IN Anywhere 5432 ALLOW IN Anywhere 22 (v6) ALLOW IN Anywhere (v6) 22/tcp (v6) ALLOW IN Anywhere (v6) 5432/tcp (v6) ALLOW IN Anywhere (v6) 5432 (v6) ALLOW IN Anywhere (v6) But I get the same results as above for netstat. As far as I can tell from researching the issue, I have the correct values in my postgresql.conf file:
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # 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) # Database administrative login by Unix domain socket local all postgres ident sameuser # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 host all all 10.0.0.0/255 trust host all all 10.11.0.0/255 trust host all all 0.0.0.0/0 trust user@UBUNTUMACHINE:~$ sudo service postgresql status ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2017-03-08 11:09:57 CST; 57min ago Process: 787 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 787 (code=exited, status=0/SUCCESS) CGroup: /system.slice/postgresql.service Mar 08 11:09:57 UBUNTUMACHINEsystemd[1]: Starting PostgreSQL RDBMS. Mar 08 11:09:57 UBUNTUMACHINEsystemd[1]: Started PostgreSQL RDBMS. Mar 08 11:32:21 UBUNTUMACHINEsystemd[1]: Started PostgreSQL RDBMS. Mar 08 11:32:26 UBUNTUMACHINEsystemd[1]: Started PostgreSQL RDBMS. 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 Opening port 5432 on ubuntu
I am trying to open port 5432 on ubuntu using ufw using sudo ufw allow 5432/tcp I then use nmap to see if port 5432 was opened and i get this:
root@domain:~# sudo nmap -sS -O 127.0.0.1 Starting Nmap 5.21 ( http://nmap.org ) at 2011-12-01 14:28 MSK Nmap scan report for localhost.localdomain (127.0.0.1) Host is up (0.000084s latency). Not shown: 994 closed ports PORT STATE SERVICE 22/tcp open ssh 25/tcp open smtp 80/tcp open http 587/tcp open submission 5432/tcp open postgresql 10000/tcp open snet-sensor-mgmt Device type: general purpose Running: Linux 2.6.X OS details: Linux 2.6.15 - 2.6.30 Network Distance: 0 hops OS detection performed. Please report any incorrect results at http://nmap.org/submit/ . Nmap done: 1 IP address (1 host up) scanned in 2.28 seconds root@domain:~# sudo nmap -sS -O 1xx.xxx.xxx.xxx Starting Nmap 5.21 ( http://nmap.org ) at 2011-12-01 14:30 MSK Nmap scan report for domain.info (1xx.xxx.xxx.xxx) Host is up (0.000086s latency). Not shown: 997 closed ports PORT STATE SERVICE 22/tcp open ssh 80/tcp open http 10000/tcp open snet-sensor-mgmt Device type: general purpose Running: Linux 2.6.X OS details: Linux 2.6.15 - 2.6.30 Network Distance: 0 hops OS detection performed. Please report any incorrect results at http://nmap.org/submit/ . Nmap done: 1 IP address (1 host up) scanned in 2.65 seconds # DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database # super user can access the database using some other method. # Noninteractive # access to all databases is required during automatic maintenance # (custom daily cronjobs, replication, and similar tasks). # # Database administrative login by UNIX sockets local all postgres ident # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5 How To Configure PostgreSQL to Allow Remote Connections
An open-source, object-based relational database PostgreSQL, provides the user with the implementation of SQL and is commonly hosted on Linux. With PostgreSQL users can expand the system by defining self-data types, functions, and operators.
PostgreSQL is used by many large companies to save and store their data for various applications and it supports various programming interfaces as well as videos, texts, and images. In this article we’ll first go through the installation of PostgreSQL on ubuntu 20.04 then we’ll configure it to allow remote connection
Prerequsities
This article assumes that you already have running a PostgreSQL server on your system. If not, use one of the below links to install the PostgreSQL database server on your system.
Find Configuration File
In order to install PostgreSQL on our system we need to update our repository and for that execute the below command:
sudo -u postgres psql -c "SHOW config_file;" Output:config_file ----------------------------------------- /etc/postgresql/13/main/postgresql.conf (1 row)
You need to change the listening address in the postgresql.conf configuration file showing in the command output. Also, you need to edit “pg_hba.conf” in the same directory to allow remote access.
Configure PostgreSQL to Allow Remote Connections
In order to allow all the IP addresses to connect to the PostgreSQL server, we need to configure the file and make some changes, for that you have located the configuration file in the previous step.
Now we need to open the file and make some changes in order to allow a remote connection. To open the file you’ve to use the keyword “nano” or you can run the command in the terminal that is provided below:
sudo nano /etc/postgresql/13/main/postgresql.conf This command will open this file and in it, you need to search “listen_addresses” and add the following line.
#listen_addresses = 'localhost' listen_addresses = '*'
All you’ve to do is change the listening address from localhost to “*” or add a new line as shown above. This will allow every IP address to be connected to the database server, or if you want to specify some particular ips you can type them with spaces between each IP address.
In order to allow the users that we want to be connected to the database then we need to make changes in the “pg_hba.conf” file. This file will be available under the same directory as above. Now open the file using the command provided below:
sudo nano /etc/postgresql/13/main/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5 host all all :/0 md5
Save the configuration file and close it.
sudo systemctl restart postgresql Now simply open the port “5432” in the firewall and you’re all set to see all the databases and you can bond from whichever ip address to the server of PostgreSQL:
That’s it. Your PostgreSQL database server is accessible from remote hosts.
Conclusion
PostgreSQL database is default set to bond with localhost which restricts the other IP address and host to connect or have the access to the PostgreSQL server. In this article, we guided you through the configuration of PostgreSQL to allow remote connection so that other IPs can bond to the server. In this way, other hosts can easily see the list of databases and connect to the PostgreSQL server remotely.