This document describes how to open a PostgreSQL database port for remote access and includes security considerations for both Linux and Windows.
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.
Please check if PostgreSQL is listening on a public port:
Linux:
# 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
Windows:
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.
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)
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.
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.
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
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:
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';
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:
psql -h <server_ip> -U projop
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.
Calle Aprestadora 19, 12o-2a
08902 Hospitalet de Llobregat (Barcelona)
Spain
Tel Europe: +34 609 953 751
Tel US: +1 415 200 2465
Mail: info@project-open.com