7

Simply to avoid many problems in the first place I do not want my postgres server program to accept/listen to anything from any network (i.e. TCP/IP 4/6) connections.

My setup is a Postgres 9.1 on an Ubuntu 12.04 box and I thought tweeking /etc/postgresql/9.1/main/pg_hba.conf to not include those lines which commented out (see below) would cause postgres to "please not listen on network TCP/IP devices"

local   all             postgres        trust

# 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

Also I know that there is the -i command line to start the postgres server with if we YES want to listen on TCP/IP . I actually seek for the opposite thing a -??? meaning NO please do NOT listen on TCP/IP.

I used a netstat -utap | grep post and it shows that postgres besides my settings in /etc/postgresql/9.1/main/pg_hba.conf is still listening on TCP/IP.

QUESTION

What did I do wrong here? How can I shut off this TCP/IP listening attitude of my Postgres server? Having only unix socket listening I am happy to the max ;)

Thank you

Addition: I also perceive that postgres establishes a UDP connection to this 127.0.0.1:38860, what would this be about?

Improve this question

2 Answers 2

Reset to default
7

Maybe it is not the finest Solution / Answer to my question, but at least it will point anybody (facing the said challenge I had in my original question)

To disable listening on TCP/IP network I used this command line option when starting the server application:

postgres [other arguments] -c listen_addresses=''

Addition: The remaining open udp 127.0.0.1:38860 connection is supposedly linked to the purpose of the the statistics collector subprocess as suggested on postgresql.org

Improve this answer
5
  • 1
    listen_addresses is specified in postgresql.conf along the other server parameters. You may change it here rather than on the command line
    – Daniel Vérité
    Commented Oct 28, 2013 at 10:27
  • @DanielVérité Your suggested is noted and correct. I think the command line form can help you override / overwrite the postgresql.conf setting in case of need (i.e. you cannot change it for file permissions).
    – humanityANDpeace
    Commented Oct 28, 2013 at 10:39
  • @DanielVérité Can you maybe confirm that listen_addressesneeds explicitly and necessarily been set to '' to provoke "No TCP/IP Listening"? I actually thought that having pq_hba.conf without hostentries was already sufficient, which anyway I did not observe (even without host lines in pq_hba.conf it still listened at TCP/IP). Thank you!
    – humanityANDpeace
    Commented Oct 28, 2013 at 10:43
  • 2
    Yes, listen_addresses must be used, and postgres does not guess the interfaces to listen to based on the rules in pg_hba.conf. Besides, to change listen_addresses a full restart of the server is necessary, whereas a simple reload is needed for a change in pg_hba.conf (the latter being harmless for established connections).
    – Daniel Vérité
    Commented Oct 28, 2013 at 11:02
  • @DanielVérité Thank you for your help and confirmation. I was misled by the address column in pg_hba.conf thinking this was powerful enough to indicate to the server where to listen and where not. Thanks to you I know am aware that setting listen_addresses='' empty is the right way to go :)
    – humanityANDpeace
    Commented Oct 28, 2013 at 11:06
0

Simple answer;

Change the listen_addresses configuration setting Here is an example;

ALTER SYSTEM SET listen_addresses TO '';

Then restart the Postgresql service

Documentation says

If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it.

Improve this answer
6
  • In modern postgres'es it is listen_addresses instead of listen_adress
    – Jürgen Weigert
    Commented Mar 10, 2022 at 17:29
  • 1
    @JürgenWeigert I updated the answer
    – Sahap Asci
    Commented Mar 10, 2022 at 22:51
  • ALTER: command not found am I using the right program postgres??
    – Soerendip
    Commented Nov 2, 2022 at 22:39
  • @Soren AFAIK it's available after version 9.4. It's an SQL statement. User must be superuser.
    – Sahap Asci
    Commented Nov 3, 2022 at 10:06
  • It has to be used with psql for example: sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses TO '*'"
    – Soerendip
    Commented Nov 3, 2022 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.