Postgres seems to ignore listen_addresses setting

Question

I'm trying to DISABLE remote client access on my Postgres 9.5.7 database (running Ubuntu 16.04).

I made what I thought are the correct changes (see below) to no avail. I can think of two reasons why my changes wouldn't work:

• I'm editing the wrong file
• I'm not restarting the service properly

I thought I ruled these out, but remote clients can still talk. So what am I missing?

Actually, what I'm trying to do is revert my changes that enabled remote client access on so I can capture those changes and re-apply them with our salt stack. Before I start running deployment tests via salt I want to know that remote access is disabled so that I can be confident that my salt stack is making the changes.

Some data

I'm starting postgres using the same config file I'm editing (ps output reformatted for readability)

postgres@testweb:~$ ps -eaf | grep bin/post
postgres [snip] /usr/lib/postgresql/9.5/bin/postgres 
                -D /var/lib/postgresql/9.5/main 
                -c config_file=/etc/postgresql/9.5/main/postgresql.conf

postgres is configured to listen on the localhost port only (this was '*' earlier)

postgres@testweb:~$ grep listen_addresses /etc/postgresql/9.5/main/postgresql.conf
listen_addresses = 'localhost'      # what IP address(es) to listen on;

Host-based auth is set to localhost. This shouldn't matter if I'm DISABLING access, correct? My understanding is that if listen_addresses='localhost' then remote client access will not work.

postgres@testweb:~$ grep ^host /etc/postgresql/9.5/main/pg_hba.conf
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

I'm editing the correct config files, yet listen_addresses claims *

postgres@testweb:~$ sql
psql (9.5.7)
Type "help" for help.

postgres=# show config_file;
           config_file
------------------------------------------
/etc/postgresql/9.5/main/postgresql.conf
(1 row)

postgres=# show hba_file;
               hba_file
--------------------------------------
/etc/postgresql/9.5/main/pg_hba.conf
(1 row)

postgres=# show listen_addresses;
 listen_addresses
------------------
*

Proof at the OS level that we're still listening on ALL interfaces (that is, that the show list_addresses; command is in effect.

postgres@testweb:~$ netstat -nlt | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp6       0      0 :::5432                 :::*                    LISTEN

I have a custom conf file in conf.d, but I'm unclear if it's being read, because I didn't touch this in order to allow remote client access. Regardless, it is set to what I want for now

postgres@testweb:~$ cat /etc/postgresql/9.5/main/conf.d/custom.conf
listen_addresses = 'localhost'
shared_buffers = 4011MB
work_mem = 320MB
maintenance_work_mem = 1018MB
effective_cache_size = 8022MB
shared_preload_libraries = 'pg_stat_statements'

No stray config files appear on the server

postgres@testweb:~$ locate postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf.bak
/usr/lib/tmpfiles.d/postgresql.conf
/usr/share/postgresql/9.5/postgresql.conf.sample

postgres@testweb:~$ locate pg_hba.conf
/etc/postgresql/9.5/main/pg_hba.conf
/usr/share/postgresql/9.5/pg_hba.conf.sample

I restart the postgres instance with service postgresql restart (I've even tried restarting the server (ugh) just to make sure. Still no luck. I've verified that if I execute service postgresql stop that my remote clients fail.

Obviously, in futzing around to ENABLE remote client access I must have tweaked something else; but for the life of me I cannot think of what it was. I have undone the changes I remember making (in the two conf files). I must have missed something ... but what?

EDIT(s)

per a comment, if I log in specifying the host, I see the same result (AFAIK, I never set the postgres user password, so I'm accessing as root).

root@testweb:~# sudo -u postgres -h localhost psql
psql (9.5.7)
Type "help" for help.

postgres=# show listen_addresses;
 listen_addresses
------------------
 *
(1 row)

postgres=# show config_file;
               config_file
------------------------------------------
 /etc/postgresql/9.5/main/postgresql.conf
(1 row)

Per another comment, here is the output after executing service postgresql restart ... seems clean (unsure about the last line ... research (thanks Daniel) inidicates that it's "inconsequential")

2017-07-18 17:43:16.440 MDT [4911] LOG:  database system was shut down at 2017-07-18 17:43:15 MDT
2017-07-18 17:43:16.444 MDT [4911] LOG:  MultiXact member wraparound protections are now enabled
2017-07-18 17:43:16.447 MDT [4910] LOG:  database system is ready to accept connections
2017-07-18 17:43:16.447 MDT [4915] LOG:  autovacuum launcher started
2017-07-18 17:43:16.848 MDT [4917] [unknown]@[unknown] LOG:  incomplete startup packet

Answer 1

Look at the postgresql.auto.conf file in your $PGDATA.

If someone uses the ALTER SYSTEM command to change the settings, you will find the value there.

postgresql.auto.conf is read after postgresql.confand each setting put in this file overwrite those in postgresql.conf...

It's worth giving a look...