Home / server / Questions / 573304
Accepted
Martin Thoma
Asked: 2014-02-06 01:57:41 +0800 CST

How can I determine PostgreSQL's port without root and make it listen to a port?

  • 772

I currently have a Python script that needs to fetch some data from a PostgreSQL database. It currently works fine, but I have to move the script to another server B. The PostgreSQL database will remain at server A.

So my Python script needs to access the PostgreSQL-database from server B connecting to server A.

I've tried adding host=servera.my.url.de but that gave me

could not connect to server: Connection refused
       Is the server running on host "servera.my.url.de" (141.X.YZ.AB) and
       accepting TCP/IP connections on port 5432?

Is the server there?

ping servera.my.url.de
PING servera.my.url.de (141.X.YZ.AB) 56(84) bytes of data.
64 bytes from XXXXX (141.X.YZ.AB): icmp_req=1 ttl=58 time=3.76 ms
64 bytes from XXXXX (141.X.YZ.AB): icmp_req=2 ttl=58 time=4.07 ms
64 bytes from XXXXX (141.X.YZ.AB): icmp_req=3 ttl=58 time=5.01 ms
^C
--- servera.my.url.de ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 3.768/4.287/5.014/0.529 ms

so the server is running and reachable.

Is my Python script the problem?

psql -h servera.my.url.de -p 5930 -c "select 1"
psql: could not connect to server: Connection refused
    Is the server running on host "servera.my.url.de" (141.X.YZ.AB) and accepting
    TCP/IP connections on port 5930?

and

psql -h servera.my.url.de -p 5932 -U myusername -W -c "select 1" 
Password for user myusername: 
psql: could not connect to server: Connection refused
    Is the server running on host "servera.my.url.de" (141.X.YZ.AB) and accepting
    TCP/IP connections on port 5932?

Portscan

$ nmap -p 5000-6000 -sT servera.my.url.de

Starting Nmap 6.00 ( http://nmap.org ) at 2014-02-05 10:40 CET
Nmap scan report for servera.my.url.de (141.X.YZ.AB)
Host is up (0.054s latency).
rDNS record for 141.X.YZ.AB: XXXXXX
Not shown: 992 closed ports
PORT     STATE    SERVICE
5000/tcp filtered upnp
5025/tcp filtered unknown
5190/tcp filtered aol
5222/tcp filtered xmpp-client
5554/tcp filtered sgi-esphttp
5555/tcp filtered freeciv
5900/tcp filtered vnc
5901/tcp filtered vnc-1
6000/tcp filtered X11

Interesting that freeciv is running ...

When I make the port range higher, I get different results:

$ nmap -p 1000-6000 -sT servera.my.url.de

Starting Nmap 6.00 ( http://nmap.org ) at 2014-02-05 10:40 CET
Nmap scan report for servera.my.url.de (141.X.YZ.AB)
Host is up (0.054s latency).
rDNS record for 141.X.YZ.AB: XXXXXX
Not shown: 4868 closed ports, 130 filtered ports
PORT     STATE SERVICE
3000/tcp open  ppp
4443/tcp open  pharos
4444/tcp open  krb524

Could port 5025 be PostgreSQL?

Other tries

$ ps aux | grep postgres | grep -v 'postgres:'
postgres 28928 0.0 0.0 90124 820 ? s 2013 0:59 /usr/lib/postgresql91/bin/postgres -D /path/pgsql/data
[my command]

$ lsof -p 28928
[does not return anything, but does not quit]

Within PostgreSQL I typed SHOW port, but it returned nothing. Does this mean that PostgreSQL is not listening to any port? How can I make it listen?

Is it some setting in /path/pgsql/data? (I cannot access this at the moment, but I can ask.)

What can I do to figure out on which port postgreSql is listening (or if it is listening at all)? How can I make it listen to a port (or change the port)? I don't have root permissions on this machine, but I can login with SSH on server A.

I did see Determining PostgreSQL's port and tried everything from there:

  • netstat needs root rights
  • lsof does not work (I have no idea whats going wrong on the server ... on my machine, it works fine)
  • pg_lsclusters seems not to be installed
postgresql

3 Answers

  1. Best Answer

    If you can connect to PostgreSQL from the original machine you can see what port it's listening on:

    SHOW port;

    and what address(es):

    SHOW listen_addresses;

    At a guess I'd say listen_addresses will be localhost, so it's not accepting TCP/IP connections from outside network interfaces.

    To confirm what PostgreSQL is listening on use:

    netstat -ltnp

    Another possibility is a network firewall on the database host that's not letting you connect. tcptraceroute to the port from the remote host, see what the result is.

    • 5

  2. First, it's not at all certain that it's freeciv that's listening on 5555. Nmap uses a file called nmap-services that lists the common application for each port (if the port is registered with IANA, it will usually be whatever application was registered for that port). But it's quite possible to use another port for any service, as you've noticed...

    If you run nmap with the option -sV, it will actually talk to the port and try different protocol to see which one matches. That will of course take a bit longer than running it with sT, and may trigger intrusion detection alarms if any are in place.

    In this particular case, I'd lay a bet that it's port 5555 that is being used - because it's unlikely that a database server to be running freeciv, and it's common for humans to use a portnumer like 5555, 3333, 6666 etc. So if I were you, I'd start by doing pgsql -p 5555 and if that doesn't work, do nmap -sV to find out which of the other ports it is.

    (Of course, at its root this isn't a technical problem but a social one. Your DB admin should tell you what port to use; if they don't, your boss should make them do so. But I, too, have been in places where nmap is both faster and more reliable...)

    • 1

  3. lsof only works for processes the user running it has started (root can see all), that is why it does not show the ports for postgresql.

    The default port for postgresql is 5432, but since show port does not show anything in your case I would guess that listening_address is set like this in postgresql.conf:

    listen_address = ''

    or the daemon is running "-c ''"

    You need to have the config changed so that listen_address listens on either '*' or a specific interface on the server.

    • -1