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.
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?
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  LOG: database system was shut down at 2017-07-18 17:43:15 MDT 2017-07-18 17:43:16.444 MDT  LOG: MultiXact member wraparound protections are now enabled 2017-07-18 17:43:16.447 MDT  LOG: database system is ready to accept connections 2017-07-18 17:43:16.447 MDT  LOG: autovacuum launcher started 2017-07-18 17:43:16.848 MDT  [unknown]@[unknown] LOG: incomplete startup packet